Skip to main content

Notifications

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.

With(
 {
 _Start: 
 Filter(
 Aircraft,
 StartsWith(
 Airline,
 Self.SearchText
 )
 )
 },
 Sort(
 Distinct(
 _Start,
 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.

 

Comments

*This post is locked for comments

  • mpunch86 Profile Picture mpunch86 6
    Posted at
    Delegation workaround - Distinct values for a large list field in Combo Box Items

    this was very helpful to me! thanks!

  • moerah Profile Picture moerah 295
    Posted at
    Delegation workaround - Distinct values for a large list field in Combo Box Items

    @WarrenBelz thank you this is amazing 

  • MarcelLehmann Profile Picture MarcelLehmann 376
    Posted at
    Delegation workaround - Distinct values for a large list field in Combo Box Items

    @WarrenBelz 
    yes i mean groupby as distinct.

    u can use also this - load all data in a collection
    Clearcollect(colData,
    groupby(
    Ungroup(
    ForAll(
    ShowColumns(Ungroup(DropColumns(AddColumns(AddColumns(Sequence(26,1,1),"FirstStage",Char(Value+64)),"SecondStage",AddColumns(AddColumns(Sequence(26,1,1),"Second",Char(Value+64),"Third",FirstStage),"Total",Third&Second)),"Value"),"SecondStage"),"Total"),
    Filter(DATA,StartsWith(Name,Total))),"Value")),ChooseGROUPCOLUMN,"TEMP")


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

  • WarrenBelz Profile Picture WarrenBelz 145,434
    Posted at
    Delegation workaround - Distinct values for a large list field in Combo Box Items

    @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.

  • MarcelLehmann Profile Picture MarcelLehmann 376
    Posted at
    Delegation workaround - Distinct values for a large list field in Combo Box Items

    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.

  • Bryant Boyer Profile Picture Bryant Boyer 106
    Posted at
    Delegation workaround - Distinct values for a large list field in Combo Box Items

    Very creative, excellent work!

  • Zaki89 Profile Picture Zaki89 13
    Posted at
    Delegation workaround - Distinct values for a large list field in Combo Box Items

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