Row is Repopulating on Sheet After it has Archived (Needs to Stay Archived)

A.C.

I have multiple sheets that are linked to each other via formulas and automations that trigger every hour from 5am - 2 pm. I believe my issue is stemming from when a certain automation triggers, but I can not seem to figure it out. For the sake of simplicity we will call the sheets A, B, & C, "gateway1" and "gateway2" sheets.

5am- every day rows/requests move into Sheet A via workflow.

Below are the steps that copy a row/request from Sheet A, to Sheet B, then to Sheet C via workflows:

-6 am every day: There is a "gateway1" sheet that links Sheet A to Sheet B. This "gateway" sheet says if a row is on Sheet A and not on Sheet B (as indicated by an unchecked box), then copy the row to Sheet B. Once the row is copied Sheet B, the box becomes checked.

-7 am every day: There is a "gateway2" sheet that links Sheet A to Sheet C. This "gateway" sheet says if a row is on Sheet A and not on Sheet C (as indicated by an unchecked box), then copy the row to Sheet C. Once the row is copied to Sheet C, the box becomes checked.

Below are the steps to archive rows on Sheet A, B, and C along with lock/unlock to refresh the sheets via workflows:

-8 am every day: Sheet C rows archive on [archive date] ("today")

-9 am every day: Sheet B rows archives on [archive date] ("today")

-10 am every day: Sheet A rows archive on [archive date] ("today")

-11 am every day: "Gateway2" sheet that links Sheet A to Sheet C locks (to refresh page).

-12 pm every day: "Gateway1" sheet that links Sheet A to Sheet B locks (to refresh page).

-1 pm every day: "Gateway2" sheet that links Sheet A to Sheet C unlocks.

-2 pm every day: "Gateway1" sheet that links Sheet A to Sheet B unlocks.

**Undesired Result:

-At 7am on the day AFTER THE ARCHIVE DATE, the row that was once on Sheet C (but archived) populates itself onto Sheet C. This then causes the row to have #No Match because the rows that were once on Sheet A (but archived on [archive date] are no longer there because they archived yesterday. Row is now in Archive sheet and Sheet C at the same time.

Request: Help to stop the row that was on Sheet C, then archived on [archive date], from populating itself onto Sheet C at 7am after the archive date.

kowal

hi @A.C.

have you setup the conditions in your workflows that once a row is "considered as archived" shall not be copied anywhere? you can setup another workflow to mark which rows are archived.

Paul Newcome

Do you have any automations built into the Archive sheet(s)?

A.C.

Good morning-

@Paul Newcome - Thanks for reaching out. I do not have automations on the Archive sheet.

@kowal - I do not have anything like that on my sheets. Do you have guidance on steps I need to take to set this up? I am new to Smartsheet and inherited these Sheets/Workspaces from someone who is no longer with our agency. Any help is greatly appreciated.

Andrée Starå

https://community.smartsheet.com/discussion/140198/row-is-repopulating-on-sheet-after-it-has-archived-needs-to-stay-archived

Hi,

I hope you're well and safe!

Have you looked at the Activity Logs for clues? What do they show?

Is there anything strange?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

A.C.

Hi, thanks for your time in reviewing my post and leaving a response. Since my original post I've made a few changes, and on 6/24 and 6/29 the updates will run for the first time since those are the upcoming archive dates.

Updates made to "Gateway2" sheet which links Sheet A to C.

-On the "Gateway2" I created a check box column called [Archived?].

-1 AM every day: on "Gateway2" where [archive date] is "today" or "is in the past" [Archived?] box will be checked via a workflow.

-7 AM every day: on "Gateway2" there is a check box formula column that says if a row is on Sheet A and not on Sheet C then box is unchecked. At 7 AM if row is not on Sheet C (unchecked box), then copy row to Sheet C.

My update to this workflow is: where row is not on Sheet C (unchecked box) and where [Archived?] is unchecked, then copy row to Sheet C.

*This should allow rows that need to copy to Sheet C to copy as desired and prevent rows that have already archived ([Archived?] is checked) from repopulating to Sheet C.

I'll provide an update once the update has had a chance to run through 6/30.

Andrée Starå

https://community.smartsheet.com/discussion/comment/487714#Comment_487714

Excellent!

Happy to help!

✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

A.C.

Looking at "Gateway2" activity log, I could not see anything strange. The requests/rows recycle through previously used rows as requests get archived and disappear.

UPDATE: My added steps in my last update did not work :( . I went on to ask ChatGPT and it recommended on the "Gateway2" sheet to add another refresh trigger before the 7AM workflow, so the page will "wake up" and refresh data so it doesn't operate on outdated data. I added a lock workflow at 5 AM and unlock workflow at 6 AM every day. This additional "bullet proof" step did not work :(. At this point, I'll just go into Sheet C periodically to delete the duplicated data that shows #No Match because the original row has already moved itself to Archive.

Either way, thanks again squad and happy Smartsheeting!!