Need Help To Target Parent Children Field to Trigger Update Request Communication

Options
Radhika
Radhika ✭✭✭
edited 11/21/23 in Formulas and Functions

Requirement :

  1. Email notification cascades to other parties as previous tasks get [Status]completed so that they can start the next task.
  2. 1.Due Time for first child to take the timestamp from parent and add duration 2. For other children look at the sibling above
  3. Auto Numbering column for Parent and Child

Assignee at level 2 -ABC,3-PQR,4-LMN. When level 1 task is completed sent out email communication to level group 2 that task is ready to be worked on.

Parent will have created date, the time stamp can be used by first child to calculate Due time by Adding created + Duration

For Second Child - First Child Completed Closed time +Duration and so on for the parent

Thanks,

Radhika


Answers

  • Radhika
    Radhika ✭✭✭
    edited 11/21/23
    Options
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @Radhika

    I am answering the "Is Predecessor Complete" part.😀

    '=IFERROR(IF(

    INDEX(Status:Status,

    MAX(

    COLLECT(MATCH:MATCH,

    [Parent Row ID]:[Parent Row ID], [Parent Row ID]@row,

    MATCH:MATCH, <MATCH@row

    )

    ))

    = "Completed", true, false), "")

    The meaning of the bolded formula is;

    • MATCH is the row number here.
    • Collect MATCH(row number) that has the same Parent Row ID, and the row number is smaller than the current row.
    • This gets the row number within the same children group just before me or the current row.
      • By using the children group = having the same parent row ID, the formula works in a multi-level hierarchy, like Grand Parent, Parent, Child, and Grandchild.
    • Using this row number, get the Status just before me.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!