cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shaahid001
New Member

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

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.

 

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

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.

View solution in original post

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)))

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,409)