In this post, i will show how to use Patch function in PowerApps to update multiple records in SharePoint list as data source. Hope it is helpful.

Introduction

Patch function is used to modify a record or a set of records of a data source. It only updates the value of the fields specified in the formula and other fields remain unaffected.

Syntax

Modify or create a record in a data source

Patch( DataSource, BaseRecord, ChangeRecord1 [, ChangeRecord2, … ])

• DataSource – Required. The data source that contains the record that you want to modify or will contain the record that you want to create.
• BaseRecord – Required. The record to modify or create. If the record came from a data source, the record is found and modified. If the result of Defaults is used, a record is created.
• ChangeRecord(s) – Required. One or more records that contain properties to modify in the BaseRecord. Change records are processed in order from the beginning of the argument list to the end, with later property values overriding earlier ones.

Modify or create a set of records in a data source

Patch( DataSource, BaseRecordsTable, ChangeRecordTable1 [, ChangeRecordTable2, … ] )

• DataSource – Required. The data source that contains the records that you want to modify or will contain the records that you want to create.
• BaseRecordTable – Required. A table of records to modify or create. If the record came from a data source, the record is found and modified. If the result of Defaults is used, a record is created.
• ChangeRecordTable(s) – Required. One or more tables of records that contain properties to modify for each record of the BaseRecordTable. Change records are processed in order from the beginning of the argument list to the end, with later property values overriding earlier ones.

Scenario

There is a SharePoint list “Tasks” which is being used as data source in PowerApps for Gallery control. Each task has a sequence and we are adding capability to move these tasks up or down in gallery control (basically updating there sequence).

Process

Custom list “Tasks” is created with below columns –

• Title (Single line of text)
• AssignedTo (Person)
• Sequence (Number)
• Status (Choice – InComplete, Complete and Active)

I have an app already created with below details –

• One screen
• One gallery control – vertical “Title and subtitle” layout
• “Tasks” list as data source
• Three labels inside gallery control – lblTitle, lblAssignedTo and lblSequence
• Two icons – up and down arrow
• Two local variables – CurSequence and NextSequence (Default value 0)

On how to create an app and add gallery control, refer this Microsoft article.

My app looks like below-

Lets add the functionality to move tasks up or down.

Move Up

• Click “Up” icon and select property “OnSelect”. Default value for this property is “Select(Parent)” which we will update.
• First things first, lets update our variables based on selected up arrow using UpdateContext function.

UpdateContext(
{ CurSequence: Parent.Selected.Sequence, NextSequence: Parent.Selected.Sequence – 1
}
)

• Create a base record table of the selected records using ClearCollect function.

ClearCollect(
BaseRecords, Parent.Selected,First(Filter(Gallery1.AllItems,Sequence=NextSequence))
)

• Modify records using Patch function. First parameter is Tasks list (data source), second parameter is base table (BaseRecordsTable) and third table is changed records. BaseRecordsTable and ChangedRecordsTable have one to one mapping of records. Sequence is the column to be updated with new values.

If(ThisItem.Sequence <> 1, Patch(
Tasks,
BaseRecords,
Table({Sequence: NextSequence}, {Sequence: CurSequence}
)
), Notify(“This is the first item and cannot be moved up.”,NotificationType.Warning)

This patch statement will be executed conditionally to check if this is not the first step.

Overall it looks like below image-

UpdateContext(
{
CurSequence: Parent.Selected.Sequence,
NextSequence: Parent.Selected.Sequence – 1
}
);
ClearCollect(
BaseRecords,
Parent.Selected,
First(
Filter(
Gallery1.AllItems,
Sequence = NextSequence
)
)
);
If(ThisItem.Sequence <> 1, Patch(
Tasks,
BaseRecords,
Table({Sequence: NextSequence}, {Sequence: CurSequence}
)
), Notify(“This is the first item and cannot be moved up.”,NotificationType.Warning)

• Save the app. Click on Play button and check the functionality for Up arrow.

Move Down

Follow same steps as for “Move Up” with an exception to UpdateContext function and Patch (If Condition) function, as in below code-

UpdateContext(
{
CurSequence: Parent.Selected.Sequence,
NextSequence: Parent.Selected.Sequence + 1
}
);
ClearCollect(
BaseRecords,
Parent.Selected,
First(
Filter(
Gallery1.AllItems,
Sequence = NextSequence
)
)
);
If(
ThisItem.Sequence <> Last(Gallery1.AllItems).Sequence,
Patch(
Tasks,
BaseRecords,
Table(
{Sequence: NextSequence},
{Sequence: CurSequence}
)
),
Notify(
“This is the last item and cannot be moved down.”,
NotificationType.Warning
)
);

References

• https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions