A lot of you would have come across a scenario where you want to bulk update records.

Maybe you are taking some photos and want to upload them all at once. Or you want to flip certain flags in your data and update the source. Or you have to submit a timesheet with a week’s data at once.

I’ve detailed out different solutions to achieve this in the blog below.

Scenario

Assume you have a checklist of tasks to do. When you are done with a few tasks you can mark them as complete. You could extend this scenario to a Product Launch checklist, Home Inspection checklist and so on.

I’ve created a simple checklist where I can check off tasks related to my blogging or posting on social media. I’ll not get into the details of building the app here. Below is the excel screenshot containing the Checklist items. My table on the excel is called as ChecklistItems. You can find the excel here.

I’m using Collections to demo this. The approach works for any backend of your choice.

I’ve imported excel data as static data into my PowerApps application. On the OnStart, I collect the excel ChecklistItems data into ChecklistItemsSource collection. Throughout the application we will use the ChecklistItemsSource as our source data.

We have a simple 2 screen application

• To review the checklist items and mark them as complete.
• To create a new checklist item. The new item will be added to ChecklistItemsSource collection.

       

Every time a checklist item is checked, we add it into a collection CheckedItems using the below formula on the OnCheck event of Checkbox control. If the item is already checked and is part of the collection, it’s removed. Else the checked item is added. You can toggle the status between Done and Pending, on the Oncheck and OnUncheck events as well.

If(!IsBlank(LookUp(CheckedItems,Id = ThisItem.Id)),Remove(CheckedItems,ThisItem),Collect(CheckedItems,ThisItem))

 

Solutions

Below, I’ve explained the different ways of bulk updating records depending on the scenario. I’ve also provided details on creating multiple records at once.

When the user clicks Done in the above scenario, we need to update ChecklistItemsSource with changes from CheckedItems collection.

Using Patch:

If your Source and Destination have the same column names, you can use a simple Patch statement. ChecklistItemsSource and the CheckedItems collections, have the same column names. Hence you can use the below formula to update the source at once with all the changes.

Patch(ChecklistItemsSource , CheckedItems)

Using ForAll and Patch:

In many scenarios, the columns in source and destination tables vary and you cannot use the Patch statement, but instead use ForAll with Patch.

With ForAll, you must loop through each record using a condition. The condition is a comparison between similar columns(i.e. Id column) of the different tables. And this comparison is hard when the source table and the destination table have the same column names( E.g. ProjectId in Project and PurchaseOrder tables). Most people run into an issue where only the first record gets updated. Let’s look at a few examples on how to achieve this easily.

1. with disambiguation operator:

To update the Status of CheckedItems to “Done”, when the source and destination table column names are the same, here is the formula

ForAll(CheckedItems,Patch(ChecklistItemsSource,LookUp(ChecklistItemsSource, Id = CheckedItems[@Id]),{Status:"Done"}))

For each item in the CheckedItems collection, we compare its Id (represented by the disambiguation operator CheckedItems[@Id]) against the Id column of ChecklistItemsSource collection and update each matched record with the Status as “Done”. The disambiguation operator is used when two columns belonging to different tables have the same name. If you don’t use this disambiguation operator you will observe that only the first record gets updated always.

2. using an additional label within the gallery

If you don’t want to use an additional collection to store the checked items, you can try the following.

Create an additional label within the gallery template, bind it to the Id column and rename the label to IdText.

Remove the code on the Oncheck of the checkbox control mentioned above.

Write the following formula on the OnSelect event of the Done Button.

ForAll(Filter(ChecklistGallery.AllItems,StatusCheckbox.Value=true),Patch(ChecklistItemsSource,LookUp(ChecklistItemsSource,Id = IdText.Text),{Status:"Done"}))

Here you are directly applying the filter on the Gallery’s items to find the checked items, and for each record in the filtered items, we find a match on the ChecklistItemsSource table by comparing the Id with value stored in IdText label. Finally, we update the status to “Done”.

The Disambiguation operator cannot be used on the Gallery’s items. Hence, we store a label within the gallery and use it for comparison.

3. with AddColumns:

This is an alternative to using the Disambiguation operator or a label inside gallery.

While creating a local copy of your data source, you can use AddColumns formula to create a new column with a different label(NewId) for the Id column in your source collection. When using ForAll with patch, you compare the NewId column, against the Id column in your source data.

Bulk create records

We have already tackled the hard problem of bulk updating records. We do generally see a need to create new records in bulk. For example, when you are app has to click some images and you may want to upload them all at once.

Let’s see how this can be achieved with the example of Checklist items above.

On the Checklist Create screen, each time you click Add, we store the information in NewChecklistItems collection. And on Submit, we use ForAll with patch to update the Source collection.

ForAll(NewChecklistItems,Patch(ChecklistItemsSource,Defaults(ChecklistItemsSource),{Id:Id,Category:Category,Description:Description,Status:Status}))

For each item in the NewChecklistItems, we are creating a new record (indicated by Defaults(ChecklistItemsSource)) in the ChecklistItemsSource collection. Id is set to the Id from the NewChecklistItems collection. Similarly, Category, Description and Status values are set.

Summary

In this blog, I’m not writing a lot of details on building the application but just concentrating on the key formulas to bulk update records. You can find all the related files here.

Summarizing the key points to bulk update records using ForAll and Patch.

• Use Patch, when source and destination columns names are same.
• Disambiguation operator [@] on the comparison column to differentiate the source and local data column name.
• Store the comparison Key in a label on the gallery representing local data.
• Use the AddColumns to rename the Comparison Key column name on your local data.