Answered

Filter SharePoint List from Lookup Column in Another SharePoint List

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Sort by

SaiKrishnaGudluru 499 on at

Like (0)

Report
Copy link

Link copied!

try
Filter(SP1, SP2.Parent_Type = Gallery1.Selected.Type)
Gallery1.Selected.Type -> a gallery with SP1 data

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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!

Was this reply helpful? Yes No