cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jacquesne
Level: Power Up

Filter Gallery using Multiselect Lookup Field SharePoint List

I'm setting up an inventory system that allows users to "tag" items with multiple metadata options based on a SharePoint list. There are two SharePoint lists, one called "Inventory Database" that contains all items and another called "Inventory Metadata" than contains a list of tags.

The Inventory Database list has a multi-select lookup column that can be set to any number of items from Inventory Metadata. The PowerApp allows the user to set these tags for each database item, so a "Cable" item could have "15 ft.", "6E", and "patch cable" as tags. The Inventory Metadata list would have each of these as a seperate item.

What I want to do is be able to filter a gallery by an arbitrary number of these tags if an item contains one or more items from a multiselect combo box. I have a combo box with the items "Distinct('Inventory Metadata',Title).Result" with multiselect set to true and a gallery with multiple search and filter criteria. For example, there is another combo box with the location that uses the filter criteria Filter('Inventory Database', Location in ComboLocation.SelectedItems).

I can't do this for the tags, however, because the "Metadata" column is a multi-select lookup column. As such, Filter('Inventory Database', Metadata in ComboMetadata.SelectedItems). If we convert to pseudocode, the Location is essentially this:

Loop i on 'Inventory Database':

    If Location = ComboLocation.SelectedItems[i] set true and break

    else stay false

What I want for Metadata is a double loop:

Loop i on 'Inventory Database':

    Loop j on Metadata[i]:

        If Metadata[i] = ComboMetadata.SelectedItems[j] set true and break

        else stay false

The closest example I've found is here:

https://powerusers.microsoft.com/t5/General-Discussion/Filter-gallery-items-based-on-a-multiselect-l...

I could not get the collection solution to work and the other solution requires a set number of values. Does anyone know of another way to essentially filter a table by another table?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter Gallery using Multiselect Lookup Field SharePoint List

Hi @Jacquesne ,

Based on the needs that you mentioned, I think the Filter function could achieve your needs. I have made a test on my side, please take a try with the following workaround:6.JPG

Set the Items property of the Gallery to following formula:

Filter('20190108_Student', Concat(School, Value & ";") in Concat(ComboBox1.SelectedItems, Value & ";"))

On your side, you may need to set the Items property of the Gallery to following:

Filter(
'Inventory Database',
If(
IsBlank(ComboMetadata.Selected.Result),
true,
Concat(Metadata, Value & ";") in Concat(ComboMetadata.SelectedItems, Value & ";"), /* <-- Add filter condition like this here */
),
Location in ComboLocation.SelectedItems
)

Please take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
cwebb365
Level 10

Re: Filter Gallery using Multiselect Lookup Field SharePoint List

I’ve been working on this same thing recently I think and got it working great. I’m a bit confused on the end result. So you have the lookup column on the main database pulling from the metadata list. What exactly are you trying to filter? And how/ from what controls?
Community Support Team
Community Support Team

Re: Filter Gallery using Multiselect Lookup Field SharePoint List

Hi @Jacquesne ,

Based on the needs that you mentioned, I think the Filter function could achieve your needs. I have made a test on my side, please take a try with the following workaround:6.JPG

Set the Items property of the Gallery to following formula:

Filter('20190108_Student', Concat(School, Value & ";") in Concat(ComboBox1.SelectedItems, Value & ";"))

On your side, you may need to set the Items property of the Gallery to following:

Filter(
'Inventory Database',
If(
IsBlank(ComboMetadata.Selected.Result),
true,
Concat(Metadata, Value & ";") in Concat(ComboMetadata.SelectedItems, Value & ";"), /* <-- Add filter condition like this here */
),
Location in ComboLocation.SelectedItems
)

Please take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (7,078)