Move Row Automation lagging terribly

Jeff Reisman

I'm leapfrogging rows from a form collection sheet to an intermediate sheet, and that move row automation works fine. From the intermediate sheet, I'm moving rows to the main data sheet. This is where things are lagging, like stopping for multiple hours for no reason. The rows that are moving have only 5 columns. There's plenty of room in the main sheet. There are only 3 formula fields, and they're not too complex.

The automation trigger is when rows are added, when any field changes, run when triggered, move the row. It hung for about 2 hours. I moved some rows manually, and then all of a sudden it moved the rest itself. Now it's been sitting 45 minutes, over 100 rows have built up, because it's not moving any right now.

Any ideas on how to keep things moving?

Jeff Reisman

Now I'm wondering if the Total Serials count from the Model Count metrics sheet is slowing things down - what if I move the rows from Final Landing into another sheet, since Move Row only moves the formula results, not the formulas, and have Model Count look there to count the Total Serials instead?

WELL WOULD YOU LOOK AT THAT!?!?!

As soon as as I edited the references in the Model Count sheet to point at the new sheet "Final Final", the rows started moving!

Why did it take me this long to consider that an external row count would slow things down so much across multiple sheets???

Note: Instead of Move Row I did Copy Row for the automation from Landing Final to Final Final.

@Paul Newcome @Kelly Moore

Thank you both for your input! I'm always impressed with your Smartsheet insight!

Paul Newcome

Do you have all of the sheets open at the same time (to try to track the movement)? I have found that tends to slow things down even with refreshing your browser pages and whatnot.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/334697#Comment_334697

I did have them open, but only because the warehouse team was asking why the rows weren't showing up in the final sheet. They're closed now, I'll give it a few minutes to see if they start moving!

Jeff Reisman

@Paul Newcome After another 20 minutes, everything moved at once, all 142 rows. 🤷‍♂️

It makes no sense. A move row automation that is supposed to fire off when rows are added should move the rows within a reasonable amount of time, no more than 2-3 minutes.

And wouldn't you know it, but there are 16 rows sitting in the collection sheet that have been there for 30 minutes now, when they're supposed to move immediately too.

Edit: the 16 rows that are sitting there were all added nearly 2 hours ago!

Jeff Reisman

I moved the 16 rows manually to the intermediate sheet, where they sat for 18 minutes before moving to the final sheet.

Kelly Moore

Hey Jeff

On the second automation, would it help if you changed 'Any field changes' to the 'Created' field changes? Maybe a more limited filter would help speed it up?

Like y'all, I'm scratching my head on this one.

Kelly

Jeff Reisman

https://community.smartsheet.com/discussion/comment/334738#Comment_334738

That's a good idea 👍️ so good in fact that I already tried it 😭

We aren't using the Primary Column for anything, so I changed the intake form to include this field with a default value of 1, but hide it. Then I set my automations to trigger when rows are added and Primary Column = 1, run when triggered. No luck!

Kelly Moore

Bummer it didn't work.

The calculations that are being done - as a test is there any way you can (1) either systematically remove each calculation from the sheet so you can see if one/more of them is the culprit and/or (2) move the calculations completely to the final destination? Or even the intake sheet?

Kelly

Paul Newcome

I would definitely suggest reaching out to support. Automations are not supposed to take more than 15 minutes to trigger. Granted that could be a total of 30ish minutes if moving from one sheet to another and then to another, but that still isn't hours.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/334760#Comment_334760

That's the whole idea behind the leapfrogging. (See that link for a description of the process and the trouble I encountered in May.) Even if the final destination sheet is large, or has many formulas, the form entries to the collection sheet should be instant, and then the move rows from the intermediate sheet to the final should at least happen continuously, even if slowly. Even if it takes a minute to move each row, at least they're moving. These were just sitting there for hours, then suddenly moving all at once.

The destination sheet is where a metrics sheet is getting its data; if the rows take hours to arrive, the supervisors who are looking for count discrepancies are left waiting around instead of resolving any bad counts. I need the formulas in that final sheet or else there's no point.

Paul Newcome

You don't have anything with the automations running off of the TODAY function do you (even if indirectly)?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/334776#Comment_334776

No, there are no dates involved and not TODAY function in any formula.

My fields in the collection sheet and the intermediate sheet are Primary Column (defaults to 1), Bin Location (4 char alphanumeric), Product Code (8-15 char alphanumeric), Serial # (8-17 char alphanumeric), Warehouse (7 char alpha).

My Final landing sheet has the 5 columns above, plus:

Duplicate Check, making sure they haven't entered the same combo of bin, product, and serial #: =COUNTIFS([Bin Location]:[Bin Location], [Bin Location]@row, [Product Code]:[Product Code], @cell = [Product Code]@row, [Serial #]:[Serial #], @cell = [Serial #]@row)

SKU, some of our boxes from different factories have bar codes that don't contain our customer facing SKU numbers, so this series of IFs and INDEX/MATCH resolves the scanned product code into a recognizable SKU: =IFERROR(IF(ISBLANK([Product Code]@row), "", IF(LEN([Product Code]@row) = 6, INDEX({Model code and EANMat}, MATCH([Product Code]@row, {Model code and EAN data model}, 0)), IF(LEFT([Product Code]@row, 1) = "0", INDEX({Model code and EANMat}, MATCH(MID([Product Code]@row, 3, 15), {Model code and EAN data Ean}, 0)), IF(OR(MID([Product Code]@row, 4, 1) = "-", MID([Product Code]@row, 5, 1) = "-"), [Product Code]@row, INDEX({Model code and EANMat}, MATCH([Product Code]@row, {Model code and EAN data Ean}, 0)))))), [Product Code]@row)

ME Indoor Product Code, Some of our indoor units do not list any product code at all, the product code has to be extracted from part of the serial number bar code:=IF(LEFT([Product Code]@row, 1) = "L", MID([Product Code]@row, 2, 6))

Lastly, on the metrics sheet called Model Count:

Supervisors scan the Bin Location and Product Code in to a row, then hand count the boxes for that Product in that Bin Location and enter the count. Then a column called Total Serials counts the rows in the Final Landing sheet that have the same Bin Location and Product Code.

=COUNTIFS({Serial Count bin}, [Bin Location]@row, {Serial Collection product code}, @cell = [Product Code]@row)

Another column compares the hand count to the count of collected serial numbers to generate a RYG ball - Green for matched counts, Yellow for more serials than hand count, Red for more hand count than serials.

Now I'm wondering if the Total Serials count from the Model Count metrics sheet is slowing things down - what if I move the rows from Final Landing into another sheet, since Move Row only moves the formula results, not the formulas, and have Model Count look there to count the Total Serials instead?

Jeff Reisman

Now I'm wondering if the Total Serials count from the Model Count metrics sheet is slowing things down - what if I move the rows from Final Landing into another sheet, since Move Row only moves the formula results, not the formulas, and have Model Count look there to count the Total Serials instead?

WELL WOULD YOU LOOK AT THAT!?!?!

As soon as as I edited the references in the Model Count sheet to point at the new sheet "Final Final", the rows started moving!

Why did it take me this long to consider that an external row count would slow things down so much across multiple sheets???

Note: Instead of Move Row I did Copy Row for the automation from Landing Final to Final Final.

@Paul Newcome @Kelly Moore

Thank you both for your input! I'm always impressed with your Smartsheet insight!

Paul Newcome

That was going to be my next guess was that one of the sheets in the process was bogged down. Glad you got it sorted!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/334801#Comment_334801

I hadn't even considered that a sheet essentially "downstream" from the move row process would slow it down so much. Amazing. I wish there was a way to access some kind of performance monitor on our sheets, to see what's taking up processing resources.

Kelly Moore

Yay - yep, that was why I was wondering about the different calculations - wondering which one was linked to something else. Phew, also glad the fix was relatively painless. 👍️