Unanswered

lookup values for dropdown field

(0) Share

Report

Posted on by ahhollan

576

I have a dropdown on my form that looks up the available values from a SharePoint list. The list has values with TPS Regions as a column and TPS Sites as a column. There is a one to many relationship between TPS Region and Sites. When I enter the items property in the dropdown field, it only returns the first 4 Regions. The SharePoint list has approx. 1500 records. Is the list too large to return all possible Regions? My formula is:

Distinct(Locations_Buildings,'TPS Region')

Thanks.

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

cds 1,001 on at

Like
a
(0)

Report
Copy link

Link copied!

change limit in app settings from 500 rows to 2000
Joel CustomerEffective 3,224 on at

Like
a
(0)

Report
Copy link

Link copied!

the issue is Distinct is not delegatable.
so by default it is just getting the values that exist in the most recently created 500 records.

You can raise the delegation limit to up to 2,000 records, so if you do that you should get the values from all 1,500 records.
but if it gets to be more than 2,000 records you will need to design it differently.

are site and region lookups? I would create a list for region and a list for site, then add lookups to region and site on your main table.
that way you can just get the list of regions and sites from those lists, not using distinct.
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ahhollan ,

I agree with @jlindstrom 's thought almost. The Distinct function is not delegable function in PowerApps, in other words, you could only process first 2000 records locally at most.

More details about the Delegation issue in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

If the amount of your SP List records is not more than 2000, please consider set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of app, then re-try the Distinct(...) formula again in your app, check if the issue is solved.

If the amount of your SP List records is more than 2000, please check and see if the thread solution I provided below could help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-get-combobox-to-show-all-the-results-from-SP-list/m-p/612204#M193064

Please try above formula, check if the issue is solved.

Best regards,