Answered

Filter table or use a view?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi all,

I currently have a working PowerApp that can read and write to a single SQL Server table. It works fine, but the issue I'm having is that the table has 500,000+ records, and I only need a subset of those records (approx 200 at any one time based on a column value - ie. Status = 'Open').

A few questions below:

Is there a way within PowerApps to filter the SQL Server table to only retrieve the records I need? My understanding of PowerApps is it will pull any available records from the table up to the data limit (set to 2000 in my case - ie. max setting), and then allow me to filter on the records it has pulled. But I believe that won't help me. Please correct me if my assumption is wrong.
I can create a view in SQL Server to filter out only the records I need and then connect that view to my PowerApp. But how would I then handle write-back to the underlying table? Is there a clean approach to accomplish this?

Thanks.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (2)

Sort by

Verified answer

WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Anonymous ,

The 2,000 limit only applies to non-delegable queries - I have a blog on Delegation and although referring to SharePoint, the same principles apply except SQL has less limitations.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Verified answer

timl 37,283 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous

>> My understanding of PowerApps is it will pull any available records from the table up to the data limit (set to 2000 in my case - ie. max setting)

This is only true of non-delegable expressions. The formula beneath is delegable, meaning that Power Apps will retrieve all target records correctly.

Filter(YourTable, Status="Open")

In answer to your second question, if you were to create a SQL Server view, you would make sure to return the primary key value in your view.

In a typical app, you would set the data source of a gallery control to the view. When a user selects a record from the gallery, you would retrieve the selected primary key value.

On an edit form, you would set the data source to the underlying table. The important difference here is that you would then set the item property of the form to look up the selected record from the underlying table using the selected primary key value from the view. The formula for the item property would look like this.

LookUp(YourTable, PKColumn=<PKValueFromGalleryControl>)

Was this reply helpful? Yes No