cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seanthare
Level: Powered On

Update Azure SQL record

I am connecting to a azure sql db (DB_A) , loading a collection (COL_B) for the selected record in the gallery displaying Azure DB records.

After updating the COL_B in an edit form, I am trying to write COL_B back to DB_A.

This does not seem to be the correct syntax:

Patch( '[dbo].[DB_A]',Defaults('[dbo].[DB_A]'),
{Field1: COL_B.Field1,
Field2: COL_B.Field2,
Field3: COL_B.Field3

})
2 REPLIES 2
Super User
Super User

Re: Update Azure SQL record

In the Patch function, the defaults() is used to create a new record.  If you are editing a record you should use LookUp() or some other method to find the record being updated before you patch it.  

quoting from the formula reference for Patch(): 

  • To modify a record, the base record needs to have come from a data source. The base record may have come through a gallery's Items property, been placed in a context variable, or come through some other path. But you should be able to trace the base record back to the data source. This is important as the record will include additional information to help find the record again for modification.

So for your collection, the record you are editing should have an ID field derived from DB_A.that can be traced back:

Patch( '[dbo].[DB_A]', LookUp('[dbo].[DB_A]'),ID=COL_B.ID),
{Field1: COL_B.Field1,
Field2: COL_B.Field2,
Field3: COL_B.Field3

})

 

There is a good discussion of the patch funciton here:

https://powerusers.microsoft.com/t5/General-Discussion/Using-the-Patch-function-to-either-create-OR-...

seanthare
Level: Powered On

Re: Update Azure SQL record

I am still having an issue witht the syntax of the Patch function to update the Azure SQL record.

 

I have A FieldSK in the DB_A as the primary key - So I would think that is my link from COL_B back to DB_A

 

Patch( '[dbo].[DB_A]', LookUp('[dbo].[DB_A]'),[dbo].[DB_A].FieldSK=COL_B.FieldSK),
{Field1: COL_B.Field1,
Field2: COL_B.Field2,
Field3: COL_B.Field3

})

 

This still gives me me 'invaild number of arguments'

 

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,062)