Blog

Data - Is the AddColumns function delegable?

A question that I hear on occasions is - is the AppColumns function delegable? This question often arises when app builders are unable to retrieve more than 2000 records. This post explores this behaviour in more detail.

When app builders attempt to retrieve data using formula that utilises the AddColumns function, the resulting behaviour is that the formula returns no more than 2000 records. The question that then arises is - is the AddColumns function delegable?

Overview of AddColumns - what does it do?

Let's start with a quick overview. AddColumns is a function that runs locally. It takes an input data source, and returns an output table that includes the extra columns that we specify.


Strictly speaking therefore, delegation does not apply to AddColumns. This is because it isn't a function or a operator that Power Apps delegates to an underlying data source for execution.

Demonstration of the problem that AddColumns can cause

To highlight the effect that AddColumns has, let's take a gallery control with the data source set to a SharePoint list of properties. This list contains more than 2,000 records. As the following screenshot shows, the gallery control shows all the records in the source SharePoint list. When the user reaches the bottom of the list, the gallery control will load the next batch of records until all records are returned.


Let's now set the "data row limit" of the app to 5 and amend the items property to incorporate a call to the AddColumns function. The formula beneath adds a column locally called "TotalRooms" that sums the bedroom and bathroom values for each row.

AddColumns(Property, "TotalRooms", Bedrooms + Bathrooms)
 

As the above screenshot illustrates, the gallery now shows 5 records only. When a user scrolls to the bottom of the gallery control, Power Apps does not load the next batch of records.

Notice also how the formula bar doesn't underline any of the formula in blue, or show any delegation warnings. This is one of the primary motivations for this post - the absence of warnings often tricks app builders into thinking that Power Apps will retrieve all rows without any limitations.

What's the workaround?

The answer to this problem is not to call AddColumns in items property of a data control. With this type of scenario, one way to workaround this problem is to set the Items property to the underlying source data, and to add the additional data that we want to show in the item template of the gallery.

The screenshot beneath higlights how we can adapt the gallery to show the total rooms through formula on a label in the item template. With this technique, the number of visible records in the gallery will not be limited to 5.


Another alternative if we were using a SQL Server data source, is to use a SQL Server view to add the additional columns server-side, rather than add the columns client-side with AddColumns.

Conclusion

When we display records in a gallery control with formula that includes a call to AddColumns, the number of records shown in the gallery control will be limited to the value that is set in the "data row limit" setting of the app.
Previous
Next
Related posts