#PowerPlatformTip 57: 'Mastering PATCH'

TL;DR: Patch() accepts tables, not only single records: Patch(DataSource, BaseRecordsTable, ChangeRecordsTable) updates or creates many records in one formula, without a ForAll() loop. Wrap it in IfError(), because single records can still fail.

💡 Challenge

We often use the Patch function to modify individual records. To save a whole edited list, many apps loop with ForAll(colChanges, Patch(...)): one Patch per row, more code, and errors that are hard to collect.

✅ Solution

Pass tables to Patch. The second argument is a table of base records (which rows), the third a table of change records (which new values), matched one-for-one. Patch returns a table with the saved records.

🔧 How It’s Done

1. Collect the changes

🔸 Keep the edited rows in a collection that still carries the record’s primary key: ID for SharePoint, the GUID column (for example Product) for Dataverse. Patch uses that key to find each record:

ClearCollect(
    colChanges,
    { ID: 1, Price: 300 },
    { ID: 2, Price: 400 }
)

2. Update all records in one call

Patch(
    Products,
    ShowColumns(colChanges, ID),
    DropColumns(colChanges, ID)
)

🔸 ShowColumns() hands Patch the base records (which product), DropColumns() the change records (which values). Both tables have the same number of rows, in the same order.

3. Create several records at once

🔸 Use Defaults() once per new record in the base table:

Patch(
    Products,
    Table(Defaults(Products), Defaults(Products)),
    Table(
        { Title: "Mint", Price: 60 },
        { Title: "Peach", Price: 80 }
    )
)

4. Catch errors

IfError(
    Patch(Products, ShowColumns(colChanges, ID), DropColumns(colChanges, ID)),
    Notify("Some records failed to update: " & FirstError.Message, NotificationType.Error),
    Notify("All changes saved", NotificationType.Success)
)

🎉 Result

All edited rows are saved with one formula, without a loop, and the user gets one clear message: everything saved, or which error occurred.

🌟 Key Advantages

🔸 Less code: one Patch replaces the ForAll() loop around single Patch calls.

🔸 One result: Patch returns a table with every saved record, ready for a follow-up ClearCollect() or a check.

🔸 One error check: IfError() around the whole call instead of error handling per row.

⚠️ Good to Know

🔸 Not a transaction. If one record fails (permissions, validation, a conflict with a change on the server), the other records can still be saved. Check the result and the error message instead of assuming all or nothing.

🔸 Same row count. The base table and every change table need the same number of records, otherwise Patch returns an error.

🔸 Delegation: Patch itself is not delegated, it writes. A delegation warning in such a formula comes from the Filter() or LookUp() that selects the records.

🎥 Video Tutorial

🛠️ FAQ

1. Can I use Patch with tables on any data source?

It works with tabular data sources such as SharePoint, Dataverse and SQL Server, and with collections. The record shape for special columns (Choice, Lookup, Person) differs per data source.

2. What happens if column names or data types don’t match?

The formula doesn’t compile, or the data source rejects the change. Build the collection with the data source’s schema first (see Tip 66 below), then the columns line up.

3. Will Patch overwrite records that are not in the tables?

No. Patch only touches the records in the base table and only the columns in the change records. Everything else stays unchanged.

🔗 Related Tips

🔸 #PowerPlatformTip 62: ‘UpdateIf’ - a condition-based alternative when you only need to change one field on matching records, not a full bulk patch.

🔸 #PowerPlatformTip 66: ‘Load Data Source Structure’ - build the collection with the right schema before you patch it back.

🔸 #PowerPlatformTip 136: ‘Patch + Coalesce Upsert’ - combine Patch() with Coalesce() to create or update in a single call.