Patch SQL, Want to Update an Record, Not Create a New One

[ez1138]

Hi, using the following code I was aiming to update a record, not create a new one.  When I edit the record, let's say the first one (e.g. St. Louis, Company_ID 1), and I change it to Las Vegas, it creates a new record.  

What do I need to modify to simply update?  Also, what other code can I add in to return back to the detail/display screen for that record?

Thanks for any assistance!!

Patch('[dbo].[table1]', Defaults('[dbo].[table1]'), {City: DataCardValue318.Text})

[mdevaney]

@ez1138 

To update the record with ID=1 use this code. 

Patch('[dbo].[table1]', LookUp('[dbo].[table1]', ID=1), {City: DataCardValue318.Text})

If you want to get back the full information about the record you updated store it in a SET variable

Set(updatedRecord, Patch('[dbo].[table1]', LookUp('[dbo].[table1]', ID=1), {City: DataCardValue318.Text}))

Then you can refer to any column in the updatedRecord like this (place it in a label Text property)

updatedRecord.City

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up." 

[ez1138]

Appreciate the reply.  I will try it out.  Quick follow-up.  You have ID=1.  Isn't that specific to that one record?  I might choose any record to update.

Set(updatedRecord, Patch('[dbo].[table1]', LookUp('[dbo].[table1]', ID=1), {City: DataCardValue318.Text}))

[mdevaney]

@ez1138 
Yes, I chose ID=1 because your example asked for record number 1 lol 😉

You can dynamically change the record to be updated by referring to a variable (varCurrentId)

Set(updatedRecord, Patch('[dbo].[table1]', LookUp('[dbo].[table1]', ID=varCurrentId), {City: DataCardValue318.Text}))

Or if your update button is inside a form or gallery you can refer to ThisItem.ID instead.

Set(updatedRecord, Patch('[dbo].[table1]', LookUp('[dbo].[table1]', ID=ThisItem.ID), {City: DataCardValue318.Text}))

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[ez1138]

LOL, yeah, not the first time I asked for the wrong thing!  With both pieces of code, it doesn't seem to like the ID...it seems to like everything else.

[mdevaney]

@ez1138 
You have to create a variable to store the ID.  I just put a sample variable name in my code.  Its not going to work unless you create a variable.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[ez1138]

Hi, now I'm confused some more.  In my table I have the following:

MyID (Primary Key)

Company_ID (references Company Table)

Contact_ID (references Contacts Table)

Regarding a variable, I'm not sure what you mean.  I have not seen variable used in other patch examples.

[mdevaney]

@ez1138 
What I mean is store the value 1 in a variable.  Then call the variable instead of the number.

Set(varCurrentID, 1);

The question for you is: how are you choosing which record will be updated?

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[v-xida-msft · Accepted solution]

Hi @ez1138 ,

How do you collect your entry? Using Edit form or individual Text Input Boxes?

Do you want to submit data back to your SQL Table using Patch function instead of SubmitForm function?

Further, how do you list your SQL Table records in your app? Using Gallery control?

I assume that you use a Gallery control in your app to list all available records from your SQL Table, and you add a Edit form control to collect entry for adding or updating records in your SQL Table, right?

If you want to update an existing record in your SQL Table, I think the Patch formula you provided is wrong. The Defaults() function within the Patch function is used to add a new record in your SQL Table rather than update.

I have made a test on my side, please consider take a try with the following workaround:

I assume that you added a Edit form in your canvas app to add or edit records in your SQL Table, and the Item property of the Edit form has been bind to the selected item in your Gallery.

Set the Item property of the Edit form to following:

BrowseGallery1.Selected

set the DataSource property of the Edit form to your SQL Table data source ('[dbo].[table1]').

Set the OnSelect property of the "Submit" button in your Edit form screen to following:

Patch(
      '[dbo].[table1]',
      If(                     // Check if you are adding or editing record
          EditForm1.Mode = FormMode.New,
          Defaults('[dbo].[table1]'),        // Type this formula when adding record
          BrowserGallery1.Selected           // Find specific record you want to update when editing
      ),
      EditForm1.Updates
)

or

Patch(
      '[dbo].[table1]',
      If(                     // Check if you are adding or editing record
          EditForm1.Mode = FormMode.New,
          Defaults('[dbo].[table1]'),        // Type this formula when adding record
          LookUp('[dbo].[table1]', MyID = BrowserGallery1.Selected.MyID)       // Find specific record you want to update when editing
      ),
      EditForm1.Updates            // you could also type {City: DataCardValue318.Text} formula here
)

Note: The BrowserGallery1 represents the Gallery in your app to list all available records from your SQL Table. You could click the item in this Gallery to navigate to the Detail Screen, within the Detail Screen, you could click "Edit" icon to navigate to Edit form screen. The EditForm1 represents the Edit form in your Edit screen, please replace it with actual Edit form name

If you want to return back to the detail screen for that record you are updating after you execute the Patch function, please consider modify above Patch formula as below:

If(                     // Check if you are adding or editing record
    EditForm1.Mode = FormMode.New,
    Patch(
         '[dbo].[table1]',
          Defaults('[dbo].[table1]'),
          EditForm1.Updates
    );Navigate(GalleryScreen),
     Patch(
         '[dbo].[table1]',
          BrowseGallery1.Selected, // or type LookUp('[dbo].[table1]', MyID = BrowserGallery1.Selected.MyID) here
          EditForm1.Updates   // you could also type {City: DataCardValue318.Text} formula here
    );Navigate(DetailScreen)
)
    

Note: The GalleryScreen represents the first screen in your app.

Please take a try with above solution, check if the issue is solved.

Best regards,

[ez1138]

Thank you so, so much! This worked great and I appreciate the extra time and depth for your post!  If you wouldn't mind, I will PM you down the line.  Again, many thanks!