Filter gallery items based on a multiselect lookup field

[Pavithra]

I have two sharePoint lists.

List A

Skills:

C++

C#

Java

.Net

List B

In List B , Skills acts as a multiselect lookup field

Name    Skills

XXX       C++  C# Java

YYY       .Net C++

I have a Powerapps with Gallery and a Multiselect List box which is bind to List A Skills column.

Gallery will be filtered based on List B

When a user selects values from the Multiselect List box , I want gallery to be filtered based on list box selected items by comparing with  List B's Skills lookup field.

I couldnt find a way to do it in Powerapps . Any help would be great

Thanks

[v-monli-msft]

Hi @Pavithra,

Please try with steps:

1. Insert a combo box control, set its Items property to:

'List A'.Skills

Set its SearchFields property to:

["Skills"]

After this, all the skills from List A would show as options in ComboBox1.

2. Set the Items property of the Gallery control to:

Filter(
'List B',
First(LastN(ComboBox1.SelectedItems,1)).Skills in Skills1.Value
&&
First(LastN(ComboBox1.SelectedItems,2)).Skills in Skills1.Value 
&&
First(LastN(ComboBox1.SelectedItems,3)).Skills in Skills1.Value 
&&
First(LastN(ComboBox1.SelectedItems,4)).Skills in Skills1.Value 
)

I just tested and this works for me.

Regards,

Mona

[RusselThomas · Accepted solution]

Hi Pavithra,

This is doable, but in much the same way as visiting Dallas via Sydney while living in New York is doable - It's possible, but probably not very efficient and you're likely to hit a few snags along the way.

That said, there are a couple of functions you can use, each with different approaches - but your first issue is Delegation. 

The functionality you're looking for could possibly be achieved with two functions, namely Search() and in - neither of which are delegable to SharePoint - meaning you would only be searching the first 500 rows of your data.  

The second issue is having a limitless number of possible search criteria - not limiting you to three or four (or ten even), requires a little fancy footwork, otherwise you can just use the Filter(source, condition && condition && condition) shown in a previous post.

To do this the way I've done it, you would either need to be working with less than 500 (or 2000 if you use preview) rows of source data or,

• You would need to PRE-filter the source into a more manageable collection of data before doing anything or,
• You would need to put your data into CDS, or a SQL server which understand Search()  🙂

To test this though, you can use collections in your app or import some data with excel and play with it.

So, all the above caveats aside;

First, collect your selected combo items into a collection. 

The items you've selected are available in ComboBox.SelectedItems as a Table already, but I found that to be unreliable - this works for me:

Clear(collectComboItems);
ForAll(ComboBox.SelectedItems, Collect(collectComboItems, {SearchSkill: Skills}))

Note: I've created a NEW column in this collection called SearchSkill (containing the Skills selected from the ComboBox) - this is on purpose to save me scope headaches.

Then, for each skill that has been collected, you need to run an inclusive filter on the source - that is;

• If the Skill searched for is "C#" then everyone with "C#" in their skills will appear once.
• If the Skills searched for are "C#" and "Java" then anyone with both skills will appear twice - once for "C#" and once for "Java"

This is necessary to ensure you get all instances where a skill has appeared for a user.  The skill could be first, second, third or twentieth in their list of skills, so you have to run a match filter like Search() or Filter(something in Skills) for each skill - So;

Clear(CollectFiltered);
ForAll(collectComboItems, 
    Collect(CollectFiltered, Search(ListB, SearchSkill, "Skills")))

and...

Clear(CollectFiltered);
ForAll(collectComboItems, 
    Collect(CollectFiltered, Filter(ListB, SearchSkill in Skills)))

...will do the same thing, except Search() is delegable in SQL and CDS, while in is only delegable in SQL 🙂

Now, logically, the people who appeared n times in the result, (where n is the same number as the number of skills searched for), are people with all the skills searched for and, more importantly, not excluding any other skills they might have.

For Example:

Search for "C#", "Java" and "ASP.Net" - there are three search criteria

The results of the above formulae is list of all people with either "C#" OR "Java" OR "ASP.Net" in their skills.

Those who appears three times in this list must have all three skills - we can therefore Filter as follows;

Clear(collectResults);
ForAll(Distinct(CollectFiltered, Name), 
If(CountRows(Filter(CollectFiltered, Name=Result))=CountRows(collectComboItems),
Collect(collectResults, LookUp(CollectFiltered, Name=Result))))

Get a Distinct() list of names, for each one see whether they appear once for each search criteria - and if they do, include them in the final result.

The collection collectResults will now hold all the users who match the query.

There might be better ways to achieve the same thing, and probably a lot of this can be optimised into a single function but this is how I got it to work 🙂

Hope this helps,

RT

[Pavithra]

Thanks for the approach. I am going to defintiely try this. But have a question. On what properties do we need to have the below set ?

Clear(CollectFiltered);
ForAll(collectComboItems, 
    Collect(CollectFiltered, Search(ListB, SearchSkill, "Skills")))

Clear(CollectFiltered);
ForAll(collectComboItems, 
    Collect(CollectFiltered, Filter(ListB, SearchSkill in Skills)))

[RusselThomas]

Hi Pavithra,

I normally use a 'scrap' screen to troubleshoot and manually test the functions with a separate button for each piece to trigger the function, and grids/galleries to view results - it's purely functional and ugly, but once I'm happy each button works in sequence and gives the result I'm looking for, I put them all together into a single action and tidy up my screen, or just copy/paste the function to another screen where I build the actual user experience.

Note: My data source is called Table2, which is the equivalent of your ListB

my ugly scrap screen 

In this case, I would suggest having a button or search icon next to your combobox labelled "Search" and then trigger all the functions from it's OnSelect property - this way you don't have to waste time (and processing) checking each change of the combobox selected items.  The user can add/remove combobox items until they're happy, then hit the "Search" button, and it gives you a convenient Action to trigger off.

My Table2 is your ListB 🙂Hope this helps 🙂

Kind regards,

RT

[Pavithra · Accepted solution]

Worked perfectly. Thanks so much this solution.

Thanks

Pavithra

[RusselThomas]

Not sure what happened to my reply, seems to have been marked as Spam for some reason - but here's a screenshot of how I pulled it all together after testing the various functions;

My Table2 is your ListB 🙂I put a search icon next to the combobox so people can fiddle with their selections as much as they like before hitting 'search'.  All the functions are on the OnSelect property of the search icon.

Hope this helps

RT