Create and update multiple items using Patch in Power Apps

Following last week’s post about using a single patch to replace Patch inside ForAll, I was asked about, how to not only update multiple items, but also create new items in SharePoint lists. Although this is not difficult, there is a small trick needed. In this post all the details.

Patch in Forall to update multiple items

In the post mentioned above we saw that rather than using a patch inside a ForAll to update many items, we can also use a single patch to update existing records.

So instead of the following code

ClearCollect(
    colLargeList,
    'Large List'
);
ForAll(
    colLargeList,
    Patch(
        'Large List',
        ThisRecord,
        {'My New Text Column': "Updated with ForAll"}
    )
)

We can use this instead

ClearCollect(
    colLargeList,
    'Large List'
);
UpdateIf(colLargeList, true, {'My New Text Column':"Updated Collection"});
Patch(
    'Large List',
    colLargeList    
)

The benefits are clear as we find that running the second code sample will run a lot faster. In my tests I found that the performance improved by a factor of 6. Where in the first sample updated 500 items in 5 minutes and the second sample managed to update 3000 items within 5 minutes.

Update or create multiple items using Patch

In the examples given so far we looked at reading items from a SharePoint list then we update the items before we patch the list with all the items.

Now how to we create new items.

Imagine that we have a list without any items and we’re going to add two items to that list

ClearCollect(colNewItems, 
             {Title: "Title 1 updated"}, 
             {Title: "Title 2 Updated"} 
);
Patch(
    'New Items List',
    colNewItems    
)

Now if we want to update the first item and add some new items. We could run the following code.

ClearCollect(colNewItems, 
             {ID: 1, Title: "Title 1 updated"},              
             {Title: "Title 3"}, 
             {Title: "Title 4"}
);
Patch(
    'New Items List',
    colNewItems    
)

We will now end up with 4 records. the first item will now have an updated title. The second item remains untouched and the 3rd and 4th item will now be created. The reason why the first item is updates is that we have included the ID of the item that we want to update.

Ok, so that is the basics out of the way.

Order of execution Patch

I’m going to have another look at adding new items using the Patch function. first I’m going to clear the list and then I’m creating 10 new items.

ClearCollect(colNewItems, 
             {Title: "Title 1"}, 
             {Title: "Title 2"}, 
             {Title: "Title 3"}, 
             {Title: "Title 4"}, 
             {Title: "Title 5"}, 
             {Title: "Title 6"}, 
             {Title: "Title 7"}, 
             {Title: "Title 8"}, 
             {Title: "Title 9"}, 
             {Title: "Title 10"}
);
Patch(
    'New Items List',
    colNewItems    
)

We can now see that the updates are happening in a random order. Therefore if we wanted to use this to collect a number of updates from a form before saving data, we would need to be careful.

Using OnChange events and a single Patch

First We’ll have to get a collection that matches the List.

ClearCollect(colNewItems,  'New Items List');

Then in the OnChange property of each control in your edit form you can Patch the collection that we have. This will result in a collection containing all the current data and updated data.

Patch(
    colNewItems,
    LookUp(
        colNewItems,
        ID = ThisItem.ID
    ),
    {Title: Self.Text}
)

And then we can do the same updates like we did before to make sure that we only need one patch to do all our updated.

Now imagine that we have a gallery with many items allowing us to update all records before we hit a save button to commit all updates.