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

How to update a record with Patch?

Hi all,

 

I have been struggling with the Patch function and how to update an existing record. This is how my OnSelect function looks when creating a new record: 

 

Patch('[dbo].[tblTicketsIT]',Defaults('[dbo].[tblTicketsIT]'), { CREATOR: txtCREATOR.Text, PRIORITY: cmbPRIORITY.Selected.Value, DEPARTMENT: txtDEPARTMENT.Text, LOCATION: txtLOCATION.Text ,ISSUE: txtISSUE.Text, IMAGE: Camera2.Photo })

 

Now when selecting this newly created record from the gallery how can I make the Patch function, fetch the data created and show in the form?😕 This is how my OnSelect function looks like when selecting an item in the gallery:

 

Patch('[dbo].[tblTicketsIT]', { CREATOR: txtCREATOR.Text, PRIORITY: cmbPRIORITY.Selected.Value, DEPARTMENT: txtDEPARTMENT.Text, LOCATION: txtLOCATION.Text ,ISSUE: txtISSUE.Text, IMAGE: Camera2.Photo }); Navigate(Screen1)

 

Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How to update a record with Patch?

Hi @DG1000 ,

Actually, after you execute your Patch function, these Text Input Boes would not be reset automatically (Which is not different from these controls within a Edit form).

 

If you use Patch function to submit your data, please add Reset() function to reset these Text Input controls. Modify your formula as below:

Patch(                               // edit existing record
          '[dbo].[tblTicketsIT]',
          LookUp('[dbo].[tblTicketsIT]', PrimaryColumn = Gallery1.Selected.PrimaryColumn),
          {
            CREATOR: txtCREATOR.Text, 
            PRIORITY: cmbPRIORITY.Selected.Value, 
            DEPARTMENT: txtDEPARTMENT.Text, 
            LOCATION: txtLOCATION.Text ,
            ISSUE: txtISSUE.Text, 
            IMAGE: Camera2.Photo
          }
);
Set(CurrentItem, Blank())  // Add this formula
Reset(TextInput1);Reset(TextInput2);Reset(TextInput3);....   // add Reset function to reset these separated controls

 

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

14 REPLIES 14
Highlighted
Super User III
Super User III

Re: How to update a record with Patch?

To show the items in the gallery, set the item of your for to:

GalleryName.Selected

 

Also ensure you ha a button that will turn the form into edit mode:
  EdieForm(YourFormName)

 

You can then go ahead to submit the edited record through Submit button or Patch:
Patch('[dbo].[tblTicketsIT]', { CREATOR: txtCREATOR.Text, PRIORITY: cmbPRIORITY.Selected.Value, DEPARTMENT: txtDEPARTMENT.Text, LOCATION: txtLOCATION.Text ,ISSUE: txtISSUE.Text, IMAGE: Camera2.Photo }); Navigate(Screen1)

 

 

Highlighted
Super User III
Super User III

Re: How to update a record with Patch?

Hi @DG1000 

@eka24 is correct if you are using SubmitForm() to edit or create items.  However, your Patch() function requires 3 elements, the datasource , the record and the columns being patched.  Your statement is missing the second element. Please see the documentation regarding the correct syntax. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch  

To patch an existing record for example, a Lookup to the record is necessary: 

Patch( Customers,Lookup( Customers, Name = "Contoso" ) , { Phone: "1-212-555-1234" } ) so it would be something like
Patch('[dbo].[tblTicketsIT]', Lookup('[dbo].[tblTicketsIT]',ID = Gallery1.Selected.ID), { CREATOR: txtCREATOR.Text, PRIORITY: cmbPRIORITY.Selected.Value, DEPARTMENT: txtDEPARTMENT.Text, LOCATION: txtLOCATION.Text ,ISSUE: txtISSUE.Text, IMAGE: Camera2.Photo }); Navigate(Screen1)

 

To create a new item:

Patch( Customers, Defaults( Customers ), { Name: "Contoso" } )

 Defaults() is used for that purpose. 

Highlighted
Helper I
Helper I

Re: How to update a record with Patch?

Hi

 

I tried using your suggestion but it just rewrites the current record with default data 😕2020-03-22_20-42-05.jpg

 

 

 

Highlighted
Super User III
Super User III

Re: How to update a record with Patch?

@DG1000 

I am not sure what you are trying to do but it appears that you are trying to patch a record from within your gallery.  If you want to use a gallery to write to a datasource, check out @Shanescows  video https://www.youtube.com/watch?v=xgznk4XlPCo  about repeating tables.  Normally, the icon ">" is used to navigate to a screen containing a form as described by @eka24  and the fields of the form will be populated automatically from the data in the gallery.  They can be modified and then saved as he describes. 

Highlighted
Super User III
Super User III

Re: How to update a record with Patch?

If you are Pactching directly from the Gallery, then use:

Patch('[dbo].[tblTicketsIT]', ThisItem, 
{CREATOR: txtCREATOR.Text, PRIORITY: cmbPRIORITY.Selected.Value,
DEPARTMENT: txtDEPARTMENT.Text, LOCATION: txtLOCATION.Text ,
ISSUE: txtISSUE.Text, IMAGE: Camera2.Photo });
Navigate(Screen1)

 

Highlighted
Community Support
Community Support

Re: How to update a record with Patch?

Hi @DG1000 ,

Do you want to populate the Edit form with selected item in your Gallery?

 

Based on the formula that you mentioned, I think there is something wrong with it. Firstly, please remove the formula within the OnSelect property of the ">" icon in your Gallery, instead, type the following formula:

EditForm(EditForm1);
Navigate(EditScreen)

 

If you want to populate the Edit form with selected item record in your Gallery, please set the Item property of the Edit form (EditForm1) to following:

LookUp('[dbo].[tblTicketsIT]', PrimaryColumn = Gallery1.Selected.PrimaryColumn)

Note: The PrimaryColumn represents the Primary Key column in your SQL Table.

 

Then you need to modify the formula within the OnSelect property of "Submit" button to following:

If(
   EditForm1.Mode = FormMode.New,
   Patch(                                     // Create new mode
         '[dbo].[tblTicketsIT]',
          Defaults('[dbo].[tblTicketsIT]'), 
          {
            CREATOR: txtCREATOR.Text, 
            PRIORITY: cmbPRIORITY.Selected.Value, 
            DEPARTMENT: txtDEPARTMENT.Text, 
            LOCATION: txtLOCATION.Text ,
            ISSUE: txtISSUE.Text, 
            IMAGE: Camera2.Photo
          }
    ),
    Patch(                               // edit existing record
          '[dbo].[tblTicketsIT]',
          LookUp('[dbo].[tblTicketsIT]', PrimaryColumn = Gallery1.Selected.PrimaryColumn),
          {
            CREATOR: txtCREATOR.Text, 
            PRIORITY: cmbPRIORITY.Selected.Value, 
            DEPARTMENT: txtDEPARTMENT.Text, 
            LOCATION: txtLOCATION.Text ,
            ISSUE: txtISSUE.Text, 
            IMAGE: Camera2.Photo
          }
    )
)

 

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.
Highlighted
Helper I
Helper I

Re: How to update a record with Patch?

@v-xida-msft 

Thank you for a great answer🙂! I tested your solution and it works great and probably will be used at some point in the future but...

I am not using FormMode because I wanted to create a combined new/edit screen from scratch so basically, I started with a blank screen and empty text fields which are then supposed to be populated via ">" from a gallery. For new records, I am using the "+" and Patch(Default) function which works flawlessly.

One might wonder why to use this approach and it is because I need more flexibility and options when building apps and I want to learn more about various ways of saving data outside of the "Form mode".

Highlighted
Helper I
Helper I

Re: How to update a record with Patch?

@eka24 

 

I have a blank screen and empty text fields which are then supposed to be populated via ">" from a gallery depending on which record from the gallery is selected. For new records I am using the "+" which uses Patch(Default) function.

Highlighted
Community Support
Community Support

Re: How to update a record with Patch?

Hi @DG1000 ,

Have you solved your problem?

 

Yeah, you could customize the formula I provided above to meet your needs. If you have solved your problem or the solution I provided above is helpful in your scenario, please go ahead to click "Accept as Solution" to identify this thread has been 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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

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

Top Solution Authors
Top Kudoed Authors
Users online (5,320)