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
Highlighted
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?
Highlighted
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
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

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

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,041)