Allow Formulas to return a collection of just a specific field from Filter results

Ideas & Features
1

I have a Collection of “Roles”, where each Role is related to a User.

I want to create a Formula field that returns the collection of all the Users linked to the Roles in the collection.

But it’s not currently possible to return a collection of a particular field from the Filter results:

image
image787×400 8.56 KB

Currently we can only do this kind of operation for a single result, not for all items in a collection:

image
image842×821 32.6 KB

IIUC this can currently only be done by using a pair of Rules (Role Linked and Role Unlinked).
Which is better than nothing, but “Rule proliferation” is a real issue.

2

You can do it with two fields:
The first is a formula
Roles.Filter(....)
and then the second is a lookup based on that
Filtered Roles > Users

3

Formulas work the same as lookups for a to-one field, but when it’s a to-many, there’s no way to return a related collection via formula like you would with lookup. This is unfortunate as it then needs lots and lots of useless lookups to get nested data as opposed to a function with many dots.

More specifically I wanted to make a rule that gets the related users from the project’s roles. But it doesn’t work. Instead I need to make a new lookup and use that one.

I thought maybe I could do a workaround where I query all the users then filter based on the first step project’s roles. But I get the error “Can’t compare Collection of Project Roles and Collection of Project Roles”. There’s no “contains” in formula filters.

I’ll make another lookup for now!

4

@Chr1sG I think this is the same feature request as Allow Formulas to return a collection of just a specific field from Filter results