PATCH Multiple Records In Power Apps 10x Faster

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

PersonNameAttended
Mary SwanNo
James ReadingNo
Jessica SandersNo
Lisa RobinsonNo
Tyler HillNo
Quinn AdamsNo
John WrightNo
Robert PortmanNo
Sally AndersNo
Fred KleinNo



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.

Matthew Devaney

Subscribe
Notify of
guest

123 Comments
Oldest
Newest
Inline Feedbacks
View all comments
André Moraes
André Moraes
4 years ago

Great tip! I will be testing very soon!

Ryan Bond
Ryan Bond
4 years ago

Hey Matthew,
I love this one because if I take a little bit of time to get my collection field names matching my data source column names, then patching becomes such a simple exercise: patch(datasource, collection) – too easy!

Krishna Vandanapu
Krishna Vandanapu
4 years ago

No Comments! This is a right source if someone really want to excel their knowledge!
Thank you Matthew for your time.

Michael Cross
4 years ago

We tested using the SQL Server connector. For 500 records, it took about 1.5 minutes using the slower method and about 1 minute for the faster method. So about 33% improvement. I’m guessing that the performance will differ depending on the connector used. But it’s still a BIG improvement! Thanks so much for sharing!

Michael Colbert
Michael Colbert
4 years ago

I’d like to compare this to calling a stored procedure in SQL passing all the rows as a JSON array. This is the method I use as it handles both updated and new records by using T-SQL MERGE. I realize that not every app maker has access to SQL, the skills for developing stored procedures, but it will be interesting to test.

Matthew, is it possible to share this app?

trackback

[…] this? Yes you heard it right. And thanks to Nataraj Yegnaraman for guiding me here and pointing to this post. You can follow his wonderful blog […]

Lucius
Lucius
4 years ago

Thanks for sharing! Absurd difference! You helped me a lot!!

Ron
Ron
4 years ago

Thanks Matthew

Virakone
Virakone
4 years ago

This is a great resource, thank you for the work on this!

I have a similar speed issue related to the for all function but for for GET requests for particular nested items.

Ex. For All Product groups GET related Products (product names, product SKUs, etc) > store in collection > display the values in a text label in a gallery

My issue is that with For All, it seems to iterate through each product group one-by-one whereas I would LOVE to have to Collect concurrently.

What ways could I approach this problem? In this case, not writing but reading from a data source.

Simon Cobb
3 years ago

Thank you Matthew for sharing this, it’s fantastic.

Christopher Carswell
Christopher Carswell
3 years ago

Is this only working for simple columns (text, number, boolean…)? I tried it with sharepoint choice and lookup columns and it didn’t seem to work (whereas the ForAll loop does). Also, can it work with new items in the list?

Crispo Mwangi
Crispo Mwangi
3 years ago

Yes, it has a problem with complex SharePoint columns

Martin S.
Martin S.
1 year ago
Reply to  Crispo Mwangi

One year later, but you saved me a lot of work, so thank you. I’d like to add it also works with complex columns (I’ve tried choice), you just have to submit the value in the correct form (for a choice column it has to be in the format “{Value: ‘Your Text’}”).

Keval Solanki
Keval Solanki
3 years ago

Thanks Matthew. This is superb article about patch and performance consideration. I tried and got it working. Nice explanation.

Peter Heffner
Peter Heffner
3 years ago

Matthew, Thanks for this article!
I gave it a try in our stock taking app, but Patch expects a record and it looks I have a table? This is confusing. Is there a solution?

BatchPatch.jpg
Peter Heffner
Peter Heffner
3 years ago

Still not working. I also tried
Patch(‘[dbo].[DST_ItemCount]’,coll_ThisShelf);
But the error won’t go away.
Patching with a ForAll loop works well, but is slow, of course.

Riley
Riley
3 years ago
Reply to  Peter Heffner

Hi Peter,

Did you ever get this resolved? I’m running into the same problem, and I can’t seem to get it working.

Aleem
Aleem
9 months ago
Reply to  Peter Heffner

Same issue

Jeff Scott
Jeff Scott
3 years ago

This is a super-great tip and is just what I was searching for. Thanks a zillion!

Davide Mercadante
Davide Mercadante
3 years ago

HI Matthew, all your blogs are amazing, but this is really super helpful. I have only one struggle trying to implement the onchange strategy for more fields. I have two dropdowns and the second is cascading from the first, but if I change only the first one (the second change automatically)
, the change of the second dropdown is not reflected in the patch. Any idea how to solve it? I am really really struggling as it impacts the usability in my case.

Rizky Aditya
Rizky Aditya
3 years ago

Hello Matthew,

Thank you for this very useful tips for patching records from powerapps.
I realized that your example is to just patch the existing record(s).

Is it also possible if we want to use the same method to patch for new records as well?
I mean when we use the Patch(yourdatasource, Defaults(yourdatasource), newrecords).

I hope to hear from you.

Thank you!

Andre
Andre
2 years ago

Hey Matthew …

This is a wonderful article and fabulous to use. Couple of points for me …

One of my apps is “moving” from 1 SharePoint List (sharepoint-list-1) to another sharepoint list of the same construct (sharepoint-list-2). In this case, the ID column needs to be “dropped” in order to do the move (since the new table doesn’t have an existing ID for the new row). I broke this down into 3 steps:-

  1. UpdateIf( collection1, true, {field being updated})
  2. ClearCollect(collection2, ShowColumns( collection1, “column1″, column2”,etc.))
  3. Patch(sharepoint-list-2, collection2)

Now, the next problem was to REMOVE the old data in the Original sharepoint-list-1 list. I used this code:-

  1. Remove(sharepoint-list-1, collection1)

The problem ironically is the performance of the remove is causing chaos and taking minutes and timing out frequently. Is there a way to do a batch remove the same way we have a batch patch.

Art Karp
Art Karp
2 years ago

If you cut/paste this code into the canvas app designer, it doesn’t work until you fix the quote marks (“) around the first and last names. Oops.

Lael Heasman
Lael Heasman
3 years ago

Hi Matthew,
Firstly, I have used so much of your stuff – it is all amazing – so thanks a million! You guys who contribute so much of your time and expertise to the PowerApps Community need real cred!

On this amazing bulk update trick of yours, my collection is derived from a SQL View which obviously does not have a primary key defined. Although there is a unique id in the collection, and I do pass it in the second parameter for the bulk update along with the fields I want updated, PowerApps is still giving me an error “The data source supplied to the function is invalid”. I am sure it is a problem with the key. Is there a way I can get PowerApps to recognise which is the “key” field in my collection?

Lael Heasman
Lael Heasman
3 years ago

Oh No! My base table has a hierarchyid in it – so no way to create collection from the table – powerapps and hierarchy id’s are sadly not compatible – and hence my view which leaves out the hierarchyid. Thanks anyway. I have gone back to my original updateif strategy – was just hoping your fancy patch would improve performance. Thanks for the response and keep up all the great work!

Nick
Nick
2 years ago

After a bit of research, it seems that this approach will not work with Dataverse. Disappointing as it would make life a lot simpler.

Elbert Q Blakely
Elbert Q Blakely
2 years ago

Mathhew: Thanks for the posting on this technique. I have used it for Sharepoint with little trouble. However, I have recently set up Dataverse tables, and I am having difficulties with it (the patch function has an error – it says something about “expecting a record value instead.” Is there anything that has to be different with Dataverse?

Elbert Q Blakely
Elbert Q Blakely
2 years ago

Worked like a charm! Thanks for the advice

michal
michal
2 years ago

How I ca use empty collection schema to create collection?

Srinivas Thota
Srinivas Thota
2 years ago

Hello Matthew,

Thank you for sharing the tips on improving performance with the Patch statement. We need to create new records into a new table which has a different schema from the old table as part of migration activity. Also, we are trying to modify the old collection with the new column names to match with the new schema, however, we are getting a syntax error saying that Patch has invalid arguments. Invalid argument type(Table). Expecting a Record value ingested. Also, we have a field name that’s matching in both source and destination tables, but having a different schema. FYI, this scenario just works fine with ForAll and Patch combination. We wanted to transform to improve the performance.

It would be great if you can confirm that whether we can use this feature mentioned in the blog between two different tables.

Thanks,
Srinivas

Srinivas Thota
Srinivas Thota
2 years ago

Thanks Matt for your prompt response. We are migrating data between Dataverse tables which have different schema. We researched on PowerQuery, PowerAutomate and other options and then finally, decided to go with the PowerApps as we do not have massive amounts of data. We will try the solution out as mentioned by you and let you know.

Rosie
Rosie
10 months ago

Hello, this doesn’t seem to be supported ClearCollect(colAttendance, FirstN(Table({PersonName: “Matthew Devaney”, Attended: true}),0))
I am trying this and I get an error FirstN has some invalid arguments.

I have tried this as I am getting an error patching to create new records.
Even though I have the column names exact I get this error “The specified column ‘ContainerNo’ does not exist. The column with the most similar name is ‘ContainerNo’.

I initially tried ClearCollect(cltBFCameraPics, FirstN(QPContainerPhotos,0)) but I get an error “The function ‘ClearCollect’ has some invalid arguments.
I then tried using ShowColumns to retrieve the specific columns but they are the ‘Name’ rather than the DisplayName so they don’t match up anyway.

Can you offer any suggestions?

Many thanks
Rosie

Michel
Michel
2 years ago

I had no idea how to push multiple records until I saw your article.

Also the trick to create the empty collection structure:

ClearCollect( TestOL2DB , FirstN(‘dbo.[N4_OrderLines]’,0))

is absolutely brilliant.

I never would have thought of using that with a 0 argument.

I have created 2 collections, one containing the ID for use with Patch to update existing records and one without the ID for use with Collect to insert new records.

The ID field in the SQL table is an auto increasing integer.

When I try to use Collect to write a collection to SQL, it fails to work in my environment. See attached picture.

But the Patch works like a charm, and so much quicker than line by line using ForAll.

Thank you very much for all this insight, Matthew. 🙂

CollectResult.png
Ali Asif
Ali Asif
2 years ago

Glad that I am able to patch data faster in excel. I have one problem that how to delete all rows of excel faster as we patch I am stuck in this kindly help.

Ali Asif
Ali Asif
2 years ago

Matthew,
Thanks for your reply but I am not using SharePoint instead of that I am using patch function to store data in excel one drive file like I am exporting Gallery data into excel and I want on remove function it will empty my file but its take a lot time. Code is below for your kind reference.
***
ClearCollect(
  col418,
 ForAll(
    Gallery4.AllItems,
    {
      Column1:Label11_42.Text,
      Column2:Label11_43.Text,
      Column3:Label11_44.Text,
      Column4:Label11_45.Text,
      Column5:Label11_46.Text,
      Column6:Label11_47.Text,
      Column7:Label11_48.Text
    }
  )
);
Patch(JBFOURONEEIGHT, ShowColumns(col418, “Column1”, “Column2″,”Column3″,”Column4″,”Column5″,”Column6″,”Column7”));

Refresh(JBFOURONEEIGHT);

RemoveIf(JBFOURONEEIGHT,true)

[Info=”Is there any way it will became faster If i used RemoveIf it slowed down the performance “]
Thanks

Last edited 2 years ago by Ali Asif
Ali Asif
Ali Asif
2 years ago
Reply to  Ali Asif

Please Guide

Ali Asif
Ali Asif
2 years ago
Reply to  Ali Asif

Sir can you help about this.