Need help showing child row statuses in parent row status

Karey

Hi! I need help creating a formula that shows the child row statuses in the parent row. Is the below possible to build into a formula that I can pop into the parent row?

• Show “Not Started” if all child rows say “Not Started”
• Show “On Track” if any of the child rows say “On Track”
• Show “With Client” if any of the child rows say “With Client”
• Show “At Risk” if any of the child rows say “At Risk”
• Show as “Complete” if all child rows are “Complete”
• Show as “Cancelled” if all child rows are “Cancelled”

Thanks!

Kelly Moore

Hey @Karey

Here is the formula requested. If there is a formula that is inserting the Child row response, let me know and we can combine the two formulas into one column formula. If you are manually choosing the response in the Child row then you must manually insert this every time into the Parent row. If you inserted a helper column, you could pull the existing Child data into the helper and then have a column formula that would automatically register the response. (If adding a helper then in all the CHILDREN() terms you would need to add CHILDREN(Status@row) ) to point to what column you are pulling Children responses from)

There are some missing conditions which will result in the Parent status being blank. For example, if you have a mix of "Not Started" and "Complete".

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "At Risk") > 0, "At Risk", IF(COUNTIFS(CHILDREN(), "On Track") > 0, "On Track", IF(COUNTIFS(CHILDREN(), "With Client") > 0, "With Client", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNTIFS(CHILDREN(), <>""), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNTIFS(CHILDREN(), <>""), "Complete", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNTIFS(CHILDREN(), <>""), "Cancelled")))))))

Will this work for you?

Kelly

Kelly Moore

Hey @Karey

Here is the formula requested. If there is a formula that is inserting the Child row response, let me know and we can combine the two formulas into one column formula. If you are manually choosing the response in the Child row then you must manually insert this every time into the Parent row. If you inserted a helper column, you could pull the existing Child data into the helper and then have a column formula that would automatically register the response. (If adding a helper then in all the CHILDREN() terms you would need to add CHILDREN(Status@row) ) to point to what column you are pulling Children responses from)

There are some missing conditions which will result in the Parent status being blank. For example, if you have a mix of "Not Started" and "Complete".

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "At Risk") > 0, "At Risk", IF(COUNTIFS(CHILDREN(), "On Track") > 0, "On Track", IF(COUNTIFS(CHILDREN(), "With Client") > 0, "With Client", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNTIFS(CHILDREN(), <>""), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNTIFS(CHILDREN(), <>""), "Complete", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNTIFS(CHILDREN(), <>""), "Cancelled")))))))

Will this work for you?

Kelly

Karey

Thank you for your help Kelly! This is precisely what I needed!