cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Dropdown not loading all values from table

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.

Prabhu_Rajaram_1-1669518513361.png

 

Prabhu_Rajaram_0-1669518454623.png

 

13 REPLIES 13
Drrickryp
Super User
Super User

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

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)

Prabhu_Rajaram_0-1669519311945.png

 

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

Drrickryp
Super User
Super User

@Prabhu_Rajaram

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

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
Super User
Super User

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. 

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
Super User
Super User

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

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

Prabhu_Rajaram_1-1670773217664.png

 

Drrickryp
Super User
Super User

@Prabhu_Rajaram 

Is your client list a static excel table?

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,862)