Find blank children and return another column for that row as 'join distinct'

Hi - hoping someone can help.

I have a parent row where I want to return: 'Complete' if all children have a value in that column, if any are blank, I want to return another column for that row.

If this is too complex I will settle for 'Complete' if all children have a value or 'Incomplete' if at least one child is blank.

See below for expected outcome:

this is the formula I was hoping would work for the full outcome but it's erroring:

=JOIN(COLLECT(CHILDREN([Fulfilment Country]@row), CHILDREN([Fulfilment Country]@row), ISBLANK(CHILDREN())), CHAR(10))

Many thanks for any help in advance.

Find more posts tagged with

Accepted answers

All comments

melimob

OK - I have figured out a formula that works for my needs so have posted below. Would still be interested though if anyone has an answer for returning and collating from another row.

result: returns 'Pending' if at least one child is blank, or 'complete' if all have a text value.

=IF(COUNTIF(CHILDREN(), ISBLANK(@cell)) > 0, "Pending", "Complete")

=IF(COUNTIF(CHILDREN(), ISBLANK(@cell)) > 0, COUNTIF(CHILDREN(), ISBLANK(@cell)), "Complete")

result: returns the number of blank children if at least one child is blank, or 'complete' if all have a text value.

hope this helps.

Genevieve P.

Hi @melimob

Well-done! The first formula looks good 🙂

To return the country names where there's a blank child cell, try this:

=IF(COUNTIF(CHILDREN(), ISBLANK(@cell)) > 0, JOIN(COLLECT(CHILDREN([Fulfilment Country]@row), CHILDREN(), ""), CHAR(10)), "Complete")

Let me know if this works for you.

Cheers,

Genevieve

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.