How to set a column true if parent row

Options
Pamb10
Pamb10 ✭✭✭✭
edited 07/17/20 in Formulas and Functions

I need to set a helper column with a value for parent rows so I can use those rows in a report.

I have tried the Parent() function, but I dont think its exactly what I need. Not really understanding what it returns.

In the report I need to filter out children and show only parent rows.

Can someone give a hand?


Thanks

Pam

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    If you only want to show parent rows, in your "Helper Column" add the below formula to every cell.

    =COUNT(CHILDREN())

    All of the parent rows will have a number greater than 1, the children rows will have a value of 0, as in the below Sheet screenshot.


    In the Report Builder, make your WHAT criteria "Helper Column" is greater than 0. This will allow only the Parent rows to come in.

    You could also spruce it up a little bit by using a checkbox or symbols to classify rows, for example I'm using the Yes/No/Hold symbols below to display "Yes" if the row is a Parent and "No" otherwise.

    Formula for the below is

    =IF(COUNT(CHILDREN()) > 0, "Yes", "No")


    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Pamb10
    Pamb10 ✭✭✭✭
    Options

    Excellent idea! Thank you so much!

  • Berto D
    Berto D ✭✭
    Options

    Worked for me, too. Thanks.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!