Using the Patch function to either create OR update a record

[gcanelon]

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.

[Meneghino]

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]

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]

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]

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.

Any ideas?

Thanks for your help, its greatly appreciated.

[Meneghino]

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]

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

 

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.

Any idea what might be causing that issue?

Thanks again Meneghino.

Regards.

[Meneghino · Accepted solution]

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

[gcanelon · Accepted solution]

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.

[ngabougaifan]

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 ?