How to compare two collections without using ForAll?

(0) Share

Report

Posted on by rbn_nue

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?

Categories:

General Questions

Using Apps Using Formulas

All responses (6)

Answers (1)

rbn_nue on at

Like (0)

Report

Re: How to compare two collections without using ForAll?

Hi @rswain

That worked! Thank you for the suggestion.
Verified answer

rswain 173 on at

Like (1)

Report
Re: How to compare two collections without using ForAll?

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.
rbn_nue on at

Like (0)

Report

Re: How to compare two collections without using ForAll?

Hi @BCBuizer

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.
BCBuizer 21,956 on at

Like (1)

Report
Re: How to compare two collections without using ForAll?

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
rbn_nue on at

Like (0)

Report

Re: How to compare two collections without using ForAll?

Hi @BCBuizer

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

Thanks.
BCBuizer 21,956 on at

Like (0)

Report

Re: How to compare two collections without using ForAll?

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.