Delegation of Aggregate functions for CDS
Sum, Min, Max, and Average functions in Canvas apps are aggregate functions that can reduce a large amount of data down to a single value. For example, Average could be used to find the average age of a class of students using Average( Students, Age ).
These functions can also be used with the Filter function to select the records to be aggregated. You could find the average age of 3rd graders with Average( Filter( Students, Grade = 3 ), Age ).
Until now, aggregate functions have been limited to operating on 500 up to a maximum of 2,000 records at a time. They were also inefficient as they needed to bring all the data down to the device to perform the operation.
I am delighted to announce that Canvas apps can now delegate aggregate functions for the Common Data Service for Apps. Your new limit is 50,000 records and the operation is performed at the source with only a single number coming down to the client. If you have really large entities, you can filter down to 50,000 records before aggregating.
For example, I have a CDS entity that contains a million records, each with a consecutively numbered decimal field. I’m using the Filter function to select the records from 900,000 through 949,999. You can do this exercise with integers and other data types as well, but you will overflow 4-byte integers with large sums. The “Calculated” column for Sum uses the formula n*(n+1)/2.
You will notice that there are no yellow bangs (or blue dots). All the delegation warnings are gone as these operations are pushed to CDS to perform.
To access this functionality, you will need to turn on the CDS experimental connector:
In the next few days we will be renaming and turning this switch on by default for new apps, in some regions you may already see this change. The switch will now be listed in the Preview instead of Experimental section:
Happy aggregating! As always, comments and feedback are most welcome.