Unanswered

Countrows Operation warnings, delegation and solution to minimize the number of rows from SQL server

(0) Share

Report

Posted on by kkandral

Hello Community,

I'm trying to get more than 2.000 records into PowerApps and I'm trying to decrease the number of every screen using let's say Projects and users (user()) on a filter. Then we're all good because no one Seller has more than 100-150 records to retrieve in real life.

I'm using an SQL Table and I receive the message of delegation warning when I'm trying to get the number of rows in advance when I'm trying to get data from the source. That's fine!!

I'll decrease/kill the number of active records (open cases that need to be retrieved) every day from the SQL using agent and Store Procedures e.g the completed cases. Still, if a seller will have more than 500 open cases (of course I will not fire him for postponing to-does) I should be happy to get the latest regarding the date 500 records and not to let SQL mechanism and gateway to choose the top or whatever 500 or 2000 records to Power Apps.

Question A: Can I control what "delegated" data will be eligible to be imported/linked at PowerApps? Let's say from biggest recID to smaller or with "where" functions

Question B: Can I use multiple small tables for every user() and then determine for user A --> use the TableA for userB-->use the tableB something like @dbo.tableXXX wildcard? (this is silly because no one can handle 200 tables at once and then even 200 union all )

Thank you

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Joel CustomerEffective 3,224 on at

Like (0)

Report

The easiest thing to do is use clear collect and bring your items into a collection and then count the items in a collection. You can use the collection as the items for your gallery rather than the table directly And won't have the delegation issues when filtering by user

You can't control which records are delegated, it just will get the last 500-2000, but if you collect all of your records, you can then filter the collection

Was this reply helpful? Yes No
GarethPrisk 2,828 on at

Like (0)

Report

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

https://docs.microsoft.com/en-us/connectors/sql/

The SQL connector is a delegable connector. In most cases, you will be able to get the records you need without being subject to delegation.

It is important to note how it applies. You could use Search, for example, to search for >2000 records and return the delegated results accurately. However, attempting to Collect >2000 records will not work.

You indicated a User may have ~150 cases, so you could accurately Collect a Filtered list of records.

ClearCollect ( colName, Filter ( sqlSourceName , srcColumn = validSearch ) )

If you were trying to show a report, let's say a list of Cases per Users, and you had 100 Users with 100 Cases (10,000) total cases. You could simply do an Aggregate query to get the table of Users and Case counts. Then to show Case Details, collect that Users cases and show them separately.

You can do paging, if you have a row id (or reliable way to divide records into chunks of 2k or less), but do you really need all that data in the app? Sometimes, yes. However, collections can become memory heavy.

Was this reply helpful? Yes No