Filtering multiple options from ComboBox on multiple values

[Sidharth_kaushi]

I got a Sharepoint list added in a power app gallery. List name is "Microsoft Services". And there is one field in this list called services and in the power app gallery I'm showing values by this formulae 'Concat(ThisItem.Services,Value,", ")'. Now I got one Combo Box named as Combobox1 where user can select multiple values of services field and gallery should update based on the combo box selection.

I've tried using various filters, but can't managed to get data. So for troubleshooting I removed the filter and I can see the data with those services which I'm trying to filter out. 

I created a simple label as well which reflects the options correctly selected in combobox.

Even if we choose multiple choices in ComboBox, how can we reflect the same options in 'OR' format on Sharepoint Gallery.

[BCBuizer]

Hi @Sidharth_kaushi ,

From the looks of it, you're dealing with a many-to-many relationship. Assuming the Items property of the combobox is set to Choices('Microsoft Services'.Services), the below should work:

ForAll(
   Combobox.SelectedItems,
   Filter(
      'Microsoft Services', 
      ThisRecord in Services
   )
)

[WarrenBelz]

Hi @Sidharth_kaushi ,

I have to make a few assumptions here, mainly that Services is a multi-select Choice field, you are selecting multiple items in the Combo Box and you want the result of this is the gallery Items

Ungroup(
   ForAll(
      ComboBox1.SelectedItems As aSel,
      Filter(
         'Microsoft Services',
         aSel.Value in Services.Value
      )
   ),
   "Value"
)

 I am a little unclear why you are using the Concat() formula for the Combo Box Items from the existing data in the field.

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

[Sidharth_kaushi]

Bingo, Thanks for quick suggestion. That's perfect. Can you guide on how this can be used for multiple filters. Such as combining with text input and few more dropdown inputs such as tags along services and what if there is no option selected in services or tags then show the default data.

[WarrenBelz]

Hi @Sidharth_kaushi ,

This depends on the nature of your other filters and can get quite complex and multi-stepped if you have another many-to-many in there. As an example (of a simple item) a Text Box using StartsWith

Ungroup(
   ForAll(
      ComboBox1.SelectedItems As aSel,
      Filter(
         'Microsoft Services',
         aSel.Value in Services.Value &&
         StartsWith(
            FieldName,
            TextBoxName.Text
         )
      )
   ),
   "Value"
)

Simply add the others (except many-to-many) with the same logic.

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

[Sidharth_kaushi]

@WarrenBelz  What will be the best way to check here if many to many filters are not there. Let's say that combo box is blank, in that case it will not show the data. For search function this is I came up with.

Search(
    Ungroup(
        ForAll(
            ComboBox1.SelectedItems As aSel,
            Filter(
                'Microsoft Service Announcements',
                aSel.Value in Services.Value
            )
        ),
        "Value"
    ),
    TextInput1.Text,
    "MessageCenterID",
    "Title"
)

Now my concern for other Many to many boxes is if I include multiple for all loops with the first filter, it will be messy and will take too much time to load data. That's why I was thinking what will be the best way to include multiple many to many filters. Such as above we got one combo box of services, and we want to add another one by tags combobox.

I tried below for Ignoring Blank, but it doesn't return any data.

Search(
    Ungroup(
        ForAll(
            ComboBox1.SelectedItems As aSel,
            Filter(
                'Microsoft Service Announcements',
                IsBlank(ComboBox1.SelectedItems.Value) || IsEmpty(ComboBox1.SelectedItems.Value) || aSel.Value in Services.Value
            )
        ),
        "Value"
    ),
    TextInput1.Text,
    "MessageCenterID",
    "Title"
)

[WarrenBelz]

Hi @Sidharth_kaushi ,

Yes, your search should work like that. Unfortunately, multiple many-to-many would look something like this - note the structure should have all Delegable elements in the top filter to give at least some Delegation capability to this.

With(
   {
      wFilter:
      Filter(
         'Microsoft Service Announcements'
         YourDelegableFiltersHere
      )
   },
   With(
      {
         wServices:
         If(
            CountRows(ComboBox1.SelectedItems) = 0,
            wFilter,
            Ungroup(
               ForAll(
                  ComboBox1.SelectedItems As aSel,
                  Filter(
                     wFilter,
                     aSel.Value in Services.Value
                  )
               ),
               "Value"
            )
         )
      },
      With(
         {
            wData:
            If(
               CountRows(ComboBox2.SelectedItems) = 0,
               wServices,
               Ungroup(
                  ForAll(
                     ComboBox2.SelectedItems As aSel2,
                     Filter(
                        wServices,
                        aSel2.Value in SecondField.Value
                     )
                  ),
                  "Value"
               )
            )
         },
         Search(
            wData,
            TextInput1.Text,
            "MessageCenterID",
            "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

[Sidharth_kaushi]

@WarrenBelz 

[WarrenBelz · Accepted solution]

Hi @Sidharth_kaushi ,

I am simply saying to put any Delegable filters in the top statement. a With() statement is limited in output to your Delegation limit (see this blog of mine), but can accept any sized input providing the filters inside it are Delegable. So in terms of the 4 "sections": -

• The first is as described above - get your Delegable filters done, so you can maximise the number of records you are working with from that point on as all processing after this is conducted "locally". Hopefully you will have all the required records at this point.
• The next two are chained "many-to-many" filters - you can add more if you want, each filtering the output of the one above.
• The last statement should then filter the last many-to-many output and contain all your other non-delegable filters (Search is one of these).

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

[Sidharth_kaushi]

@WarrenBelz  Thanks that works.