dropdown/listbox/combobox not showing all values.

[Anonymous]

Hi,

I'm using dropdown to select value,unfortunately it's not showing all the field values from the table.

Thanks

[mdevaney]

@Anonymous 

You need to supply more information for the forum to help solve your problem.  Make sure to include:

• The code you are using in the Items property of the control
• Tell us exactly what control you are using.  Each of dropdown/listbox/combobox behaves differently.
• What datasource you are using
• Take a screenshot

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

Thank you for the reply.

I'm using Azure SQL table as Data source and dropdown coltrol.

Distinct(Tariff,Effectivedate) code used in dropdown Items property.

[mdevaney]

@Anonymous 

How many records are in your Tariff table?  The DISTINCT function is not delgable which means that it will only look at the first 500 records (or 2,000 if you have increased the delegation limit in advanced settings) and return the unique values.  It will not consider any rows past the 500th, nor will it return them in the result set.

[Anonymous]

thank you,

I was tryed with both delegations limits ,when delegation is 500 ,the number of dates on dropdown 6,when delegation limit 2000 then number of dates were 15,the actual table contains 23 distinct dates.

[mdevaney]

@Anonymous
Thank you for proving my theory. You have more records in your table than the delegation limit allows so DISTINCT cannot be used here. Instead, you must find some other way.

[Anonymous]

Thank you,

what is the alternative way for dates especially,please suggest.

[mdevaney · Accepted solution]

@Anonymous 

The general rule is: when you cannot avoid delegation you must build a the information into your datasource in advance.

I think @timl put it best with his forum post:

"""A workaround is to create a SQL Server View that returns the distinct [Product_Name] values in your table. You can then add this data source to your app and set the items property of your combo box to your view.""

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[timl]

Hi @mdevaney 

Thanks for the mention 🙂

In this case, I agree that using a view is the best way to do this.

[mdevaney]

@timl 

Just wanted to give credit where it was due.