PATCH Multiple Records In Power Apps 10x Faster

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/patch-multiple-records-in-power-apps-10x-faster/. Author: Matthew Devaney.

PATCH Multiple Records In Power Apps 10x Faster Posted by - Matthew Devaney on - August 2, 2020 130 Comments

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) ) Code language: JavaScript ( javascript )

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) Code language: JavaScript ( javascript )

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 PersonName Attended Mary Swan No James Reading No Jessica Sanders No Lisa Robinson No Tyler Hill No Quinn Adams No John Wright No Robert Portman No Sally Anders No Fred Klein No

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 Code language: CSS ( css )

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}) Code language: CSS ( css )

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" )); Code language: JavaScript ( javascript )

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}) ); Code language: CSS ( css )

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) Code language: JavaScript ( javascript )

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); Code language: JavaScript ( javascript )

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); Code language: JavaScript ( javascript )

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" Code language: JavaScript ( javascript )

Text(varDuration_ForAllPatch, "[$-en-US]0" )& "ms" Code language: JavaScript ( javascript )

Setup of the speed test is now finished. Click the ‘Submit Fast’ button and the ‘Submit Slow’ button to see the results Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up 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. Collections Gallery Control PATCH function Performance/Optimization Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps