Updating a single record in SQL Server using Patch()

[snaefellnes]

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

[Drrickryp]

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

1.  the datasource being patched
2. the record in the datasource being patched
3. 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. 

[snaefellnes]

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]

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.

[snaefellnes]

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

[eka24 · Accepted solution]

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.

[snaefellnes]

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]

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.

[snaefellnes]

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.