Answered

Filter table or use a view?

Like (0) Share

Report

Posted on 4 May 2022 07:06:24 by Community Power Pla...

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:

Connectors

Connecting to Data

All responses (2)

Answers (2)

Verified answer

timl 35,118 Super User 2025 Season 1 on 04 May 2022 at 08:38:24

Like (1)

Report
Re: Filter table or use a view?

@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>)
Verified answer

WarrenBelz 147,341 Most Valuable Professional on 04 May 2022 at 07:13:12

Like (1)

Report

Re: Filter table or use a view?

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