Filter gallery based on combo box selections and multi-select SharePoint list column data

(0) Share

Report

Posted on by Riokku Microsoft Employee

Good afternoon, all:

Firstly, thank you to everyone that contributes to this forum. I've found it extremely helpful over the years!

Here's an outline of the issue I'm stuck on:

Data source: SharePoint list

App type: Canvas app

Goal: Filter a gallery via a combo box over a multi-select SharePoint list column. I'd like for users to be able to select multiple options and only display results if one of those options is in the SharePoint list column (the column itself is a multi-select choice field).

Roadblock: I've ran into issues filtering the gallery because it appears I'm attempting to compare two table records, which PowerApps doesn't seem to like.

Example:

Let's say my SharePoint list column is a multi-select field:

Column name: Options

Choices: ["Test 1", "Test 2", "Test 3", "Test 4", "Test 5"]

In my Power App there is a combo box for each of these options. If a user were to select "Test 1" & "Test 3", for example, I want to filter my gallery to show every item that has either "Test 1" or "Test 3" as an Options choice selected.

Does anyone have guidance on how to accomplish this? Appreciate your time and happy to provide further information if that is helpful.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

WarrenBelz 153,075 Most Valuable Professional on at

Like (0)

Report
Hi @Riokku ,

You have a many-to-many query required - try a collection

ForAll( ComboBox.SelectedItems, Collect( colChoices, Filter( YourDataSource, Value in YourMatchingField ).YourRequiredDisplayField ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Riokku Microsoft Employee on at

Like (0)

Report
Hi, Warren! Appreciate the quick reply.

Hoping to share my attempted application of your solution to ensure I have the right fields/names in the right spots. Would you please let me know if the below appears correct?

ForAll( ComboBox.SelectedItems, Collect( colChoices, //"colChoices" is arbitrary Filter( YourDataSource, Value in YourMatchingField //"Value" is arbitrary, "YourMatchingField" is my Sharepoint column name ).YourRequiredDisplayField //What does "YourRequiredDisplayField" refer to here? ) )

Lastly, would the hope be to embed this entire ForAll function side my gallery Items Filter function, such as the below?

Filter( MyDataSource, ForAll( (above content) ) )

Thanks so much.

Was this reply helpful? Yes No
WarrenBelz 153,075 Most Valuable Professional on at

Like (0)

Report
Hi @Riokku ,

To answer your questions

Call colChoices whatever you want

Value is the valid output of the combo box - I assumed it was either hard-coded or a Choices field

Matching field name is the one that matches TestX

You do not need the last one - I see now it is a gallery you require (I mis-read)

Why do you want a ForAll in a gallery filter?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Riokku Microsoft Employee on at

Like (0)

Report

Hi @WarrenBelz,

Thank you for the additional guidance.

My ultimate hope is to be able to filter a gallery based on a multi-select field in the SharePoint list. The user could select one or more options via a combo box and have the gallery return results if they match one or more of the options chosen in the combo box, which is why ForAll may be able to help. You're correct that the many-to-many query has proved to be the issue.

Do you have any insight on how to accomplish this type of filter logic?

Thank you.

Was this reply helpful? Yes No
WarrenBelz 153,075 Most Valuable Professional on at

Like (0)

Report

Hi @Riokku ,

The ForAll() is not the gallery Items Filter - it produces the collection that now needs to form your gallery Items - you cannot dynamically resolve a many-to-many relationship with a Filter function.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Riokku Microsoft Employee on at

Like (0)

Report

Hi @WarrenBelz,

Thank you for helping me work through this.

I was afraid that would ultimately be the answer. My goal was to filter the gallery by up to five different combo boxes, some with a many-to-one relationship with the data (these were easier to figure out), and several others with a many-to-many relationship. I'll try to think through ForAll() a bit more to see if a I can leverage it to create numerous collections and eventually merge them into a master collection that I can then filter on top of.

Appreciate your time this weekend.

Was this reply helpful? Yes No
Omar_203 43 on at

Like (0)

Report

Hi @WarrenBelz, I am trying to do something similar for an asset management system.

I am trying to filter a gallery (with a collection of SharePoint lists) based on a combo box dropdown which uses user names from another Sharepoint List. So if one person's name is selected, it should only shows items which have that user's name. Currently, I am using a collection to display all items in the gallery which works fine, but it's just showing all items.

Combobox Items query: Choices(Users.'Full name')
Gallery query: Collect(AllAssets, hardwareList, softwareList, accountList)

What would be the best way to achieve this ?

Thank you.

Omar

Was this reply helpful? Yes No