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

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

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
Highlighted
Super User III
Super User III

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

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.

 

Highlighted
New Member

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

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

Highlighted
Super User III
Super User III

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

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

Highlighted
New Member

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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