Sending single mail after creating multiple list items in sharepoint from power automate.

[BKGOUD]

Hi All,
I want to create a workflow using Power automate and I am starting this workflow on item created. Now when I am adding multiple items at a time it is starting workflow for each item but I want to send all items details in single mail and also I want to know whether the mail triggered or not for that group of items.

I'm inserting common I'd and choice column(yes/no) for group of items.

Can someone please let me know how can I achieve this.

[EricRegnier · Accepted solution]

Hi @BKGOUD ,

It seems like your workflow needs to trigger on the parent of the items and not on the item itself. If you need to trigger on the item then it is the expected behavior to trigger for each. I don't know a lot about your scenario, but the approach I would recommend is something like this:

1. If you don't already have a status or a flag field on the parent item.. Say for this example, we have a status of "Pending" (default status) and "Complete". When all the items have been added to the parent, update the status to "Complete".
2. Update the workflow aka "flow" in Power Automate to trigger on update of the parent item and filter where status = "Complete". It would look something like this using the "Common Data Service (current environment)" connector which is the one I recommend:
   More info on Power Automate and CDS: https://docs.microsoft.com/en-us/power-automate/connection-cds
   
   
3. Include a "List records" Action/Step in your flow to retrieve your items and then, you can formulate the body with the list of items and include in your email step.
   

Hope this helps!

[BKGOUD]

Thanks for your help.
I'm using two lists here. First one is actual list, in which I'm inserting multiple items at a time with common id and flag value as No. Another list inserted by single item with common id(i.e from first list). Then I'm using when item is created.

[EricRegnier]

Thanks, so my approach would work. Trigger the email on the 2nd list (single item) with the common id, but you have to trigger only when all the items have been added, so you have to tell the 2nd list when it's done (e.g. change the status)

[v-xida-msft]

Hi @BKGOUD ,

Do you want to fire your flow only once time, and then send an email including multiple records details?

Based on the needs that you mentioned, I agree with your solution. I think two Lists could achieve your needs. On your side, you just need to set up a flow based on your second list using "When an item is created" trigger of SharePoint connector.

Then retrieve related records from your first List based on the common Id value (using "Get items" action). After that, you could convert the retrieved results into a HTML table, then embed the HTML Table into your email body.

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

Within the "Filter array" action, click "Edit in advanced mode", type the following expression:

@equals(item()?['Id'], triggerBody()?['Id'])

Note: The Id represents the common id value that you mentioned in your First List and Second List. On your side, you should replace it with actual field name from your First List and Second List. The format as below:

@equals(item()?['IdColumnInFirstList'], triggerBody()?['IdColumnInSecondList'])

Please take a try with above solution, check if the issue is solved.

Best regards,

[BKGOUD]

Hi @v-xida-msft ,

Thanks for your great support. 

I have tried your way it works fine me. In my first sharepoint list i have few columns. When im creating multiple items, except one column, remaining columns values for each item are similar. So when i'm choosing this columns in mail body it is going to For Each loop and sending multiple mails to me. 

Foe Ex: Column1    Column2    Column3

              x                    test         ABC

              y                    test         ABC 

i'm appending Column1 values to one string var and passing that variable in email body.

Also please let me know how would i know whether the email sent to for a group of items which are inserted with common id.

Mail Body Should be: 

Column1 : X,Y

Column2 : test
Column3 : ABC

[v-xida-msft · Accepted solution]

Hi @BKGOUD ,

Is the solution I provided above helpful in your scenario?

Within the "Send email" action, please do not reference the dynamic content value from the "Get items" action. The Output of the "Get items" action is a table, if you reference the Dynamic content value from the "Get items" action directly, actually, you are referencing a single column table value, so it would add a "Apply to each" action outside the "Send email" action automatically.

Please take a try with my solution, and convert the retrieved records as Html Table using "Create HTML Table" action. Then embed the Html Table dynamic content within the "Send Email" action.

For your second question, I think you could consider configure "Create HTML Table" as a custom HTML Table as below:

Specify the desired column names within the Header, and within the Value part, please use the following formula to reference column value from the current loop item:

item()?['Id']

item()?['Title']

item()?['Column3']

...

...

You could defined your expected HTML Table format within the "Create HTML Table" action, then put the Output dynamic content within the email body.

Best regards, 

[BKGOUD]

Hi @v-xida-msft,

Thanks for your help.

Is it possible to get the below output in mail body.

For Ex: Column1    Column2    Column3

              x                    test         ABC

              y                    test         ABC 

Mail Body Should be: 

Column1 : X,Y

Column2 : test
Column3 : ABC

[Samnang]

This is exactly what I am looking for but I still don't get the solution.
Can you please help with screenshot with SharepointList?
My SPL called AssetMovementLog, fields:

- SingleMoveID (Autuincrement by another flow), and everyitem will get one ID

- BulkMoveID (Generate by PowerApps) : a collection of items will have the same ID for bulkmovement

- NewOwner (Person) and the rest are text.
Everying work well including filtering for the unique email and html table but email keep sending equal to the number of newly created items.

Look like If I can solve it by filtering or grouping the triggering of the flow from the beginning. 

Any advice please.

[EricRegnier]

Hi @Samnang,

Would do you mean by "email keep sending equal to the number of newly created items."? Would it be possible to ellaborate on your issue? Thanks