Execute SQL Queries from Microsoft Power Apps

Problem

Microsoft Power Apps offers options to connect to many and different data sources. Very few of these data sources support handling bigger amounts of data though. Luckily the Microsoft SQL Server Data Platform data source does not impose a specific limit on how many rows you can bring into your app provided certain conditions and guidelines are observed. This tip will showcase the most vital points to keep in mind when you must query (lots of) SQL data for business apps.

Solution

Microsoft Power Apps Example

The data source for this tip is Adventure Works 2019. You can download it here. Alternatively, you can read through the tip and apply the techniques on your own data (Azure, Excel, Oracle, CRM, Dynamics 365, etc.). In this case, the core requirement will be to display sales data and allow user to filter, search and sort them by different criteria. Thus, we showcase the Filter, Search and SortByColumns functions in Power Apps, how they work with SQL Server and how to use them to leverage the relationships available in the database.

App setup

First let us make a simple low-code app. From the Power Apps home screen click on Apps, then New App to start our app development. On the following screen, choose Tablet layout. This option will create the app in landscape mode with a bigger canvas as compared to the Phone layout where the layout is in portrait and canvas is smaller. The app being in tablet mode also makes it very suitable to be embedded on a SharePoint Online page or to be displayed as a tab on a Teams channel.

Now let us get some data. The data tab on the left in the Power Apps studio allows you to add data by choosing a connector. Here we need the SQL connector with a connection to AdventureWorks2019. I have preconfigured the connection, so it appears in the list. You can also configure a new one by clicking on Add a connection. For on-premises resource a data gateway will be required.

Once you pick a connection, a list of tables from the database will pop up on the right. Let us select Person.Person, Production.Product, Sales.SalesOrderDetail and Sales.SalesOrderHeader. This set of tables will allow us to display information about the sales with product and client information. Click connect to bring the reference to those tables into the app (note: the data is not being imported into the app). As a result, you will see this list in the data tab:

Sales overview

We can easily display general sales information by adding a gallery and referencing Sales.SalesOrderHeader table in the gallery’s Items property. Inside the gallery’s template add three labels for SalesOrderId, Date and total amount:

Additionally, we can add one more label to display the names of the customer:

To provide a good overview of the sales per each SaleOrderHeader we can use a nested gallery. To add a nested gallery to the existing gallery template, select the first row of the current gallery and choose Gallery > Horizontal Gallery.

This approach will work but not directly out of the box. Inside the nested gallery we can use the existing relationships in the database and show the products for each of the current sales orders like this:

As you see though, a small warning icon appears next to the nested gallery. When you see this warning, it indicates the formula will potentially produce wrong results especially with big datasets. Here “big” means anything above 500 items. This limit can be increased up to 2,000 items by going to File > Settings > Advanced settings and adjusting the data row limit for non-delegable queries.

So, what is the reason for this behavior? Wasn’t Power Apps supposed to handle freely anything SQL throws at it? Yes and no. The reason is with the in operator: it is not entirely delegable. To be precise, it can behave in two ways:

• string search: if a certain string is contained in another string, or
• membership search: if a certain record is contained in a table.

So, in this case, the membership part of this operator is not delegable. Our way forward is to discard the in and try using ForAll so we can use the built-in relationship between the SalesOrderDetail and the Production.Product table in order to show the actual product name for each order header in the main gallery.

This is the formula for doing so:

Ungroup(
    ForAll(
        Filter(
            'Sales.SalesOrderDetail',
            SalesOrderDetailID = ThisItem.SalesOrderID
        ).ProductID,
        {
            Items: Filter(
                'Production.Product',
                ProductID = ThisRecord.ProductID
            )
        }
    ),
    "Items"
)

With this formula we iterate over all relevant rows in SalesOrderDetail. For each of those rows, we compare the ProductID to ThisRecord.ProductID. For convenience, we apply the Ungroup function which flattens the result so we can easily use it in the gallery. This is the result:

Similarly, we can nest a gallery for SalesOrderDetail to show extra details for each order. Beware the performance of the app will depend heavily on the client’s device speed and connectivity. Nesting too many galleries may not be advised in certain scenarios.

Sort menu

Let us implement a sort option that allows sorting by Date or by Total order value, as well as changing the sort direction (ascending to descending and vice-versa). We must apply the following configurations:

Set(
    sortDirection,
    true
)

Set(
    sortDirection,
    !sortDirection
)

["Date", "Total"]

SortByColumns(
    'Sales.SalesOrderHeader',
    If(
        SortPicker.Selected.Value = "Date",
        "OrderDate",
        "TotalDue"
    ),
    If(
        sortDirection,
        Ascending,
        Descending
    )
)

If(
    SortPicker.Selected.Value = "Date",
    SortByColumns(
        'Sales.SalesOrderHeader',
        "OrderDate",
        If(
            sortDirection,
            Ascending,
            Descending
        )
    ),
    SortByColumns(
        'Sales.SalesOrderHeader',
        "TotalDue",
        If(
            sortDirection,
            Ascending,
            Descending
        )
    )
)

If you perform a quick sanity check on the raw data, you will notice that the sorting function works correctly. It does sort the items in the gallery by sending a query to SQL server:

Result from query:

Result from the app:

We have an identical expected behavior if we sort by Total due. Alternating the Sort column or direction will generate queries to the data source and Power Apps will provide feedback by a couple of gray dots running over the top of the screen until the query terminates its execution and returns the result set to the gallery.

Filter by date

We can also consider filtering by date. However, if we just add two date pickers and implement a filter condition in the Items property of the gallery, we will get this error:

Power Apps does not automatically parse the date picker value to the exact data type of the source column. We could try parsing the OrderDate value but then we will get a delegation warning and in fact no results returned at all:

To circumvent this, we need a column in the SQL database that represents the date in integer format, for example:

ALTER TABLE Sales.SalesOrderHeader
ADD OrderDateAsInt as (CONVERT(INT,(((RIGHT(DATEPART(YEAR, [OrderDate]),(2))*(10000)
                       +DATEPART(MONTH, [OrderDate])*(100))
                       +DATEPART(DAY, [OrderDate]))))) PERSISTED 

Now you must refresh your connection to SalesOrderHeader and the new computed column will be available for selection. Depending on the default settings in Power Apps studio you may, however, get an error when picking this column because it is a computed one (although persisted). If you get such an error, go to File > Settings > Advanced Settings and turn the setting Explicit column selection off.

Now the formula for the Items property of the SalesOrderGallery will look like that:

If(
    SortPicker.Selected.Value = "Date",
    SortByColumns(
        Filter(
            'Sales.SalesOrderHeader',
            And(
                IntegerDate >= Value(
                    Text(
                        StartDate.SelectedDate,
                        "[$-en-US]yymmdd"
                    )
                ),
                IntegerDate <= Value(
                    Text(
                        EndDate.SelectedDate,
                        "[$-en-US]yymmdd"
                    )
                )
            )
        ),
        "OrderDate",
        If(
            sortDirection,
            Ascending,
            Descending
        )
    ),
    SortByColumns(
        Filter(
            'Sales.SalesOrderHeader',
            And(
                IntegerDate >= Value(
                    Text(
                        StartDate.SelectedDate,
                        "[$-en-US]yymmdd"
                    )
                ),
                IntegerDate <= Value(
                    Text(
                        EndDate.SelectedDate,
                        "[$-en-US]yymmdd"
                    )
                )
            )
        ),
        "TotalDue",
        If(
            sortDirection,
            Ascending,
            Descending
        )
    )
)

The formula is indeed repetitive, but it will work over any number of rows in the database.

Filter by order total

We can also add two text input fields and configure both accordingly:

Now the core Filter function should look like this with two conditions added for TotalDue:

/*…*/
Filter(
    'Sales.SalesOrderHeader',
    And(
        IntegerDate >= Value(
            Text(
                StartDate.SelectedDate,
                "[$-en-US]yymmdd"
            )
        ),
        IntegerDate <= Value(
            Text(
                EndDate.SelectedDate,
                "[$-en-US]yymmdd"
            )
        ),
        TotalDue >= Value(TotalDueMin.Text),
        TotalDue <= Value(TotalDueMax.Text)
    )
)
/*…*/

Search menu

We can also integrate the Search functionality to provide the users an option to search in a certain column. Let us do so:

If(
!IsBlank(SearchBar.Text),
    true,
    false
)

The results should look like this:

Beware the Search formula works only for text columns.

Now what we must do is wrap the Items property of the SalesOrderGallery gallery with a check like this:

If(
    !IsBlank(SearchBar.Text),
    If(
        SortPicker.Selected.Value = "Date",
        SortByColumns(
            Search(
                'Sales.SalesOrderHeader',
                SearchBar.Text,
                "PurchaseOrderNumber"
            ),
            "OrderDate",
            If(
                sortDirection,
                Ascending,
                Descending
            )
        ),
        SortByColumns(
            Search(
                'Sales.SalesOrderHeader',
                SearchBar.Text,
                "PurchaseOrderNumber"
            ),
            "TotalDue",
            If(
                sortDirection,
                Ascending,
                Descending
            )
        )
    ),
/*outer if true result ends here*/
    If(
        SortPicker.Selected.Value = "Date",
        SortByColumns(
            Filter(
                'Sales.SalesOrderHeader',
                And(
                    IntegerDate >= Value(
                        Text(
                            StartDate.SelectedDate,
                            "[$-en-US]yymmdd"
                        )
                    ),
                    IntegerDate <= Value(
                        Text(
                            EndDate.SelectedDate,
                            "[$-en-US]yymmdd"
                        )
                    )
                )
            ),
            "OrderDate",
            If(
                sortDirection,
                Ascending,
                Descending
            )
        ),
        SortByColumns(
            Filter(
                'Sales.SalesOrderHeader',
                And(
                    IntegerDate >= Value(
                        Text(
                            StartDate.SelectedDate,
                            "[$-en-US]yymmdd"
                        )
                    ),
                    IntegerDate <= Value(
                        Text(
                            EndDate.SelectedDate,
                            "[$-en-US]yymmdd"
                        )
                    )
                )
            ),
            "TotalDue",
            If(
                sortDirection,
                Ascending,
                Descending
            )
        )
    )
)

In a nutshell with this formula the gallery will be populated based on either the Search formula or the Filter formula. Alternatively, you can insert the Search formula into the inner Filter formula to provide users both options at the same time. Such a choice will not impact the query delegation:

/*…*/
Filter(
    Search(
        'Sales.SalesOrderHeader',
        SearchBar.Text,
        "PurchaseOrderNumber"
    ),
    And(
        IntegerDate >= Value(
            Text(
                StartDate.SelectedDate,
                "[$-en-US]yymmdd"
            )
        ),
        IntegerDate <= Value(
            Text(
                EndDate.SelectedDate,
                "[$-en-US]yymmdd"
            )
        )
    )
)
/*…*/

Conclusion

This tip discusses some of the most important aspects of delegation in Power Apps when working with SQL data, as well as the core formulas for filtering, searching, and sorting data. By following some predictable guidelines, Power Apps can work with unlimited amounts of SQL data based on your business needs for a positive user experience.

Next Steps

• Understand delegation in a canvas app – Power Apps | Microsoft Docs
• Filter, Search, and LookUp functions – Power Apps | Microsoft Docs