With checkbox column, how do I automatically check the children when the parent is checked?

Options

I have an automation that sends completed projects to a new list when the completed column is checked. I want to make sure the children rows move with the parent row without having to check each individual child. How can I create a formula that automatically checks the column of the children rows when the parent row is checked?

Tags:

Best Answer

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    Try this in the checkbox column on the Child rows:

    =PARENT()

    or =IF(PARENT() = 1, 1, 0)

    Either will work.

    If the child checkbox is in a different column than the parent checkbox, just put the parent checkbox column name inside the parentheses, for ex. =PARENT(ParentCheckboxColumn)

    https://help.smartsheet.com/function/parent

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 03/07/22
    Options

    You won't be able to check the boxes of the children, but you won't need to. When you move the parent, the children will go with the parent!

    You can do some stuff with formulas, to make the children look like they got checked, but those formula results can't trigger workflows.

    If you want to make one column that will look at the status of the parent, and then check the box if the record is a parent and checked, and also check the box if the record is a child and the parent is checked, you can do that like this:

    =OR(IF(Checkbox@row = true, true) = true, IF(COUNT(ANCESTORS([Row ID]@row)) = 1, (INDEX(Checkbox:Checkbox, IF(COUNT(ANCESTORS([Row ID]@row)) = 1, (MATCH(PARENT([Row ID]@row), [Row ID]:[Row ID], 0)), "")))) = true)

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    I did some testing on this:

    James is correct that using =PARENT() or =PARENT(ParentCheckboxColumn@row) works to make the box appear checked, but for some reason doesn't truly evaluate to 1 or true, and will not kick off a workflow triggered by "When the checkbox column changes to checked." Also, if the checkbox is unchecked manually, the formula gets overwritten (unless it's a column formula.)

    James is also correct that moving or copying the parent row brings the child rows with it. So a simple workflow that moves a row when Completed is checked will bring child rows with it.

    Now here's just some additional info on checkboxes / hierarchy rows / and automation:

    What DOES work to change the checkbox to checked AND trigger workflow is using two checkbox columns and an IF formula.

    Columns: ParentCheckbox and Checkboxes

    Column formula in the "Checkboxes" column: =IFERROR(IF(PARENT(ParentCheckbox@row) = 1, 1, 0), 0)

    English: If the parent of the parentcheckbox cell in this row is equal to 1 (aka checked,) then set the Checkboxes column on this row to 1 (aka checked.) If there's an error, leave the checkbox unchecked.

    Workflow trigger: When a row is added or changed, when Checkboxes changes to checked...

    Action: Send an alert to me.

    I checked the ParentCheckbox on parent row "Course 1". The child rows' Checkboxes cells changed to checked automatically, based on the formula.

    Activity Log:

    Alert received, indicating three changed rows and including the parent row "Course 1" in the notification.

    Setting a workflow to move rows when Checkboxes column changes to check will move ONLY the child rows in the above example. But setting a workflow to move rows when the ParentCheckbox is checked will move the parent and all the child rows, regardless of any checkboxes on the child rows.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Laura O
    Laura O ✭✭
    Answer ✓
    Options

    This is great! I decided to keep it simple since the children automatically move with the parent. Thank you for all your feedback!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!