cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alienvolm
Advocate II
Advocate II

Dropdown list not showing all values from DBO

Hello, 

 

I have a dropdown list in my app that displays the distinct values from a collection. The collection simply takes the values from a SQL database.

 

Some users have noted that they can find some rows (we are searching by BatchName) only with the search bar, but the same BatchName is not shown in the dropdown list. See the example below with batch Pilot Plus_07072021_Addition: we can see the two records associated to that batch in the gallery below by entering the batch name in the search bar, but the batch will not show anywhere in the dropdown list. 

 

PilotPlus.png

Alienvolm_1-1625839859818.png

 

 

This is the code for my Screen | OnVisible property: 

 

ClearCollect(collectBatches, {Result: "All"});
Collect(
    collectBatches, 
    Distinct('[dbo].[JourneyUser]', BatchName)
)

 

 

And my dropdown simply calls the collection in the Items property:

 

collectBatches

 

 

Any suggestions? This is very important as any other function in the app works with the list in the dropdown. 

 

Thanks in advance! 

 

~Alienvolm

1 ACCEPTED SOLUTION

Accepted Solutions

@Alienvolm 2K is the max. You have 2 options to solve this,

  1. Filter at the source (the better option).
  2. Collect the data into collections in batches of 2K and combine them into one big collection and use that in the Items property of the Combobox.

From a design perspective I don't think it would be ideal for users to go through a list of 2200 records to find something.

 

Please remember to give a 👍 and accept the solution as it will help others in the future.

View solution in original post

8 REPLIES 8
CNT
Super User
Super User

@Alienvolm Do you have more than 2K records in the database? If so this could be a delegation issue.

Drrickryp
Super User
Super User

@Alienvolm 

Put

ClearCollect(
    collectBatches, 
    Distinct('[dbo].[JourneyUser]', BatchName)
)

in the OnVisible property of the screen with the dropdown and set the Items property in the dropdown to Search(collectbatches, TextInput1.Text, "Result")  

  

zmansuri
Super User
Super User

Distinct does that some times. In my opinion it's not reliable. I have 2 apps they have same schema. In one of them distinct show all the values. In another it just doesn't show some values. I had to add search bar because of that. Tried posting here once or twice. didn't fix anything. Just replying to get notification if someone answers this.

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


Was I helpful? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. It will help other users to find it faster.

follow me on Twitter @zmansuri123,
connect with me on LinkedIn from Here

Hi @CNT, I just checked, and yes... we have hit 2200 records in the DB. Is there anything that I can do to extend that threshold? 

 

Otherwise I will have to filter them somehow...

 

Thanks for the hint! 

 

~Alienvolm

@Alienvolm 2K is the max. You have 2 options to solve this,

  1. Filter at the source (the better option).
  2. Collect the data into collections in batches of 2K and combine them into one big collection and use that in the Items property of the Combobox.

From a design perspective I don't think it would be ideal for users to go through a list of 2200 records to find something.

 

Please remember to give a 👍 and accept the solution as it will help others in the future.

Thanks @Drrickryp

This  just proves that the BatchName doesn't make it to the list: 

Alienvolm_0-1625842475936.png

There are no batches with that name. 

@Alienvolm 

As @CNT says at point one, the most reliable thing to do is to let server do the job. So, use a view in SQL server to prepare the distinct values and in app use this view as source of your dropdown. 

Thanks, will do! 

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.

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,195)