Troubleshooting Automation: Copying Only Top/Parent Row Instead of Entire Project Plan

Khadija Ali

Can you help troubleshoot the automation that's supposed to copy only the top row/Parent row of the project plan (which contains specific key information) into the 'Timeline Variance and Weeks Remaining' sheet whenever a project plan is updated? Currently, the automation is copying the entire project plan instead of just this specific row, and I want to understand why it's not behaving as expected. The row I want copied includes key details such as the project status and weeks remaining, but right now, the entire plan is being transferred instead of just this top row/parent row. Could you help figure out what's missing or why this is happening?

Here is a snippet of my automation:

Amit Wadhwani

Hi @Khadija Ali

This has been discussed earlier

https://community.smartsheet.com/discussion/75218/copy-parent-row-only

Jason Tarpinian

It is working as Smartsheet intended, if you set up a copy/move rows automation, any children, no matter how you filter or try to trick the conditions, will copy as well. So the workaround is to not have children rows! I had a similar issue recently that in the end didn't end up using it, but here's my theory on how it works:

Sheet1 (left screenshot):

• Use the "RowID" column and a couple helpers to # off the parents. This "Parent#" will be used in the 2nd sheet to pull over just the parent rows.
• "Hierarchy" used for if a row is a child or not: =IF(COUNT(CHILDREN()) > 0, "Parent", "Child")
• "Parent#", what # parent is the particular row, used to number off on Sheet2: =IF(Hierarchy@row = "Child", "", COUNTIFS(RowID:RowID, <=RowID@row, Hierarchy:Hierarchy, "Parent"))

Sheet2 (middle screenshot)

• This is where all the magic happens. The automation technically comes from this middle sheet. But because cross-sheet formulas cannot be used to trigger copy rows automations, we have to use the daily @ preset times for our trigger with a special "Change?" field as the condition.
• "Range Extender", just a checkbox with manually checked box to extend the sheet, in case there are more parents that need to pull from sheet1. Extend as far as you'd think needed.
• "RowID" is the system column, and use the "Row#": =MATCH(RowID@row, RowID:RowID, 0)
• Task/Start/End… and whatever other fields you'd want to copy in your automation. Use INDEX/MATCH to pull this data in, there are plenty of questions on the forums how to build those if you're unfamiliar.
• "Previous Start" and "Previous End" are needed since cross-sheet formulas can't be used to trigger the automation. These dates will pull the latest dates of that particular task from Sheet3. The following formula will make sure it always pulls the most recent date: =INDEX(COLLECT({3 - Copy Target - Start}, {3 - Copy Target - Task}, Task@row), COUNTIFS({3 - Copy Target - Task}, Task@row))
• "Change?" is used for our condition in the Copy Rows automation. It just checks any of fields from the Sheet1 don't match the dates from Sheet3 =IF(OR(Start@row <> [Previous Start]@row, End@row <> [Previous End]@row), true)
• The automation can then be set up like so:
  
  

Sheet3 (right screenshot)

• This sheet will then be the final product of the copy rows. There's plenty you can do once the data gets here, but nothing really special to mention about the setup.

This is all a very convoluted way to do what you're asking, but I can't think of any other way to do it until Smartsheet develops a "COPY PARENT ROW ONLY" automation. No worries if you don't actually use this, it was fun to theory-craft 😁