cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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

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

 

2020-05-07_9-42-38.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Highlighted
Super User III
Super User III

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

@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." 

Highlighted
Helper V
Helper V

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

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

 

Highlighted
Super User III
Super User III

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

@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."

Highlighted
Helper V
Helper V

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

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.

 

2020-05-07_10-17-30.jpg

Highlighted
Super User III
Super User III

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

@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."

Highlighted
Helper V
Helper V

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

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.

Highlighted
Super User III
Super User III

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

@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."

Highlighted
Community Support
Community Support

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

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper V
Helper V

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

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!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,584)