How to update a parent status based on a child status.
I want to be able to update the status of a parent row based on the status of the child row.
Currently I have dropdowns set up for "Active", "Inactive" and "Pending". I am hoping to find a formula that when I select "Active" for ANY of the children under that parent it changes the status to Active. Otherwise if ALL children are "Inactive" or "Pending" the parent would reflect the appropriate status.
There would only ever be a combination of "Active"/"Inactive". The "Pending" status is a holder under that parent row is evaluated at the first of the month and then all of the children are updated to "Active" or "Inactive"
Answers
-
You can use a formula but it could be long depending on how many children it has, when using the phrasing Active/Inactive, and it could have to be edited for each parent. If the options were something like In Progress/Inactive/Pending you could use the formula below ...
=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(CONTAINS("Pending", CHILDREN()), "Pending", "Inactive"))
the reason this wouldn't work for Active/Inactive is because Inactive has active within it and it causes the parent row to show an Active status. (see below)
-
Thank you Kimberly that makes sense. I will play with that formula and see what I can come up with.
Edit - so it seems this won't work as a column formula? Which I guess makes sense. I was just trying to avoid having to past it in each parent column individually.
-
@Kimberly Loveless @damon.tackett
I've been trying to work on this in my free time today but am not quite there yet. The formula Kimberly provided isn't working for me because if there are Child rows with Active it still marks them as Inactive and if there are only some Inactive it still marks the parent as Inactive.
If I get this working I'll update you.
-
@Mike TV i think that is, as @Kimberly Loveless pointed out, the word Inactive contains "active" in the word. For my purposes i changed Inactive to "Cancelled" and it allowed the children to update the parent appropriately, but I can into another problem in that I have to add that formula to each parent instead of being able to change it into a column formula. When i change it to a column formula it seems to make everything "Cancelled"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!