PATCH Multiple Records In Power Apps 10x Faster

There are several scenarios where you would want to use Power Apps to update multiple records at-once: an attendance tracking app, a to-do checklist app, a workplace audit app, and many more. These apps usually include a gallery control where the user inputs data for each item and then presses a ‘submit button’ to PATCH each individual row in the datasource (see image below).

I will show you the fastest way to PATCH multiple records in Power Apps with a technique that does not appear in the official Power Apps documentation.

‘FORALL + PATCH’ vs. ‘PATCH Only’ Method

A common method used to update a datasource with changes from a collection uses the FORALL function to PATCH each change one-by-one.

// FORALL + PATCH Method
ForAll(
    CollectionOfChanges,
    Patch(Datasource, DatasourceRecord, UpdateRecord)
)

The faster way to update the datasource is to use only the PATCH function: supplying the datasource as the 1st argument and the collection of changes as the 2nd argument. This allows Power Apps to make the all of the updates simultaneously as opposed to one-at-a-time.

// PATCH Only Method
Patch(Datasource, CollectionOfChanges)

CollectionOfChanges must have at least two columns: one column with the matching ID found in the datasource and one or more columns having the values to be changed. All column names must be exactly the same as the datasource. Performance gains achieved will become greater as the number of records in the collection increases.

Attendance Tracking App Example

To illustrate the concept we will build an ‘Attendance App’ to track who was present at an event and who did not show-up. Create a SharePoint List called ‘Attendance’ with the PersonName field as a single-line text column and Attended as a Yes/No column

Now go to Power Apps Studio and create a blank app. Add a connection to the ‘Attendance’ SharePoint List and then put this code in the OnStart property of the app

ClearCollect(colAttendance, Attendance)

Place a gallery control on the canvas with the collection used as the datasource

colAttendance

Insert a label inside the gallery to show the PersonName and then put a toggle beside it to allow the user to track attendance.

Set the Default value of the Toggle to the current value in the collection using this code

ThisItem.Attended

Then write this code inside the OnChange property of the Toggle to update the collection when Toggle is pressed.

Patch(colAttendance, ThisItem, {Attended: Toggle1.Value})

The app can now be used to track employee attendance. Once attendance has been recorded the user will submit the results to the datasource. Create a ‘Submit Fast’ button and place it on the canvas as shown below.

Use this code in the OnSelect property of the button to update the datasource with attendance information.

Patch(Attendance, ShowColumns(colAttendance, "ID", "Attended"));

The ShowColumns function reduces the collection to only the two necessary columns. ID holds the unique identifier which is matched with the record in the datasource and then updated with information from the Attended column. It also removes any ‘read-only’ fields from the collection that could cause an error when attempting a change in the datasource.

Change the Toggle to ‘Yes’ for all the Attendees and then click the ‘Submit Fast’ button to see the changes reflected in the SharePoint List.

To make a comparison in speed create another button called ‘Submit Slow’ and place it beside the ‘Submit Fast’ button.

Use this code in the OnSelect property of the button to update the datasource with attendance information.

ForAll(
    ShowColumns(colAttendance, "ID", "Attended"),
    Patch(Attendance, ThisRecord, {Attended: Attended})
);

Test the button by changing the Toggle to ‘No’ for all attendees. Then click the ‘Submit Slow’ button to update the SharePoint List.

Please note, to make an equal comparison when trying each button you must change attended for an equal number of records in the datasource from yes-to-no OR no-to-yes. Updating records in SharePoint with the same values they currently hold: yes-to-yes OR no-to-no does not result in a record writing to the database.

Speed Test

You should notice a clear difference in performance between the ‘Submit Fast’ and the ‘Submit Slow’ buttons. But to determine the exact difference in time between the ‘FORALL + PATCH’ and ‘PATCH Only’ methods we can setup a basic speed test. Follow the instructions below to do it.

Put this code in the OnStart property of the app to create several variables needed for the test.

// default value of Toggle control
Set(varYesNo, true);

// stores time-to-update measurement for each method 
Set(varDuration_PatchOnly, 0);
Set(varDuration_ForAllPatch, 0);

ClearCollect(colAttendance, Attendance)

Change the Default property of the Toggle to this code

varYesNo

Replace any code in the OnSelect property ‘Submit Fast’ button with this code.

// store the start time
Set(varStartTime_PatchOnly, Now());

Patch(Attendance, ShowColumns(colAttendance, "ID", "Attended"));

// calculate the difference between start time and end time
Set(varDuration_PatchOnly, DateDiff(varStartTime_PatchOnly, Now(), Milliseconds));

// change toggle values to opposite of current value
Set(varYesNo, !varYesNo);

Similarly, replace any code in the OnSelect property of the ‘Submit Slow’ button with this code.

// store the start time
Set(varStartTime_ForAllPatch, Now());

ForAll(
    ShowColumns(colAttendance, "ID", "Attended"),
    Patch(Attendance, ThisRecord, {Attended: Attended})
);

// calculate the difference between start time and end time
Set(varDuration_ForAllPatch, DateDiff(varStartTime_ForAllPatch, Now(), Milliseconds));

// change toggle values to opposite of current value
Set(varYesNo, !varYesNo);

Finally, create a set of new labels and place this code inside the Text property to display the time it took to update all the records using each method.

Text(varDuration_PatchOnly, "[$-en-US]0")&" ms"

Text(varDuration_ForAllPatch, "[$-en-US]0")& "ms"

Setup of the speed test is now finished. Click the ‘Submit Fast’ button and the ‘Submit Slow’ button to see the results

Questions?

If you have any questions or feedback about PATCH Multiple Records In Power Apps 10x Faster 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.