Archiving Design Pattern in Smartsheet
We currently have a very large Smartsheet we use to collaborate with external business partners. I have created workflows to add rows each week, and our cross-functional teams complete tasks related to each of these rows over a period of weeks.
We are struggling to find the best way to declutter this sheet once all tasks are completed for a given row. Originally, I had a workflow that would move rows to an "archive" sheet 6 months after the task was supposed to be completed. We then changed to checking for certain conditions to determine if a row should be moved, but ultimately we were moving rows to the archive sheet too early. I am leveraging the Smartsheet API in Python, and the issue with any "move" operation in Smartsheet is that we lose information in any cell that contains a formula. You can see the problem with this; if a row was moved too early, we cannot move it back without the risk of information loss.
Our next line of thinking is to manually archive stores, meaning that a user will check a box in a certain column, and the next time the workflow runs, it will check if that column is checked. If it is, then it will move the row to the archive. This would minimize any movement required from the archive back to the main sheet. But we would still have the same problem of information loss when moving rows to another sheet.
Is there a better design pattern for what we're doing here? Our end goal is to remove completed rows in the main sheet without losing information.