Patch one column of multiple rows with one command?

[BrianHFASPS]

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"
            )
        }
    )

[timl]

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]

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]

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.

[v-yutliu-msft]

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,

[BrianHFASPS]

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]

Can anyone provide any insight on this one?

[R3dKap]

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]

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.

[R3dKap]

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