Data Table to Email in PowerApps

[mattthew]

Hi-

We have been able to successfully email a table from the HTMLText control but I noticed it only submits the first selection of the table.  How can I modify the selection to submit the complete table? 

  <tr>
    <th>Work Order</th>
    <th>Task Selection </th> 
    <th>Hazards</th>                                                                                                                                          <th>Permits/Documents</th>                                                                                                                                <th>Consequence</th>                                                                                                                                      <th>Mitigations</th>                                                   
  </tr>
  <tr>
    <td>"&DataTable1.Selected.WorkOrder&"</td>
    <td>"&DataTable1.Selected.Title&"</td>
    <td>"&DataTable1.Selected.Hazards&"</td>
  </tr>
  <tr>
    <td>"&DataTable1.Selected.WorkOrder&"</td>
    <td>"&DataTable1.Selected.Title&"</td>
    <td>"&DataTable1.Selected.Hazards&"</td>
  </tr>
  <tr>
    <td>"&DataTable1.Selected.WorkOrder&"</td>
    <td>"&DataTable1.Selected.Title&"</td>
    <td>"&DataTable1.Selected.Hazards&"</td>
  </tr>
</table>

</body>
</html>

[v-xida-msft · Accepted solution]

Hi @mattthew,

Do you want to send your whole Data Table values as a HTML table within an email?

The DataTable1.Selected formula used to get the selected record within your Data table. Currently, there is no formula supported within PowerApps to get all records within the Data Table control.

As an alternative solution, you could use the formula within the Items property of the Data Table control as the data source, then send HTML table email based on this data source.

I have made a test on my side, please take a try with the following workaround:

Set the Items property of the Data Table control to following:

'20181122_case14'   /* <-- '20181122_case14' represents my SP List data source */

Set the HtmlText proeprty of the Html Text control (HtmlText1) to following:

"<h3>Data Table HTML Email</h3>" &
"<strong> Items: </strong>" &
"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &
     "<tr style='background-color:#efefef'>
         <th>ProductName</th> <th> Amount </th> <th> Price </th><th> ProductionDate </th>
     </tr>
     <tr>" &
         Concat('20181122_case14',
             "<td>" & ProductName & " </td>
              <td>" & Amount & " </td>
              <td>" & Price & " </td>
              <td>" & ProductionDate & " </td>","</tr><tr>") &
"</table>"

On your side, you should type:

"<h3>Data Table HTML Email</h3>" &
"<strong> Items: </strong>" &
"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &
     "<tr style='background-color:#efefef'>
         <th>ProductName</th> <th> Amount </th> <th> Price </th><th> ProductionDate </th>
     </tr>
     <tr>" &
         Concat('The data source/formula that you provided within the Items property of your Data Table control',
             "<td>" & WorkOrder & " </td>
              <td>" & Title & " </td>
              <td>" & Hazards & " </td>","</tr><tr>") &
"</table>"

Set the OnSelect property of the "Send Email" button to following:

Office365.SendEmail("Test1@xxxxxx.onmicrosoft.com","Data Table HTML Email", HtmlText1.HtmlText, {IsHtml:true})

On your side, you should type:

Office365.SendEmail("The email address you want to send to","Data Table HTML Email", HtmlText1.HtmlText, {IsHtml:true})

The Html Table email sent successfully as below:

 

In addition, you could also consider take a try to set the OnSelect proeprty of the "Send Email" button to following:

Office365.SendEmail(
     "The email address you want to send email to",
     "Data Table HTML Email",
     "<h3>Data Table HTML Email</h3>" &
     "<strong> Items: </strong>" &
     "<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &
     "<tr style='background-color:#efefef'>
      <th>ProductName</th> <th> Amount </th> <th> Price </th><th> ProductionDate </th>
     </tr>
     <tr>" &
    Concat('The data source/formula that you provided within the Items property of your Data Table control',
       "<td>" & WorkOrder & " </td>
        <td>" & Title & " </td>
        <td>" & Hazards & " </td>","</tr><tr>") &
    "</table>",
    {
     IsHtml:true
    }
)

Please also check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/html-email-reporting-with-tabular-data/

Best regards,

Kris

[mattthew]

@v-xida-msft  awesome & thanks!! 

[Anonymous]

Works like a charm! Thanks a million!

[cofordpa]

I'm having issues populating the HTML Table from the a DataTable created from a Collection as DataSource.  Will this method still work? 
Thanks

[CW]

Yes it works with a collection. Make sure you have the proper number of headers to columns!! That got me for a few minutes.

[CW]

Works great thanks!!!

However, My collection in the datatable is sorted. The HTML blows away the sort orders. Is itpossible to maintain that order in HTML?

[CindyZ]

Hello,

NOTE:  Please see below the blue line of entry -- my question has changed.  

I'm trying to get all items from a SharePoint list (enhanced rich text field) that is in a PowerApps (HTMLText control) converted to a PDF.

I am able to generate the HTML with no code showing in the HTML.HtmlText field and also have enough correct code to "create file" (thanks to @Pstork1)  for one drive. Finally -- that much success!

However, I'll attach a print out of what "Create file" is returning. It is only the table headers - not all items from my collection. And it is putting the name of my source between the Title and the table which is unexpected.

I also tried the approach you suggested using a data table and got the same result. Again, my data table returns all items just as my HtmlText field in PowerApps does but when I create file, in PowerAutomate I get only the headers.

Any suggestions as to how to adapt my code in order to get all items? Also, I noted that someone in this forum asked about sort but I suppose that is something I'll have to finagle with in HTML -- correct? I'm also losing my sort during this translation.

Thanks to both of you for getting me this far!!!

Below is the HTML code I'm putting into the Expression in OneDrive Create file control in Power Automate.

concat('<!DOCTYPE html> <html> 

<body> 

<h2>colMovingToModern</h2> 

<Table style="width:100%"> 

<tr> 

<th>Question</th> <th>Answer</th>  </tr>' , 

concat(' DataTableAllItemsHTML.HtmlText','<tr><td>' , ' Title ' , '</td><td>' , ' Answer ' , '</td></tr>') , 

'</Table> 

</body> 

</html>')

________________________________________________________

I'm trying to get all items from a SharePoint list (enhanced rich text field) that is in a PowerApps (HTMLText control) converted to a PDF.

I have now figured out how to get all items into a table.  I have one thing left to do and that is get the one column of HTML text converted so I don't see code.  

My workflow is Get Items, Select, Create HTML Table, Compose...My formula in compose is...

replace(body('Create_HTML_table'),'<table>','<table border = "3" bgcolor = "87ceeb">') and here is my result...

Thank you.

[CW]

Hi Cindy.

I would definitely love to help you but am cancelling the subscription, partially due to what you said "4 days" to try and figure something out.

I found EVERYTHING I did in anything in their apps made me spend countless hours and days and much stress and consternation doing research to do something that for may accounts should already exist and do so in other company's apps only to find if you do get something right, it breaks something else or you can do it but only if it's on a Tuesday with a full moon. So it's adios time for Microsoft.

Best of luck!!

[Jael]

I have an other question, If I want to add all the values of any column of the HTML collection and show it on the body of this HMTL, How I can do it?