cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter SharePoint List from Lookup Column in Another SharePoint List

I have 2 SharePoint lists. SP1 and SP2

SP1 looks like:

IDTypeDetails
1Adetail 1
2Bdetail 2
3Cdetail 3

 

SP2 looks like:

IDParent_Type (this is a SharePoint lookup column from SP1)Details
1Afoo1
2Afoo2
3Bfoo3

 

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

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))

 

1.JPG

Best Regards,

Bof

 

View solution in original post

Anonymous
Not applicable

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!

 

 

View solution in original post

4 REPLIES 4
krishnags
Responsive Resident
Responsive Resident

try

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

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

Anonymous
Not applicable

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
Community Support
Community Support

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))

 

1.JPG

Best Regards,

Bof

 

View solution in original post

Anonymous
Not applicable

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!

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,834)