Automation Rule --> Move Rows to another Sheet

Bruce Case

Hi Team

I am trying to create a load process whereby when new rows are added to my "master" sheet, I want the Automation to move any row previously in the "master" sheet over to my archive sheet. In my "master" sheet, I have a date field, and I've tried a duration field. When my new rows are added, I still cannot get the Automation to move all rows that has a date prior to today. It does appear the Automation is looking at ALL the rows in my sheet, but rather just the new rows added. Thoughts?

Neil Watson

Hi @Bruce Case, can you add a helper column that uses the max formula and checkbox to identify the most recent entry? And then in your automation use a condition to exclude this row from being moved.

Bruce Case

I've tried multiple helper type columns --> such as a date and duration.

But the problem I have is with the automation rule. I'm simply asking the rule to trigger when new rows are added --> then I want it to review all 500'ish rows in the sheet and move out anything that was there prior to the most recent row. In the example above, since I tested it this morning, that way they all have today's date --> but in theory, a new file would be loaded in a couple days, so the date above would still be 28Jan and the Duration would have increased --> so I'm not sure why the rule below is not working.

Neil Watson

@Bruce Case I suspect that the issue is with the system-created date column which always uses UTC time (even though the display is as per your time zone settings. I would try and adjust your helper column by either adding or subtracting the time difference. For example, below the created date is 29 Jan 2023, but when referenced it displays as 28 Jan 2023 since I am 10.5 hrs ahead of UTC. If I add this difference then the date shows properly.

Bruce Case

Uggg. I am so close, I can taste it. I have added a few more columns, and since I am in Arizona, that is -7 hours from UTC. I then have a Column for Loaded Today with a checkbox. This logic is working just fine. PS. Just for reference, it was exactly 656p on 29Jan (my time) when I loaded these.

My problem is still with the Automation. The weird thing is, when the new rows come in, the one from previous day(s) should move. This does not seem to trigger --> however, if I force this rule to "Run Now", then the unchecked ones move. In my opinion, it is only considering the new rows added, and not the entire sheet (including what was already there).

Neil - I do appreciate your time and thoughts

Neil Watson

@Bruce Case so close, I agree! I think however that you are right, the automation is only considering the new rows added and not the existing rows. What if you change the trigger to include when rows are added or changed?

Kelly Moore

Hey @Bruce Case and @Neil Watson

Sorry to enter the conversation late but I was just helping someone within my company with a similar problem. I'm not sure if it is the same..

In my colleagues case, it was the Date portion of the UTC storage that messed his automation up. When rows were created during the span when UTC has crossed midnight but his timezone had not, Created date was moving ahead a day faster than his time zone, so the date wasn't in the past (yet) in his local time. Of course the rows would eventually catch up to the correct date but not on the time sequence he was expecting. I suggested he create an additional date column and use Record Date automation to capture the local date of the row creation.

Kelly

Bruce Case

Hi Kelly --> thanks for the information. Yes, we did cover that, but I do thank you for that. Neil/Kelly --> by switching the trigger to Added or Modified rows does seem to fix the issue, however, I'll now need to make sure nobody changes anything in that Sheet or it will trigger again. I've reported this into the helpdesk as well because I'm not sure why it is only looking at the new rows when I say Rows Added, instead of the entire sheet. Perhaps just my way of thinking.

PS. Nice to talk to both of you 5-star users. :-) I seem to be stuck at 4 stars.

Neil Watson

@Bruce Case a few thoughts to try.

When you enter the data into the sheet for the first time, all the "Loaded Today" boxes will be checked, because these records are all the latest data

The following day, the new data will become checked, and yesterday's data will become unchecked

Could you use that event to trigger the automation?

Kelly Moore

Hey Bruce

Is there a way to flip your formula so it does the opposite of what you have set up - it checks the rows to move, not to exclude. Then, you should be able to trigger off of checkboxes being changed?

Alternatively, I have used a workflow to insert a value into a helper cell when something I am interested in changes, and have checkboxes cells, by formula looking for the value in that cell plus other criteria, check the boxes. I then later run a different automation on a schedule to clear that value, which then causes the formula to clear all the checkboxes.

Would either of these ideas work?

Kelly

Bruce Case

All good things to think about and I appreciate it.

I have a call with the requester tomorrow to demo what I have. If nobody is going to disturb the data in that target Sheet, then I'll be OK with include Add/Modify as my trigger. Otherwise, I might circle back and try a couple more of your thoughts. I don't everything we've talked about, but not all in one process. 😎 Cheers for now.