cancel
Showing results for
Did you mean:
Responsive Resident

## How to compare two collections without using ForAll?

Hi Folks,

I'm trying to compare two collections in canvas app:

Collection A:

 ID DisplayName 123 Group A 1222 Group B 1342 Group C

Collection B:

 OfficeGroupID DisplayName 111 testname1 1222 testname2 1342 testname3 101 testname4 102 testname5 103 testname6

I need to compare these two collections and return true if at least one of the IDs in Collection A is present in Collection B. Is there anyway I can compare this without using ForAll in canvas app?

1 ACCEPTED SOLUTION

Accepted Solutions
Resolver I

``````Yes, you can use the Filter function to accomplish this without using ForAll. Here's an example formula that should work:

CountRows(Filter(CollectionB, OfficeGroupID in CollectionA.ID)) > 0

This formula uses the Filter function to create a new collection containing only the items in CollectionB where the OfficeGroupID is present in CollectionA.ID. The CountRows function is then used to count the number of items in this filtered collection. If the count is greater than 0, it means at least one ID from CollectionA is present in CollectionB. The formula returns true in this case, and false otherwise.``````

6 REPLIES 6
Multi Super User

Hi @rbn_nue ,

May I ask why ForAll can't be used?

The only other way I can think of is by using recursion, but that is more complicated to build.

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.
Responsive Resident

The number of both rows in real time scenario is much higher. Just trying to best method without slowing down the app.

Thanks.

Multi Super User

Hi @rbn_nue ,

Please try the below to see how the performance is. For me it responded quite quickly on two collections that had 1000 items each.

``````Sum(
ForAll(
'Collection A'.ID,
If(ThisRecord in 'Collection B'.ID, 1,0)
),
Value
)>0``````

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.
Responsive Resident

I had already used forAll and it was quite fast. But looks like the code you provided is much more optimized. Will try this and let you know.

Thank you.

Resolver I

``````Yes, you can use the Filter function to accomplish this without using ForAll. Here's an example formula that should work:

CountRows(Filter(CollectionB, OfficeGroupID in CollectionA.ID)) > 0

This formula uses the Filter function to create a new collection containing only the items in CollectionB where the OfficeGroupID is present in CollectionA.ID. The CountRows function is then used to count the number of items in this filtered collection. If the count is greater than 0, it means at least one ID from CollectionA is present in CollectionB. The formula returns true in this case, and false otherwise.``````

Responsive Resident

Hi @rswain

That worked! Thank you for the suggestion.

Announcements

#### April 4th Copilot Studio Coffee Chat | Recording Now Available

Did you miss the Copilot Studio Coffee Chat on April 4th? This exciting and informative session with Dewain Robinson and Gary Pretty is now available to watch in our Community Galleries!   This AMA discussed how Copilot Studio is using the conversational AI-powered technology to aid and assist in the building of chatbots. Dewain is a Principal Program Manager with Copilot Studio. Gary is a Principal Program Manager with Copilot Studio and Conversational AI. Both of them had great insights to share with the community and answered some very interesting questions!     As part of our ongoing Coffee Chat AMA series, this engaging session gives the Community the unique opportunity to learn more about the latest Power Platform Copilot plans, where we’ll focus, and gain insight into upcoming features. We’re looking forward to hearing from the community at the next AMA, so hang on to your questions!   Watch the recording in the Gallery today: April 4th Copilot Studio Coffee Chat AMA