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
),
Value
)
)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.
14 Comments
Mario
Interesting and useful. Is there a way to make this work with the two-field ComboBox? I am guessing I need to replace Distinct with GroupBy perhaps.
Warren Belz
That would be a good approach – I have not tried this however.
Kim M. Williams
Once I select the value from the ComboBox, I need to get other fields from my secondary list to populate other fields on my form. How would I pull over those other fields that I need?
Warren Belz
Hi Kim,
You you do not need Distinct, then remove that from the bottom filter and you will get all fields. If you do need Distinct, you will have to do a separate Lookup outside the filter to retreive the matching record field
Deo L. Cayanan
Do I still need to use the With(), or would the below work?
SortByColumns(
Distinct(
Filter(
Datsource,
StartsWith(
Title,
Self.SearchText
)
),
Title
),
“Value”
)
Warren Belz
Yes – that should work
JD
Great tip!
One thing, I have removed the DISTINCT filter so that I can retrieve all columns but now it does not seem to work correctly. For example, I am searching a TITLE column and I know there is an entry called “Joel”. However when I search for “Joe” it doesn’t appear?
Warren Belz
Hi Justin,
Using the values from my example post – I ran this here
With({
_Start:
Filter(
Aircraft,
StartsWith(
Airline,
Self.SearchText
)
)
},
Sort(
_Start,
Airline
)
)
and it performed as expected on a list with 10k items
CE Lee
what if my distinct values are over 2900 items?
Warren Belz
If you have more than 2,000 records starting with the first letter the user types in, they will need to enter the next letter (this is noted in the blog)
Warren Belz
Type in another letter – you would have over 2,000 starting with the first two letters
Renan
would it be possible to replace “_start” with something like “contains” to be able to use part of the center of the string in the search?
Warren Belz
That is the whole problem – Search (contains) does not work on large data sets with some data sources. StartsWith is a workaround to get the numbers down to what the user is able to select from. If it worked (contains) on large sets, you would not need this code at all.
Rib
Thanks, helps me a lot
My Combobox didn’t show all Values with the DISTINCT Funktion for 2600 records.
With first Letter-input it’s works fine for me.