Help - Pulling data from multiple Sharepoint lists and generating email

[Jmorgan83]

I am a complete newbie to creating flows and I am in need of creating a flow that will pull data from 7 different Sharepoint lists and then allow me to use that data into a email that will send daily.   I currently have my flow set up that starts with the recurrence.  The flow will generate every morning at a certain time and run. Then it configures my time zone run date.   And then I currently have 7 parallel branches that will pull the different lists I need to pull data from for my recap email.  What I am having trouble with is, I have created an initialize variable and compose for each "get Items" branch.  When I apply to the send email, it will allow me to format my email and pull each piece of data from each list I need...  But when the flow runs... it is not pulling the most recent data from each list, and I am getting 4 identical emails every time the flow runs.  I think that is because there are 4 different "Apply to each" on the send email.   

How do I make it only pull the data from the same date for each list and only send me one email????

TIA

[trice602]

Hi @Jmorgan83 ,

I've love to look at this.  Can you share your flow with the actions expanded and your last run output please?

[David_MA]

Not sure if this is what you are looking for, but what you are asking sounds like something I am doing. Since you want to send one e-mail, you will manually generate an HTML table from your different lists and then convert it to a pdf file since there are limits to how many rows you can send in an html table in an e-mail. Here is the basics of my flow:

You can ignore the child flow as it is not essential to what you need. The two initialize variables are:

1. Row, which is of type array with a default value of []
2. Count, which is an integer with a default value of 0, which I use to return the total number of items from all of the lists.

The next eight scope actions all do the same thing except they get data from different lists:

I use a filter query action in the get items action to return only what I need, which for this report is pending items. This is an example of the filter query:

Approval_x0020_Status1 eq 'Pending'

I use a length expression to make sure that the get items action found at leats one item, which uses this expression:

length(outputs('Get_items_General_Legal_Requests')?['body/value'])

Then we create the rows for our table:

• The first append to array action creates a header row to define the list the rows that follow come from.
• Then you process each of the items found from the get items action in an Apply to each and append the data you want with another append to arrary action on your rows array.
• And then increment the count variable by 1.

The final Scope action pulls everything together:

The first compose action creates the able header and includes the CSS for styling:

<style>
h2 { font-family: Arial, Helvetica, sans-serif; text-shadow: 2px 2px 2px #CCCCCC; font-size: 22px; color: #0038A6;  }
Table { font-family: Arial, Helvetica, sans-serif; background-color: ; #ffffff;
  border-collapse: collapse; }
Table td, Table th { border: 1px solid #000000; padding: 6px; }
Table th { font-size: 15px; font-weight: bold; text-align: center;
  background-color: #bad8f2; color: #000000; }
body { margin: 6px; }
</style>
<h2>@{variables('Count')} Pending Legal Requests as of @{formatDateTime(utcNow(),'D')}:</h2>
<table>
  <tr>
    <th>Form</th>
    <th>Request #</th>
    <th>Submitted</th>
    <th>Assigned to</th>
  </tr>

The second compose action closes out the table and has a couple of paragraphs of explanatory text, which I will not show.

</table>
<p>Explantion information</p>
<p>Contact information</p>

The final compose action joins all of the rows in the array to compress the HTML:

join(variables('Row'),' ')

 The Create HTML file puts everything together:

The order of the Outputs is Compose Header, Compose Rows, Compose Footer.

I think the reset of the actions are self-explanatory. But if you have questions on what they do, just ask.

[Jmorgan83]

I didn't know that you could only pull so much data for rows in HTML.

I am generating a Shift Recap.   SO my 7 lists are one for each shift, and each has 11 columns of data that I am going to pull into a recap email.   I basically want to pull the name of the person from each shift in a table at the top of the email and then I want a table that will be for each of those 7 shifts saying who the person was, their shift and then pull all of the data for each column on their list.  And then repeat.  The attached image is what I am looking to have outsource in the HTML.  I have the HTML created, but I need to pull the data and I guess its too much data to pull.   I dont want to export to a pdf.  I guess I need to make the flow generate each table and then combine those in the HTML code to generate the email?

[David_MA]

Yes, you may need to generate a couple of tables based on your image. I cannot find anything documented with Outlook and a limit on rows, but I was having problems when just putting the HTML into Outlook and sending it. Outlook is picky about HTML, so it may have been something in my coding rather than a limitation. If you don't have any issues with putting the HTML table in Outlook, then at least you'll have the PDF idea as an option to fall back on.

[Jmorgan83]

So how do I best create the table that I want to create?   When I try to create a HTML table in the flow, it only lets me create columns, I want my 11 columns from the sharepoint to be listed as 11 rows.   From the photo attached, I can only make columns?

[Jmorgan83]

SOOO I have great news!!!!  I managed to get the table to work out!   I went a completely different route and here is my flow.

But I am now having a different issue....  Now when my data exports into the table in the email.... How do I get that data in the one column to be all different paragraphs?  Is it even possible since that data is being pulled from the sharepoint?   I should have 4 separate lines, in that one cell.

[trice602]

That's great you figured it out!  Great job!  I will have to think about this and see what can be done to the break lines.

[Jmorgan83]

A peer of mine mentioned parse the data.  How do I do that???