#PowerPlatformTip 131: 'ForAll & Patch Optimization in PowerApps'

TL;DR: Two patterns update many records: ForAll with Patch inside, or Patch(DataSource, BaseRecords, Changes). Both worked and took practically the same time in a 25-record test, so the table form is not a speed trick. The real trap is ThisRecord inside LookUp.

💡 Challenge

You have a table of new values, for example in a collection, and want to write them to existing records of a data source. Formulas like LookUp(SharePointList, ID = ThisRecord.ID) inside ForAll look right but compare the wrong thing, and it is easy to mix up the two ways of combining ForAll and Patch.

✅ Solution

Name the loop record with As so it stays reachable inside LookUp. Then choose between ForAll with Patch inside, or one Patch that receives a table of base records and a table of changes.

flowchart LR
    subgraph A["Pattern 1: Patch inside ForAll"]
        A1["ForAll(Source As s, ...)"] --> A2["Patch(DataSource,<br/>LookUp(...), changes)<br/>per source row"]
    end
    subgraph B["Pattern 2: ForAll inside Patch"]
        B1["Patch(DataSource,<br/>base table,<br/>change table)"] --> B2["one function call<br/>for all rows"]
    end

🔧 How It’s Done

1. Name the loop record with As

🔸 Inside LookUp, ThisRecord is the row of the data source that is being searched. In LookUp(Accounts, Account = ThisRecord.id) the name id is not found, because the source row is not reachable through ThisRecord. Inside ForAll, give the loop record its own name:

ForAll(
    Source As s,
    Patch(
        Accounts,
        LookUp(Accounts, Account = s.id),
        { 'Account Name': s.n }
    )
)

🔸 Source is a table with the columns id and n, for example a collection. Source can also be Gallery.AllItems; the values of the controls in the gallery are available there as columns.

2. Update with a table of base records and a table of changes

🔸 Patch accepts a table of existing records plus a table with one change record per base record:

Patch(
    Accounts,
    ForAll(Source As s, LookUp(Accounts, Account = s.id)),
    ForAll(Source, { 'Account Name': n })
)

🔸 The third argument must be a table. A single change record together with a table of base records is rejected as an invalid argument type.

🔸 The base records do not have to be fetched with LookUp. A record with only the primary key is enough:

Patch(
    Accounts,
    ForAll(Source As s, {Account: s.id}),
    ForAll(Source, { 'Account Name': n })
)

Run against a Dataverse Accounts table with two source rows, this returned the two patched records with accountid and the new name, and a Filter on the table afterwards showed the new names. The variant with LookUp(Accounts, Account = s.id) as base table returned the same.

3. Know what differs

🔸 Pattern 1 runs one Patch per source row and returns the patched records.

🔸 Pattern 2 is one Patch call that gets all base records and all changes and returns the patched records as well.

🔸 Both patterns updated the same records with the same values.

🎉 Result

🔸 Correct match: each source row updates exactly the record with its key.

🔸 Two working patterns: use whichever reads better in your app.

🔸 Measured: 25 records on a Dataverse table took about 4.2 to 4.3 s with ForAll and Patch, and about 4.5 s with the base and change table form.

🌟 Key Advantages

🔸 As removes the ThisRecord ambiguity in nested functions.

🔸 The base and change table form keeps the whole update in one expression.

🔸 Both patterns update existing records by their key.

🛠️ FAQ

1. Why does LookUp(DataSource, ID = ThisRecord.ID) inside ForAll not find the right record? Inside LookUp, ThisRecord is the row of the data source being searched, not the row of the ForAll loop. Name the loop record with As, for example ForAll(Source As s, ...), and compare with s.ID.

2. What is the difference between ForAll(Source, Patch(...)) and Patch(DataSource, BaseRecords, Changes)? ForAll with Patch runs one Patch per source row. Patch(DataSource, BaseRecords, Changes) takes a table of existing records and a table of change records in one function call. Both update the same records and took practically the same time in a 25-record test.

3. Can I pass one change record to Patch together with a table of base records? No. The third argument must also be a table with one change record per base record. A single record is rejected as an invalid argument type.

4. Do the base records have to come from a LookUp? No. A record that holds only the primary key works as a base record: Patch(Accounts, ForAll(Source As s, {Account: s.id}), ForAll(Source, { 'Account Name': n })) updated the right records on a Dataverse table without one LookUp per row.

🔗 Related Tips

• #PowerPlatformTip 57: Mastering Patch, the fundamentals behind bulk Patch operations.
• #PowerPlatformTip 81: Fast DataSource Updates, more ways to speed up data writes.
• #PowerPlatformTip 91: If Function in Formulas, wrap the Patch inside an If() to only update records that meet a condition.