cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to separate one table into two in email

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).

Oseme_0-1600267509834.png

I can send single table via mail using this logic:

Oseme_1-1600267547831.png

It works well and sends following message:

Oseme_2-1600267637953.png

 

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) 

Oseme_3-1600267743064.png

Then output should look like this

Oseme_4-1600267790109.png

 

 

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

Oseme_5-1600267861062.png

Then output message should look like this:

Oseme_6-1600267883872.png

 

 

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

Oseme_7-1600267929010.png

Then output message should look like this:

Oseme_8-1600267959397.png

 

How should i modify PowerApp logic to achieve this?

 

Many thanks in advance!

 

Regards,

Oleh

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

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
Not applicable

Thanks - worked perfectly!

 

Best regards, Oleh

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,447)