Filtering multiple choices using combo box to a column with multiple values.

[AIDENMJ]

Good Day, 

Goal : To able to filter multiple selected item in SP list columns contains concat data. i.e. A, B, C
Combo box items ["A", "B", "C"]. * allow multiple selection.

My table 

ID	Letters	Column1	Column2	Column3
1	A, B	Completed	TRUE	TRUE
2	B,C	In Progress	TRUE	FALSE
3	A	Reviewed	TRUE	TRUE
4	A,C	Completed	FALSE	FALSE
5	A, B, C	In Progress	TRUE	TRUE
6	C, B	In Progress	TRUE	TRUE

Current: I have 9 total filters include this multi selection filter. 
I able to filter last selected value only. Let's say I selected "A" in combobox
will filter Row 1, 3, 4, 5, and I add one more selection "C", results will filter 2, 4, 5, 6. Only focus on the last selected value.



Some snippets. this is the Gallery Items.

Filter(
    If(sortAZ,SortByColumns(colProject,"Project_Type",SortOrder.Ascending),SortByColumns(colProject,"Project_Type",SortOrder.Descending)),
    And(
        Or(
            IsBlank(TextInputTitle_EditPage_1),
            IsEmpty(TextInputTitle_EditPage_1),
            TextInputTitle_EditPage_1.Text in Title
        )And
        Or(
            IsBlank(ComboBoxLetter.SelectedItems),
            IsEmpty(ComboBoxLetter.SelectedItems),
            S_ID in Concat(colLetter,S_ID_Letters) 
        )And
.......................

Combo box on change collection

Clear(S_IDAffProd);
ForAll(ComboBoxLetter.SelectedItems,Collect(colLetter,ShowColumns(Filter(Letter,Title=Value),"S_ID_Letter")));

It's collecting based on filter in selectedItems in OR condition. I want it to be AND.

Another solution I tried was concat the combox box filter selection items, this way only works if the data patch in the same exact order. Another dead end.

Is there any solution on filter multiple selected items to concat columns along with other filters.

I have tried to have separate sharepoint list just for this column, as when patching new rows. on the multi selected item will patch in new rows with main SP list ID. Same result. Only able to filter to last selected value. 

Please reach out to me if not understand my questions.

thanks in advance

[v-chengfen-msft · Accepted solution]

Hi @AIDENMJ ,

Do you want to use other filter conditions and filter multi-select select columns to filter out the desired results?

I use the combination of Column1 and multi-select columns to filter and make a template for your reference:

Filter(colProject,Concat(ComboBox3.SelectedItems,Value,",") in Concat(Letters,Value,","),If(IsBlank(TextInput3),true,TextInput3.Text=Column1.Value))

Best Regards

Cheng Feng

[AIDENMJ]

I would definitely try this. My current format of that that multi valued column is Text. I concat it with ",".
But I was trying this choices approach as well.

I have question on this first image. Combo box selection A & B, the result should show ID 1 & 5 right? Even tho it's not in the same order? will it show up. For example if combo box selection is C & B.
2 rows much show ID 2 & ID 6

I just tried your method with is almost similar with mine. As I store as text in a column. It filter only if it's in order.

[v-chengfen-msft]

Hi @AIDENMJ ,

I have two filter conditions in the formula, the first is the option of Combobox, and the second is column1 is equal to Completed

When textinput is blank

When textinput is Completed

When combobox is c b

This multi-choice filtering method can only be judged according to the sequential splicing

Best Regards

Cheng Feng

[AIDENMJ]

Hi Cheng Feng, 

Thanks for the explanation. The ComboBox will be available to create new item or edit as well. User might select in order or may not. That is why concat way in sequential would not fix my issue. 

Using this I can do filtration but it displays all as in if it contains A & C.
The result will show all. ID 1 - ID 6. Can it be down with just both of it. 

If(
   Len(ComboBoxLetter.Selected.Value) = 0,
   colProject,
   Ungroup(
      ForAll(
         ComboBoxLetter.SelectedItems As aSel,
         Filter(
            colProject,
            aSel.Value in Letter
         )
      ),
      "Value"
   )
)

 Or should I add in SortByColumn for the combobox before it patch in. and add the sort for the filter as well 
Thanks again. Will update if I succeed 

[v-chengfen-msft]

Hi @AIDENMJ ,

We can try to arrange the values of the multiple selections in order, and then arrange the multiple selections in the list in the same order, and then spell them into strings for comparison.

In this way, no matter how the user chooses in the list, or chooses at will in the combobox of apps, it can match

Filter(colProject,Concat(Sort(ComboBox3.SelectedItems,Value,SortOrder.Ascending),Value,",") in Concat(Sort(Letters,Value,SortOrder.Ascending),Value,","),If(IsBlank(TextInput3),true,TextInput3.Text=Column1.Value))

Best Regards

Cheng Feng