Unanswered

Filter Sharepoint Multichoice Field

(0) Share

Report

Posted on by luvagoldenk9

117

Have a Share Point list (SPList) with a choice field (can select multiple) named SPColors (Blue, Green, Red, Yellow, etc.). Sample records are:

SPList Record 1 SPColors => Blue, Green, Red

SPList Record 2 SPColors => Gray, Yellow

SPList Record 3 SPColors => Green, Purple, Red

Have a Power App with 6 different search fields that need to be && together to filter the Share Point data to display in the gallery.

The Share Point Color choices are loaded into a Color Collection when the Power App starts. There is a combobox (ddlbColors), with multi-select turned on, which displays the Color Collection and the user can select multiple colors. The gallery should then display only those records that matches all colors selected. In the sample data above, if the user selects Green and Red in the combobox, the gallery should display Record 1 and 3.

In the gallery Items field, the gallery returns only those Share Point records that match the last color selected:

Filter('SPList',ddlbColors.Selected.Value in SPColors.Value)

Since the combobox returns a table, we cannot use the 'in' function with SelectedItems:

Filter('SPList',ddlbColors.SelectedItems.Value in SPColors) <- the word "Value" errors as "Invalid Argument Type. Cannot use Table values in this context"

Tried Reza's solution but it did not work. Thoughts on how to filter the gallery based on all selected values in the combobox?

Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report
Hi @luvagoldenk9 ,

Try this

Filter( 'SPList', ddlbColors.SelectedItems in SPColors.Value )

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
luvagoldenk9 117 on at

Like (0)

Report

WarrenBelz,

Thank you for the quick response. 🙂

I tried your suggestion. 'SelectedItems' has a red squiggly line underneath it and gives the error "Invalid argument type. Cannot use Table values in this context."

I think because the combobox data type is table, plus I have it as multiselect, it does not work with the 'in' operator.

Ideas?? Thank you again!

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

Like (0)

Report
Hi @luvagoldenk9 ,

I was suspecting this, but hoping not.

You cannot do "many in many" filters directly, but you can make a collection

Clear(colColors); With( {wList: 'SPList'}, Distinct( ForAll( ddlbColors.SelectedItems, Collect( colColors, Filter( wList, Value in SPColors.Value ) ) ), Value ) )

Your gallery Items would then be colColors
NOTE the With() statement is to manage the Delegation issue with the in filter - if your list is more than 2000 items, this will not work properly.

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
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

Hi @luvagoldenk9 ,

Your post inspired me to delve a little deeper into this and I did a blog which may be useful to you.

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
luvagoldenk9 117 on at

Like (0)

Report

@WarrenBelz

Thank you for the explanation and the detailed blog. Will definitely help with getting this request to work.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

Thanks @luvagoldenk9 ,

Please remember to 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.

Was this reply helpful? Yes No