cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stephenJames
Level 8

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
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
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."

stephenJames
Level 8

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 

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

Community Support Team
Community Support Team

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,778)