Cascading combo boxes with large data set (airports)

[NickiPT]

We have a data standard list that contains airport information and has a total of 9,000+ entries (source: SharePoint). This list contains the country, the state/territory (if United States), the city, the airport name, and the airport IATA code (along with some other columns).

I created cascading combo boxes for the country, state (if United States), and city which each use "Distinct" in their formulas to pull back the information, and this works fine. All of these fields submit to plain text columns. If anyone is curious, here is the Items formula for the City combo box:

If(
    cbCountryFlightDeparture.Selected.Value = "United States of America (the)",
    Sort(
        Distinct(
            Filter(
                'ADC-Airport Code',
                cbCountryFlightDeparture.Selected.Value = "United States of America (the)" And ASSOCIATED_STANDARD_STATE_TERRITORY_US_EXTENDED_Name = cbDepartureState.Selected.Value
            ),
            AIRPORT_CODE_CITY_NAME
        ),
        Value,
        SortOrder.Ascending
    ),
    Sort(
        Distinct(
            Filter(
                'ADC-Airport Code',
                ASSOCIATED_STANDARD_COUNTRY_NIEM_Name = cbCountryFlightDeparture.Selected.Value
            ),
            AIRPORT_CODE_CITY_NAME
        ),
        Value,
        SortOrder.Ascending
    )
)

The problem is with my Airport Code combo box. I wanted this to be a double value combo box to display both the airport code and the airport name, so the user is comfortable knowing that they are picking the right airport, like this:

BWI

Baltimore-Washington International Airport

The value that gets updated to the list is just the IATA Code (in this example, "BWI").

However, I'm running into an error because the data source is too large, and the only way I can get around it is to add "Distinct" into the Items property - which appears to mean that I can only pull back either the IATA Code or the airport name. 

This is what I want the formula to be:

If(
    cbCountryFlightDeparture.Selected.Value = "United States of America (the)",
    Sort(
            Filter(
                'ADC-Airport Code',
                cbCountryFlightDeparture.Selected.Value = "United States of America (the)" And ASSOCIATED_STANDARD_STATE_TERRITORY_US_EXTENDED_Name = cbDepartureState.Selected.Value And AIRPORT_CODE_CITY_NAME = cbDepartureCity.Selected.Value
            ),
            AIRPORT_CODE_IATA_CODE
        ),
        SortOrder.Ascending
    ,
    Sort(
            Filter(
                'ADC-Airport Code',
                ASSOCIATED_STANDARD_COUNTRY_NIEM_Name = cbCountryFlightDeparture.Selected.Value And AIRPORT_CODE_CITY_NAME = cbDepartureCity.Selected.Value
            ),
            AIRPORT_CODE_IATA_CODE
        ,
        SortOrder.Ascending
    )
)

But it has the "data source is too large" error. (Before the data source had been fully populated with all airports and only had 4,000 or so items in it, this worked great.)

Isn't filtering what you're supposed to do in order to only return a small subset of records? The result would be 1 or 2 airports at most that exist in a single city in a specified country!

@WarrenBelz I found your blog post (https://www.practicalpowerapps.com/delegation/distinctcomboboxitems/) but can't figure out how to apply it to this specific scenario. I am afraid that using "StartsWith" could result in me returning airports that exist in cities of the same name but that are in different states or countries...

Or maybe the answer lies in collections?? I just can't figure it out. Thanks in advance for any guidance anyone can provide!

[BrianKennemer]

I had a similar issue with a large dataset and what I ended up doing was putting a very small textbox just below the dropdown that was displaying the full name of the value that was selected. 

not EXACTLY what you want but it will provide the feedback for the user without having to spend hours debugging your formula. 

[WarrenBelz]

Hi @NickiPT ,

I think you really only need this

With(
   {
      _Start: 
      Filter(
         'ADC-Airport Code',
         ASSOCIATED_STANDARD_COUNTRY_NIEM_Name = cbCountryFlightDeparture.Selected.Value &&
         StartsWith(
            AIRPORT_CODE_CITY_NAME,
            Self.SearchText
         )
      )
   },
   Sort(
      _Start,
      AIRPORT_CODE_CITY_NAME
   )
)

and then you can display both fields in the Combo Box

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[NickiPT · Accepted solution]

@BrianKennemer and @WarrenBelz - thank you for your responses! @WarrenBelz - your solution ended up allowing me to show double values without receiving a delegation error but it showed me ALL airports (I think all U.S. airports, but I just know that it definitely wasn't filtered down by city). So I just had to add in one additional section to the filtering formula (AIRPORT_CODE_CITY_NAME = cbDepartureCity.Selected.Value &&) before the "StartsWith" line and now it works!

With(
   {
      _Start: 
      Filter(
         'ADC-Airport Code',
         ASSOCIATED_STANDARD_COUNTRY_NIEM_Name = cbCountryFlightDeparture.Selected.Value &&
         AIRPORT_CODE_CITY_NAME = cbDepartureCity.Selected.Value &&
         StartsWith(
            AIRPORT_CODE_CITY_NAME,
            Self.SearchText
         )
      )
   },
   Sort(
      _Start,
      AIRPORT_CODE_CITY_NAME
   )
)

 Thanks again for your help!

[WarrenBelz]

@NickiPT ,

Sorry a bit lost here - this is the city code combo box so how can it filter using itself ?