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.