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

 

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!

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

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