Showing results for 
Search instead for 
Did you mean: 

Delegation workaround - 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.


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.



Thank you. This saves my life. Not all items are scrollable but searchable. Great work.  

Very creative, excellent work!

I like the solution but would use groupby. Then u can reference later to the Data of selected item.


As you mentioned the user must put more letters in to be under the Delegation limit.


I prefere getting the distinct data from Powerautomate or sync the distinct to an extra table before.

@MarcelLehmann ,

GroupBy() is a hidden Delegation limit as well - it will only Group the first (limit) items. If you are meaning instead of Distinct (still using the SearchText and With() statement), then yes that will do the same thing.

yes i mean groupby as distinct.

u can use also this - load all data in a collection

with the inner function u have 676 rows and each can have 500 (to 2000) rows 😉