Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Frederick Luk]

Hi all,

I have a view (‘[dbo].[retail_data]’) in a SQL server database. It returns 2295 rows.?? One of the column is “City”. When I run this “Distinct(‘[dbo].[retail_data]’,City)” for “items” for a Drop Down box, I got 4 values returned in the drop down. While in fact I have 15 distinct values for “City”. What could be wrong ? Is there a limit for the number of items in a drop down list ?

Thanks in advance,

Fred Luk

[Warren Belz]

Hi Fred,
The Distinct() Filer is not Delegable, which means it will return the first 500 (you can increase to 2000) records for client-side processing, so you will only get the Distinct values from this data set.
Unfortunately, this requires some work-arounds/data management. I generally use reference lists (which normalizing would use anyway) for all items I need to use as drop-down filters.

——————————
Warren Belz
Rockhampton Qld Australia
+61 409 315 509
——————————
——————————————-

[Frederick Luk]

Hi Warren,

Thank you for your response. I tried to change the the number of returned record to 2000. I understand that I have to log in to make.powerapps.com and click on Solutions on the navigation panel on the left to go through multiple steps to make the change. But it says there is no solution in my environment and I have to create a new environment ! My app is in my default environment. I guess I just have to figure out some other ways to get the data.

Thanks,
Fred

——————————
Frederick Luk
President
Santa Clara CA
4088001224
——————————
——————————————-

[Warren Belz]

OK Fred,
You can do a multiple collection stages if you can group them with delegable filters in batches of less than 2000 then run Distinct on this (which is does not require Delegation as all processing is client-side).

——————————
Warren Belz
Rockhampton Qld Australia
+61 409 315 509
——————————
——————————————-