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
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 421 members 3,913 guests
Please welcome our newest community members: