Solved: Send different emails using the same button based ...

Julien2 · ‎04-07-2020

Hello,

I have a SharePoint list that I am customizing it using PowerApps Forms.

In this list, I have the following columns:

Title,Manager Approval 1 (Column Type: Choice), Manager Approval 2 (Column Type: Choice)

Manager Approval 1 Signature (Column type: Single Line of text), and Manager Approval 2 Signature same column type as the first.

Additionally, I have a flow that triggers on this list when an item is created in SharePoint and generate two approval actions.

The process is the following:

When the first manager makes his first signature and clicks on send an email it will be sent based on the value of the first manager approval assigned from the SP list column and the column "Manager Approval 1 Signature" will be updated to "Signed". (This email will be sent from PowerApps with the attached signature and not from the Flow).

After the first manager has approved the item, the second manager will receive to approve the item and add his signature. When he adds his signature he will click also on send an email button. (This email will be sent from PowerApps the attached signature and not from the Flow).

What I am trying to achieve is the following:

When the second approval clicks on to send an email, I am trying to send this email based on the Manager approval 2 choices value column and not the manager approval 1 choice value, and update the "Manager Approval 2 Signature" to Signed.

The reason that I am using the same "Send an email" button for both approvals is that because I am sending their signatures attached in their emails and this should be based on these Approval 1 & Approval 2 Choices Values.

I am finding a way to check if Manager Approval 1 Signature equals "Signed" then, an email will be sent to the Manager Approval 2 choice value and if it's not equal to "Signed" then it will be sent for the manager approval 1 choice value.

Besides, In the formula property, the condition checks if the manager approval 1 signature equal to Singed. If yes, the user will get a notification that is already signed, I want to do the same for the second approval if he tried to update his signature again.

Please have a look at the formula property below:

Set(varHTMLImage, "<!DOCTYPE html>
<html>
<head>
<title>Testing</title>
</head>
<body>

<h1>Testing Signature</h1>
<p> Test HTML from PowerApps.</p>

<img src=" &JSON(PenInput1.Image, JSONFormat.IncludeBinaryData ) & ">

</body>
</html>");


If(            
     !IsBlank(LookUp('Travel Request', ID = SharePointIntegration.SelectedListItemID && Manager Approval 1 Signature = "Signed")),
     Notify( "This item is already signed, you cannot sign it again!", NotificationType.Error),
     UpdateIf(
               'Travel Request',
               ID = SharePointIntegration.SelectedListItemID,
               {
                 Manager Approval 1 Signature: "Signed"
               }
     );
     Office365Outlook.SendEmailV2(DataCardValue7.Selected.Email, SharePointIntegration.SelectedListItemID, varHTMLImage,           {Attachments:Table({Name:"FirstApprovalSignature.jpg",ContentBytes:PenInput1.Image, '@odata.type':""})});
     Notify("This item has been signed successfully!", NotificationType.Success)
)

What should be the formula property to achieve the following scenario?

Can please someone explains in detail and provide an example?

Any help will be greatly appreciated.

Thank you.

WarrenBelz · ‎04-09-2020

Ok @Julien2 ,

Hopefully the last one now with a morning brain (7:20am here). Forget about code shortcuts - try this - you can trim it later it you want. Again, watch brackets, commas as free-typed.

Set(
   VManager2,
   Toggle1.Value
);
Set(
   vID,
   SharePointIntegration.SelectedListItemID
);
If(
   VManager2,
   Set(
      vNotSigned,
      IsBlank(
         LookUp(
            'Purchase Order', 
            ID = vID && 
            SecondMangSign= "Signed"
         )
      )
   ),
   Set(
      vNotSigned,
      IsBlank(
         LookUp(
            'Purchase Order', 
            ID = vID && 
            FirstMangSign= "Signed"
         )
      )
   )
);
If(      
   !vNotSigned,
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),
   If(
      VManager2,
      UpdateIf(
         'Purchase Order',
         ID = vID,
         {SecondMangSign : "Signed"}
      ),
	  UpdateIf(
         'Purchase Order',
         ID = vID,
         {FirstMangSign : "Signed"}
      )
   );
   If(
      !VManager2,
      Office365Outlook.SendEmailV2(
         DataCardValue7.Selected.Value, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"FirstApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
            )
         }
      );         
      Notify(
         "This item has been signed successfully!", 
         NotificationType.Success
      ),                
      Office365Outlook.SendEmailV2(
         DataCardValue8.Selected.Value, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
            Table(
               {
                  Name:"SecondApprovalSignature.jpg",
                  ContentBytes:PenInput1.Image, 
                  '@odata.type':""
               }
            )
         }
      );
      Notify(
         "This item has been signed successfully!", 
         NotificationType.Success
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

WarrenBelz · ‎04-07-2020

Hi @Julien2 ,

I have firstly parsed it below for clearer understanding.

Set(
   varHTMLImage, 
   "<!DOCTYPE html>
   <html>
      <head>
         <title>Testing</title>
      </head>
      <body>
        <h1>Testing Signature</h1>
        <p>Test HTML from PowerApps.</p>
        <img src=" &JSON(PenInput1.Image, JSONFormat.IncludeBinaryData) & ">
      </body>  
   </html>"
);
If(            
   !IsBlank(
      LookUp(
         'Travel Request', 
         ID = SharePointIntegration.SelectedListItemID && 
         Manager Approval 1 Signature = "Signed"
      )
   ),
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),
   UpdateIf(
      'Travel Request',
      ID = SharePointIntegration.SelectedListItemID,
      {
         Manager Approval 1 Signature: "Signed"
      }
   );
   Office365Outlook.SendEmailV2(
      DataCardValue7.Selected.Email, 
      SharePointIntegration.SelectedListItemID,
      varHTMLImage,
      {
         Attachments:
         Table(
            {
               Name:"FirstApprovalSignature.jpg",
               ContentBytes:PenInput1.Image, 
               '@odata.type':""
            }
         )
      }
   );
   Notify(
      "This item has been signed successfully!", 
      NotificationType.Success
   )
)

Firstly I am a little intrigued as to whether your JSON code displays the pen input image in the body of the email - I have not seen the code used for this purpose before.

But back to your question. The code above clearly checks for Manager Approval 1 Signature (I am also wondering if this should be 'Manager Approval 1 Signature' ) is set to signed on the current record and

If so notifies the user that it cannot be signed again
If not, sets the data source field Manager 1 Approval Signed to Signed and sends an email (I assume to the signer).

So now you want the same button to react to the second manager with the same process. The first problem I see is that you do not know who is pushing the button, so depending on this, the user (Manager 1) will get the current message or Manager 2 needs essentially the same process on different fields.

You could do some logic on User().FullName or User().Email with a reference table or have a toggle/drop down where they set this value. Whatever the logic, I would set a Variable at the start with this and the do something like.

Set(
   VManager2,
   true/false   //however
);
Set(
   varHTMLImage, 
   "<!DOCTYPE html>
   <html>
      <head>
         <title>Testing</title>
      </head>
      <body>
        <h1>Testing Signature</h1>
        <p>Test HTML from PowerApps.</p>
        <img src=" &JSON(PenInput1.Image, JSONFormat.IncludeBinaryData) & ">
      </body>  
   </html>"
);
If(            
   !IsBlank(
      LookUp(
         'Travel Request', 
         ID = SharePointIntegration.SelectedListItemID && 
         If(
            vManager2,
            Manager Approval 2 Signature = "Signed",
            Manager Approval 1 Signature = "Signed"
         )
      )
   ),
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),
   UpdateIf(
      'Travel Request',
      ID = SharePointIntegration.SelectedListItemID,
      If(
         vManager2,
         {Manager Approval 2 Signature: "Signed"},
         {Manager Approval 1 Signature: "Signed"}
      )
   );
   Office365Outlook.SendEmailV2(
      DataCardValue7.Selected.Email, 
      SharePointIntegration.SelectedListItemID,
      varHTMLImage,
      {
         Attachments:
         If(
            vManager2,
            Table(
               {
                  Name:"SecondApprovalSignature.jpg",
                  ContentBytes:PenInput1.Image, 
                  '@odata.type':""
               }
            ),
            Table(
               {
                  Name:"FirstApprovalSignature.jpg",
                  ContentBytes:PenInput1.Image, 
                  '@odata.type':""
               }
            )
         )
      }
   );
   Notify(
      "This item has been signed successfully!", 
      NotificationType.Success
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Julien2 · ‎04-08-2020

Hello @WarrenBelz ,

Thanks for such a detailed answer.

Exactly, each signer will receive an email that contains his signature attached, and at the same time, the Managers approval signatures fields (1 and 2) will be updated based on toggle you mentioned. (If it's no then this is the first approval if it's yes that's the second one).

I have added a toggle inside the signature popup to apply the logic based on the above. Regarding the formula you mentioned, I've got some error while modifying it inside the if conditions of the managervariables. But now since I have added the toggle I think we don't need the manager variable anymore, and currently, I am stuck here to implement the logic based on the Toggle value.

Besides, in my list, I have two different fields for the approvals that are assigned to sign the item and in the formula, we are sending the same email-based only on the first approval choice value, for example, DataCardValue7 (First approval assigned), DataCardValue8(Second Approval assigned). So I think we should have two email actions instead of one and of course the second email will be sent if the toggle is On.

I have tried to modify the formula as below but I got a bunch of errors:

Set(
   VManager2,
   true/false   //however
);
Set(
   varHTMLImage, 
   "<!DOCTYPE html>
   <html>
      <head>
         <title>Testing</title>
      </head>
      <body>
        <h1>Testing Signature</h1>
        <p>Test HTML from PowerApps.</p>
        <img src=" &JSON(PenInput1.Image, JSONFormat.IncludeBinaryData) & ">
      </body>  
   </html>"
);
If(            
   !IsBlank(
      LookUp(
         'Purchase Order', 
         ID = SharePointIntegration.SelectedListItemID && 
         If(
            VManager2, // (Toggle1.Value=?)
            FirstMangSign: "Signed",
            SecondMangSign: "Signed"
         )
      )
   ),
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),
   UpdateIf(
      'Purchase Order',
      ID = SharePointIntegration.SelectedListItemID,
      If(
         VManager2, // (Toggle1.Value=?)
         FirstMangSign: "Signed",
         SecondMangSign: "Signed"
      )
   );
   Office365Outlook.SendEmailV2(
      DataCardValue7.Selected.Email, 
      SharePointIntegration.SelectedListItemID,
      varHTMLImage,
      {
         Attachments:
            Table(
               {
                  Name:"FirstApprovalSignature.jpg",
                  ContentBytes:PenInput1.Image, 
                  '@odata.type':""
               }
            )
         )
      }
   );

   //Second approval
   Office365Outlook.SendEmailV2(
      DataCardValue8.Selected.Email, 
      SharePointIntegration.SelectedListItemID,
      varHTMLImage,
      {
         Attachments:
            Table(
               {
                  Name:"SecondApprovalSignature.jpg",
                  ContentBytes:PenInput1.Image, 
                  '@odata.type':""
               }
            )
         )
      }
   );
   Notify(
      "This item has been signed successfully!", 
      NotificationType.Success
   )
)

What should be the updated formulas based on what I've mentioned?

Looking forward to your response.
Thanks again!

WarrenBelz · ‎04-08-2020

Hi @Julien2 ,

Firstly for such a big lump of code, I am only trying to show you the suggested structure. Without vision of your data and control structure, I have to rely on you a bit to put some things, particularly correct field references in the right place. I will have another go on this basis, but expect to possibly have to tidy it up. As also noted, I have not looked very hard at the logic of any area not updated. By the way, I do like your logic of the two emails - it is the way I would do it.

Set(
   VManager2,
   YourToggleName.Value    //put toggle name here
);
Set(
   varHTMLImage, 
   "<!DOCTYPE html>
   <html>
      <head>
         <title>Testing</title>
      </head>
      <body>
        <h1>Testing Signature</h1>
        <p>Test HTML from PowerApps.</p>
        <img src=" &JSON(PenInput1.Image, JSONFormat.IncludeBinaryData) & ">
      </body>  
   </html>"
);
If(            
   !IsBlank(
      LookUp(
         'Purchase Order', 
         ID = SharePointIntegration.SelectedListItemID && 
         If(
            !VManager2,
            FirstMangSign: "Signed",
            SecondMangSign: "Signed"
         )
      )
   ),
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),
   UpdateIf(
      'Purchase Order',
      ID = SharePointIntegration.SelectedListItemID,
      If(
         !VManager2,
         FirstMangSign: "Signed",
         SecondMangSign: "Signed"
      )
   );
   If(
      !vManager2,
      Office365Outlook.SendEmailV2(
         DataCardValue7.Selected.Email, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"FirstApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
               )
            )
         }
      ),
      Office365Outlook.SendEmailV2(
         DataCardValue8.Selected.Email, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"SecondApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
               )
            )
         }
      )
   );
   Notify(
      "This item has been signed successfully!", 
      NotificationType.Success
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Julien2 · ‎04-08-2020

Hi @WarrenBelz ,

Thanks for updating the formulas.
Yes, the field references are in the right place.

I have updated the formulas based on what you mentioned, I still have errors inside the "Updateif condition", and the first send email action, and there is a warning based on the first condition.

Please refer to the following screenshots errors:

This is the updated formulas:

Set(
   VManager2,
   Toggle1    //put toggle name here
);
Set(
   varHTMLImage, 
   "<!DOCTYPE html>
   <html>
      <head>
         <title>Testing</title>
      </head>
      <body>
        <h1>Testing Signature</h1>
        <p>Test HTML from PowerApps.</p>
        <img src=" &JSON(PenInput1.Image, JSONFormat.IncludeBinaryData) & ">
      </body>  
   </html>"
);
If(            
   !IsBlank(
      LookUp(
         'Purchase Order', 
         ID = SharePointIntegration.SelectedListItemID && 
         If(
             
            !VManager2,

            FirstMangSign= "Signed",
            SecondMangSign= "Signed"
             
         )
      )
   ),
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),
   UpdateIf(
      'Purchase Order',
      ID = SharePointIntegration.SelectedListItemID,
      If(
         !VManager2,
         FirstMangSign= "Signed",
         SecondMangSign= "Signed"
      )
      
   );
   If(
      !VManager2,
      Office365Outlook.SendEmailV2(
         DataCardValue7.Selected.Email, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"FirstApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
               )
            )
         }
      ),
      Office365Outlook.SendEmailV2(
         DataCardValue8.Selected.Email, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"SecondApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
               )
            )
         }
      )
   );
   Notify(
      "This item has been signed successfully!", 
      NotificationType.Success
   )
)

What should be the formulas to solve these errors?

Looking forward to your response.
Thank you!

WarrenBelz · ‎04-08-2020

Ok @Julien2 ,

This is the danger of not being able to test code at my end - also noticed typo - the Variable needs to be

Set(
   vManager,
   Toggle1.Value
)

This should fix a lot of it.

Julien2 · ‎04-08-2020

Hi @WarrenBelz ,

Yes, I have missed adding Value to the toggle. After I have added Value to the toggle, nothing has been changed and the same errors occur.

Set(
   VManager2,
   Toggle1.Value    //put toggle name here
);
Set(
   varHTMLImage, 
   "<!DOCTYPE html>
   <html>
      <head>
         <title>Testing</title>
      </head>
      <body>
        <h1>Testing Signature</h1>
        <p>Test HTML from PowerApps.</p>
        <img src=" &JSON(PenInput1.Image, JSONFormat.IncludeBinaryData) & ">
      </body>  
   </html>"
);
If(            
   !IsBlank(
      LookUp(
         'Purchase Order', 
         ID = SharePointIntegration.SelectedListItemID && 
         If(
             
            !VManager2,

            FirstMangSign= "Signed",
            SecondMangSign= "Signed"
             
         )
      )
   ),
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),
   UpdateIf(
      'Purchase Order',
      ID = SharePointIntegration.SelectedListItemID,
      If(
         !VManager2,
         FirstMangSign= "Signed",
         SecondMangSign= "Signed"
      )
      
   );
   If(
      !VManager2,
      Office365Outlook.SendEmailV2(
         DataCardValue7.Selected.Email, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"FirstApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
               )
            )
         }
      ),
      Office365Outlook.SendEmailV2(
         DataCardValue8.Selected.Email, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"SecondApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
               )
            )
         }
      )
   );
   Notify(
      "This item has been signed successfully!", 
      NotificationType.Success
   )
)

Any idea what we've missed to add?

Thanks.

WarrenBelz · ‎04-08-2020

OK - another go - I really have to rely on you to tell me EXACTLY what errors you get and where. I am flying blind on this large piece of code and as mentioned, to get it all correct free-typing is almost impossible.

Your original query was around the syntax required for a second manager and I did it on the assumption the rest of your code worked and you would simply apply the principle to your code. So firstly, and ID filter is not delegable - I have set a Variable to this and should fix the first bit.

Set(
   VManager2,
   Toggle1.Value
);
Set(
   vID,
   SharePointIntegration.SelectedListItemID
);
Set(
   varHTMLImage, 
   "<!DOCTYPE html>
   <html>
      <head>
         <title>Testing</title>
      </head>
      <body>
        <h1>Testing Signature</h1>
        <p>Test HTML from PowerApps.</p>
        <img src=" &JSON(PenInput1.Image, JSONFormat.IncludeBinaryData) & ">
      </body>  
   </html>"
);
If(            
   !IsBlank(
      LookUp(
         'Purchase Order', 
         ID = vID && 
         If(
            VManager2,
            SecondMangSign= "Signed",   
            FirstMangSign= "Signed"
         )
      )
   ),
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),
   If(
      VManager2,
      UpdateIf(
         'Purchase Order',
         ID = SharePointIntegration.SelectedListItemID,
         SecondMangSign= "Signed"
      ),
	  UpdateIf(
         'Purchase Order',
         ID = SharePointIntegration.SelectedListItemID,
         FirstMangSign= "Signed"
      )
   );
   If(
      !VManager2,
      Office365Outlook.SendEmailV2(
         DataCardValue7.Selected.Email, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"FirstApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
               )
            )
         }
      ),
      Office365Outlook.SendEmailV2(
         DataCardValue8.Selected.Email, 
         SharePointIntegration.SelectedListItemID,
         varHTMLImage,
         {
            Attachments:
               Table(
                  {
                     Name:"SecondApprovalSignature.jpg",
                     ContentBytes:PenInput1.Image, 
                     '@odata.type':""
                  }
               )
            )
         }
      )
   );
   Notify(
      "This item has been signed successfully!", 
      NotificationType.Success
   )
)

Julien2 · ‎04-08-2020

Hi @WarrenBelz ,

Hello,

Thank you very much for your support.

I was able to resolve the formulas errors, and the mistakes were the followings:
-Instead of FirstMangSign (=) "Signed" it should be (:).
-Instead of DataCardValue8.Selected.Email should be DataCardValue8.Selected.Value because it's a choice.
-The last notification function should be after the second email closed parenthesis.

Besides, A warning appears here:

If( 
!IsBlank(
LookUp(
'Purchase Order', 
ID = vID &&

/// HERE THERE IS A WARNING
If(
VManager2,
SecondMangSign= "Signed", 
FirstMangSign= "Signed"
)
)
),
/// TILL HERE

Do you think this is important to take care of?

Finally, my last question is I might later, add more approvals and apply the same process using the same button so do I have to add more toggles for each new approval? Or is there any other simple way to achieve it?

Looking forward to your response.
Thanks again!

WarrenBelz · ‎04-08-2020

Thanks @Julien2 ,

It was getting late last night (7:00am here) and I wanted to solve it for you with the help you needed to provide to do this. I suspected that there were issues with your control values from the errors that were being shown on what I thought was correct structure.

The delegation warning is interesting as firstly I fixed the potential issue with the ID above and the items being queried have text values, which as generally delegable, which only leaves FirstManageSign and/or SecondManageSign, so what type of fields are these in the data source? Also to test another bit of logic, replace that bit with this (and yes, you really should fix this).

If(
   Countrows(        
      Filter(
         'Purchase Order', 
         ID = vID && 
         If(
            vManager2
            SecondMangSign= "Signed",
            FirstManagesign= "Signed"
         )
      )
   )>0, 
   Notify(
      "This item is already signed, you cannot sign it again!", 
      NotificationType.Error
   ),

or the long version

If(
   vManager2 &&
   Countrows(        
      Filter(
         'Purchase Order', 
         ID = vID && 
         SecondMangSign= "Signed",
      )
   )>0
   ||
   !vManager2 &&
   Countrows(        
      Filter(
         'Purchase Order', 
         ID = vID && 
         FirstMangSign= "Signed",
      )
   )>0,
    Notify(
   "This item is already signed, you cannot sign it again!", 
   NotificationType.Error
   ),

For more signers, a drop-down would be better setting a text value to test - you would end up with a long bit of code.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.