Fastest Way To Patch All Gallery Items In Power Apps
It is useful to know how to patch all items in a gallery when you want to update several items in a SharePoint list at once. To perform batch updates you can use a little-known variation of the patch function. This offers faster performance than the traditional ForAll + Patch Function approach which performs the patches one-by-one.
Table of Contents
โข Introduction: The Equipment Serial Numbers App
โข Setup The SharePoint List
โข Insert A Gallery Into The Canvas App
โข Add A Text Input Field To The Gallery
โข Patch Gallery Updates To SharePoint List
โข Handle Errors When Patching Gallery Updates
โข Cancel Updates Made To The Gallery
Introduction: The Equipment Serial Numbers App
Employees at a farm equipment repair company track their customer’s equipment inside of an app. The repair company records the serial number of each part inside the equipment for warranty purposes.
Setup The SharePoint List
Create a new SharePoint list named Equipment Serial Numbers with the following columns:
- ID
- EquipmentName (single-line text)
- Title (single-line text)
- SerialNumber (single-line text)
Populate the SharePoint list with the following values:
ID | EquipmentName | Title | SerialNumber |
1 | Tractor 1 | Engine | 12345 |
2 | Tractor 1 | Front Axle | 56789 |
3 | Tractor 1 | Brakes | 56789 |
4 | Tractor 1 | Gear Box | 45678 |
5 | Tractor 1 | Differential | 23456 |
6 | Tractor 1 | PTO | 45678 |
7 | Tractor 1 | Steering System | 98765 |
8 | Tractor 1 | Rear Linkages | 12345 |
After the values are input into the Equipment Serial Numbers SharePoint list it will look like this.
Insert A Gallery Into The Canvas App
Open Power Apps Studio and start a new canvas app from a blank screen. Add a label with the words Equipment Serials to act as the titlebar. Then create two more labels with the words Title and Serial Number to serve as headings for the gallery.
Add Equipment Serial Numbers as a datasource and then insert a new gallery into the app.
Use this code in the Items property of the gallery.
'Equipment Serial Numbers'
Add A Text Input Field To The Gallery
We want to display all rows from Equipment Serial Numbers in the gallery and give the user a way to edit the serial numbers. Insert a label into the gallery to display the Title.
Use this code in the Text property of the label.
ThisItem.Title
Then add a text input control to the gallery for the user to write-in a serial number.
Use this code in the Default property of the text input. When the SharePoint list item has a serial number it will be display as the text input’s initial value.
ThisItem.SerialNumber
Patch Gallery Updates To SharePoint List
When the user presses the Save button we want to write all of the text input fields whose values changed to SharePoint. We will exclude any unchanged text inputs from the set of updates.
Insert a new button with the word Save below the gallery.
Write this code in the OnSelect property of the Save button. First, we create a collection named colGalleryUpdates to capture any rows in the gallery that changed. We do this by filtering the gallery to only show rows where the SerialNumber in SharePoint does not match the Serial Number from the text input. Notice that the SharePoint column name is inside of a Text function. This is necessary to prevent to change any null values in SharePoint to a blank text string for comparison.
Then we use the Patch function to write the updated values in colGalleryUpdates to SharePoint. When the changes are completed we show a success notification at the top of the screen.
// create a collection of SharePoint list items to be updates
ClearCollect(
colGalleryUpdates,
ForAll(
Filter(
gal_EquipSerials_List.AllItems,
Text(SerialNumber) <> txt_EquipSerials_SerialNo.Text
),
{
ID: ThisRecord.ID,
SerialNumber: ThisRecord.txt_EquipSerials_SerialNo.Text
}
)
);
// update SharePoint list with gallery values
Patch(
'Equipment Serial Numbers',
colGalleryUpdates.ID,
colGalleryUpdates
);
// show a success notification
Notify(
$"{CountRows(colGalleryUpdates)} serial number(s) updated successfully",
NotificationType.Success
);
// reset the gallery
Reset(gal_EquipSerials_List)
Preview the app and write some values serial number into the serial numbers fields. When we press the Save button the values are updates in SharePoint.
Handle Errors When Patching Gallery Updates
We cannot safely assume that patching the gallery updates to SharePoint will happen without any errors. A dropped network connection could interrupt the update. Or maybe the user did not make any changes before pressing Save and there is nothing to updates. For these reasons and more we must build error-handling into our code.
Update the gallery Items code with these changes. The IsError function is used to check whether the Patch function executed successfully. Then an error message is displayed in the event of a failure. The code also includes a condition to check for no changes to the gallery.
// create a collection of SharePoint list items to be updates
ClearCollect(
colGalleryUpdates,
ForAll(
Filter(
gal_EquipSerials_List.AllItems,
Text(SerialNumber) <> txt_EquipSerials_SerialNo.Text
),
{
ID: ThisRecord.ID,
SerialNumber: ThisRecord.txt_EquipSerials_SerialNo.Text
}
)
);
If(
// check whether any updates were made to the gallery
!IsEmpty(colGalleryUpdates),
If(
IsError(
// update SharePoint list with gallery values
Patch(
'Equipment Serial Numbers',
colGalleryUpdates.ID,
colGalleryUpdates
)
),
// show an error notification
Notify(
"Serial numbers update failed",
NotificationType.Error
),
// show a success notification
Notify(
$"{CountRows(colGalleryUpdates)} serial number(s) updated successfully",
NotificationType.Success
)
),
// show error notification for no changes
Notify(
"No serial numbers were changed before save",
NotificationType.Error
)
);
// reset the gallery
Reset(gal_EquipSerials_List)
Cancel Updates Made To The Gallery
A user may decide they do not want to keep the changes they have made to the gallery. We will provide them with a cancel button to revert the gallery back to the current values in SharePoint. Create a new button with the text Cancel and place it beside the save button.
Write this code in the OnSelect property of the Cancel button. The variable gblResetGallery is toggled on and off to trigger a reset of the text inputs while the Reset Function moves the gallery back to the top position if it has been scrolled down.
Set(gblResetGallery, true);
Set(gblResetGallery, false);
Reset(gal_EquipSerials_List)
Then go to the text input and update the Reset property.
Use this value in the Reset property of the gallery.
gblResetGallery
Run the app in preview mode. Enter a few values into the text inputs then press Cancel. The text fields will revert to their original values.
Did You Enjoy This Article? ๐บ
Subscribe to get new Power Apps & Power Automate articles sent to your inbox each week for FREE
Questions?
If you have any questions or feedback about Fastest Way To Patch All Gallery Items In Power Apps 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.
Thanks. Is there a way to extend this to add a new/delete button as well on top and ability to add/delete records at the same time?
cheers
Asad,
Yes I have a tutorial that I wrote about how to make an Excel Style Editable table that shows how to Add, Edit and Delete.
https://www.matthewdevaney.com/power-apps-excel-style-editable-table-part-1/
Matt — thanks for at least worrying about error handling. Most ignore this. Ideally you would keep track of the failures and successes and give the user the opportunity to resubmit the failed items perhaps in the case of a lost connection when the network becomes available again. This is tricky as you can’t modify the collection that is the subject of the ForAll loop. I create two additional collections for success and failure and move the records as processed in the ForAll loop into the respective collections. You can then reprocess the failed records at a later time. To protect against unexpected termination of the application with unprocessed records you can persist the collections on the local device and you need to use the success collection to avoid reprocessing successful insert. You can also use these techniques to build pretty robust off-line applications.
Will,
Thank you for acknowledging the error handling in this solution. I believe it is important, and an instant โtellโ of whether someone is a novice or better Power Apps programmer.
I agree with the method you detailed. And I use something similar in my client-work. The hard part about writing tutorials is decided โhow far to go.โ What to leave in, and what to leave out
Fortunately I am lucky to clever readers like yourself who are willing to jump in and share with other. Cheers!
Hey Mathew! Thanks for the article!
Do you know a way to patch from one database to another?
I’m trying to do this with galleries, tables and even collection hahah but it’s not working.. In general, I’ve a excel list that i imported to powerapps, where the data came, and I want to pacth this data to a Sharepoint list where I have the same columns.
I’m understanding that ThisRecord in Patch, when used with ForAll only works with the same database, not different..
And I just tried somethig that shows me the error msg sayng that Pacth function doesn’t works with ForAll Functions?!?!?!
I’m kinding facing some troubles…If you could help me I’ll be very greatfull!
Laila,
Patch works inside of the ForAll function. But you cannot PATCH the same datasource you are looping over in ForAll.
Correcting myself: Patch function can not operate with the same database as forall function?
So this is faster then show columns method
Moerah,
What is the ShowColumns method?
Patch(
Employees,
ShowColumns(
colUpdateEmployees,
“ID”
),
colUpdateEmployees
Hi Mat, how about if we need to create new record?
Base,
You can look at one of the first articles I ever wrote about that very subject:
https://www.matthewdevaney.com/power-apps-excel-style-editable-table-part-1/
THANK YOU very much this has worked very well and its fasstttt ๐
Matthew… great article. Is there an analog for Dataverse?
Does this only work on SharePoint lists?
i tried the patch( table, collection.id, collection) for a dataverse or sql table and it said it was invalid arguments.
I am trying to patch a gallery data which has Text Input control in View mode and few of the columns are lookup value. How do I patch these data. I am unable to refrence the lookup values to their respective tables. Please share your thoughts.