Updating a single record in SQL Server using Patch()

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Greetings,

I am trying a very simple application, solely made of a button . On this button OnSelect(), I have the following code:

Patch('[Audit].[AgentTrigger]', First(Filter('[Audit].[AgentTrigger]', id=1)),{BackupLiveToDev:1})

Basically sending the value 1 to column BackupLiveToDev

Works the first time. Then it does nothing afterwards. I have to close the app and restart. The test steps I am doing are as follow:

- from the app, click the button, column is updated

- from ssms, i update the value back to 0

- from the app, click the button, nothing happens, column remains to 0 in the sql server table

So there seems to be a cache or data context that I am not managing.

It is a simple one-button app, no collection, no gallery. Learning purpose to grasp the methods to handle record outside of an editForm. I already have an app in production using editForm - but I want to learn how to use patch as well)

Regards,

Eric

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @snaefellnes

Patch() is one of the most useful but misunderstood functions in PowerApps. It can add one or more records to a dataset, or can modify one or more records in a dataset Basically it consists of three elements

the datasource being patched

the record in the datasource being patched

the field in the record being patched.

new record added Patch(datasource,Defaults(datasource),{field1:Datepicker1.SelectedDate, field2:TextInput1.Text, field3: dropdown1.Selected.Value}) Modifying record from inside a gallery Patch(datasource,ThisItem,{field1:Datepicker1.SelectedDate, field2:TextInput1.Text, field3: dropdown1.Selected.Value}) Modifying a record from outside a gallery Patch(datasource,Lookup(datasource,ID=Gallery1.Selected.ID),{field1:Datepicker1.SelectedDate, field2:TextInput1.Text, field3: dropdown1.Selected.Value})

In its simplest form, Patch can be used to create a new record or to modify an existing record. In the event that a new record is being created the second element is Defaults(datasource). When Patch is used to modify an existing record, the specific record must be identified such as Lookup(datasource, ID = Dropdown1.Selected.ID). If patch is used inside a gallery, then simply ThisItem is sufficient to identify the record being patched.

Patch() can also be used to merge records from outside a datasource into the datasource. For more information regarding Patch() see https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch and @Shanescows video

https://www.youtube.com/watch?v=MclJ_O9HvJQ shows how to use Patch() and ForAll() in selecting multiple records in a Gallery to patch to the datasource.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @Drrickryp ,

thanks for the quick reply and the link; I'll start watching it right after typing this, watched another of his video presenting patch() in a gallery context, very informative. I'all also try the lookup syntax in your example, see I can get patch() to work every time without leaving the app.

Regards,

Eric
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Based on your formula,
The first Filter ID = 1 means that before Patch should happen, it should check the Datasource where ID = 1 the column BackupLiveToDev value should be changed to 1

So if you change the ID to zero, nothing will happen because the ID is not equal to 1.

For more information on Patch
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

If you want to add new record look at the above Link on Patch Defaults
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

hello @eka24 ,

I am not changing the id, this is the unique primary key used to fetch the record to update, I am updating successfully the backupLiveToDev column to 1 the first time. Afterwards, clicking the button again does nothing. Have to close the app and restart it.

I just tried with the lookup function, same result:
Patch('[Audit].[KDCAgentTrigger]', LookUp('[Audit].[KDCAgentTrigger]', id=1), {BackupLiveToDev:1})

Same result with First() or First() with Filter():
Patch('[Audit].[KDCAgentTrigger]', First('[Audit].[KDCAgentTrigger]'), {BackupLiveToDev:1})

My understanding of the patch function is as follow for updating a record:
- table to update
- record selection filter
- {fields and their value}

Regards
Verified answer

eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Please take a look at my explanation again. If you want to change another record for example use first filter ID= 2 or ID=3

Once that is patched, clicking the button again won't change anything unless you change either the ID or the value for backupLiveToDev

To make it dynamic replace the replace the ID=1 to ID=Value (Textbox1.Text)

In that case whatever you enter in Textbox1 Will represent the particular row you want the change to occur.
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Ok, now I get it, there is an internal buffer and if no changes are done to the record field, it will discard the update. I added a second button. First one set the value of BackupLiveToDev to 1 and second one set the value of BackupLiveToDev to 2:

Patch('[Audit].[KDCAgentTrigger]', LookUp('[Audit].[KDCAgentTrigger]', id=1), {BackupLiveToDev:1})
Patch('[Audit].[KDCAgentTrigger]', LookUp('[Audit].[KDCAgentTrigger]', id=1), {BackupLiveToDev:2})

Now the changes are reflected if I alternate between buttons.

Thanks
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

That is fine but the best approach is to insert a Textbox1 to make it dynamic such that whatever you enter in the Textbox will be used as the Id

Patch('[Audit].[KDCAgentTrigger]', LookUp('[Audit].[KDCAgentTrigger]', id=Value Textbox1.Text)), {BackupLiveToDev:1})

With your formula if you want to change 10 times, you need 10 buttons.
But with what I have suggested, you only need to enter the id number into the Textbox and it will be patched. So only one button. If you enter 4 in Textbox1 it will Patch. You can also clear and enter 3 and so on
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

As I wrote in my original post:

"Learning purpose to grasp the methods to handle record outside of an editForm."
🙂

It is not a real world app, just a study object.
CU04021832-1 171 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello. Novice PA learner here. I think this discussion is basically addressing what I'm after, but I wasn't following 100%. Here is my question if you could please assist @eka24. Much appreciated.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Using-patch-to-update-a-record-in-SQL-server/m-p/2545087#M630502