Execute SQL Queries from Microsoft Power Apps


By:   |   Updated: 2021-06-09   |   Comments   |   Related: More > 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.

Create an app screen

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.

Power App SQL server connection

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:

List of tables in the app

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:

SalesOrderHeader gallery configuration

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

Using LookUp to display client name

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.

Inserting a nested 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:

Using the in operator

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.

adjusting data row limit for non-delegable data sources

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:

Nested gallery with product names without delegation warning

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:

Object Property Value Explanation
Screen1 OnVisible
Set(
    sortDirection,
    true
)
Helper variable to track the state of the sort direction.
Sort icon OnSelect
Set(
    sortDirection,
    !sortDirection
)
Alternate the value of the helper variable between the two possible values: true or false.
Dropdown Items
["Date", "Total"]
An array of values.
SalesOrdersGallery Items (option 1)
		
SortByColumns(
    'Sales.SalesOrderHeader',
    If(
        SortPicker.Selected.Value = "Date",
        "OrderDate",
        "TotalDue"
    ),
    If(
        sortDirection,
        Ascending,
        Descending
    )
)
This formula will produce another delegation warning that we cannot circumvent. While the function SortByColumns is delegable, making one of its parameters dynamic will result in loss of the delegation capability. As a result, we may get wrong result for larger datasets (> 2000 items) such as the current one.
SalesOrdersGallery Items (option 2)
If(
    SortPicker.Selected.Value = "Date",
    SortByColumns(
        'Sales.SalesOrderHeader',
        "OrderDate",
        If(
            sortDirection,
            Ascending,
            Descending
        )
    ),
    SortByColumns(
        'Sales.SalesOrderHeader',
        "TotalDue",
        If(
            sortDirection,
            Ascending,
            Descending
        )
    )
)
Alternatively, we should take the if-condition out of the SortByColumns formula. Thus, there will be no delegation warning generated.

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:

checking the max order date in sql

Result from the app:

checking the max order date in 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:

error when comparing different data types

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:

Delegation warning for datetime column parse

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:

Object Property Value Explanation
Text Input Default 0 Any default value that makes sense for the app.
VirtualKeyboardMode Numeric This setting ensures the small numeric-only keypad will pop up on mobile devices (iOS, Android, etc.).

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:

Object Property Value Explanation
Text input Hint Text "Search by Purchase order number"
Cancel icon Visible
If(
!IsBlank(SearchBar.Text),
    true,
    false
)
Show the icon only if the input control contains any text.
OnSelect Reset(SearchBar) Resets the text input.

The results should look like this:

Search by Purchase order number

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


Last Updated: 2021-06-09


get scripts

next tip button



About the author
MSSQLTips author Hristo Hristov Hristo Hristov is a Microsoft certified data professional, specializing in Power Apps and Power BI.

View all my tips



Comments For This Article





download





Recommended Reading

Power Apps Canvas Apps Examples

Customize SharePoint online list and library forms with Power Apps

Configure Fields in SharePoint Online Forms with Power Apps

Calling a SQL Server Stored Procedure from Power Apps

Power BI Comments Form with Power Apps














get free sql tips
agree to terms