Understanding Delegation

Query delegation is a feature that speeds up data retrieval. Not all data sources support delegation, but SharePoint, SQL Server, and Dataverse are the popular data sources that do support delegation.

What exactly does the term delegation mean? When we search data from a delegable source, the data source performs the search and returns the result. If we carry out the same operation against a non-delegable data source, the data source returns all the data, and Power Apps then carries out the search on the local device. The non-delegable search works inefficiently because Power Apps needs to download more data than is necessary. Because the network connection is often the slowest link in any computerized system, non-delegable searches perform far more slowly. Additionally, mobile devices generally contain slower hardware compared to servers, and therefore, filtering data on a mobile device will be slower. Figure 8-1 illustrates this concept in the form of a diagram.

SQL Server and Dataverse provide delegation support for a wider range of operations. For example, they both support the EndsWith and the Search functions. The Search function enables us to search multiple columns for values that match an input string. With SharePoint, there is no support to carry out this type of “contains” search in a delegable way. Another benefit of SQL Server and Dataverse is that there is delegation support for aggregate functions that include Sum, Average, Min, and Max.

Because it’s preferable to write a formula that is delegable, the designer shows a warning when we write data access formulas that are non-delegable. To demonstrate the type of warning we would receive, let’s take the SQL Server table that’s shown in Figure 8-2.

Let’s suppose we want to show records where the country field matches the value United States or England. If we implement this search by calling the in function, the designer shows the warning in Figure 8-3.

With this expression, the designer will not show a warning.

Increasing the Data Row Limit

By default, the data row limit for non-delegable queries is 500 rows. We can increase this to 2,000 rows through the advanced settings of an app, as shown in Figure 8-4.

In cases where it isn’t possible to rewrite a query using non-delegable operators, we can retrieve more accurate results by increasing the limit to the maximum value of 2,000. Of course, this isn’t a complete fix, and the problem will still exist if our source data exceeds 2,000 rows.

It is useful to note that delegation support is a feature that Microsoft improves continually. It is likely that in the future, delegation support will extend to support a wider range of operators.

How Do Controls Manage Large Quantities of Data?

Let’s suppose we add a gallery control to our screen and we attempt to display all records with a country that matches England or United States. In total, there are 10,000 records. This is a large quantity of data. What do we think will happen when we run our screen? Will there be a delay as Power Apps loads the data, or will this amount of data be too much for Power Apps to handle?

The actual experience feels very smooth and quick. The gallery control populates almost instantly with data. The reason for this is because Power Apps optimizes this process by retrieving only the first 100 records. If we scroll to the end of the gallery control, there will be a slight delay while Power Apps retrieves the next set of 100 records and appends those to the end of the list. This method of retrieving records in bite-size pieces provides a more responsive and fluid experience for the end user.

One benefit of using SQL Server is that we can use a free tool called SQL Server Profiler to monitor how Power Apps is accessing the database. Figure 8-5 shows the output from a SQL Server trace.

Although the content of this trace may look obscure, the important point is that it verifies that Power Apps retrieves data in 100 record chunks, and it also demonstrates how we can use this tool to diagnose and optimize data retrieval.

Will this function succeed in retrieving all 10,000 records? Unfortunately, the answer is no. The ClearCollect function will collect only up to the maximum number of records that is specified in the “Data row limit for non-delegable queries” setting, even if we specify a data source and query condition that supports delegation.

Basic Search Functions

In the sections that follow, we’ll examine how to use these functions in greater detail.

Filtering by Drop-Down Values

The core parts that make up our search screen are a gallery control and a combo box. Figure 8-8 shows how our screen is set up.

There are two steps required to build our basic search screen. The first step is to add a combo box and to configure the data source for this control. The second step is to configure a gallery control so that it filters by the selected item in the combo box.

As Figure 8-8 shows, we add a combo box called cmbTenant, and we set the Items property to the following formula:

AddColumns('[dbo].[Tenant]', "FullName", Firstname & " " & Surname)

This technique sets up the combo box to show friendlier item values by combining the Firstname and Surname values. To set up our gallery control to display the filtered results, we set the Items property to the following formula:

Filter('[dbo].[Issue]', TenantID=cmbTenant.Selected.TenantID)

This formula shows how the Selected property of the combo box enables us to retrieve additional fields, in this example, the TenantID field . This example demonstrates the common practice of showing a friendly description in a combo box and the formula that we would use to retrieve the numeric key value that is associated with the selected item.

Providing a “Show All”/Wildcard Option

A typical way that a search screen behaves is that if a user chooses not to enter any search criteria, the search screen will return all results. To adapt our search screen to behave in this fashion, we would change the Items property of our gallery control to the following:

Filter('[dbo].[Issue]',

       CountRows(cmbTenant.SelectedItems)=0 ||

       TenantID=cmbTenant.Selected.TenantID

)

To explain this formula in more detail, the Filter function takes two arguments. The first argument defines the data source, and the second argument defines the expression for inclusion into the result. The Filter function evaluates this expression for each record in the data source and includes the record if the expression resolves to true.

The expression in this example evaluates the number of selected items in the combo box. If no items are selected, the expression resolves to true, and the item will appear in the result.

If the combo box contains a selected item, the second part of the “or” operator will be evaluated (the || keyword denotes the logical “or” operator). In this case, the record will be included if the tenant ID value matches the selected tenant ID in the combo box.

For this example to work correctly, we need to configure the combo box to allow only one selection by disabling the “Allow multiple selections” property. Another feature of the combo box control is that we can set the placeholder text that appears when the combo box is empty. We can use this to indicate that the search will return records for all tenants (Figure 8-9).

Just for some background, the combo box control did not exist during the first edition of this book. In the initial release, we needed to manually append a blank row to the top of the combo box control to provide a wildcard search. The combo box control provides a great improvement because it includes a blank entry by default.

Adding a Search Button

The implementation of our current search screen connects the gallery control directly to the data entry controls. Because of this, the gallery control refreshes as soon as a user enters any data into any of the controls. This behavior is not ideal because the gallery control will refresh more than it needs to, particularly if the user wants to enter multiple search criteria. To address this issue, here’s how to add a search button to the screen to trigger the search operation.

To build this feature, we would amend the Items property of the gallery control so that it filters the data by variables, rather than control values. We would then set the variable values on the click of the search button. Figure 8-10 illustrates the formulas we would use.

Tip

To build a search screen where users can enter multiple pieces of search criteria, we can provide a better user experience by triggering the search from a button.

Joining Records: Showing Related Records

This formula calls the AddColumns function to append the associated tenant record to the issue data source. The interesting thing this demonstrates is how we can create a structure of nested tables. Figure 8-12 shows the output of this function.

This formula also illustrates an important learning point. It demonstrates how to fully qualify field names when we nest data with functions such as AddColumns, Filter, and LookUp.

The name for the @ operator is the disambiguation operator. Another scenario where this operator applies is in situations where a variable, collection, or data source matches a field name. In this case, we would specify the variable, collection, or data source in the format [@ObjectName] to distinguish it from the field name.

Now that we’ve set the Items property for our gallery control, we can access all the fields in the tenant record for each issue record through the TenantRecord column, as shown in Figure 8-13.

Matching Against Lists of Data

The first type of query that causes difficulties is where we want to find records that match a user-supplied list. As an example, Figure 8-14 shows a screen where the user can select multiple tenants from a list box. The search feature returns all records that contain any of the selected tenants.

The function that we use here is the in function. This function can be particularly confusing because there are two versions of this function which carry out different tasks. We can call the in function to check if a string contains an input value, or we can call it to check if a table contains one or more values that we provide as an input.

If we know that our source data will never exceed 2,000 rows, this will not be a problem. But in situations where it can, we need to find an alternate way to carry out the search.

One work-around is to loop through the items in the list box with the ForAll function, search the data source for each item row by row, and collect the results in a collection. This technique enables us to retrieve up to 2,000 issue records per tenant, rather than 2,000 records in total.

This process requires us to nest two record-scoped functions – ForAll and Filter. To disambiguate the TenantID field from the source list box and the TenantID field from the issue table, we call the RenameColumns function to rename the TenantID column in the source list box to SourceTenantID.

The ForAll function loops over this data source, and for each selected tenant, we collect the issue records that match the tenant ID into a collection called SearchResults.

This example highlights another useful disambiguation technique that relies on renaming columns to remove any ambiguity.

At the time of writing, there is a bug that prevents the correct resolution of field values when we call the As function to alias a source and include a nested call to the Filter function to filter a SQL Server data source. Hopefully, this problem will be fixed in a future release; but until then, the rename technique provides an effective alternative.

Using in to Match Substring Text

As we’ve just seen, the reason why the in operator can be confusing is because there are two usages for this function. Not only can it test that an input set of records belongs to a table or collection but it can also test for the existence of a string within another string. Figure 8-15 provides a simple way to illustrate this type of usage.

This example returns all records where the description field includes the characters “ing.” Although this formula works fine with SQL Server, a caveat is that with SharePoint, this formula is not delegable.

A closely related function is the exactin function . From a Power Apps perspective, the exactin function works the same way as the in function, except that the matches are case sensitive. Unfortunately, the usefulness of the exactin function is limited because it is not delegable for both SharePoint and SQL Server data sources.

When we call the in function with SQL Server to match a substring, Power Apps will delegate the query to SQL Server as we would expect.

The case sensitivity of queries with SQL Server depends on the collation sequence. If the collation sequence of the target database columns is set to a case-sensitive collation, any searches that we make with the in function will be case sensitive, as opposed to the standard behavior in Power Apps which is case insensitive.

Here’s an interesting way that we can take advantage of this behavior. Let’s take the example of an app that connects to a SQL Server instance with a case-insensitive collation sequence. If we want to carry out case-sensitive searches, we can create a SQL Server view that casts our target columns to a case-sensitive collation. Any queries that we carry out with functions such as Filter or Search will now be case sensitive. The section “SQL Server Views” later in this chapter provides the example SQL syntax that we can use.

The in and exactin functions can be difficult to comprehend, so to help summarize all the different usage types, Table 8-1 provides some example syntax.

Table 8-1

Examples of how to call the in and exactin functions

Function	Description
Filter('[dbo].[Tenant]',             Postcode in                  ["BA3 7KD" ," LN2 1IV"] )	Usage type – belonging to a table (case insensitive). This formula returns all records with a postcode that exactly matches the postcode “BA3 7KD” or “LN2 1IV”. The search is not case sensitive. Note that we cannot use this technique to find a partial match from within the Postcode column. This query is not delegable with SQL Server and SharePoint.
Filter('[dbo].[Issue]',             "smith" in Firstname )	Usage type – substring test (case insensitive). This formula returns all records that contain the string “smith” in the Firstname column. This query is delegable with SQL Server, but not delegable with SharePoint.  With SQL Server, this search might be case sensitive, depending on the database collation sequence.
Filter('[dbo].[Tenant]',             Postcode exactin                 ["BA3 7KD" ," LN2 1IV"] )	Usage type – belonging to a table (case sensitive). This formula returns all records with a postcode that exactly matches “BA3 7KD” or “LN2 1IV”.  This search is case sensitive. This query is not delegable with SQL Server and SharePoint.
Filter('[dbo].[Issue]',             "Smith" exactin Firstname ).	Substring test (case sensitive). This formula returns all records that contain the string “Smith” in the Firstname column. This search is case sensitive and will not return records that contain the lowercase value “smith”. This query is not delegable with SQL Server and SharePoint.

Checking for Nonexistence

An important task that we often need to carry out is to check for the nonexistence of a group of data within another dataset. As an example, traditional order processing systems will use this technique to find customers who have not placed any orders. To highlight this technique, here’s how to show the names of tenants that are not associated with any issue records.

This formula filters the tenant table to show records where a matching TenantID doesn’t exist in the issue table. Just like the previous example, the syntax we use here is not delegable; and therefore, the code here may not return all expected records.

Matching Blank Fields

Another common requirement is to search for records with null, empty, or blank fields. To demonstrate this topic, Figure 8-16 shows an excerpt from the issue table. We’ll use this to examine how to filter by empty or null values in the Description and CloseDateTime columns.

With this type of data structure, a typical requirement could be to return all records with a null CloseDateTime value . This would enable users to see all records that are in an open status.

The function that tests for null or empty values is called IsBlank. This function accepts an input value and returns true if the input is empty string or null.

This function would return all records with a null or empty date. However, a big limitation is that the IsBlank function is not delegable. If there are more than 2000 records in the issue table, the results will not include all matching records.

This expression returns records with the IssueID values 1 and 2. In other words, the IsBlank function matches both null and empty string values.

This formula would return the record for IssueID 2 only. Power Apps can delegate the operation of matching against an empty string, so we can use this method to help overcome delegation limitations.

Returning Distinct Records

Sometimes, there may be the requirement to return a list of distinct records. To give an example, let’s suppose we want to return a list of distinct tenants who have raised issues.

Figure 8-17 shows how this data looks after the GroupBy operation .

The TenantID column shows the distinct data. Once we apply this formula to a gallery control, we can add a label and call the LookUp function to retrieve the user details that are associated with the TenantID, as shown in Figure 8-18.

However, with SQL Server and SharePoint data sources, the date filter bug prevents this formula from returning correct data. As we saw earlier in this chapter, filtering data sources by date will incorrectly return zero rows.

One way to overcome this problem is to add the criteria && Day(StartDate) >0 to force the filter operation to run on the local device. The caveat of this approach is that this expression is non-delegable and will not return accurate results with data sources that exceed 2000 records.

SQL Server Views

With SQL Server, a great way to overcome delegation issues is to build a view. Because SQL Server is a large separate topic, we won’t cover this in too much detail. However, the important thing to take away from this section is that there is an effective technique that we can apply to overcome query delegation problems.

Figure 8-19 shows the design of the view in Management Studio. The SQL in this view returns a numeric representation of the month and year of the CloseDateTime field by calling the SQL Month and Year functions . By converting datetime fields to integers, we can now query the data in a delegable way. From Power Apps, we can now return a distinct list of tenants who have raised issues in the current month with the following function:

Filter('[dbo].[viewDistinctTenants]',

 CloseDateMonth = Month(Now()) &&

        CloseDateYear = Year(Now())

)

SharePoint Columns

With SharePoint, the options to work around delegation issues are more limited. SharePoint calculated columns do not help because these are also not delegable.

By storing this date value in a number column, we can then filter the value using operators that include the greater than and less than operators.

If we were adding these new fields to an existing list, we could use a Power Automate flow to carry out the initial population.