Filter Gallery using Multiselect Lookup Field SharePoint List

[Jacquesne]

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?

[cwebb365]

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?

[v-xida-msft · Accepted solution]

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:

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,

[jharville]

@v-xida-msft Jacquesne

Hello! I am trying to do the exact same thing. I have a quick question however.

"Is it possible to have a multi-select lookup column? If so, how do you set it to *multi-select* " ?

Just like you, I have created two lists:

* 'tagList' only has one column ('Title'), which contains individual tags (type: single line of text) listed in their own row inside of that column.

* 'itemList' contains all of my items (which use multiple columns) ... and one of the columns: 'tagLookup' , is a lookup-column (that looks up to the 'Title' column inside my 'tagList'), but the user can only select one item...not multiple.

(I saw that if you take away the "Enforce Behavior Relationship" of the lookup column, you can allow multi-select for the column. But wouldn't we want to enforce the behavior relationship to allow consistency between the lookup column and that list it looks up to?)

Is there another way to allow multi-select for that lookup column without having to disable "enforce behavior relationship" ?

[cwebb365]

Man, this is an old post, lol. The original answer wasn't exactly right either. But the only way I've found, there may be another hack, but I use a ForAll on the selectedItems of the multiselect control. Then do a collect to get my records into a collection. Then I use that collection to show the filters items. 

I don't have it up now, but it's like
ForAll(control.selecteditems,
collect(col, Filter(taglist,ThisRecord.Tag = Tag)) 

Not anywhere close to formatting, but should get your started. Then on your Gallery you point it to the col you created. This will loop through the selected items, filtering the list into the collection for your gallery to show the results. 

Hope this helps. 

[jharville]

@cwebb365  This is very helpful information, I'll play around with it and should be good. Thanks again! 🙂

[Madhankumar_L]

Hello @cwebb365 ,

As you mentioned above collect the filter items in the collection to show the items in the gallery, but in case my filter result more than 2000 means how can I store in the collection because the collection limit is only 2000 records right? How can I achieve this @cwebb365 kindly help me.

Thank you in advance...