Powerapps Patch similar to an SQL Update procedure (OnSubmit)

[Anonymous]

Hi Everyone.

Basically, i have a sharepoint list as my datasource, let's call it SiteCompliance1. I will be using this list to track compliance across several location and the powerapp will be used by the compliance officer. The Compliance Officer's name is stored in a column called: "Verified By". My plan is to throw the value of user().fullname on to the "Verified By" column relative to Site selected and active in DetailScreen1 up to EditScreen1.

This is what my IconAccept code looks like: (note i have used a powerapps to send an email on submit, i am not sure if it's affecting patch i added it here in this case.

The columns used here are all Single Line of Text formatted.

ClickabuttoninPowerAppstosendanemail.Run("email@email.com", DataCardValue3 & " site compliance has been modified", "Hi Team, I have modified the site Compliance for the site " & DataCardValue3 ); Patch( SiteCompliance, First( Filter( SiteCompliance, Site = DataCardValue3.Text ) ), { Verified By: user().fullname} )SubmitForm(EditForm1);

ps. i haven't used Patch before so pardon my ignorance.

[wyotim · Accepted solution]

Hi @Anonymous, in looking at the bit of code you posted I have a couple of suggestions about why it might not be working for you. Here is my suggested fix:

ClickabuttoninPowerAppstosendanemail.Run(
    "email@email.com", 
    DataCardValue3.Text & " site compliance has been modified", 
    "Hi Team, I have modified the site Compliance for the site " & DataCardValue3.Text ); 
Patch( 
    SiteCompliance, 
    First(Filter(SiteCompliance, Site = DataCardValue3.Text)), 
    {Verified By: User().FullName} 
);
SubmitForm(EditForm1)

There was a missing semicolon before the SubmitForm function, which was probably giving an error that looked like the wrong number of variables were being used or that a name was being referenced wrong. This should hopefully correct the issue, but let me know if it doesn't! And for the record, your Patch statement looks great. You have the data source, the record to be patched, and the data you are patching. All good!

Also, a couple of minor suggestions: first, using LookUp() instead of the First(Filter()) method will perform a bit better. Something like this:

ClickabuttoninPowerAppstosendanemail.Run(
    "email@email.com", 
    DataCardValue3.Text & " site compliance has been modified", 
    "Hi Team, I have modified the site Compliance for the site " & DataCardValue3.Text ); 
Patch( 
    SiteCompliance, 
    LookUp(SiteCompliance, Site = DataCardValue3.Text), 
    {Verified By: User().FullName} 
);
SubmitForm(EditForm1)

Basically, rather than filtering a table, grabbing the resulting table, and choosing the first one, it looks for a match and takes the first one it finds. What you did, which might have come from the documentation, will work but the LookUp method will be just a bit faster for the people using your app. 

Second, I am pretty sure the User().FullName looks up the information every time it is called. To prevent this, you could store that value in a global variable so that it only looks it up once per app launch and is able to be referenced on any screen in your app. Here is one way to do that:

// This would go in the OnStart property of the app
Set(glbUserFullName, User().FullName)

You would then replace any time you use User().FullName with glbUserFullName. Again, a small detail but one that will help your app be just a bit faster.

I hope that helps but feel free to let me know if it doesn't!

[Anonymous]

Thanks wyotim. Not only did i missed the semicolon, powerapps got some issues with List Column names with 2 spaces (the actual name of the column is "Last Verified By"). When i trimmed it to LastVerifiedBy, the patch worked. I also used the global variable as you mentioned. 

This is quite a learning curve as this is completely new for me and it is quite fun. More fun than Excel VBA.

[wyotim]

Hey @Anonymous, I'm a bit late in my reply but just wanted to note that for references with spaces in Power Apps you would use single quotes. I should have caught that in my suggestion to you, but obviously that one still gets me from time to time! If the patch works, all is probably good, but for future reference, I thought I should throw that in. Here is an example using my previous suggestion:

ClickabuttoninPowerAppstosendanemail.Run(
    "email@email.com", 
    DataCardValue3.Text & " site compliance has been modified", 
    "Hi Team, I have modified the site Compliance for the site " & DataCardValue3.Text ); 
Patch( 
    SiteCompliance, 
    LookUp(SiteCompliance, Site = DataCardValue3.Text), 
    {'Last Verified By': User().FullName} 
);
SubmitForm(EditForm1)

I agree that there is quite a steep learning curve, especially coming from VBA! I came from an Excel VBA background myself, so I can appreciate what you are experiencing. Many concepts to learn, but once they start to firm up it gets much easier! If you have any further issues, feel free to @ me in your posts!