Sending E-Mail to each user with respective HTML table

[BurcuIsik]

Dear  all,

I need to send for each user the HTML Table but only with his data (referencing mail). When I test this flow I receive one HTML Table and one email which is correct. But the table includes all users. I want to create each HTML Table for each user.

What is not correct? How can I loop between users (mail for each user is in one table of the data)

[BurcuIsik]

@SudeepGhatakNZ 

[SudeepGhatakNZ]

You need to add a Filter action just above the Create HTML table action inside the loop. The filter will return the data for the specific user. Your email action should also be inside the loop, so that it sends individual emails to each user with their data. Makes sense?

[BurcuIsik]

Hi @SudeepGhatakNZ 

Many thanks for your reply. I included now the send an mail action and Pars JSON inside the loop. Thats my flow: 

I added also the Filter Array Action: I need to receive one mail for each user. But with this flow I receive all 18 Mails with the similar table and all users in the table.

Thats my action Filter Array:

From: 

@{outputs('All_data')?['body/firstTableRows']}

Filter Query:

item()?['vdwc_ProjC_Contacts']?[0]?['mail'] is equal to 

items('Apply_to_each')?['vdwc_ProjC_Contacts']?[0]?['mail']

I want to send for each mail which are in the column "vdwc_ProjC_Contacts'[mail]". It should filter for mail and send only respective table to each user.

For "apply to each" I am referencing to the 

@{outputs('All_data')?['body/firstTableRows']}

Should I provide only the e-mails als "Run a query against a data" and loop in the apply to each? 

Many thanks

Burcu

[SudeepGhatakNZ]

I suspect there is something wrong with your Filter Query. Are you crafting the expression yourself or selecting the field from dynamic selector?

Are you able to provide the JSON data returned by the Power BI action? I can give it a go.

[BurcuIsik]

Hi @SudeepGhatakNZ 
Thanks for your reply. I am crafting the expression myself. Not selecting it from dynamic selector. 
Here is the JSON Output of "Filter Array Action" the data are just examples:

{

    "body": [

        {

            "t_Signatures_on_Documents[Responsible]": "Person1",

            "t_Signatures_on_Documents[CBU]": "XY",

            "t_Signatures_on_Documents[Decision Answer Type]": "",

            "t_Signatures_on_Documents[Document Status]": "Release process",

            "t_Signatures_on_Documents[PlanName]": "",

            "t_Signatures_on_Documents[Position]": "Project Leader",

            "t_Signatures_on_Documents[Project Name]": "Name1",

            "t_Signatures_on_Documents[Project Leader]": "Projectleader1",

            "t_Signatures_on_Documents[Statement]": "",

            "t_Signatures_on_Documents[Title]": "Title1",

            "t_Signatures_on_Documents[Document Type]": "Protokoll",

            "t_Signatures_on_Documents[PlanId]": 0,

            "t_Signatures_on_Documents[Project No]": "ProjectNo1 « 

            "t_Signatures_on_Documents[Project Responsible OU]": "OU1",

            "t_Signatures_on_Documents[Project Status]": "Potential",

            "t_Signatures_on_Documents[Description]": "",

            "t_Signatures_on_Documents[Decision]": "",

            "t_Signatures_on_Documents[Date]": "",

            "t_Signatures_on_Documents[CreatedOn]": "…",

            "vdwc_ProjC_Contacts[mail]": "

            "[MinProject_Link]": XY

            "[MinDocument_Link]": XY

        },

        {

            "t_Signatures_on_Documents[Responsible]": "Person2",

            "t_Signatures_on_Documents[CBU]": "XY",

            "t_Signatures_on_Documents[Decision Answer Type]": "",

            "t_Signatures_on_Documents[Document Status]": "Release process",

            "t_Signatures_on_Documents[PlanName]": "",

            "t_Signatures_on_Documents[Position]": "PMO",

            "t_Signatures_on_Documents[Project Name]": "Name2",

            "t_Signatures_on_Documents[Project Leader]": "Projectleader2",

            "t_Signatures_on_Documents[Statement]": "",

            "t_Signatures_on_Documents[Title]": "Title2",

            "t_Signatures_on_Documents[Document Type]": "Protokoll",

            "t_Signatures_on_Documents[PlanId]": 0,

            "t_Signatures_on_Documents[Project No]": "ProjectNo2",

            "t_Signatures_on_Documents[Project Responsible OU]": "OU1",

            "t_Signatures_on_Documents[Project Status]": "Declined/Cancelled",

            "t_Signatures_on_Documents[Description]": "",

            "t_Signatures_on_Documents[Decision]": "",

            "t_Signatures_on_Documents[Date]": "",

            "t_Signatures_on_Documents[CreatedOn]": "",

            "vdwc_ProjC_Contacts[mail]": "",

            "[MinProject_Link]": "XY",

            "[MinDocument_Link]": "XY"

        },

        {

For "Person1" there are 17 entries and for "Person2" one entry. The mail is also available for the two person. Expectation is: that person 1 receives one overview table with all his data (one mail) and person 2 receives one overview table all of his data (one mail). In the JSON output it seems like splitted. But thats ok as soon as they get a summarized table for each user.

Many thanks

Burcu 

[BurcuIsik]

@SudeepGhatakNZ  I mean with entries: for Person 1 there are 17 times the same columns with different data and for Person 1 one time the same columns also different data

[SudeepGhatakNZ]

Try this flow

[BurcuIsik]

Hi @SudeepGhatakNZ 

Many thanks for providing a flow that I can also configure and test. I am not sure if I understood right: 

For Filter Array I just Compared 

item()?['vdwc_ProjC_Contacts']?[0]?['mail'] and the Output of Compose 1 (which has the outputs of "All data" Run a query against a dataset. I put ParseJSON before the Apply to each Loop. Is this the correct way?

Many thanks and Best regards

Burcu

[BurcuIsik]

@SudeepGhatakNZ Hi,

I have this flow now: 

I get an mail with one table and all users: I can show you the steps and value:

1. Recurrence
2. All data
3. Initialize variable : AllHTMLTables Type Array with Value []
5. Compose for checking: 

@{outputs('All_data')['body']}
6. Filter array 1: From: @{outputs('All_data')['body'].results} and Filter Query: 

@{item()?['vdwc_ProjC_Contacts[mail]']} is not equal to "null"
7. Apply to each loop: @{outputs('Filter_Array_1')['body']}
--> Create HTML Table: @{outputs('All_data')?['body/firstTableRows']}
--> Append to array variable 1: Name AllHTMlTables Value: @{outputs('Create_HTML_table')}
--> Parse JSON: (for getting only one table because all are similar): 

first(variables('AllHTMLTables'))
--> Send and email: Body: 

@{body('Parse_JSON')?['body']}

My aim is to split the table for each user, now I get one mail and one table but all users are in, I need for each user one table. Maybe you can help me. Many thanks for your support! Burcu

[BurcuIsik]

Hi Tom @trice602 

 I reproduced your solution from 

For Filter Array: 

It seems like there is sth wrong. I want to split the tables for each user: 

I got the data from run a query against a dataset(Table from Power BI where the email is a column  'vdwc_ProjC_Contacts'[mail]