Create an HTML from SQL, filter by area and send to resposible

[FHFN]

Hello,

I would like to create an HTML table taking data from SQL, then make a filter by area/region and send the list filtered to the responsible. How can I do this?

This is my SQL Data:

I would like to send to responsible1 only the lines that "Área" is AM1 and to responsbile2 the line which "Área" is AM2.

I tried to do like this:

But I had two problem:

1. It didnt filter with the "Área", and;
2. It executed 9 times (applied for each), so I will have 9 e-mails sent with the list and it should be two e-mail (AM1 and AM2).

Could you help me? 🙂

Thank you!

[grantjenkins]

I would get a unique list of Areas then loop over each Area and use a Filter array to just get the items for that Area, build up the HTML table from the output of the Filter array, then send the email using that HTML table.

I can mock up an example for you tomorrow if you like - just off to sleep now (past midnight for me).

[FHFN]

Yes! I would like to have it . 

how can a get this list of areas ?

[grantjenkins · Accepted solution]

I don't have a SQL connector so just added your raw JSON output into a Compose for this example.

Below is the full flow. I'll go into each of the actions.

I've used a Manual Trigger for this example.

I then have a Compose that contains the data that you would get back from your SQL table.

[
    {
        "Notification": 123456,
        "Area": "AM1",
        "E-mail": "responsible1@microsoft.com"
    },
    {
        "Notification": 3736453,
        "Area": "AM1",
        "E-mail": "responsible1@microsoft.com"
    },
    {
        "Notification": 9940596,
        "Area": "AM1",
        "E-mail": "responsible1@microsoft.com"
    },
    {
        "Notification": 1132435,
        "Area": "AM2",
        "E-mail": "responsible2@microsoft.com"
    },
    {
        "Notification": 223344,
        "Area": "AM2",
        "E-mail": "responsible2@microsoft.com"
    }
]

Next, I extract out just the Area and E-mail which we'll use to loop over. We don't want the Notification in this case since it's different for each object. The expressions used for Area and E-mail are below. Note that if your JSON data returned had an associated schema then you would likely just be able to select these properties rather than add the expressions.

item()['Area']
item()['E-mail']

The Select will give us objects that are duplicated so we want to use a union expression to only get back unique objects that we can use to loop over. The expression used here is:

union(body('Select'),body('Select'))

I then add some CSS that we can use to style the final HTML table. I add this here (outside the loop) so we can reuse it for each email sent. This will give the HTML table a colored header, left aligned, padding, width, etc. The CSS is below. You can change this to suit your style requirements.

<style>
table {
  border-collapse: collapse;
  max-width: 60%;
  width: 100%;
}
table td, table th {
  border: 1px solid #ddd;
  padding: 8px;
}
table th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #1C6EA4;
  color: white;
}
</style>

We then have our Apply to each that will loop over each of the objects in the Compose Areas.

The Filter array will bring back only items where the Area is equal to the current Area we are looping over. The expressions used here are below. Note that the input in my case comes from the first Compose - In your case it would be the raw JSON data that was returned from your SQL table.

item()?['Area']
items('Apply_to_each')?['Area']

Next, we Create HTML table passing in the output from the Filter array.

And finally, we Send an email with the CSS and HTML Table in the body of the email. The expressions used to get the Area and E-mail in this case are:

items('Apply_to_each')?['E-mail']
items('Apply_to_each')?['Area']

The email would look similar to that below:

Any issues please let me know.

[FHFN]

It worked!

Thank you very much!