Working with SQL Server Data in Power Apps

Problem

Power Apps allows you as the developer to use SQL Server as a back-end database. An important part of an app, be it a canvas or any other one, is saving and persisting the data which we will cover in this tutorial.

Solution

In this tip we look in detail how to use the Power Apps Patch function with SQL on-prem. Furthermore, we will explore the core SQL data types and how they map to input coming from Power Apps.

Environment

For this tip I am using a simple database called Inventory created on my localhost. I have created this table with the following schema:

CREATE TABLE categories (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(255),
    maxproductweight DECIMAL(5,2),
    subcategories VARCHAR(255),
    launchdate DATE,
    isactive BIT,
    signature IMAGE,
    secondaryid UNIQUEIDENTIFIER
)

This schema covers the basic but also most popular data types, including the binary Image type and GUID.

Power App

In my personal environment I have created a basic canvas app in tablet mode. The app consists of the following controls and elements placed on a single screen:

Here’s how it looks in edit mode:

In essence this allows creating a new “category” in our database table, imagining it has something to do with food.

Configure the Patch function

All of the magic will happen in the call to the Patch() function. Let us look in how to configure that function with the data input fields we have. This is how the formula language call looks:

UpdateContext(
    {
        subcategoriesConcatenated: Concat( /*1. variable to concatenate the dropdown values*/
            SubCategories.SelectedItems,
            ThisRecord.Value,
            "-"
        )
    }
);
Patch(
    Categories, /*2.*/
    Defaults(Categories), /*3.*/
    {
        Name: Name.Text,/*3.1.*/
        MaxProductWeight: Value(MaxProductWeight.Text), /*3.2.cast to numeric*/
        Subcategories: subcategoriesConcatenated, /*3.3.*/
        LaunchDate: LaunchDate.SelectedDate, /*3.4.*/
        IsActive:IsActive.Value, /*3.5.*/
        Signature:SignatureInput.Image, /*3.6. */
        SecondaryId:GUID(Guid.Text) /*3.7.cast to GUID*/
    }
)

Here is what is being done in detail:

1. In the beginning of the code block we set a variable by using the UpdateContext function. The variable holds a concatenation of all values selected from the dropdown control.
2. A bit further in the code block is the Patch function. The first argument is the name of the data source, namely our SQL table name. Reminder: table names from SQL cannot be renamed in Power Apps, so take care to name them by also accounting for the app context.
3. Next, we call the Defaults function. Its job is to tell Patch that we want to create a new record, instead of updating an existing one. Should we want to update an existing record, substitute Default with for example the LookUp function, which can provide an existing record to Patch. Then we pass a mapping of all of the columns we want to populate in SQL:
   1. Name is just text, so we call the .Text property of the input control.
   2. Product weight is a decimal. Notice we actually must cast to numeric it by using the Value function. To prevent any possible errors and since there is nothing like “try/catch” or “assert” in Power Apps, the Format property of the input control must be set to Numeric. The default setting is Text. Numeric will accept only numbers and on mobile devices will pop up the numeric keyboard instead of the general one.
   3. For the subcategories, we use the variable created already.
   4. The DatePicker control’s selected data property is directly compatible with Date in SQL, so we just call the property SelectedDate
   5. IsActive is directly compatible too with Bit, so we call the Value property without any casting or transformations either.
   6. The Image property of the Pen Input control provides a binary representation of the image that was drawn. This value is also directly compatible with the SQL Image data type.
   7. Finally, we pass the GUID input value that the GUID function generated. Note that this value must also be cast explicitly to GUID by using the GUID function again.

Here is how it looks on the screen:

The bottom line is:

• Text, binary, date, and image values can be directly written to SQL columns.
• Numeric and GUID values must be cast once again, which enables them to be saved successfully.

Here’s the result of hitting the Save button:

Cancel button

App makers should provide a cancel button or some other form of enabling users to reset the form if needed. So, on the Cancel Button’s OnSelect, I will put a Reset function that resets the inputs of all controls if the user decides to cancel:

Concurrent(
    Reset(Name),
    Reset(MaxProductWeight),
    Reset(SubCategories),
    Reset(LaunchDate),
    Reset(IsActive),
    Reset(Guid),
    Reset(SignatureInput)
)

The Reset function accepts only one argument; therefore, you need to repeatedly call it as many times as the number of controls you wish to reset. You can though wrap it in a Concurrent to reset all of them at once and improve performance.

Considerations

Finally, a couple of considerations:

1. Performance: don’t create forms with too many inputs that will make you save to hundred or more columns in SQL. If indeed it is a complex scenario, you can consider creating a row in the SQL table from the first screen of your app. Then on every screen distribute an equal or logical amount of the inputs you need, e.g. if you have 100 fields, make 4 or 5 screens, each with 25 or 20 input fields per screen. Look up the record and patch to the SQL table on a step-by-step manner. Chances are you may not be able to fit so many on one screen as well.
2. Errors: the Patch function can be difficult to debug. Notice what happens when I turn SecondaryId column into a mandatory input but forget to do so in the app:

Actually, this is a verbose error response because of the extended compatibility between SQL and Power Apps. But there are cases where the error may be way more cryptic and difficult to debug. The maker should take care to appropriately implement the corresponding constraints from SQL into the front-end of the app, e.g. column types, mandatory fields, input length constraints, etc.

3. Being flexible: lastly the power of Patch– as you can see it can be called like any other function tied to a behavioral property. That feature makes it powerful as you can save inputs to multiple data sources at a time. Custom analytics could even be implemented with hidden Patch() calls, e.g. connected to screen transitioning or the app being opened. You must always consider the payload, which should be as small and optimized as possible.

Next Steps

• The Patch function
• The Concat function