Filter COLLECT(RANGE) by IF Statement

Rob W.

Scenario:

Trying to return a list of active record UIDs based on a common component ID reference value within the row. The following formula is doing 95% of what I need:

=JOIN(COLLECT([Record ID]:[Record ID], [Component ID]:[Component ID], [Lookup ID]@row), CHAR(10))

Related community discussion - thanks to @Shanky Paul for assisting with this: https://community.smartsheet.com/discussion/comment/392701#Comment_392701

Problem:

I'm able to get all the matching row UIDs to return correctly based on the reference, but now can't filter out UIDs for records that are closed.

Approach:

I would normally solve this type of problem with COUNTIFS() or a similar conditional array lookup function, but I don't see that COLLECT has an associated conditional modifier and I'm not just looking for # of instances, but to return text values for any matches.

Because I'm trying to apply the IF condition to the rows in my source RANGE and not on the local lookup / row reference, I'm having a hard time wrapping my head around how to configure this formula. Feels like the answer should look something like this, but this is applying the IF condition to the result of the array and not integral to the array query itself and the syntax is expecting the IF to have an IF TRUE and IF FALSE path, whereas I trying to COLLECT if true and ignore if false.

=JOIN(COLLECT(IF([Record ID]:[Record ID],[Status]@row<>"Closed"), [Component ID]:[Component ID], [Lookup ID]@row), CHAR(10))

Appreciate any assistance in resolving this.

Paul Newcome

You don't need an IF statement. Just another range/criteria set within the COLLECT function.

Paul Newcome

You don't need an IF statement. Just another range/criteria set within the COLLECT function.

Rob W.

Thanks @Paul Newcome, I don't know why I didn't see that in the COLLECT syntax.

Adjusted formula:

=JOIN(COLLECT([Record ID]:[Record ID], [Component ID]:[Component ID], [Lookup ID]@row), [Status]:[Status], "Active"), CHAR(10))

I modified this a bit more to include multiple status conditions I wanted to return records for - problem solved!

=JOIN(COLLECT([Record ID]:[Record ID], [Component ID]:[Component ID], [Lookup ID]@row), [Status]:[Status], OR(@cell = "Active", @cell = "Approved", @cell = "Submitted")), CHAR(10))

Paul Newcome

Happy to help. 👍️

In the future... You can also exclude using the AND function if that list happens to be shorter.

AND(@cell <> "Complete", @cell <> "On Hold")