Delegation Management – Multi-Choice Combo Boxes

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.Value) = 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.Value) = 0,
   Devices,
   ForAll(
      cbType.SelectedItems As _Sel,
      Filter(
         Devices,
         DeviceType = _Sel.Value
      )
   )
)

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.Value) = 0,
   Devices,
   Ungroup(
      ForAll(
         cbType.SelectedItems As _Sel,
         Filter(
            Devices,
            DeviceType = _Sel.Value
         )
      ),
      "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(
   {
      _List: 
      Filter(
         Devices,
         Price > 1500
      )
   },
   If(
      Len(cbType.Selected.Value) = 0,
      wList,
      Ungroup(
         ForAll(
            cbType.SelectedItems As _Sel,
            Filter(
               _List,
               DeviceType = _Sel.Value
            )
         ),
         "Value"
      )
   )
)

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

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

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

Many to Many filter

Now we will look at the scenario that this is being filtered on a Multi-Choice field. Depending on the data source, this may not be Delegable as the in operator is necessary, and also ForAll and Ungroup have lesser but existing limitations.
A gallery can be presented with a single filter searching for any item selected in the Combo Box existing in any of the values contained in the multi-choice field in the data source.
To do this, the ForAll() operator is needed to create a Table filtering the data source field for each of the selected values. This process results in a series of records that are themselves Tables with a single field Value, so Ungroup() is used to resolve the final list.
Also, allowance has been made for no entry in the Combo Box.

If(
   Len(cbType.Selected.Value) = 0,
   Devices,
   Ungroup(
      ForAll(
         cbType.SelectedItems As _Sel,
         Filter(
            Devices,
            _Sel.Value in MultiChoiceField.Value
         )
      ),
      "Value"
   )
)