Delegation for Sum, Average, Min, and Max functions on SQL Server
The newest update for PowerApps (version 680) includes an important new capability: the delegation of aggregate functions!
Delegation refers to pushing a data operation to the source. PowerApps is capable of many operations on data, but first the data must be downloaded to the device or browser. Let’s say you need to work with 1 million records. Downloading all that data in order to search, sort, and calculate across it would take prohibitively long, especially over a cellular network. It is far more efficient to push the work to the cloud or on-premise server that can much more easily work with all that data.
In this release you can now delegate Sum, Average, Min, and Max functions to SQL Server.
Want to see it in action? Connect to your favorite SQL Server and run the following script. Do you remember the old adage: start with a penny, double it every day, and within a month you will be a millionaire? This script does just that: repeatedly doubles a table 20 times until it contains 220 rows or 1,048,576 rows. The LongNumber column increments with each row giving us something to aggregate. This script takes about 5 minutes to run on a modest SQL Azure instance.
CREATE TABLE LongTable ( LongNumber BIGINT IDENTITY (1,1) PRIMARY KEY, ShortNumber BIGINT );
INSERT INTO LongTable ( ShortNumber ) VALUES ( 1 );
DECLARE @i INT; SET @i = 1; WHILE @i <= 20
BEGIN
INSERT INTO LongTable SELECT LongNumber AS ShortNumber FROM LongTable
SET @i = @i + 1
END;
Connect to this table with PowerApps and add a Label control with a Text property set to:
Sum( ‘[dbo].[LongTable]’, LongNumber )
After a few seconds of watching ants race across the top of the screen the answer appears in our label: 549,756,338,176. We know the sum of the numbers from 1 to n is n(n+1)/2 and sure enough that checks out.
Aggregate functions aren’t limited to just whole tables. We can compose Sum with Filter to operate over a selected portion of the table. For example, let’s change our app to skip the first 100,000 rows:
Sum( Filter( '[dbo].[LongTable]', LongNumber > 100000 ), LongNumber )
Based on our math formula, the sum of numbers to 100,000 is 5,000,050,000. Sure enough, if we subtract this from our previous result we match the correct answer shown in the label: 544,756,288,176. All within a few blinks of an eye.
Ok that’s swell, math is always fun, but what about a practical use?
Let’s take an example from the trusty Adventure Works sample database. We’ll look at two tables:
- SalesOrderHeader contains all the details about an order in a single row and includes a primary key field SalesOrderID.
- SalesOrderDetail contains all the line items for a particular order. A set of these are associated with a SalesOrderHeader through a 1-many relationship, using a foreign key field SalesOrderID. For example, if an order contains an Orange, an Apple, and a Pear, then there would be 3 SalesOrderDetail rows associated with the order.
In our app, we’d like to display a list of orders and with each the total of the detail records for each order. The first part is familiar: create a new app, connect to the SQL database, add a Gallery control to a screen to display the SalesOrderID field:
This next part is new. For the Subtitle1 field of the Gallery, we will use a combination of the Filter and Sum functions. The filter will limit our summation to just the detail records for this order, by matching the foreign key of SalesOrderDetail (SalesOrderID in the formula, part of the filter’s record context) with the primary key of SalesOrder (ThisItem.SalesOrderID in the formula). The filtered result is then added up and the result shown in our gallery:
And just like that we are preforming a grouped sum on a related table.
You will notice that no blue dots appeared in this article. The examples given here are only limited in scale by what SQL Server supports and that is very large. But unfortunately, that doesn’t mean there aren’t any blue dots left to be found. Not to worry, we are far from done, more delegation is on the way.
Al always, the What’s New page chronicles what has been added with each PowerApps release. Have feedback for us? We would love to hear from you - please post in our forums! We are always actively listening, learning, and responding to your comments and questions. If there’s a feature you’d like to see in an upcoming release, please submit your idea to us!
Happy delegating!
-PowerApps Team