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

Using the Patch function to either create OR update a record

Hi, we are developing a free system that allows to coordinate the efforts of volunteer rescue and emergency groups working in a disaster or conflict zone. For us it's extremely important to be able to know where our medics and support teams are located. Since they use a PowerApps to create a report for every patient they attend, we incorporated into this same app a formula that is triggered with the OnVisible event and that sends us their location so it can be stored in a SharePoint list and then shown in a Power BI map. This is the formula to report their location  

 

Patch('SharepointList',Defaults('SharepointList'), {Title:"Title", Latitud:Location.Latitude,Longitud:Location.Longitude})

 

This formula is working great, however it creates a separate record every time. We would like to alter this formula to evaluate if the current user already has a location record in the list or not, and to create a record if it doesn't already have one or modify it if a record for this user already exists. This way we would only have the most update location for the user.

 

Any help with this would be greatly appreciated.

 

Best regards.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Meneghino
Level 10

Re: Using the Patch function to either create OR update a record

Hi @gcanelon

Yes, I think the issue is that MyPatchedRecord is a table and not a record.  Try this instead:

ClearCollect(MyPatchedRecord, Patch('Ubicacion de personal de recate',{ID:CurrentUserID}, {Title: CurrentUser.FullName, Latitud:Location.Latitude,Longitud:Location.Longitude}));
If(IsBlank(CurrentUserID), Set(CurrentUserID, Last(MyPatchedRecord).ID))

Please let me know

View solution in original post

gcanelon
Level: Powered On

Re: Using the Patch function to either create OR update a record

Awesome Meneghino, it works flawlessly. I tried it with several users and its smooth and does what it's intended to do. Thanks for your help. Its greatly appreciated.

 

Regards.

View solution in original post

13 REPLIES 13
Meneghino
Level 10

Re: Using the Patch function to either create OR update a record

Hi @gcanelon

I would use something like this:

If(IsEmpty(Filter('SharepointList', User=CurrentUser)),
   Patch('SharepointList',Defaults('SharepointList'), {Title:"Title", Latitud:Location.Latitude,Longitud:Location.Longitude}),
Patch('SharepointList',{User: CurrentUser}, {Title:"Title", Latitud:Location.Latitude,Longitud:Location.Longitude})
)

User, and CurrentUser are psedocode for however you identify your user.  You could use the Title column.

gcanelon
Level: Powered On

Re: Using the Patch function to either create OR update a record

Hello Meneghino, thanks for your reply. Unfortunately its not working, it still creates a new record every time. Here is the way I am applying your suggested formula:

 

If(IsEmpty(Filter('Rescue personnel location', Title=User().FullName)),
   Patch('Rescue personnel location',Defaults('Rescue personnel location'), {Title:User().FullName, Latitud:Location.Latitude,Longitud:Location.Longitude}),
   Patch('Rescue personnel location',{Title:User().FullName}, {Title:User().FullName, Latitud:Location.Latitude,Longitud:Location.Longitude})
)

 

Any suggestions?

 

Thanks in advance.

Meneghino
Level 10

Re: Using the Patch function to either create OR update a record

Hi @gcanelon

The problem is due to the User() function, which needs to make a remote call every time it is used.  Since this is slow, it often returns a blank/null value in formulas, so that basically you look to see if Title is blank of any record.  Since it is not, then you create a new record.

The solution is to put this in the OnStart property of the first screen:

Set(CurrentUser, User())

This will create a global variable CurrentUser that contains the User() information. Since the user does not change during a session, then there are no problems.

Then in your formulas everywhere, just change User().FullName to CurrentUser.FullName

Your app should also run faster.

Please note that once you set the OnStart property you need to save the app, close it and then re-open it in order for the OnStart property to set the variable to the current user if you need to test the app in Studio.

Please let me know if this resolves your issue.

If not, then I have other ideas.

gcanelon
Level: Powered On

Re: Using the Patch function to either create OR update a record

Hi Meneghino, unfortunatelly is not working. The local variable seems to be doing the job of improving performance as I no longer get the performance warning message next to the button. However it's still creating a new record every time.

 

Here are the screen shots of both relevant screens showing the formulas and their location.

Screenshot 2017-08-22 07.06.54.pngScreenshot 2017-08-22 07.07.04.png

 

Any ideas?

 

Thanks for your help, its greatly appreciated.

Meneghino
Level 10

Re: Using the Patch function to either create OR update a record

Hi @gcanelon

 

Apologies, my mistake.  I disregarded the fact that you use a SharePoint list as a data source, so we may have to reference the ID of the list entry in the Patch function.

 

Let's take this one step at a time to get it right.

 

Please expand the OnStart property of the first screen to this:

Set(CurrentUser, User()); Set(CurrentUserID, LookUp('Ubicacion de personal de recate', Title=CurrentUser.FullName, ID))

You will need to save and reopen the app.

Then create a label and set its Text property to CurrentuserID.

It should show the ID of the correct list item if the person exists already or blank/null if they do not.

 

Once you confirm this then we can proceed.

 

PS I am going to lunch now, so if this works as expected, then the next step is for the button to have this action:

Set(MyPatchedRecord, Patch('Ubicacion de personal de recate',{ID:CurrentUserID}, {Title: CurrentUser.FullName, Latitud:Location.Latitude,Longitud:Location.Longitude}));
If(IsBlank(CurrentUserID), Set(CurrentUserID, MyPatchedRecord.ID))

Basically this will work even more efficiently than before, since we only test to see if the user exists once at the beginning of the app.  There is no more need for the If statement since if CurrentUserID is blank (i.e. not zero or any other value) then the Patch will automatically create a new record, and if it is not then it will replace the record with the existing ID)

gcanelon
Level: Powered On

Re: Using the Patch function to either create OR update a record

I completed step number one and the label shows the number 145 whic is the element ID.

 Screenshot 2017-08-22 15.04.01.png

 

That completed step 1.

 

I went ahead for step 2 and placed the formula in the OnSelect action and got this error in the last statement: "The types of specified global variables are incompatible wth the types specified elsewhere". See image below.

 

Screenshot 2017-08-22 22.38.29.png

 

Any idea what might be causing that issue?

 

Thanks again Meneghino.

 

Regards.

Meneghino
Level 10

Re: Using the Patch function to either create OR update a record

Hi @gcanelon

Yes, I think the issue is that MyPatchedRecord is a table and not a record.  Try this instead:

ClearCollect(MyPatchedRecord, Patch('Ubicacion de personal de recate',{ID:CurrentUserID}, {Title: CurrentUser.FullName, Latitud:Location.Latitude,Longitud:Location.Longitude}));
If(IsBlank(CurrentUserID), Set(CurrentUserID, Last(MyPatchedRecord).ID))

Please let me know

View solution in original post

gcanelon
Level: Powered On

Re: Using the Patch function to either create OR update a record

Awesome Meneghino, it works flawlessly. I tried it with several users and its smooth and does what it's intended to do. Thanks for your help. Its greatly appreciated.

 

Regards.

View solution in original post

ngabougaifan
Level: Powered On

Re: Using the Patch function to either create OR update a record

Hi I have faced something similar and need some help !

 

I have the submit button with the OnSelect function "Patch(Table1,Defaults(Table1),Form9.Updates,Form10.Updates)"

 

However, when I wanted to edit and update the records, it will create a new row of record instead of updating the selected record. How could I work around with this ?

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 359 members 4,346 guests
Please welcome our newest community members: