How to perform bulk update operation in SharePoint list using ForAll patch function

PowerApps bulk update using ForAll patch function

No comments

 835 total views,  9 views today

In this PowerApps Tutorial for Beginners (PowerApps bulk update using the ForAll patch function) tutorial, we will learn how to update bulk records at a time using the PowerApps ForAll patch function in the SharePoint list. In this demo, we will show how we can insert or update multiple records to the SharePoint list using the PowerApps ForAll patch function.

What you will learn from this article?

  • How to perform bulk update operation in SharePoint list using ForAll patch function?
  • What is the importance of the ID parameter for the patch function?
  • How to filter the gallery control items based on the currently logged-in user?
  • The drawback of the ForAll loop and patch function in PowerApps.

PowerApps bulk update using ForAll patch function – add multiple items to SharePoint list

Update selected list item in SharePoint list from PowerApps Gallery control
Update selected list item in SharePoint list from PowerApps Gallery control

In the above demo, we have added the data table control at the top of the screen to display the updated SharePoint list item data (we assume, you know to connect to the SharePoint list from PowerApps, if you need help, you can refer to our article – PowerApps SharePoint connection step by step ).  And below that, we have added a gallery control whose data source is the SharePoint list (‘PO Line Items’), this is just an example, you can connect to any SharePoint list.

Select the list of checkboxes for the approval or rejection of the PO against the purchase order number displayed in the gallery control, then click on the “Update Selected PO” button. Let us see, how this works practically.

From PowerApps gallery control update selected items in SharePoint list
From PowerApps gallery control update selected items in SharePoint list

Once we select the purchase order number checkboxes from the PowerApps gallery control and click on the “Update Selected PO” button, the value of the “IsPOApproved” column will be updated to true from false (in the code we are just updating a single column for this PoC).

Now let us click on the “Update Selected PO” button.

Purchase Request approver app using the PowerApps gallery control
Purchase Request approver app using the PowerApps gallery control

Notes:

  • Once we have clicked on the “Update Selected PO” button, the value of the “IsPOApproved” columns has been changed to true from false for the selective purchase orders.
  • Now, the gallery control becomes empty as we have set a filter on this gallery control only to display the value where the “IsPOApproved” value is false.

Now, let’s verify the SharePoint list.

Go to the SharePoint list and see that “IsPOApproved” has been updated to “yes” value for the “PO0003” and “PO0004” purchase orders.

Update multiple items in SharePoint list using PowerApps
Update multiple items in SharePoint list using PowerApps

Note:

  • The “IsPOApproved” column is the type of Yes/No, however, on the PowerApps side when it gets rendered, it displays as a true or false value, we will explore and update this post later on about the reason.
Edit SharePoint Online Yes-No column type
Edit SharePoint Online Yes-No column type

PowerApps bulk update using ForAll patch function – Technical implementation

Step1: Filter PowerApps data table items based on the logged-in user

Add a data table control to the screen.

For the items property add the below text:

Filter('PO Line Items', POApproverName.DisplayName=User().FullName)

In the above filter, we are getting the data from the ‘PO Line Items’ SharePoint list, and adding filter conditions to get the list items if the logged-in user is the purchase order approver (POApproverName is the column name).

Filter PowerApps data table items based on the logged in user
Filter PowerApps data table items based on the logged-in user

Similarly, add a gallery control to the screen.

Step2: Filter PowerApps gallery control items based on the logged-in user

Add a gallery control to the screen.

For the items property of the gallery control add the below text:

Filter('PO Line Items', POApproverName.DisplayName=User().FullName && IsPOApproved=false)

In the above filter, we are getting the data from the ‘PO Line Items’ SharePoint list, and adding filter conditions to get the list items if the logged-in user is the purchase order approver (POApproverName is the column name) and if the “IsPOApproved” is equal to false.

Filter PowerApps gallery control items based on the logged-in user
Filter PowerApps gallery control items based on the logged-in user

Step2: Update SharePoint list from the PowerApps Gallery selected items

Below is the code to submit data to the SharePoint list using the ForAll loop and Patch function in PowerApps.

ForAll(
Gallery1_1.AllItems, 
Patch('PO Line Items', 
{ ID: ID, 
IsPOApproved: If(
Checkbox3.Value = true,
true,
false
) 

}))

Note:

  • In the above code, the “ID” column is very important, it is a SharePoint unique column, by passing the ID column parameter, the first code will try to find the item in the list, if it is found, then it will update that row.
  • If we do not pass the ID column, it will create the new items in the list.
  • Here, other than the ID column, we have passed another column as a parameter i.e. “IsPOApproved” (boolean).
  • We can pass column parameters as many we want in the form of the JSON format.
Update SharePoint list from the PowerApps Gallery selected items
Update SharePoint list from the PowerApps Gallery selected items

The ‘ID’ column parameter is a must for the update operation, otherwise, you will end up with multiple new items in the SharePoint list, which leads you to confusion, so, don’t forget to add the ID column in the parameter list.

PowerApps bulk update: The drawback of the ForAll loop and patch function in PowerApps

From the performance point of view, the ForAll loop and patch function is not recommended one, it is good if you have fewer items in the list, however, for the larger list items, this technique is not recommended, this will slow down the performance as this will loop through each item in the data connection list and see if there are any changes in the columns mentioned in the parameter (here for example, “IsPOApproved”), the code will update that column values, the bottom line is, this method time-consuming. In our next article, we will show how we can update the list items in the SharePoint list faster. So, please keep reading these articles and follow our site for the upcoming articles.

Summary: What we have learned from this article?

Thus, in this article, we have learned the below with respect to the ForAll loop and patch function in PowerApps:

  • How to perform bulk update operation in SharePoint list using ForAll patch function?
  • What is the importance of the ID parameter for the patch function?
  • How to filter the gallery control items based on the currently logged-in user?
  • The drawback of the ForAll loop and patch function in PowerApps.

See Also

You may also like the below PowerApps articles:

Buy SharePoint Online & Office 365 administration eBook

Buy the premium version of SharePoint Online & Office 365 administration eBook from here:


Buy SharePoint Online & Office 365 Administration eBook

Reference URL