Send one email with information on several rows and to different recipients

[Ksenia1]

Hi there,

In my App I am scheduling appointments. For each appointment a new row is added to the table "Appointment" with the status "new". Depending on the value in my column "Type" (being a Ref column to another table, not directly to my recipient list but the connection to my "Recipient" Table), this information shall be sent to a specific recipient.

After finishing scheduling, I want to press on an action button to trigger an automation to send out emails to these recipients regarding all appointments with status "new". Now the trick is, I want only one email per recipient to be sent out with their relevant information and not an email per row.

After the emails are sent out, the status for all affected columns shall change to "informed".

How do I do that?

[baba_sawane]

Hi Ksenia,

you can for example use a Start expression in your Email Body. 

For example : 

<<Start: TableAname[tableAkey]>> <<[tableAcol1]>> | <<[tableAcol2]>> ... <<End>>

You can use select() if you need filters (as the status). If you have a hard time configuring the expression, create a slice, filter it, then use <<Start:SliceName[...] >> instead.

I don't have much experience with start expression in emails, so I cannot help you further than this. I  believe you will need to format the response a little, with linebreaks or html to create a table.

Hope it help tho ! 

[Ksenia1]

Thank you for your suggestion. That would be a suitable solution if the email went out to only one addressee, which might not be case.

[baba_sawane]

If you wan't to show specific data per email, it'll need to send several emails, thus cannot be done in a single step. 

If the amount of users is not huge, it may be conveniant to just create several 'send email' steps 🤔

Good luck !

[AleksiAlkio · Accepted solution]

I would probably trigger this from the "Type" table as it sounds you have an email column or columns where that email should be sen. If you add an action button (inline), you can trigger the "Type" row one by one. If there isn't any new scheduled row, hide that action. Then you have rows here and there to trigger.

Then you can use the Start & End formula on your template to read those rows where the status is new. You can use for example <<Start: ORDERBY(SELECT([Related appointmens][Key],[Status]="New"),[Created],FALSE)>>

[Ksenia1]

Good point and I should have elaborated on that. My "Type" ref column is not directly connected to my "Recipient" table. It acutally needs to go through two other tables before getting there.

[AleksiAlkio]

But you have a relation between Type and Recipient, right?

[Ksenia1]

No direct relationship, no. [Type].[Vehicle].[Recipient] would be the expression to get the information being in Table "Appointment"

[AleksiAlkio]

It doesn't need more than that. You only need to read the recipient for the To: field.

[Ksenia1]

Thanks, it's almost complete! I just fail to get the Start & End formula together:

<<Start: ORDERBY(SELECT(Appointment[uid],AND([Status]="new", [Recipient] = [_THISROW].[Recipient])),[Scheduled for],FALSE)>><<End>>

...unfortunately doesn't give me a result in the email body.

Though, if I put the expression into a virtual column, it shows the result I want to have.

Of course I can take that virtual column to show my result in the email, but it will be great to know why the formula doesn't work.

[AleksiAlkio]

Before the <<End>> you need to add those columns that you want to show. If you add <<[uid]>> it will bring you list of ids.  You can add multiple columns in this way.. before the <<End>>.

[Ksenia1]

Thank you @AleksiAlkio for your help!

[AleksiAlkio]

You're welcome!