Answered

Dropdown not updating with changes to database

(0) Share

Report

Posted on by baumana

Hi All, I have what seems like a straightforward problem, but I can't figure it out (I'm quite new).

I've got a dropdown as part of a larger form, which gets captured later and sent to my SQL database. The available options are given just by doing "Distinct('[dbo].[material]', category)" to offer the available categories. It mostly works. The problem is, when I add materials in the database (from the back end, not in my interface) with brand new categories, they never show up in the dropdown. They DO show up just below where I have search functionality.

It's got me really scratching my head as to why database changes don't reflect here. Some cache behavior? I've refreshed using Refresh() and a couple other things, but no dice.

Thanks for the help.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@baumana

The DISTINCT function is to blame here. DISTINCT cannot be delegated so it only looks through the first 2,000 rows of the database and finds the unique values within that range.

Since you are using SQL you could likely create a SQL view to find the unique values and read that into your dropdown instead. The only downside of using SQL views is you cannot write data to them in PowerApps only read. But for your use case that would not likely be a problem.

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

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @baumana ,

How many records existed in your '[dbo].[material]' table? More than 2000?

Actually, it is an known issue within PowerApps -- When you made some changes into your data source, the changes would not take effect immediately within your canvas app. You need to refresh the data source manually within your app to apply the changes into your app or execute the Refresh() function to refresh data source in your app.

In addition, the Distinct function is a Non-Delegable function in PowerApps. In other words, this function could only process 2000 records at most from your SQL Table. Please make sure you have set the "Data row limit for Non-delegable query" option to maximum value -- 2000 within Advanced settings of App settings of your app.

In order to get rid of the Delegation issue within your app, please consider create a SQL View based on your '[dbo].[material]' table, then add this SQL View as data source in your app. Then set the Items property of the Category Dropdown to following:

Distinct('[dbo].[materialSQLView]', category)

Note: The SQL View would not be subject to the Delegation issue limits

Best regards,

Was this reply helpful? Yes No
baumana 4 on at

Like
a
(0)

Report
Copy link

Link copied!

I've got just over 500 rows (currently) in my database, so new entries were beyond the delegable limit. Nothing like getting bit by something to really learn it!

Anyway, creating a view and just referencing that was a perfect and easy fix.

Thanks all!

Was this reply helpful? Yes No