How to separate one table into two in email

[Anonymous]

Hi all, i'm stuck and hope you'll help me

I need PowerApp to separate one table into two in email based on column "Quality Status" - please see example of source table below (table and PowerApp code will be attached in excel).

I can send single table via mail using this logic:

It works well and sends following message:

What i'm trying to achieve is to filter output message based on field "Quality Status"

There are 3 possible scenarios:

1) Column "Quality Status" has both attributes - "Overdue" and "Check"(see example below) 

Then output should look like this

2) Column "Quality Status" has attribute "Overdue" only(see example below):

Then output message should look like this:

3) Column "Quality Status" has attribute "Check" only (see example below):

Then output message should look like this:

How should i modify PowerApp logic to achieve this?

Many thanks in advance!

Regards,

Oleh

[v-bofeng-msft · Accepted solution]

Hi @ Oseme:

My suggestion is to use the if function to divide the body of the email into three situations for processing:

If(
""Overdue"" in [@PowerBIIntegration].Data.'Age on Stage' && ""Check"" in [@PowerBIIntegration].Data.'Age on Stage';
   Code1;
""Overdue"" in [@PowerBIIntegration].Data.'Age on Stage' && !(""Check"" in [@PowerBIIntegration].Data.'Age on Stage');
   Code2;
!(""Overdue"" in [@PowerBIIntegration].Data.'Age on Stage') && ""Check"" in [@PowerBIIntegration].Data.'Age on Stage';
   Code3;
)

Finally, the complete formula should be (because of regional issues, the operator we use is different, so I can't test this code, there may be some minor errors):

Office365Outlook.SendEmail(
   First([@PowerBIIntegration].Data).Email;
   "Revenue Notification";
If(
""Overdue"" in [@PowerBIIntegration].Data.'Age on Stage' && ""Check"" in [@PowerBIIntegration].Data.'Age on Stage';
   ""Hi "" & First([@PowerBIIntegration].Data).Owner&"",""& ""<br>"" &
  
""Currently,you have teh following XXXXX"" & ""<br>"" &
  
""<table border=1>
<tr>
<th>Account</th>
 
<th>Opportunity</th>
 
<th>Revenue</th>
<th>Sales Forecast</th>
 
<th>Sales Stage</th>
<th>Age on Stage</th>
<th>Deal Desk</th>
 
<th>CustomerAcceptance</th>
<th>Quality Status</th>
</tr>"" &
Concat(
 
   ForAll( Filter([@PowerBIIntegration].Data;""Overdue"" in 'Quality Status');
""<tr><td>"" & Account &
""</td><td>"" & Opportunity &
""</td><td>"" & Revenue &
""</td><td>"" & 'Sales Forecast' &
""</td><td>"" & 'Sales Stage' &
""</td><td>"" & 'Age on Stage' &
""</td><td>"" & 'Deal Desk' &
""</td><td>"" & CustomerAcceptance &
""</td><td>"" & 'Quality Status' &
""</tr></td>""
);
Value
) & ""<br>"" &
 
""Also please reviewXXXXXXXXXX"" & ""<br>"" &
  
""<table border=1>
<tr>
<th>Account</th>
 
<th>Opportunity</th>
 
<th>Revenue</th>
<th>Sales Forecast</th>
 
<th>Sales Stage</th>
<th>Age on Stage</th>
<th>Deal Desk</th>
 
<th>CustomerAcceptance</th>
<th>Quality Status</th>
</tr>"" &
Concat(
 
   ForAll( Filter([@PowerBIIntegration].Data;""Check"" in 'Quality Status');
""<tr><td>"" & Account &
""</td><td>"" & Opportunity &
""</td><td>"" & Revenue &
""</td><td>"" & 'Sales Forecast' &
""</td><td>"" & 'Sales Stage' &
""</td><td>"" & 'Age on Stage' &
""</td><td>"" & 'Deal Desk' &
""</td><td>"" & CustomerAcceptance &
""</td><td>"" & 'Quality Status' &
""</tr></td>""
);
Value
);
""Overdue"" in [@PowerBIIntegration].Data.'Age on Stage' && !(""Check"" in [@PowerBIIntegration].Data.'Age on Stage');
""Hi "" & First([@PowerBIIntegration].Data).Owner&"",""& ""<br>"" &
  
""Currently,you have teh following XXXXX"" & ""<br>"" &
  
""<table border=1>
<tr>
<th>Account</th>
 
<th>Opportunity</th>
 
<th>Revenue</th>
<th>Sales Forecast</th>
 
<th>Sales Stage</th>
<th>Age on Stage</th>
<th>Deal Desk</th>
 
<th>CustomerAcceptance</th>
<th>Quality Status</th>
</tr>"" &
Concat(
 
   ForAll( Filter([@PowerBIIntegration].Data;""Overdue"" in 'Quality Status');
""<tr><td>"" & Account &
""</td><td>"" & Opportunity &
""</td><td>"" & Revenue &
""</td><td>"" & 'Sales Forecast' &
""</td><td>"" & 'Sales Stage' &
""</td><td>"" & 'Age on Stage' &
""</td><td>"" & 'Deal Desk' &
""</td><td>"" & CustomerAcceptance &
""</td><td>"" & 'Quality Status' &
""</tr></td>""
);
Value
);
!(""Overdue"" in [@PowerBIIntegration].Data.'Age on Stage') && ""Check"" in [@PowerBIIntegration].Data.'Age on Stage';
""Hi "" & First([@PowerBIIntegration].Data).Owner&"",""& ""<br>"" &
  
""Please reviewXXXXXXXXXX"" & ""<br>"" &
  
""<table border=1>
<tr>
<th>Account</th>
 
<th>Opportunity</th>
 
<th>Revenue</th>
<th>Sales Forecast</th>
 
<th>Sales Stage</th>
<th>Age on Stage</th>
<th>Deal Desk</th>
 
<th>CustomerAcceptance</th>
<th>Quality Status</th>
</tr>"" &
Concat(
 
   ForAll( Filter([@PowerBIIntegration].Data;""Check"" in 'Quality Status');
""<tr><td>"" & Account &
""</td><td>"" & Opportunity &
""</td><td>"" & Revenue &
""</td><td>"" & 'Sales Forecast' &
""</td><td>"" & 'Sales Stage' &
""</td><td>"" & 'Age on Stage' &
""</td><td>"" & 'Deal Desk' &
""</td><td>"" & CustomerAcceptance &
""</td><td>"" & 'Quality Status' &
""</tr></td>""
);
Value
);
);
           {IsHtml: true})

Best Regards,

Bof

[Anonymous]

Thanks - worked perfectly!

Best regards, Oleh