cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrianHFASPS
Level 8

Patch one column of multiple rows with one command?

I am trying to use Patch to update one (or more) columns across multiple records in a datasource. It seems you can do this but I can't figure out formating of command.

 

Usually when I am doing Patch I do something like:

Patch(SQLTable,
LookUp(SQLTable, Key = 1),
{Column : 2})

Now I could wrap that in a ForAll() and do a bunch of records but I thought I could do something like:

Patch(SQLTable,
Filter(SQLTable, Item =2),
{Column : 2})

This would then update all the rows that Filter returns with the modification that I specify. This is similar to the SQL query:

UPDATE SQLTable
SET Column = 2
WHERE Item = 2

I am getting the error Invalid Arguement type (Table). Expecting Record. Any pointers appreciated.

 

My actual code:

Patch(
        '[dbo].[Daily Activity Attendance]',
        Filter(
            '[dbo].[Daily Activity Attendance]',
            AttendanceDate_x0020_String = TodayVar && AfterBusLoad = true && AfterBusRouteID = CurrentRouteID && AfterStopID = CurrentStopID
        ),
        {
            AfterBusExit: 1,
            SignoutTime: Text(
                Now(),
                "[$-en-US]hh:mm:ss"
            )
        }
    )
9 REPLIES 9
Super User
Super User

Re: Patch one column of multiple rows with one command?

Hi @BrianHFASPS 

I think you can better accomplish this by calling the UpdateIf function rather than Patch.

The formula would look something like this....

 

UpdateIf('[dbo].[Daily Activity Attendance]',
          AttendanceDate_x0020_String = TodayVar && 
AfterBusLoad = true &&
AfterBusRouteID = CurrentRouteID &&
AfterStopID = CurrentStopID, { AfterBusExit: 1, SignoutTime: Text( Now(), "[$-en-US]hh:mm:ss" ) } )
BrianHFASPS
Level 8

Re: Patch one column of multiple rows with one command?

Thanks for the suggestion but UpdateIf isn't Delegable though and my Table is thousands of rows long. I could shrink with a View but a View can't be written to. It says it is possible in Patch documentation I just can't figure out the syntax?

BrianHFASPS
Level 8

Re: Patch one column of multiple rows with one command?

I can do this:

ClearCollect(
        StopList,
        Filter(
            '[dbo].[BusStopStuList]',
            AttendanceDate_x0020_String = TodayVar && AfterBusLoad = true && AfterBusRouteID = CurrentRouteID && AfterStopID = CurrentStopID
        )
    );
    ForAll(
        StopList,
        Patch(
            '[dbo].[Daily Activity Attendance]',
            LookUp(
                '[dbo].[Daily Activity Attendance]',
                DailyAttID = DailyID
            ),
            {
                AfterBusExit: 1,
                SignoutTime: Text(
                    Now(),
                    "[$-en-US]hh:mm:ss"
                )
            }
        )
    )

This is a loop with a bunch of individual calls across Internet to Azure SQL for a tiny write instead of one delegable call with a mass write command. The performance won't be anywhere near similar.

Community Support Team
Community Support Team

Re: Patch one column of multiple rows with one command?

Hi @BrianHFASPS ,

The formula that you listed lately is right.

Patch function is used to update one record at a time. It's not supported to update multiple records at the same time.

Its syntax is :

Patch(table,item,{...})              //the item that you want to update, just one

UpdateIf function could be used to update multiple records, but it will replace other fields'value.

 

To sum up, using ForAll and Patch function is the best way.

Here' my formula for your reference:

 

ClearCollect(
             collection1,
Filter(SQLTable, Item =2)); //save the items that you want to update as a collection
ForAll(collection1, Patch( SQLTable,
LookUp(SQLTable, ID=[@collection1]ID), //unique field
{Column : 2}
)
) //update filtered multiple records

 Here's a doc about using ForAll to update multiple records in details for your reference:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BrianHFASPS
Level 8

Re: Patch one column of multiple rows with one command?

Quoting from https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch :

"Patch can also be used to create or modify multiple records with a single call.

Instead of passing a single base record, a table of base records can be provided in the second argument. Change records are provided in a table as well, corresponding one-for-one with the base records. The number of records in each change table must be the same as the number of records in the base table."

 

How do I do that?

 

BrianHFASPS
Level 8

Re: Patch one column of multiple rows with one command?

Can anyone provide any insight on this one?

egallis
Level 10

Re: Patch one column of multiple rows with one command?

Hi @BrianHFASPS,

From what I understand, if you want to use the Patch function to change multiple records in one call, your Patch second and third parameters must have the same number of items. Then, all records in the second parameter will be patched with the records from the third parameter.

We could represent this that way:

Patch(DataSource,Collection1,Collection2)

will result in:

  • record 1 of Collection2 updates record 1 of Collection1
  • record 2 of Collection2 updates record 2 of Collection1
  • etc.

This means that you will have to build up a Collection2 with the properties that you want to update in your Collection1. And if you want to set a property Column to 2 on each record in Collection1, you will have to create a Collection2 with the same number of records than in Collection1 and where Column=2 on each record.

But I guess you had figured that out already...

Emmanuel

BrianHFASPS
Level 8

Re: Patch one column of multiple rows with one command?

I had not figured that out already. I was hoping you could use Patch like and SQL UPDATE command. You would pass in a datasource (SQL Table), a Filter like WHERE clause and then a column to change and what to change it to. It sounds like that is what it could do but I can't figure out how if it is possible. What you are describing is not at all what I was thinking but it may be the only way it works.

egallis
Level 10

Re: Patch one column of multiple rows with one command?

AFAIK it WILL be the only way to do it... Keep us posted... Smiley Happy

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,644)