I have a parent row that needs to populate with appropriate 'Status' based on it's children

I have many parent rows with many children, in some cased grandchildren. Each child (or grandchild) has a status. Status choices are;

Not Applicable
Not Started
In Progress
Complete

I have to account for the following possibilities;

I have been able to write a formula for the first 4;

I am having great difficulty writing the remaining 3 (#'s 5, 6 & 7) IF statements. As well, I'm not entirely sure where they should be inserted.

Any help would be and always is GREATLY appreciated.

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

One thing to keep in mind with nested IF statements is that it will stop on the first true value. This means that to get to the second IF, the first must be false. To get to the third IF then the first and second must be false.

It also looks to me like you want to essentially ignore the "Not Applicable" unless all are "Not Applicable". This means you can (for example) combine 2 and 6 into a single IF to say that if the count of "Complete" and "Not Applicable" is equal to the count of children then output "Complete". You can also combine 3 and 7.

So if we leverage this logic we can actually say something more like this:

If all are "Not Applicable" then output "Not Applicable".

If all are either "Not Applicable" or "Complete" then output "Complete".

If all are either "Not Applicable or "Not Started" then output "Not Started".

Every other combination would output "In Progress".

=IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNT(CHILDREN()), "Not Applicable", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Not Started")) = COUNT(CHILDREN()), "Not Started", "In Progress")))

Paul Newcome

Happy to help. 👍️

Honestly... My take on formulas and solutions is driven more by experience with being lazy. Haha. I don't want to work any harder than I really need to, so I am always evaluating formulas and whatnot to try to find the most efficient method to accomplish my goals.

All comments

Heather_Verde

They would need to be inserted at the beginning of your formula because you are looking at multiple criteria and then you would flow to the single criteria if the multiple criteria was not met. If you keep the single at the beginning, then you will never make it to the multiple because the single options would have already been met and the formula executed from that point.

PeggyLang

@Heather_Verde your explanation makes no sense to me as there would only be one scenario for any individual situation.

Heather_Verde

Ok. I have just found that when I do formulas, they tend to work better when I have the multiple condition items first. Sorry if it doesn't make sense.

PeggyLang

@Andrée Starå I'm wondering if you have any ideas on this question?

Paul Newcome

So if we leverage this logic we can actually say something more like this:

If all are "Not Applicable" then output "Not Applicable".

If all are either "Not Applicable" or "Complete" then output "Complete".

If all are either "Not Applicable or "Not Started" then output "Not Started".

Every other combination would output "In Progress".

PeggyLang

@Paul Newcome I have been struggling with this for DAYS!!!!!

THANK YOU, THANK YOU, THANK YOU!!!!!

Can you recommend some articles, videos, teachings that will help me to see things the way you did?

Paul Newcome

Happy to help. 👍️

Oliver Lang

@Paul Newcome Thank you for your contribution, I totally agree with Peggy: THANK YOU!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.