Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Moving Rows with a Hierarchy

I have some automation setup to move rows as they hit 120 days past the date column. In this sheet I have hierarchy setup to because of the amount of data so that all of rows in "January" go under a parent row name January. The rows don't move as soon as they get to said header row though. Advice?

Answers

  • Community Champion

    Hi @abolton

    Smartsheet move or copy rows automation moves children rows when the rows to move or copy is a parent row.

    So, to add a condition that the row to move or copy is a parent row, I added a helper column, [is Parent].

    [is Parent] =IF(COUNT(CHILDREN()) > 0, 1)

    Then, I added two helper columns to determine if a row has reached 120 days past the creation date. (You can combine them if you want.)

    [Since Creation] =TODAY() - DATEONLY(Created@row)

    [Past 120 days] =IF([Since Creation]@row > 120, 1)

    Source Sheet

    Site faviconSmartsheet

    Workflow Automation

    Then, I added a workflow automation like this;

    As I could not wait 120 days to test this automation, I changed the [Past 120 days] column formula to cell formulas and checked some of the checkboxes. (My automation action is "Copy rows", but it should work for "Move rows" as well.)

    First, I checked the [Past 120 days] of the second row and verified that no "Copy rows" action happened.

    Then, I checked the first row and ensured all the rows under Parent 1 were copied to the target sheet. [8:36 AM]

    Lastly, I checked the 18th row and confirmed that in the Grand Grand Parent(Parent 1-x), Grand Parent(Parent 1-y), and Parent(Parent 2) hierarchy, only the group of rows under the 18th row or Grand Parent(Parent 1-y) were copied.[8:38 AM]

    Target Sheet

    Site faviconSmartsheet

  • ✭✭✭

    Unfortunately, that created another set of problems. What I've done in the meantime is create permanent parent rows for each of the months and the child rows seem to be moving over automatically without issue

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions