cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User III
Super User III

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
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Check out the Power Apps Community Call for January 2021

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (5,732)