Using Power Apps Patch Function with SQL Server

By:   |   Updated: 2021-11-02   |   Comments (2)   |   Related: > Power Apps


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.


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.


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 (
Name VARCHAR(255),
maxproductweight DECIMAL(5,2),
subcategories VARCHAR(255),
launchdate DATE,
isactive BIT,
signature IMAGE,

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:

  Description Control type
1 Name Text input
2 Product weight Numeric input
3 Subcategories Dropdown with an array of text values for the Items property
4 Date Date picker
5 Yes or No Toggle control
6 Disabled text field Text input, Default property set to GUID().This function generates a new GUID every time the screen loads.
7 Signature Pen input control
8 Save and Cancel Buttons for controlling the behavior of the form.

Here's how it looks in edit mode:

canvas app overview

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:

        subcategoriesConcatenated: Concat( /*1. variable to concatenate the dropdown values*/
    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:

patch function configuration overview

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:

example result

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:


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.


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:
error example

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.

  1. 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Hristo Hristov Hristo Hristov is a Data Scientist and Power Platform engineer with more than 12 years of experience. Between 2009 and 2016 he was a web engineering consultant working on projects for local and international clients. Since 2017, he has been working for Atlas Copco Airpower in Flanders, Belgium where he has tackled successfully multiple end-to-end digital transformation challenges. His focus is delivering advanced solutions in the analytics domain with predominantly Azure cloud technologies and Python. Hristo's real passion is predictive analytics and statistical analysis. He holds a masters degree in Data Science and multiple Microsoft certifications covering SQL Server, Power BI, Azure Data Factory and related technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-11-02

Comments For This Article

Saturday, May 7, 2022 - 7:16:54 AM - Kostas Chairopoulos Back To Top (90066)
Another consideration is the offline browsing not supported by canvas app which.leads to an uncertain state of underlying data.grat post hy the way

Wednesday, November 3, 2021 - 10:05:52 AM - Hristo Hristov Back To Top (89405)
Small correction, there is actually an Assert() function and it does exactly the same as in a usual programming language. Check it out here: So under p.3.2.this function can be used as a further improvement.

get free sql tips
agree to terms