Bulk Data Registration in Power Apps: Using Collections and Patch Without ForAll

I’m writing down a method for bulk data registration to Power Apps data sources, as this is a frequently asked question.

Collections Can Be Passed as the Second Argument of the Patch Function

It’s not widely known, but you can pass a table of data source records as the second argument of the Patch function.
By using this table-passing method, you can bulk register data without calling the Patch function multiple times within a ForAll function.

What Happens When Implementing Bulk Registration with ForAll

Let’s look at a practical example:

Consider this SharePoint Online list:

And this UI where each time the “Add” button is pressed, a record with the same format as the SPO list above is accumulated in a collection called “Contact Collection”:

When trying to implement bulk registration using the ForAll function when the “Bulk Register” button is pressed, the formula would look like this:

While this does work correctly:

The main issue is that performance significantly degrades as the number of records to be bulk registered increases.

Directly Passing Collections as the Second Argument of the Patch Function

By modifying the “Bulk Register” button’s formula as shown below, where we specify a collection containing records in the same format as the SharePoint Online list as the second argument:

The Patch function alone can handle bulk data registration! Note: If the registration order is important, sorting may be necessary.

While this approach isn’t widely known, it’s highly recommended as it performs significantly faster than the ForAll method.

Resolving the “Invalid argument type (Table). A Record value is required instead.” Error

While bulk registration using only the Patch function is convenient, you may encounter the error “Invalid argument type (Table). A Record value is required instead.” if the collection is not structured correctly.

This error occurs when the record format in your collection doesn’t match the required structure. For SharePoint Online data sources like in this example, instead of using the display names shown in the image below:

You need to use the internal names shown in the top right.

By modifying the collection structure (column names) like this:

The error will be successfully resolved.

How to Identify Incorrect Column Names

While identifying the cause is relatively straightforward for SharePoint Online lists by using internal names, it can be challenging to pinpoint which column names are incorrect when working with other data sources.

In such cases, using the ShowColumns function as described in this reference is an effective approach:

Consumer Privacy Act (CCPA) Opt-Out Icon

Quickly search for answers, join discussions, post questions, and work smarter in your business applications by joining ...

powerusers.microsoft.com

The method is simple: wrap the second argument of the Patch function with ShowColumns and gradually add the desired record column names until you encounter an error.

For example, when we add the “Title” column like this, we can confirm it’s correct because no error appears:

Then, when we add the “住所” (Address) column, an error occurs, indicating that this column name is incorrect:

Similarly, adding the “電話番号” (Phone Number) column also triggers an error, confirming that this column name is also incorrect:

While troubleshooting errors this way might require some patience, bulk registration is highly recommended as it’s extremely convenient and produces clean, readable code!

Patch vs UpdateIf vs ForAll

Actually, bulk registration and updates are possible with functions other than Patch. I’ve compared the performance speed of different functions, so check it out if you’re interested!

How to Bulk Update SharePoint Data in Power Apps: Patch vs UpdateIf vs ForAll

I was asked about bulk updating SharePoint data, so I decided to conduct a performance comparison test.Note: For bulk cr...

ippu-biz.com

2024.11.02

Power Apps Dataverseのデータを一括更新する方法 Patch vs UpdateIf vs ForAll

前回SharePointのデータの一括更新の方法と、その性能比較をしてみたので、今回はDataverseでの比較。前回はこちらやってみたこと今回はアイテムが500個あるDataverseに対し、文字列型の列「str」に値を一括設定してみた。...

ippu-biz.com