how to set up automation logic for move row based on field value

Carroll Wall

Hello!

I have a sheet populated by a form evaluating items identified by #'s. . There are several hundred items to be evaluated and users may fill out this form several times a year. My goal is to have the sheet showing only the most recent evaluation for each item.

I would like to create an automation so that each subsequent time they fill out the form for item #XXXX, the row containing the previous form entry for item #XXXX is moved to another sheet.

I am not sure how to set up the trigger block logic for this.

I am starting with

When Rows Are Added and Run Work Flow When Triggered

But I need to add a condition where "when the item # matches an existing one, move the oldest entry to new sheet"

but there is no option for "matches." The closest I can find is "is equal to" but I don't have a way to specify the number.

Also the "Move Row" action does not allow me to specify the older row should be moved.

Any suggestions would be very appreciated.

Thank you!

Carroll

Carroll Wall

@Leibel Shuchat 

Thanks for your reply.

I am trying to create the formula for the 3rd step in my sheet but am getting an unparseable error.

I substituted my actual column name Contract Number for Product# in your example. Smartsheet added the brackets when I clicked the column.

YOUR formula

=IF(AND(COUNTIF(PRODUCT#:PRODUCT#,PRODUCT#@row)>1,MATCH(PRODUCT#@row, PRODUCT#:PRODUCT#,0)=ROW#@row),1,0)

MINE

=IF(AND(COUNTIF([Contract Number]:[Contract Number],[Contract Number]@row)>1,MATCH([Contract Number]@row, [Contract Number]:[Contract Number],0)=ROW#@row),1,0)

Did I miss something?

Thanks,

C

Leibel S

Create the following columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
3. "IS_OLD" CheckBox Column Formula: =IF(AND(COUNTIF(PRODUCT#:PRODUCT#,PRODUCT#@row)>1,MATCH(PRODUCT#@row, PRODUCT#:PRODUCT#,0)=ROW#@row),1,0)

Run your automation on "IS_OLD" becomes checked.

Kelly Moore

Hi Carroll,

Here's one possible solution.

You'll need a helper column formatted as a Checkbox column. This is where the formula will go. You'll also need the system-generated Created (Date) column.

This formula looks for duplicates of Items, excluding blanks (Countifs >1) and if there are duplicates, it looks if the Created Date of the current row equals the Minimum Created Date of the right Item numbers (gathered by the Collect function). The checkbox gets checked (the last 1 in the formula) when the Created date is that minimum - which means its the oldest date of that series.

You would set your automation to trigger with rows added or changed, and when the checkbox column is checked, plus any other conditions (if any) that are relevant to your process.

Don't forget to change the column names in the formula to match yours.

=IF(COUNTIFS(Item:Item, Item@row, Item:Item, @cell <> "") > 1, IF(Created@row = MIN(COLLECT(Created:Created, Item:Item, Item@row = @cell)), 1))

I hope this helps.

Carroll Wall

@Leibel Shuchat 

Thanks for your reply.

I am trying to create the formula for the 3rd step in my sheet but am getting an unparseable error.

I substituted my actual column name Contract Number for Product# in your example. Smartsheet added the brackets when I clicked the column.

YOUR formula

=IF(AND(COUNTIF(PRODUCT#:PRODUCT#,PRODUCT#@row)>1,MATCH(PRODUCT#@row, PRODUCT#:PRODUCT#,0)=ROW#@row),1,0)

MINE

=IF(AND(COUNTIF([Contract Number]:[Contract Number],[Contract Number]@row)>1,MATCH([Contract Number]@row, [Contract Number]:[Contract Number],0)=ROW#@row),1,0)

Did I miss something?

Thanks,

C

Leibel S

Try the below. You just need to add brackets around the ROW# field.

=IF(AND(COUNTIF([Contract Number]:[Contract Number], [Contract Number]@row) > 1, MATCH([Contract Number]@row, [Contract Number]:[Contract Number], 0) = [ROW#]@row), 1, 0)

Chris Phillippe

Hello, I am trying to do a similar move. It is based on a tracking number we use. We use that tracking number (RL Number) multiple times for actions. I created the checkbox column and inserted this formula: =IF(COUNTIFS(Item:Item, Item@row, Item:Item, @cell <> "") > 1, IF(Created@row = MIN(COLLECT(Created:Created, Item:Item, Item@row = @cell)), 1))

The lines that have that duplicate have a #INVALID OPERATION instead of being checked.

I'm not sure what I am missing.