OnSubmit Update TextInput and Send Email

[Dave-ITMan]

Hi Experts,

I'm in need of some desperate help please with a formula for the OnSubmit of a button on my form that links to a sharepoint list. Below is what i'm trying to achieve as i'm not sure how to go about the "But" scenarios and I have SubmitForm at the end but this may need to happen before the email gets sent in order to access the data, i'll await your comments on this. What i'm looking to achieve is the below:

If DataCardValue26.Text = "Submitted" then update the text in DataCardValue26 to "1st Stage Approved" and then send email to an email address with subject 1st Stage Approved and in the body of the email it should show the information that has just been entered into DataCardValue24 (this is some 1st stage comments). It then needs to CC the user that originally created the form. It should then send another email to a different email address to say it is pending 2nd Stage Approval.

But, If DataCardValue26.Text = "1st Stage Approved" then update the text in DataCardValue26 to "2nd Stage Approved" and then send email to an email address with subject 2nd Stage Approved and in the body of the email it should show the information that has just been entered into DataCardValue25 (this is some 2nd stage comments). It then needs to CC the user that originally created the form.

But, If DataCardValue26.Text = "Rejected" then update the text in DataCardValue26 to "Rejected" and then send email to an email address with subject Rejected and in the body of the email it should show the information that has just been entered into DataCardValue24 AND DataCardValue25 (this is some 1st and 2nd stage comments). It then needs to CC the user that originally created the form.

SubmitForm(EditForm_1);Navigate(Home)

Thanks in advance!

[AaronKnox]

Hi @Dave-ITMan ,

To govern what is written to the data source during SubmitForm(EditForm_1), you just need a formula in the data card that contains the text control 'DataCardValue26'.  Each data card has a property called Update.  This is where the data card tells the data source how to create/update this column in the record.  The formula in DataCard.Update right now is probably just DataCardValue26.Text.  The new formula for DataCard.Update would be something like:

If(
 DataCardValue26.Text = "Submitted", "1st Stage Approved",
 DataCardValue26.Text = "1st Stage Approved", "2nd Stage Approved",
 DataCardValue26.Text
)

Now we have a blueprint for how to send the email.  But first we need to fix the Submit button:

OnSubmit should only contain SubmitForm(EditForm_1).  The Navigate(Home) should come in the EditForm_1.OnSuccess property.

Likewise, any email should also be in EditForm_1.OnSuccess.  You can use EditForm_1.LastSubmit to see the entire record that was just created.  The formula in EditForm_1.OnSuccess would be something similar to the DataCard.Update formula:

If(
 DataCardValue26.Text = "Submitted", 
    Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: 1st Stage Approved", 
	"Body:  1st Stage Comments: " & EditForm_1.LastSubmit.1STSTAGECOMMENTS , 
	{​​​​​​ 
	Cc: EditForm_1.LastSubmit.'Created By'.Email,  
	}​​​​​​ 
	);
    Office365Outlook.SendEmailV2(  
	"another.email@address", 
	"Subject: pending 2nd Stage Approval", 
	"Body:  pending 2nd Stage Approval" , 
    )
 ,
 DataCardValue26.Text = "1st Stage Approved",
   Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: 2nd Stage Approved", 
	"Body:  2nd Stage Comments: " & EditForm_1.LastSubmit.2NDSTAGECOMMENTS , 
	{​​​​​​ 
	Cc: EditForm_1.LastSubmit.'Created By'.Email,  
	}​​​​​​ 
	)
 ,
 DataCardValue26.Text = "Rejected", 
   Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: Rejected", 
	"Body:  1st Stage Comments: " & EditForm_1.LastSubmit.1STSTAGECOMMENTS &
	"2nd Stage Comments: " & 	EditForm_1.LastSubmit.2NDSTAGECOMMENTS , 
	{​​​​​​ 
	Cc: EditForm_1.LastSubmit.'Created By'.Email,  
	}​​​​​​ 
	)
);
Navigate(Home)

[Dave-ITMan]

Hi @AaronKnox ,

Thank you for the very informative description. The issue that I have is that I have 2x buttons on the form, one is named Approve and the other is named Reject so would need the formula to be in the OnSubmit of the button rather than OnSuccess of the form depending on which button was clicked. So really, the below should be in the Reject button OnSubmit, would your code still work in this approach or would it need to be tweaked?

 DataCardValue26.Text = "Rejected", 
   Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: Rejected", 
	"Body:  1st Stage Comments: " & EditForm_1.LastSubmit.DataCardValue24.Text &
	"2nd Stage Comments: " & 	EditForm_1.LastSubmit.DataCardValue25.Text , 
	{​​​​​​ 
	Cc: EditForm_1.LastSubmit.'Created By'.Email,  
	}​​​​​​ 
	)

Thank you for your help!

[AaronKnox · Accepted solution]

Hi @Dave-ITMan ,

I just modified my original post.  The emails that are sent in OnSuccess should reference the LastSubmit record and not a control in the form data card.  Since I don't know the name of the 1st and 2nd stage comments columns I just wrote that in all caps so you will need to change this to match your data source:  EditForm_1.LastSubmit.1STSTAGECOMMENTS.

The reject button should only update a local variable and then SubmitForm.  Unless you don't want to update the data source if rejected, the emails should always come after SubmitForm.  Then you can use that local variable to determine if we should go down the rejected path:

btnReject.OnSubmit:

UpdateContext({locRejected:true}); SubmitForm(EditForm_1)

btnAccept.OnSubmit:

UpdateContext({locRejected:false}); SubmitForm(EditForm_1)

DataCard.Update :

If(
 locRejected, "Rejected",
 DataCardValue26.Text = "Submitted", "1st Stage Approved",
 DataCardValue26.Text = "1st Stage Approved", "2nd Stage Approved",
 DataCardValue26.Text
)

OnSuccess :

If(
 locRejected, 
   Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: Rejected", 
	"Body:  1st Stage Comments: " & EditForm_1.LastSubmit.1STSTAGECOMMENTS &
	"2nd Stage Comments: " & 	EditForm_1.LastSubmit.2NDSTAGECOMMENTS , 
	{​​​​​​ 
	Cc: EditForm_1.LastSubmit.'Created By'.Email  
	}​​​​​​ 
	)
 ,
 DataCardValue26.Text = "Submitted", 
    Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: 1st Stage Approved", 
	"Body:  1st Stage Comments: " & EditForm_1.LastSubmit.1STSTAGECOMMENTS , 
	{​​​​​​ 
	Cc: EditForm_1.LastSubmit.'Created By'.Email  
	}​​​​​​ 
	);
    Office365Outlook.SendEmailV2(  
	"another.email@address", 
	"Subject: pending 2nd Stage Approval", 
	"Body:  pending 2nd Stage Approval"
    )
 ,
 DataCardValue26.Text = "1st Stage Approved",
   Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: 2nd Stage Approved", 
	"Body:  2nd Stage Comments: " & EditForm_1.LastSubmit.2NDSTAGECOMMENTS , 
	{​​​​​​ 
	Cc: EditForm_1.LastSubmit.'Created By'.Email  
	}​​​​​​ 
	)
);
Navigate(Home)

[Dave-ITMan]

Hi @AaronKnox ,

Thank you, i'm getting quite a few errors though, i've updated the comments ('1st Approver Comments' and '2nd Approver Comments') but i'm getting "Invalid Number of Arguments: Receive 8, Expected 3-4". I'm also seeing in the Errors section for OnSuccess of EditForm_1 we expect a colon at this point but i'm not sure which point, that same error comes up 3 times so my guess would be somewhere within each email section. I'm also seeing several "The formula contains ... but we expect ..." there's about 4 different ones of those coming up? 

Many thanks

[AaronKnox]

@Dave-ITMan Yep, I had a comma at the end of the second Submitted email:  

"Body: pending 2nd Stage Approval" ,

This probably caused the error.  I updated the original post to remove this comma.

[Dave-ITMan]

Hi @AaronKnox ,

Thanks, just to confirm this is the OnSuccess of EditForm_1? I've updated it as per the below but still got errors which i'll also share:

If(
 locRejected, 
   Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: Rejected", 
	"Body:  1st Stage Comments: " & EditForm_1.LastSubmit.'1st Approver Comments' &
	"2nd Stage Comments: " & 	EditForm_1.LastSubmit.'2nd Approver Comments' , 
	{
	Cc: EditForm_1.LastSubmit.'Created By'.Email,  
    }
	)
 ,
 DataCardValue26.Text = "Submitted", 
    Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: 1st Stage Approved", 
	"Body:  1st Stage Comments: " & EditForm_1.LastSubmit.'1st Approver Comments' , 
	{
	Cc: EditForm_1.LastSubmit.'Created By'.Email,  
    }
	);
    Office365Outlook.SendEmailV2(  
	"another.email@address", 
	"Subject: pending 2nd Stage Approval", 
	"Body:  pending 2nd Stage Approval"
    )
 ,
 DataCardValue26.Text = "1st Stage Approved",
   Office365Outlook.SendEmailV2(  
	"an.email@address", 
	"Subject: 2nd Stage Approved", 
	"Body:  2nd Stage Comments: " & EditForm_1.LastSubmit.'2nd Approver Comments' , 
	{
	Cc: EditForm_1.LastSubmit.'Created By'.Email,  
    }
	)
);
Navigate(Home)

The 6 errors that i'm getting are below:

We expect a colon at this point in the formula

Unexpected characters. The formula contains 'CurlyClose' where 'Ident' is expected

We expect a colon at this point in the formula

Unexpected characters. The formula contains 'CurlyClose' where 'Ident' is expected

We expect a colon at this point in the formula

Unexpected characters. The formula contains 'CurlyClose' where 'Ident' is expected

Many thanks

[AaronKnox]

@Dave-ITMan There should be no comma after the CC. 

However, even with those gone I'm still getting the same errors.  Something is buggy with the {Cc:""} section of the code, I brought this up last year (my old account). 

Try to get it to work without the {CC:""} part, then save the PowerApp and go back in and see if you can add it w/o error.

[Dave-ITMan]

Thanks @AaronKnox ,

Removing those comma's didn't bring up any further errors for me so I will do some testing to make sure it does what I need it to and let you know if there's any further issues. Thanks again for your help, it's very much appreciated. 

[Dave-ITMan]

Hi @AaronKnox ,

I'm approving at Stage1 but it's setting it to Rejected? Do you know why that is please? I did a test (approving at 1st and 2nd stage) before testing the rejection route and it worked fine but everything that i'm Approving at 1st stage now is setting everything to Rejected.

Many thanks