In our last post within this series we have provided an introduction to the Delegation topic in PowerApps: https://adat.is/2naJylO.
In this blog post, we will be going in more detail on the Delegation topic in PowerApps, to explain better through an example.
In fact, delegation can be understood better in this clip:
Here I have put up a quick PowerApps screen, split in 3 swimlanes – each showing a different type of list. The scope of this is to focus on the Delegation concept itself, and hence some best practices have not been taken into consideration.
The first (far-left) is a Gallery being loaded directly from the Product List table in SQL, and filtered on the choice of the Product Type selected from the Type Filter dropdown just above it. The command for this Gallery is:
Filter('[dbo].[Ori Product List 1]', Type = Dropdown2.SelectedText.Result)
Hence this Gallery will be based on a delegatable query.
Here is a screenshot of the table contents in SQL, which has 9905 rows:
In PowerApps there is the “Load Collection” button, which will truncate an existing collection named TypeList and re-populate it using the ClearCollect() command from the SQL table with no filters. In order for the query to be deterministic, a SortByColumns() has been included – however this will only affect the order of the result (and hence a differing count by Type between PowerApps and a SQL query if it was not included). Other than that, it would not affect the outcome that we are going to achieve in terms of the total number of records, had the SortByColumns() not been included in the same command. The full command is here:
ClearCollect(TypeList, SortByColumns('[dbo].[Ori Product List 1]',"ProductID"))
The second swimlane is another Gallery which is picking from the TypeList collection, and which as we can see, it will be blank prior to the population of the Collection through the “Load Collection” button. This Gallery is also filtered by the choice of the Dropdown2 Type Filter value chosen (similar to the first Gallery). The command for this is:
Filter(TypeList,Type = Dropdown2.SelectedText.Result)
Thus, since a collection is involved, this Gallery will be based on a non-delegatable query.
At the bottom of both the first and second Gallery, a count of the rows showing in that Gallery list has been included – generated directly from PowerApps using this code: CountA(Gallery4.AllItems.ProductID)
The third Gallery, is being populated from a View created directly in SQL, named [Count By Product Type] and which in this case is picking a count of all the products grouped by their Type shown under the column [CountByType] in SQL and aliased as [Count All] in PowerApps screen. A sub query has also been done to do a similar count but only picking the first 500 products. And this is because we have the data row limit of 500 set in PowerApps for non-delegated queries.
The SQL code of the view is:
SELECT [Type] ,COUNT([ProductID]) AS [CountByType] ,ISNULL(( SELECT COUNT([ProductID]) FROM [dbo].[Ori Product List 1] S WHERE M.[Type] = S.[Type] AND [ProductID] <= 500 GROUP BY [Type] ), 0) AS [Count500] FROM [dbo].[Ori Product List 1] M GROUP BY [Type]
What we notice when we load the PowerApps screen, the “Type Filter” dropdown is defaulted on “Clothing”. Thus the first Gallery list is filtered on that value, and even if in the database (as we can see from the far-right Gallery list) there are 214 rows in total matching this Product Type, only 100 rows are being shown by default – as the count shows. But, as we drag the scroll bar for the Gallery down, a further 100 rows will be retrieved, putting the count up to 200, and once that is scrolled by, the remaining 14 rows are also gathered. And this is how delegation works. In order not to load all the data which might not be used, the application is getting chunks of data as it is being requested by the user. As a downside of this, one will clearly see a performance trade-off, whereby the user is waiting that extra split of a second for the next chunk of data to load.
And one of the advantages of Collections in PowerApps is that when they are used, performance improves, because these are populated through a single server call and then fed back from the application memory when requested. And this leads us to loading the Collection through the “Load Collection” button. What we need to keep in mind however, is that Collections are non-delegatable. And the way PowerApps deals with this is, that for non-delegatable commands, rather than sending data in chunks as they are being requested, it sends back the maximum data rows allowed for non-delegatable queries (specified in the App settings) – which for this case we have left as the default 500. To clarify this, it will be getting the first 500 rows of the entire SQL table, because we have populated the Collection from the table (and we are not populating it each time a selection is done from the dropdown to get the filtered results). Once the collection loading is completed, one thing we can immediately noticed, is the improved speed for loading the records in its Gallery list (as opposed to first Gallery). Also all the records available that satisfy the filter criteria, are sent at once and not in 100 by 100 chunks – because now all the required data has been transferred at once into the collection already.
In the first 500 rows of the SQL table, there were 24 rows that had the Product Type set to “Clothing” – as we can see from the “Count Top 500” column on the far-right Gallery list. And that is what has been loaded in the Collection.
When we change the filter to “Electronics”, we notice that both lists are loaded instantly because there are less than 100 in the SQL table (hence achieved in one chunk), and there are only 8 in the first 500 rows of the SQL table (and thus returned through the Collection in the second Gallery list).
With “Food & Beverages” being the largest Product Type dataset having a total count of 7808 in the SQL table, we notice the initial 100 are loaded in the first Gallery. And as we go along, the next 100 are always being called – beyond the 500 limit set in the App settings, because this is a delegatable query. On the other hand only 396 rows where part of the initial 500 rows sent to the Collection – and again these were loaded at once.
One thing which we can also notice is that in the dropdown list for the Type Filter, not all Product Types that are specified on the count view on the far-right are available. For example even if there are records available for “Accessories” or “Construction, furniture, homeware and related” in the SQL table, these are not part of the list. Why is this? Again, it has to do with delegation. We are getting the list for the dropdown using this query:
SortByColumns(Distinct('[dbo].[Ori Product List 1]',Type),"Result")
Even if we are calling the SQL table directly, we are here using the Distinct command – with the scope of only getting multiple Product Type occurrences as one record. But the Distinct command is non-delegatable, and hence the returned list is again based on the initial 500 rows in the SQL table. This explains why the dropdown list only shows the Product Types which have a “Count Top 500” not set to 0 in the counting view on the far-right (as in that column count we are mimicking the delegation logic of selecting the TOP 500 rows).
So keeping delegation in mind when developing the application is important because we might end up misleading the users by the data presented to them. If the users were only presented with the Gallery shown in the second swimlane, they might think there are only 500 rows in total spread across the different Product Types. The same applies if we are getting aggregations off that collections. The presented figures will not be reflecting the true values in the database.
It is also important to evaluate the scalability of the application even if it currently is not working with large datasets. Is there a chance for the quantity of data to increase? If yes, consider this on development.
This sums up this blog post. In the next blog post in this series, we will continue covering more points worth keeping in mind during PowerApps development.
Feel free to get in touch, comment or ask any questions on this blog post either here or through my twitter handle: @bonello_jason