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
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (72,473)