cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Filter between 2 collections and different columns

Hello,

         i have 2 collections that have the same columns in each. I am trying to filter one based on the column data of the other. 

The two columns i am using are a simple text column (column1) and and choice column (column2). Column 2 also contains text values.

I am wanting to search collection 1 , column 1 by the text value of collection 2 , column 2. The result should only return 1 row. 

What i have tried so far is 

 

Filter(collection1, column1 in collection2.column2)

 

 i get a red line under column2 part of the code that says " Cant convert this data type. Powerapps cant convert this text to a record " . 

Should i be using Lookup or Search for this task?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Filter between 2 collections and different columns

@stephenJames 

Sorry, I missed the part where you said it was a LookUp column.  That's why you needed .Value after the square brackets.

 

The square brackets purpose is to help with disambiguation.  In your scenario a column named column1 appears in two datasources within the same code block: collection2 and collection3  To tell PowerApps which datasource should be referenced we can write collection2[@column1] or collection2[@column3].

 

Specifically, you must use disambiguation because collection2 is in the outer scope of this code (meaning outside the brackets).

 

LookUp(collection1, column1 = collection2[@column2].Value)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: Filter between 2 collections and different columns

@stephenJames 

You are super close to the solution.  Your syntax must be modified only slightly.  You should try using the square brackets and @ symbol instead.  I successfully tested on my side.

 

Filter(collection1, column1 in collection2[@column2])

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Resolver I
Resolver I

Re: Filter between 2 collections and different columns

Thanks @mdevaney  that helped alot. 

 

After plugging in your suggestion it originally didnt work . It did figure it out however and for some reason had to add a ForAll to it. 

So my code reads

ClearCollect(collection3, ForAll(collection2,LookUp(collection1, column1 = collection2[@column2].Value))));

 

Could you explain what the @ and [] are used for. Ive seen the @ symbol followed by things like filter and lookup and other columns. But im not sure what it mean and what the difference between filter and @filter for example, or what the use is for the []. 

 

Thanks 

Highlighted
Super User
Super User

Re: Filter between 2 collections and different columns

@stephenJames 

Sorry, I missed the part where you said it was a LookUp column.  That's why you needed .Value after the square brackets.

 

The square brackets purpose is to help with disambiguation.  In your scenario a column named column1 appears in two datasources within the same code block: collection2 and collection3  To tell PowerApps which datasource should be referenced we can write collection2[@column1] or collection2[@column3].

 

Specifically, you must use disambiguation because collection2 is in the outer scope of this code (meaning outside the brackets).

 

LookUp(collection1, column1 = collection2[@column2].Value)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Community Support
Community Support

Re: Filter between 2 collections and different columns

Hi @stephenJames ,

Based on the needs that you mentioned, I think the formula you provided above could achieve your needs.

 

Actually, the '@' operator is a Disambiguation operator within PowerApps app. On your side, Collection1 and Collection2 have same columns, when you used column2 in your ForAll function, PowerApps app may not recognized which data source (Collection1 or Colelction2) the column2 is from accurately. The '@' operator could help PowerApps app to recognize which data source the same column is from.

Please refer to the follwing article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators#disambiguation-oper...

 

In addition, you could also consider take a try with the following formula to achieve your needs:

Filter(
        collection1, 
        column1 in Concat(ForAll(collection2.column2, Concat(column2.Value, Value & ";")), Value & ";")
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,154)