Dropdown not loading all values from table

[Prabhu_Rajaram]

I have created a tool in which i have cascading dropdown fields. in each fields user can select the options based on the previous field selection. and i connected the primary field with table it contains more than 10K unique records from more than 80K records (this could be increase more in future). but the problem is the primary field is not loading all the records from the table.

Below is the formula i used in Items property of the primary field.

Distinct(Query1,'Client Name')

- where Query1 is the name of the table, and 'Client Name' is the column from the table.

Is there any workaround to pull all the records from table?

------------------------------------------------------------------------------------------------------------------------------------------

Below is the formula i used in 2nd field to lookup the Engagement name from table, based on the selection from previous field.

[Drrickryp]

@Prabhu_Rajaram

What datasource are you using? 

Distinct() is not delegatable so your formula cannot work. You will need to create a separate table of unique values for the Items of the first combobox. 

[Prabhu_Rajaram]

I have tried to connect with different data sources like SQL database, Excel Online. but due to some access restriction it's not going well. so, I use Static Excel loaded in power apps (shown below)

This sheet will have multiple columns which used to lookup the value to the other fields.

[Drrickryp]

@Prabhu_Rajaram

With static Excel, your app will be read only. You will not be able to make modifications or edit data. 

[Prabhu_Rajaram]

yes exactly, but i have no other option other than manually reload the excel, until i get access to the other options.

i would really like to check if there any workaround to load all the distinct values from the excel table to the dropdown field?

[Drrickryp]

Use power query to create a single column table of unique client names. Sort it and import it to SharePoint. Use SharePoint for your other tables. You will be able to filter them using the client table as the items for your combobox. 

[Prabhu_Rajaram]

i have done so, i created excel table using power query to get the unique values and linked the table in powerapps. totally i have more than 11K unique values though dropdown not loading all of it.

[Drrickryp]

@Prabhu_Rajaram 

Dropdown controls are limited to the number of records they can display. Anyways, no one has the patience to scroll down 11k names.

Put a textinput box above the dropdown and make the items property of the dropdown Filter(table1, StartsWith('Client Name',TextInput1.Text)

[Prabhu_Rajaram]

great thanks for the response. but when i get the below error.

[Drrickryp]

@Prabhu_Rajaram 

Is your client list a static excel table?