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

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

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
Highlighted
Impactful Individual
Impactful Individual

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

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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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