How do I automate removal of rows?

Blue Rock Planning

I have a project budgeting document that has Phases and sub tasks. They are all connected to about 14 different documents with all the phases and subtasks.

How can I setup smart sheet so that if I remove a phase or a subtask in a phase on the main document that it will automatically be removed on every other sheet that it is linked to it in the project?

Thanks!

Scott Orsey

Hi @Blue Rock Planning , Wouldn't it be nice if that were possible? This is a basic database function related to enforcing referential integrity, Unfortunately, SS isn't a database. It consists of flat files that are unrelated to each other. You can "reference" data from another sheet, but you can't do anything to that data remotely.

I believe that you are describing a situation where you have "orphan" records. Once the item in the main document is deleted, the items in the other sheets no longer have any purpose. Is this correct?

One solution I've used for this situation is to set up a checkbox column in each of your sub-documents. Let's call it "Orphans". If the checkbox is checked, then it's an orphan and you no longer need it. The logic for this would be to use MATCH to find the value of the phase or subtask in your main sheet. If MATCH returns zero (there is no match) then you would set the flag to "1". Otherwise "0".

The next step is to get rid of the orphans. To do this, you'll need to make a "trash" sheet. This is just a place to send all the rows that you are "deleting" from your other sheet. Then you can make an automation that fires once per day, looks for orphans and moves them to the trash sheet.

Does this accomplish what you'd like?

Good luck and be well

Blue Rock Planning

This sounds like a potential solution that could work. I will try it. Thanks for taking the time to answer my question!