Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 21

Optimizing Data Operations with ForAll and Patch

Recommended resources

Hello! Let's dive into our next topic on building powerful Canvas Apps.

In our last lesson, we learned how to manage temporary data using variables and collections. We saw how collections are perfect for staging data, like creating a "shopping cart" of items before submitting an order. Today, we'll complete that pattern by learning how to efficiently save that entire collection to your data source.

This lesson directly addresses the learning outcome: Implement bulk data operations by combining the ForAll and Patch functions. Mastering this is a cornerstone of building performant Power Apps that handle multiple records. From your experience with backend development, you'll recognize this as a classic performance optimization: moving from row-by-row processing to batch operations to minimize network calls and database transactions.

The Intuitive (but Slow) Method: Looping with ForAll

When faced with saving a collection of records, the most intuitive approach for many developers is to loop through the collection and save each record one by one. In Power Fx, this translates to using the ForAll function to iterate through a collection and calling the Patch function for each item.

ForAll(myCollection, Patch(MyDataSource, ThisRecord))

While this code works, it is highly inefficient. For every single record in your collection, Power Apps makes a separate network request to the data source. If you have 50 items in your collection, that's 50 individual round-trips. This is a classic "N+1 query problem" that leads to slow performance and a poor user experience, especially on mobile networks.

To see this inefficiency in action, let's watch the first part of a demonstration by Reza Dorrani.

PowerApps bulk update collection with Patch

This video clearly demonstrates the performance difference between inefficient and efficient bulk operations. We'll start by watching the segment on the slow, row-by-row method.

Watch from 01:54 to 06:48. Pay close attention to the ForAll(Patch(...)) formula being used and the time it takes to update 41 records. This establishes a performance baseline we will aim to beat.

As you saw, updating just a few dozen records took around 17-19 seconds. This is unacceptable for a production application.

The Efficient Method: Batching with Patch

The Patch function in Power Fx is overloaded. It can modify a single record, but it can also accept a table of records to modify the data source in a single, batched operation. This is the key to high-performance bulk data operations.

The syntax is beautifully simple:

Patch(MyDataSource, myCollectionOfChanges)

When you use this syntax, Power Apps sends the entire collection to the data source in a single network call. Dataverse, in particular, is optimized to handle this as a single transaction, resulting in a dramatic performance improvement.

Key Requirements for Batch Patching

For this to work, Power Apps needs to know how to match the records in your collection to the records in the data source.

  1. Schema Match: The collection (myCollectionOfChanges) must have a column structure that matches the data source (MyDataSource).
  2. Primary Key: For updates, each record in the collection must contain the value of the primary key for the corresponding record in the data source. For Dataverse, this is the unique GUID column. For SharePoint, it's the ID column.

Now, let's see the efficient method in action and compare the performance.

PowerApps bulk update collection with Patch

Continuing with the same video, let's now watch the correct, high-performance method.

Watch the segment from 06:48 to 09:08. Notice the simplified formula and the incredible difference in execution time. The same operation now takes only 2 seconds.

This ~90% performance improvement is achieved simply by changing the formula to leverage the built-in batching capability of the Patch function.

For a concise, practical walkthrough, the following article by Matthew Devaney is an excellent resource. It codifies this "Patch Only" method.

PATCH Multiple Records In Power Apps 10x Faster

This article provides a clear, step-by-step guide to implementing the efficient 'PATCH Only' method. It reinforces the concept with a practical example.

Please read the following sections: 'FORALL + PATCH' vs. 'PATCH Only' Method: This introduces the two approaches side-by-side. Attendance Tracking App Example: Follow the example of how to set up a collection (colAttendance) and then use the efficient Patch(Attendance, ShowColumns(colAttendance, "ID", "Attended")) formula. The use of ShowColumns is a good practice to ensure only necessary columns are sent. Dataverse Considerations (in comments): Read the exchange between Elbert Q Blakely and Matthew Devaney about using this with Dataverse. The tip ClearCollect(colOrders, FirstN('Orders', 0)) is a common and important pattern for initializing a collection with the correct Dataverse table schema to avoid errors.

Test your understanding!

You have a Canvas App for project managers to update multiple tasks at once. The tasks are stored in a Dataverse table named Tasks.

On one screen, you have collected user modifications into a collection named colUpdatedTasks. This collection has the same schema as the Tasks table, including the primary key column, Task (the unique identifier for the Tasks table).

Write the single, efficient Power Fx formula to save all the changes from colUpdatedTasks back to the Tasks data source.

Show answer 
Patch(Tasks, colUpdatedTasks)

This simple formula leverages the batching capability of Patch. It will send the entire colUpdatedTasks collection to Dataverse, which will then update all the matching records based on their primary key.

Beyond Updates: Bulk Create, Copy, and Delete

The same batching principles can be extended to creating, copying, and deleting records. The logic hinges on the presence of the primary key in the records you provide.

Bulk Create and Copy

When you Patch a collection to a data source, Power Apps inspects each record in the collection:

  • If a record contains a valid primary key, Patch performs an update.
  • If a record does not contain a primary key, Patch performs a create (i.e., it inserts a new record).

This allows for powerful "upsert" logic and makes bulk creation straightforward. To bulk copy records, you would first collect the source records, modify them as needed in the collection, and then remove the primary key column before patching.

Watch how this is done.

PowerApps bulk update collection with Patch

Let's return to Reza's video to see how to perform bulk create and delete operations.

Watch the following two segments: Bulk Copy/Create (09:08 - 12:36): Observe how the DropColumns function is used to remove the primary key from the collection. This signals to Patch that it should create new records instead of updating existing ones. Bulk Delete (12:36 - 13:26): Note the use of the Remove function. Similar to Patch, it can accept a collection of records (with primary keys) to delete them all in a single, efficient operation.

The formulas for these operations are:

  • Bulk Create: Patch(MyDataSource, DropColumns(myCollectionOfNewItems, "PrimaryKeyColumnName"))
  • Bulk Delete: Remove(MyDataSource, myCollectionOfItemsToDelete)

Summary of Bulk Operation Patterns

Operation Power Fx Formula Key Concept
Bulk Update Patch(DataSource, Collection) Collection records must contain the primary key.
Bulk Create Patch(DataSource, Collection) Collection records must not contain the primary key. Use DropColumns or ShowColumns to ensure this.
Bulk Delete Remove(DataSource, Collection) Collection records must contain the primary key.

These patterns are fundamental to building scalable and responsive apps. Using them correctly is a mark of a professional Power Platform developer.

Conclusion

Today you've learned how to move beyond slow, row-by-row data operations and implement efficient, batch-based CUD (Create, Update, Delete) operations. This skill is critical for any app that needs to handle more than a handful of records at a time.

Key Takeaways:

  • Avoid the ForAll(Patch(...)) pattern due to its poor performance from making N+1 network calls.
  • Use Patch(DataSource, Collection) for high-performance bulk updates and creates.
  • The presence or absence of a primary key in the collection's records dictates whether Patch updates or creates.
  • Use Remove(DataSource, Collection) for efficient bulk deletions.

Up Next:
Now that we've covered how to efficiently save data, it's crucial to ensure the data is valid before we attempt to save it. In our next lesson, we will focus on client-side validation, where you'll learn to "Implement input validation on user entry fields using Power Fx functions like IsMatch and IsBlank."