Dynamic Filter for Storing data to a Collection based on a Combobox control

[shaahid001]

Hi Everyone,

I am trying a create a collection based on a filter query dynamically generated from the Combobox control. The data source is a SharePoint list with around 20000 records, so we should keep in mind about not using non delegable functions on the filter query (For e.g in).

Combobox Items = ComboboxValue1, ComboboxValue2, ComboboxValue3

I am trying to convert the following to a dynamic query

ClearCollect(DataList,Filter(SharePointListName,(TextColumn1SP="ComboboxValue1" Or TextColumnSP="ComboboxValue2" Or TextColumnSP="ComboboxValue3") && Column2SP="StaticData"))

to something similar which cannot handle delegation because of the operator "in"

ClearCollect(DataList,Filter(SharePointListName,(TextColumn1SP in ComboBox1.SelectedItems)&& Column2SP="StaticData"))

Could you help

[WarrenBelz]

Hi @shaahid001 ,

Firstly, are these multi-select or single choice combo boxes?

If single, try the below - note xxxxx will depend on the source of the combo box Items property and will either be Value, Distinct or the actual field name from the data source.

ClearCollect(
   DataList,
   Filter(
      SharePointListName,
      (
         TextColumn1SP=Combobox1.xxxxx ||
         TextColumnSP=Combobox2.xxxxx ||
         TextColumnSP=Combobox3.xxxxx
      ) 
      && Column2SP="StaticData"
   )
)

If a multi choice, you will have some difficulty achieving this, but happy to explore further.

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.

[shaahid001]

Its a multiselect Combobox control & there is only one combobox control. I forgot to mention this on my initial post.

[WarrenBelz · Accepted solution]

Ok @shaahid001 ,

I will give you a concept here to explore.

You should be able to collect all the selected items in the ComboBox

ClearCollect(
   colChoices,
   ComboboxName.SelectedItems
)

You might then think about something like

ForAll(
   ColChoices,
   Collect(
      ColWhatever,
      Filter(
         SPListName,
         FieldName = colChoices.FieldName &&
         YourOtherFilters
      )
   )
)

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.

[shaahid001]

Your concept worked great @WarrenBelz . I've slightly modified yours instead of using a collection to store the Combobox selected values I've used ForAll directly on the selected items

ForAll(ComboBox1.SelectedItems,Collect(ListData, Filter(SPList, SPListFieldName=Value && OtherFilters)))