JOIN COLLECT DISTINCT CHILDREN FROM ANOTHER COLUMN WHERE CHILDREN OF CURRENT IS TICKED

melimob

I have two columns.

primary and column B

On the parent row for column B, I want to look at any children which are ticked and then join collect distinct values from primary column.

If I was to do a basic formula in column B it would be this for children:

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>"")), CHAR(10))

I want to return results from Primary column only where column B is checked.

any advice?

KPH

Try this:

=JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

I made three additions, shown in bold here:

=JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

This part tells the collect to use the value in the Primary Column not the column that the formula is in.

=JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

This part says only collect from children that have a value in the Primary Column.

=JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

This part says to only collect from children that have a checked box in Column B.

KPH

Try this:

=JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

I made three additions, shown in bold here:

=JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

This part tells the collect to use the value in the Primary Column not the column that the formula is in.

=JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

This part says only collect from children that have a value in the Primary Column.

=JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

This part says to only collect from children that have a checked box in Column B.

melimob

@KPH thank you so so much. Not only for the right answer but also taking the time to elaborate and break it down so clearly.

Has helped me immensely.

Thank you again!

KPH

Thanks for the feedback. I'm pleased I could help!