Simplified Multi-Item Editing / Patching

Microsoft's methods for Patch(), Update(), and UpdateIf() make it somewhat stupid when it comes to editing/updating records en masse.  Trying to pull this off can get convoluted if you aren't careful.

This is an example method for taking a Gallery where you display/edit multiple items at once and then save them all as a part of a single command.

Overview

Let's say you have a Collection of items that you want the end-user to view/edit simultaneously.  Imagine a "quick list" of a few specific fields that are commonly updated on a large number of items at once vs. having to go into the details page on each item.

In this scenario, I have a simple list of meal orders:

Also, we have a few fields that are a part of this Gallery and a few that are not:

This is a fairly common scenario.  Where we have an "Advanced" editing page that shows all fields relating to a record, but we also have a "Simplified" page where the most commonly edited fields can be done to many records at once.

So how do we properly structure this in a way where we don't have to do a lot of manual work here to maintain it?

Setting up the Collection

To make this simple on yourself, you first need to build out a Collection that is identical to the Data Source you're writing to.  In this example, I'm using a SharePoint list named multiUpdateExample.

To set this up, we're going to create the Collection using a bit of a trick here.  If we put this in the OnVisible event for the page, then it gets things in order:

//OnVisible for Page

ClearCollect(

    itemsToChange,

    Filter(

        multiUpdateExample,

        false

    )

);

Clear(itemsToChange);

We aren't really populating the Collection w/ anything except the headers, but I also don't fully trust the list value unless I Clear() it at the end.  The "trick" here is using the criteria for the Filter as simply being "false".

Note: using the Filter() w/ a criteria of simply "false" will throw a warning, but everything will work.

Setting up the Gallery

We'll use a blank Vertical Collection here and add a few Labels and a Dropdown.  The Dropdown is what we're assuming our end-users will be changing rapidly.  When they make changes, we'll enable/warn users to save or abandon their changes.  We could of course do these change immediately, but our assumption here is that we want to allow the user to accept their changes before we make them active.

To properly do this, when a user makes a change, we want to add this specific Record to our itemsToChange Collection from above.  To do this, we add the following code to our OnChange event for our status drop-down.

//OnChange for Field(s)

RemoveIf(

    itemsToChange,

    ID = ThisItem.ID

);

Patch(

    itemsToChange,

    Defaults(itemsToChange),

    ThisItem

);

UpdateIf(

    itemsToChange,

    ID = ThisItem.ID,

    {status: statusUpdate.Selected.Value}

);

To explain what's happening here, we are working with a Collection that is all of the data we intend to (eventually) write back to the original data source (e.g. multiUpdateExample).  Step by step we are:

• Clearing any Records that match the ID of the record the user just changed
• Adding the original full record to the same Collection
• Change the record value that was updated

All of this is in-memory at this point.  Nothing has been written back to the main Datasource.  We're just setting ourselves up to eventually write this easily back to the original permanent repository.

If we are including multiple fields that could be updated as a part of the same record (e.g. Status and deliveryDriverName) we can even just put that same code above in all possible fields that might change.  So all values that might be edited have the exact same code in their OnChange event.

While this might seem inefficient code-wise to you, it is VERY efficient from an "I have to maintain this thing" perspective.

If you were feeling especially creative you could add in an invisible Button (e.g. updateItemsToChange) within your Gallery, add the above code to that, then make the OnChange event for all changeable values to simply be: Select(updateItemsToChange).  Thereby making so that all the real logic for this is in a single place and easy to update going forward.

Saving back to the Source

This is where our hard work above shows how this saves us a lot of effort.  Since we've already built out our Collection w/ the exact values we want to write back to the Datasource, we can do this very simply.  

Place the following code on your Save icon.

//Save Button

Patch(

    multiUpdateExample,

    itemsToChange

);

Clear(itemsToChange);

That's it.  

This will write out all of the records that need to be changed back to the source w/ all values that were altered.  

How to tell if Anything Changed?

You also might notice that I altered the DisplayMode for the Save Text/Icon to be the following: 

If(
    CountRows(itemsToChange) > 0,
    DisplayMode.Edit,
    DisplayMode.Disabled
)

This means that we can use the CountRows() function against this queue of changes for any other warnings or dialogs that we want to populate to inform the end-user that they have unsaved changes.

You could of course also do an assigment during the OnChange event as well to a local variable, but using CountRows() works fine.

Things that don't (quite) work

There are some really crappy ways you might try to solve for this using methods that seem more obvious.  Using a ForAll() w/ an UpdateIf() statement will not work without expanding your knowledge to include the "As" operator.  

If(
    CountRows(itemsToChange) > 0,
    ForAll(
        itemsToChange,
        UpdateIf(
            multiUpdateExample,
            ID = ThisRecord.ID,
            {status: statusUpdate.Selected.Value}
        )
    )
);

The iteration through the items in the Collection just fails.  Trying to use ThisRecord references within the ForAll works on its own, but trying to use it inside the UpdateIf() then fails.  If you dig into it, you will discover that the "This Record" it is dealing with is the one you're about to act on within the UpdateIf() block and not the original one you are iterating through within the ForAll() block.  

You can fix this by altering your code though to the following:

If(
    CountRows(itemsToChange) > 0,
    ForAll(
        itemsToChange As A,
        UpdateIf(
            multiUpdateExample,
            ID = A.ID,
            {status: statusUpdate.Selected.Value}
        )
    )
);

Final Thoughts

While you don't have to do this as a large Save for all items at once, it is a commonly requested scenario.  Performing an UpdateIf() for individual fields OnChange events will work in most cases, but this also sometimes might cause problems w/ your Filters if the change alters the values you are Filtering on.  Imagine you have a Filter() set up on your Items to only show certain Status values.  If you did the update in real-time, then items would disappear as you updated them.  This might be what you want, but then again...

The UpdateIf() statement and ForAll() seems like the way to go here, but it doesn't work.  As well, the Patch() statement almost works if you don't go to the trouble of building out an identical record to the original Datasource.  However, the Patch() statement is finicky and it is easy to get into the weeds if you are not careful.  

Notably, if/when you start getting into conversations around what if multiple users are editing the same record at the same time then using the Patch() statement in this way will stop you from overwriting changes made by someone else.  It will error out (at least if you have Versions enabled for your SharePoint List) because the version you're trying to write is lower than the one that exists currently in SharePoint.  I'll save walking you through how to handle that error more graciously for another post.

In the end, setting things up this way makes the code fairly simple to manage and minimizes read/writes to the Datasource while still offering some pretty strong flexibility.  Just having a single command push a bunch of changes at once is a very good thing.