How to make Parent helper column reflect values based on Children values with multiple conditions

I am very new at using formulas with hierarchy. I simply cannot figure this out or find a similar situation to manipulate the formula. I want the Parent value in helper column Parent Proficiency to be based on CHILDREN values in Proficiency column

Levels:

Untrained

In Process

Trained

Proficient

Preceptor

Parent helper row value should reflect lowest rating in the children rows except for the following:

If all CHILDREN = Untrained then PARENT = Untrained

If CHILDREN Untrained >= 1 and any others >0 = In Process

Parent row is "Place an intravenous catheter in a stable patient" with children Canine and Feline

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey @decrom

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), "Untrained"), "Untrained", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), "Untrained") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), "Untrained")), "In Progress", IF(COUNTIFS(CHILDREN(Proficiency@row), "In Process") >= 1, "In Process", IF(COUNTIFS(CHILDREN(Proficiency@row), "Trained") >= 1, "Trained", IF(COUNTIFS(CHILDREN(Proficiency@row), "Proficient") >= 1, "Proficient", IF(COUNTIFS(CHILDREN(Proficiency@row), "Preceptor") >= 1, "Preceptor")))))))

Please let me know what we need to tweak

Kelly

All comments

Kelly Moore

Hey @decrom

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?

Please let me know what we need to tweak

Kelly

decrom

Thank you! Works perfectly!

Is it possible to then copy the values from the children rows to show up in the same column as part of the formula?

Kelly Moore

Hey @decrom

Will this work for you?

Kelly

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.