7 Ways To Use The PATCH Function In Power Apps (Cheat Sheet)

7 Ways To Use The PATCH Function In Power Apps (Cheat Sheet)

Power Apps PATCH function is the hardest function to understand. Why? The Patch function is the only function you can use 7 different ways. Yeah, you heard me right. Seven. That’s why I built a no-nonsense cheat sheet that you can use to quickly figure out how the patch function works.

Note: in all of the examples below the datasource called Employees where records are being created/updated is SharePoint list.




Table Of Contents:

PATCH A Single Record To A Table
• Create A New RecordUpdate An Existing RecordGet The Result Of The Patch Function

PATCH Multiple Records To A Table
• Create Multiple New RecordsEdit Multiple Existing RecordsUpsert Multiple Records

PATCH Changes To A Record Variable
• Change Values In A Record Variable

Bonus Content:
• More Power Apps Patch Function Tips And Tricks 




1. Create A New Record With Power Apps Patch Function



Syntax
Patch(Datasource, BaseRecord, NewRecord)


Input

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Lemon095811/03/2015true
3David Johnson056308/15/2013false


Code
Patch(
    Employees,
    Defaults(Employees),
    {
        FullName: "Sarah Green",
        EmployeeNumber: 1002,
        HireDate: Date(2018,3,14),
        Active: true
    }
)


Output

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Lemon095811/03/2015true
3David Johnson056308/15/2013false
4Sarah Green100203/14/2018true




2. Update An Existing Record Using Power Apps Patch Function



Syntax
Patch(Datasource, BaseRecord, ChangeRecord)


Input

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Lemon095811/03/2015true
3David Johnson056308/15/2013false
4Sarah Green 1002 03/14/2018true


Code
Patch(
    Employees,
    LookUp(
        Employees,
        ID=4
    ),
    {
        FullName: "Sarah Brown",
        EmployeeNumber: 1003
    }
)


Output

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Lemon095811/03/2015true
3David Johnson056308/15/2013false
4Sarah Brown100303/14/2018true




3. Get The Result Of The Patch Function



Syntax
Set(VariableName, Patch(Datasource, BaseRecord, ChangeRecord))


Input

Employees Table In SharePoint

IDFullNameEmployeeNumberCreatedCreated By
1Matthew Devaney105005/08/2022Matthew Devaney
2Alice Lemon095805/10/2022 Matthew Devaney
3David Johnson056305/13/2022 Matthew Devaney
4Sarah Green 1002 05/13/2022 Matthew Devaney



Code
Set(
    varEmployeeCurrent,
    Patch(
        Employees,
        Default(Employees),
        {
            FullName: "Kelly Smith",
            EmployeeNumber: 1066
        }
    )
)


Output

Employees Table In SharePoint

IDFullNameEmployeeNumberCreatedCreated By
1Matthew Devaney105005/08/2022Matthew Devaney
2Alice Lemon095805/10/2022 Matthew Devaney
3David Johnson056305/13/2022 Matthew Devaney
4Sarah Green 1002 05/13/2022 Matthew Devaney
5Kelly Smith 106605/29/2022Matthew Devaney



varEmployeeCurrent record in Power Apps

{
    ID: 5,
    FullName: "Kelly Smith",
    EmployeeNumber: 1066,
    'Created By': Date(2022, 05, 29),
    Created: Matthew Devaney,
    Modified: Date(2022, 05, 29),
    Modified By: Matthew Devaney
}



4. Create Multiple New Records With Power Apps Patch Function



Syntax
Patch(Datasource, BaseRecordsTable, NewRecordsTable)


Input

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Lemon095811/03/2015true
3David Johnson056308/15/2013false
4Sarah Green 1002 03/14/2018true
5Kelly Smith 106605/20/2022true




Code
ClearCollect(
    colNewEmployees,
    Table(
        Employees@{
            FullName: "Mary Baker",
            EmployeeNumber: 0798,
            HireDate: Date(2022, 06, 06),
            Active: true
        },
        Employees@{
            FullName: "John Miller",
            EmployeeNumber: 1203,
            HireDate: Date(2022, 06, 11),
            Active: true
        },
        Employees@{
            FullName: "Susan Wright",
            EmployeeNumber: 0590,
            HireDate: Date(2022, 06, 23),
            Active: true
        }
    )
);
Patch(
    Employees,
    ForAll(
        Sequence(CountRows(colNewEmployees)),
        Defaults(Employees)
    ),
    colNewEmployees
);



Output

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Lemon095811/03/2015true
3David Johnson056308/15/2013false
4Sarah Green 1002 03/14/2018true
5Kelly Smith 106605/20/2022true
6Mary Baker079806/06/2022true
7John Miller120306/11/2022 true
8Susan Wright059006/23/2022 true




5. Edit Multiple Existing Records Using Power Apps Patch Function



Syntax
Patch(Datasource, BaseRecordsTable, UpdateRecordsTable)


Input

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Lemon095811/03/2015true
3David Johnson056308/15/2013false
4Sarah Green 1002 03/14/2018true
5Kelly Smith 106605/20/2022true


Code
ClearCollect(
    colUpdateEmployees,
    Table(
        Employees@{
            ID: 2,
            FullName: "Alice Henderson",
            EmployeeNumber: 1001
        },
        Employees@{
            ID: 4,
            Active: false
        },
        Employees@{
            ID: 5,
            HireDate: Date(2022, 08, 01)
        }
    )
);
Patch(
    Employees,
    ShowColumns(
        colUpdateEmployees,
        "ID"
    ),
    colUpdateEmployees
);


Output

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Henderson100111/03/2015true
3David Johnson056308/15/2013false
4Sarah Green 1002 03/14/2018false
5Kelly Smith 106608/01/2022true




6. Upsert Multiple Records With Power Apps Patch Function



Syntax
Patch(Datasource, BaseRecordsTable, UpsertRecordsTable)


Input

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Lemon095811/03/2015true
3David Johnson056308/15/2013false


Code
ClearCollect(
    colUpsertEmployees,
    Table(
        Employees@{
            ID: 2,
            FullName: "Alice Henderson",
            EmployeeNumber: 1001
        },
        Employees@{ 
            ID: Blank(),
            FullName: "Sarah Green",
            EmployeeNumber: 1002,
            HireDate: Date(2018, 03, 14),
            Active: false
        },
        Employees@{ 
            ID: Blank(),
            FullName: "Kelly Smith",
            EmployeeNumber: 1066,
            HireDate: Date(2022, 05, 20),
            Active: true
        }
    )
);
Patch(
    Employees,
    colUpsertEmployees
);


Output

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true
2Alice Henderson100111/03/2015true
3David Johnson056308/15/2013false
4Sarah Green 1002 03/14/2018false
5Kelly Smith 106608/01/2022true


7. Change Values In A Record Variable Using Power Apps Patch Function



Syntax
Patch(Record1, Record2)


Input

Record stored in a global variable named gblEmployee

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney105005/28/2010true


Code
Patch(
    gblEmployee,
    {EmployeeNumber: 1063}
);


Output

Employees Table In SharePoint

IDFullNameEmployeeNumberHireDateActive
1Matthew Devaney106305/28/2010true




More Power Apps Patch Function Tips And Tricks

Want to learn more about the Patch function? Check out these awesome otherarticles I’ve written:


Everything You Need To Know About Power Apps Patch Forms

A full tutorial on how to build a Power Apps patch form including the topics: form submissions, data validation, error-handling and updating a previously submitted record.

Patch Multiple Records In Power Apps 10X Faster

A nifty trick I discovered to submit multiple records at once really really quickly.

Power Apps Patch Function Examples For Every SharePoint Column Type

Example of how to patch every SharePoint column type in an easy to read format.

Patch Function Error-Handling

Learn how to check a form for errors on submission and eliminate the possibility to losing entered data.

Power Apps Excel-Style Editable Table

Make an excel-style table in Power Apps you users will love by using the Patch function







Questions?

If you have any questions about 7 Ways To Use The PATCH Function In Power Apps please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest

78 Comments
Oldest
Newest
Inline Feedbacks
View all comments
richard c
1 year ago

Hi Matthew, as usual another great article. On this topic… I’d like some examples for patching more complex fields like a “person” field or a “persons” field from a combobox. In addition it would be nice to know the correct way to blank out person and persons columns.

Have you already covered these topic before?

Pierre Dupont
Pierre Dupont
1 year ago

Hello
Thanks for this good cheat sheet.

I found 2 little errors:

In code of section 2 : “EmployeeNumber: 1003” –> “EmployeeNumber: 1002”
In code of section 6 : You Patch the colUpdateEmployees collection and declare colUpsertEmployees collection.

Thanks for your quality content.

Andrew G
Andrew G
1 year ago

Typo: In example 2, shouldn’t it read ID=4 rather than ID=2?

Kylzbaba
Kylzbaba
1 year ago

Grand master of the patch function.
Thank you Matthew.

Andrés Martino
Andrés Martino
1 year ago

Great article! We can also use Patch for mix Forms.

Andrés Martino
Andrés Martino
1 year ago

I was referring to this. Just an example 🙂

Sin título.png
Micha H
Micha H
1 year ago

Hi Matthew,

Not sure if I’m asking this in the right place, but I’m looking for a way to patch into a SharePoint list in the following way;

In Power Apps I’m looking to build a form that allows me to select an event name (sourced from a SharePoint List), date and start time and another input for number of participants.

This needs to patch into another SharePoint list where a row is created depending on the number of participants given. So if for example I have an “introduction” event, with a number of participants of 7, the SharePoint list should have 7 rows where Introduction event and the start date and time is patched in. This list would have two additional columns for name and transport required which is information that is collected later and is entered into the SharePoint list directly.

I looked at your post on “Power Apps Excel-Style Editable Table” which will let me build a way to input the information on the form but I would then have to enter the name of the event and the start date and time multiple times which would become an issue if there would be a few dozen attendees.
Any suggestions are greatly appreciated!

Micha H
Micha H
1 year ago

Hi Matthew,

Thanks for the reference, it was really helpful for creating editable tables.
I was able to get it to create the number of rows in a different list using power automate so i have a working solution!

Thanks again!

Sarah
Sarah
1 year ago

Great article, thanks, and thanks to the other eagle eyed readers that spotted colUpsertEmployees too.

Sapna
Sapna
1 year ago

Hi Matthew, I was working with Bulk Patch using collection, but I am not able to handle errors in this, after patch I have a success screen but if there is any error it should not go to success screen. Can you help me with this? Thanks

Sapna Karda
Sapna Karda
1 year ago

Thanks

Takashi
Takashi
1 year ago

Hello Matthew, thanks for this great article.
Section 6 results seem like different behavior in my lab.
If the ID column is blank (such as Sarah, Kelly in the collection), a new record will be created.
However, in this article section 6, Sarah, Kelly already exist (ID:4,5) and the records are updated after executing the patch function even though IDs are blank in the collection.
I guess the ID:4,5 rows in the input isn’t necessary.

Takashi
Takashi
1 year ago

Thanks

Matthieu G.
Matthieu G.
1 year ago

Hi Matthew,
When you execute ClearCollect(<colName>: Table(<records>)), the colon character instead of a comma generates an error right ?
Or am I missing something with formula experimental features that I should tick in the studio settings?

PS: I guess also that Table( ) function is only mandatory in a table type variable declaration with Set( ) but not in a Collect( ) function when you can directly stack the records in a JSON fashion

Last edited 1 year ago by Matthieu G.
Meg
Meg
1 year ago

Can we be best friends. Just saying. Your articles make my life so much easier.

Last edited 1 year ago by Meg
Meg
Meg
1 year ago

Well, I think we just became best friends. Can’t wait to keep seeing articles from you!

steve
steve
1 year ago

Hi Matthew,

Thanks for the great examples! Maybe I’m just looking at this incorrectly, but is “upsert” not a typo? If not, then I’m confused and you can ignore this comment 🙂

E Ferguson
E Ferguson
1 year ago

Thank you for all of the articles that you post, Matthew.. They are all very helpful!

Is it possible to upsert multiple Sharepoint items using example 6 above if a choice column is one of the columns that needs to be updated? When I include the choice column in the collection using ColorChoice: {Value: “Green”} the Patch function has an “Invalid argument type (Table). Expecting a Record value instead” error.

AzAh
AzAh
1 year ago

Hello – I am trying to patch to Salesforce using #4 above.. I can patch with a ForAll(collection,Patch(source,…)).It takes forever for a larger collection (i.e. ~10,15,20-30 or more)..My goal is to patch faster than I currently am but failing miserably.

I have also reviewed your “PATCH Multiple Records In Power Apps 10x Faster” but nothing seems to be working. Do you have any recommendations on bulk patching to Salesforce?

Last edited 1 year ago by AzAh
Johan Christensson
Johan Christensson
1 year ago

Great article and very helpful examples. Is it a typo where the collection names on 5 and 6 are mixed up? update on the creating and upsert on the patching and vice versa?

Tim
Tim
1 year ago

Hello Matt! Awesome article, the bulk patch with a collection is a lifesaver. I have a question though. I am dealing with Offline canvas apps using Dataverse as the datasource. I have defined a new primary key to be an offline GUID that is created inside the application. I am wondering if there is a way to do an Upsert using bulk patch method with a collection but using my new primary key as the “ID”? Please let me know if you have any ideas and thank you again for this excellent content.

–Tim

Andrew Crompton
Andrew Crompton
1 year ago

Hi Matthew – thanks for a super-helpful article. Patch is such a confusing function.

I have a question about one usage of Patch which seems not to be covered here. I have an app which contains the following code:

  // Add the new Registrations to the live database
  Patch(tRegistrations, collNewRegistrations) ;

Here, tRegistrations is a SharePoint list to which I am adding a bunch of new registrations. The new registrations are assembled in the collection collNewRegistrations, and then added to tRegistrations all at once by the Patch statement. collNewRegistrations has columns corresponding exactly to those in tRegistrations except that it does not have the ID column or other read-only columns.

This use of Patch seems not to be covered in your article (in all your examples, the Patch function has 3 arguments) or in MS’s documentation (unless I’ve overlooked it).

It’s a bit of a surprise to me to find that this usage works. Are you able to explain it?

Ryan
Ryan
1 year ago

Hi Matthew,

Thank you for the detailed examples on using the Patch function. In #7 the gblEmployee variable references what exactly?

Similarly, I’m trying to update the value in a record but I cannot get it to work.

Ryan
Ryan
1 year ago

Hello Matthew,

Thank you for the prompt update. Just tried it out again and I got it to work. Nice work!

Kevin P
Kevin P
1 year ago

Thanks for this article! This finally made me understand the differences and I found where I went wrong in my app.

AerisLives
1 year ago

Hello! This is better than MS articles!

I just want to ask, do you have something like the real Upsert?
Like Update if found, Insert if not on only a single record?

AerisLives
11 months ago

oh yes, from FFVII! <3

As for #6, I saw it but I think the update and inserts are separate.

I would like first to check if, let’s say, Character Name: Aeris is present in the List, then update Died: Yes from Died: No.
If not, Insert New Character Name: Aeris, Died: Yes to List instead.
I could do an IF statement but is there a more efficient one?