Fastest Way To Patch All Gallery Items In Power Apps

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:

IDEquipmentNameTitleSerialNumber
1Tractor 1Engine12345
2Tractor 1Front Axle56789
3Tractor 1Brakes56789
4Tractor 1Gear Box45678
5Tractor 1Differential23456
6Tractor 1PTO45678
7Tractor 1Steering System98765
8Tractor 1Rear Linkages12345



After the values are input into the Equipment Serial Numbers SharePoint list it will look like this.

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'

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

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.

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)

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.





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.

Matthew Devaney

Subscribe
Notify of
guest

16 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Asad
Asad
9 months ago

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

Will
Will
9 months ago

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.

Laila
Laila
9 months ago

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!

Last edited 9 months ago by Laila
Laila
Laila
9 months ago

Correcting myself: Patch function can not operate with the same database as forall function?

Moerah
Moerah
9 months ago

So this is faster then show columns method

Moerah
Moerah
9 months ago

Patch(
Employees,
ShowColumns(
colUpdateEmployees,
“ID”
),
colUpdateEmployees

base
base
9 months ago

Hi Mat, how about if we need to create new record?

MoeK
MoeK
7 months ago

THANK YOU very much this has worked very well and its fasstttt ๐Ÿ˜€

Scott McKenzie
Scott McKenzie
6 months ago

Matthew… great article. Is there an analog for Dataverse?

Janus Agbing
Janus Agbing
23 days ago

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.

Anand Tiwari
Anand Tiwari
18 days ago

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.