cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
A_Rodgers
New Member

Filter Gallery (SharePoint List) with Multiple Values from Other SharePoint List

Hi,

 

I have a SharePoint List with Languages and the Products available in those languages. I have another list called Product Packages and the Products that compose those packages. Products is also a SharePoint list and is connected to Languages and Product Packages as a look up column. 

 

I need to filter Languages for all Products in a selected Product Package. I am able to select a single product and filter languages with this expression. 

 

If(Dropdown1_4.Selected.Title = "No Filter",'Languages - Complete', Filter('Languages - Complete', Dropdown1_4.Selected.Title in Products.Value))

 

 

I am not able to figure out how to filter Languages for all Products in a selected Product Package (dropdown). I have had minor success by translating the product titles into an html text box and filtering Languages with text in the text box, however several items are missed because it searches for the literal string and there are variations in placement/punctuation.

 

This is the code for adding the Product titles from the selected Product Package to a text box

 

If(Dropdown1.Selected.Title = "No Filter", "", Concat(Dropdown1.Selected.Products,Value,";"))

 

 

This is the code that kind of filters based on the string in the text box 

 

If(Dropdown1.Selected.Title = "No Filter",'Languages - Complete', Filter('Languages - Complete', HtmlText1.HtmlText in Concat(Products,Value & ";")))

 

 

Anyone have advice for filtering for all records between tables? Thank you in advance for the help!

2 REPLIES 2
KranthiTupakula
Responsive Resident
Responsive Resident

@A_Rodgers  Do you mind sharing a screenshot of the your app screen with dropdowns?

Sorry, my first post disappeared. 

@KranthiTupakula here are screenshots and more explanation.

I tried another route with a duplicate screen, still with no success.

Both screens have this code onVisible

ClearCollect(collectPackages,{Title:"No Filter"});Collect(collectPackages,SortByColumns('Product Packages',"Title"));

The dropdowns are

collectPackages

On screen 1, I'm trying to directly filter the gallery based on the products in the package selected in the dropdown.

A_Rodgers_0-1599141191537.png

 

If(Dropdown1.Selected.Title = "No Filter",'Languages - Complete', Filter('Languages - Complete', ForAll(Dropdown1.Selected.ProductsInPKG.Value) in Products.Value))

On screen 2, I added a data table of the Products list, and filtered that table based on the package selected in the dropdown

A_Rodgers_0-1599143638922.png

data table Items

If(Dropdown1_1.Selected.Title = "No Filter", Products, Filter(Products, Dropdown1_1.Selected.Title in Packages.Value))

gallery items

If(Dropdown1_1.Selected.Title = "No Filter",'Languages - Complete', Filter('Languages - Complete', ForAll(DataTable1.Selected.Title in Products.Value)))

 

Some more explanation of the SharePoint Lists. All lookup columns are multiselect. Languages has a lookup to Products, Products has a lookup to Packages (added to make the data table work), Packages has a lookup to Products. The app needs to filter based on AND, so for example if a package has products 1, 2, and 3, and the products are available in the following languages, then only the languages listed in all 3 packages should show (A and C in this example).

product1product2product3
language alanguage alanguage a
language blanguage blanguage c
language clanguage c 
 language d 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,273)