Delegation Management - Multi-Choice Combo Boxes

[WarrenBelz]

This article looks at some degree of Delegation Management where a Multi-select Combo Box is used to filter a Data Source for any matching values in either a Text or Single Choice field. The output is a Table and can be used as the Items of either a Gallery or a subsequent cascading value Combo Box.

In the below, I have a list of Devices and a field (Text) called DeviceType. I have a Combo Box (cbType) containing a Distinct list of the DeviceType (see this blog on how to do that in a Delegable manner). I have also used the Len() function below as it covers both Blank and empty string "".
So firstly, the normal approach would be

Filter(
   Devices,
   Len(cbType.Selected.Result) = 0 || cbType.SelectedItems in DeviceType
)

which of course is not Delegable and will get the appropriate warning (and will only action the first xxxx records up to your limit).

We can however take another approach here - ForAll can contain a Delegable filter and action this filter for each item. So, the first thing we can do is

If(
   Len(cbType.Selected.Result) = 0,
   Devices,
   ForAll(
      cbType.SelectedItems As aSel,
      Filter(
         Devices,
         DeviceType = aSel.Result
      )
   )
)

However, when you do this, you will receive one record for each selected item, each with one Table field called Value, so you need to expand these using Ungroup.

If(
   Len(cbType.Selected.Result) = 0,
   Devices,
   Ungroup(
      ForAll(
         cbType.SelectedItems As aSel,
         Filter(
            Devices,
            DeviceType = aSel.Result
         )
      ),
      "Value"
   )
)

You will now have the filtered Table required and no Delegation warning. It is not a total "magic bullet" as there is a "hidden" delegation limit on the output of both Ungroup and ForAll. However, if your matching items are less then this number, it should work for you.

But what about other filters ? You can take two approaches to this - an example I want to only show Devices records where the Price is greater than $1,500.

You can "pre-filter" if you are sure the first filter in the With() statement is going to return less than your Delegation limit.

With(
   {
      wList: 
      Filter(
         Devices,
         Price > 1500
      )
   },
   If(
      Len(cbType.Selected.Result) = 0,
      wList,
      Ungroup(
         ForAll(
            cbType.SelectedItems As aSel,
            Filter(
               wList,
               DeviceType = aSel.Result
            )
         ),
         "Value"
      )
   )
)

Another approach is to include the other filter/s in two options

If(
   Len(cbType.Selected.Result) = 0,
   Filter(
      Devices,
      Price > 1500
   ),
   Ungroup(
      ForAll(
         cbType.SelectedItems As aSel,
         Filter(
            Devices,
            DeviceType = aSel.Result && Price > 1500
         )
      ),
      "Value"
   )
)

There are of course other possible combinations here with additional filters, but this structure should give you something to start with.