Several multiple select combo boxes to filter SharePoint list

[gizmobrat]

Hello,

    I have been pulling my hair out on this one. I have a SharePoint list that I need to filter down based on several multi select combo boxes.  I have the following code that only returns values based on the first item selected in a multi select combo box. The app is working with a large data set, so I need to be able to use delegation with SharePoint. 

If(
    IsBlank(Comb_FY_Filter.Selected.Title),
        If(IsBlank(Comb_Method_Filter.SelectedItems),
            Filter(DPW_Jobs_Sharepoint,StartsWith(Title,Box_Job_Search.Value)),
            Filter(DPW_Jobs_Sharepoint,StartsWith(Title,Box_Job_Search.Value) && StartsWith(Job_Contract_Method,Comb_Method_Filter.Selected.Title))
        ),
        If(IsBlank(Comb_Method_Filter.Selected.Title),
            Filter(DPW_Jobs_Sharepoint,StartsWith(Title,Box_Job_Search.Value) && StartsWith(Job_FY,Comb_FY_Filter.Selected.Title)),
            Filter(DPW_Jobs_Sharepoint,StartsWith(Title,Box_Job_Search.Value) && StartsWith(Job_FY,Comb_FY_Filter.Selected.Title) && StartsWith(Job_Contract_Method,Comb_Method_Filter.Selected.Title))
        )
)

[WarrenBelz · Accepted solution]

Hi @gizmobrat ,

Assuming the selections in the Combo Box match the field options, you should only need this

Filter(
   DPW_Jobs_Sharepoint,
   StartsWith(
      Title,
      Box_Job_Search.Value
   ) &&
   (
      Len(Comb_FY_Filter.Selected.Title) = 0 ||
      Job_FY = Comb_FY_Filter.Selected.Title
   ) &&
   (
      Len(Comb_Method_Filter.Selected.Title) = 0 ||
      Job_Contract_Method = Comb_Method_Filter.Selected.Title
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[gizmobrat]

@WarrenBelz,

When using you solution it only shows items that match the first selected record in the list. The field I am filtering on is a text field and the combobox is looking up the title field from another list.

[WarrenBelz]

@gizmobrat ,

Sorry you have lost me a bit here - you are looking up: -

• The Title field with Box_Job_Search
• The Job_FY field matching Comb_FY_Filter
• The Job_Contract_Method matching Comb_Method_Filter

So my two main questions are: -

• What type of fields are Job_FY and Job_Contract_Method and if Choice fields are they single or multiple value ?
• Are Comb_FY_Filter and Comb_Method_Filter single or multiple selection Combo Boxes and what exactly are you trying to match with them ?

[gizmobrat]

@WarrenBelz ,

1. The Job_FY & Job_Contract_Method fields are single line of text fields. 

2. The filter combo boxes are multi select combo boxes.

3. I am attempting to filter a gallery so that only the records that records that records are excluded when they don't match the selected. I.E if I have FY24 & FY25 selected with nothing in the contract method, I want records with FY24 or FY25 to be returned. Currently only records that match FY24 will be returned. While at the same time keeping delegation to share point in mind. 

[WarrenBelz]

@gizmobrat ,

You do indeed have a Delegation issue as it you want to match any selection in Comb_FY_Filter with Job_FY and any selection in Comb_Method_Filter with Job_Contract_Method then you need the in Filter (which is not Delegable) and would look like this

Filter(
   DPW_Jobs_Sharepoint,
   StartsWith(
      Title,
      Box_Job_Search.Value
   ) &&
   (
      Len(Comb_FY_Filter.Selected.Title) = 0 ||
      Comb_FY_Filter.SelectedItems.Title in Job_FY
   ) &&
   (
      Len(Comb_Method_Filter.Selected.Title) = 0 ||
      Comb_Method_Filter.SelectedItem.Title in Job_Contract_Method
   )
)

You can manage it a bit like this

With(
   {
      _Data:
      Filter(
         DPW_Jobs_Sharepoint,
         StartsWith(
            Title,
            Box_Job_Search.Value
         )
      )
   }
   Filter(
      _Data:
      (
        Len(Comb_FY_Filter.Selected.Title) = 0 ||
        Comb_FY_Filter.SelectedItems.Title in Job_FY
      ) &&
      (
        Len(Comb_Method_Filter.Selected.Title) = 0 ||
        Comb_Method_Filter.SelectedItem.Title in Job_Contract_Method
      )
   )
)

but bear in mind the top filter needs to return less than your Delegation limit.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[gizmobrat]

@WarrenBelz,

That was what I was afraid of. In this case I will make just have the combo boxes be select one only and users can manually search latter if required. 

Thanks for your help.