Data,  Delegation

Distinct values for a large list field in Combo Box Items

This idea is an extension of a workaround in Combo Boxes dealing with large lists and the Delegation issues surrounding these and the Distinct function. I have tested this on a large list (30k items) and it works perfectly.

Two caveats here – the number of items displayed in your combo box and the number of filtered items subject to the Distinct function (after the leading letters are entered) will both be subject to your Delegation Limit, but these should not generally be an issue.

This example is using a big list Aircraft with a field Airline. The idea is to start typing the Airline name and all Distinct values starting with the input will then be avalable for selection in the Combo Box. There are two “tricks” here – one is to use the Combo Box SearchText to limit the fields available and then avoid the Delegation issue using a With() statement to “pre-filter” the output subject to Distinct.
After that, the code is quite straight-forward.

With(
   {
      wStart: 
      Filter(
         Aircraft,
         StartsWith(
            Airline,
            Self.SearchText
         )
      )
   },
   Sort(
      Distinct(
         wStart,
         Airline
      ),
      Result
   )
)

So at the top, it is grabbing all the field names starting with the Combo Box Search input – StartsWith() is Delegable, so this will work on any sized list, then providing the output of this (the user may have to type in a few more letters at times) is under your Delegation Limit, the Distinct function then operates (locally) and is not subject to Delegation.

NOTE: You need to turn Allow Searching back on (it will automatically disable) in your Combo Box. You may also get a Delegation warning here, but you can ignore it.

Leave a Reply

Your email address will not be published.