Moving Rows with a Hierarchy

Options

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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    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

    https://app.smartsheet.com/b/publish?EQBCT=f35dfb27ce3142609a466bd4dd04bd12

    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

    https://app.smartsheet.com/b/publish?EQBCT=861900cca15542f8a31ce91f3c3e3c48

  • abolton
    abolton ✭✭
    Options

    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!