Filter SharePoint List from Lookup Column in Another SharePoint List

[Anonymous]

I have 2 SharePoint lists. SP1 and SP2

SP1 looks like:

ID	Type	Details
1	A	detail 1
2	B	detail 2
3	C	detail 3

SP2 looks like:

ID	Parent_Type (this is a SharePoint lookup column from SP1)	Details
1	A	foo1
2	A	foo2
3	B	foo3

I am trying to load a Gallery control with the items in SP1 that are selected by a user && are contained in SP2.Parent_Type.

So if a user picks SP1.Type = A and SP1.Type = C, I want my Galley to only display SP1.Type = A (because SP2.Parent_Type does not contain C).

I know that PowerApps sees SP2.Parent_Type as a record because I can load a table with Items=SP2.Parent_Type and there are two avalible fields, ID and Value - the Value field is a text field and the one I want in this case.

So the ITEM formula that I have tried in my gallery is: "Filter(SP1, Type in SP2.Parent_Type.Value)" but I get an error saying "name is not valid. identifier not recognized" on the "value" part. BUT if i leave the value off I get "can't convert this data type. powerapps cannot convert text to record". 

How to I format the filter correctly so that I can get the selected items in SP1 that also overlap with the items in SP2?

[krishnags]

try

Filter(SP1, SP2.Parent_Type = Gallery1.Selected.Type)

Gallery1.Selected.Type -> a gallery with SP1 data

[Anonymous]

This still does not work.

I get an error on the "=" that says "invalid argument type"

I get the "name is not recognized" on the 'Parent_Type' if I use "in" rather than "="

I get the "name is invalid. Identifier not recognized" if I use Parent_Type.Value with either the "in" or "="

And if I switch the ordering to Filter(SP1, = Gallery1.Selected.Type in SP2.Parent_Type) then i get the "can't convert this data type. Powerapps can't convert this Text to a Record" error 

Also, I am collecting the selected items from the gallery containing SP1 into C1 and would prefer to use the collection (C1) rather than reference the specific gallery. But this doesn't seem to change anything because it is the SP2.Parent_Type that seems to be causing all the problems.

[v-bofeng-msft · Accepted solution]

Hi @Anonymous :

Could you tell me :

• what is Type(SP1)'s data type?
• Does Parent_Type allow multiple selections?

I assume Type(SP1)'s data type is "Text" and Parent_Type do not allow multiple selections.

Please try this code:

Filter(SP1,Type in ForAll(SP2,Parent_Type.Value))

Best Regards,

Bof

[Anonymous · Accepted solution]

WOW. @v-bofeng-msft  This worked Perfectly.

• Type(SP1) is simple TEXT
• Parent_Type IS NOT multiselect

I would have never thought to use the ForAll because Parent_Type(SP2) is not a multiselect and because when I built a table to debug and set Table1.Items = SP2.Parent_Type and Column1.Field = ThisItem.Value it worked as I expected.

There must be some difference between the way the Table control and the Gallery control see a SharePoint Lookup column.

Anyway thanks again!