HTML tables with lookup names not GUIDs in Power Automate
I want to send a daily email summary of members in Dynamics 365 who have updated their details which impact how the clubs they are member of are invoiced. In a life before power automate our humble classic workflows couldn’t handle such a simple thing, but of course you can with Power Automate! There is heaps of handy blogs out there all about HTML table formatting so I won’t re-boil the ocean on that one and leave you with a link to a wonderful article by Ryan Maclean ‘cause he does it so much better than me [1]. Things were going swimmingly until I hit the dreaded lookup fields which left the email looking rather non-human readable.
This shouldn’t be rocket science but it left me scratching my head (googling) in search of a clean and simple way to get the ‘Name’ rather than the GUID. The answer lies in using an Array a that word means JSON and is prone to scaring me away. Firstly create a scheduled flow and define your schedule then we begin.
Initialise an Array and give it a name, you might even think of something more creative than ‘Array’! Then list the records eligible for the daily summary using the filtering method of your choice.
If there have been no eligible changes, we don’t want to send an empty email. So this condition checks whether the List records search above returned any results (TRUE). If true, you could perhaps send an email to say ‘no updates today’ or in my case just do nothing - no news is good news.
empty(body('List_Printed_subscriptions_changed_last_24_hours')?['value'])
If the statement is false then there is at least one result returned from the search - we need to send a summary table. Loop through each eligible record to get the lookup values and capture them in the Array, using the Dynamic content available
{
"Name": FULL NAME,
"ID": ID,
"Club": Club,
"URL":
}
Now the array is filled with all the juicy data we need, its super easy to create the HTML table using automatic mapping. Then we inject some CSS styling to it (check out [1] for details on that). Finally, we shoot an email containing the CSS formatted HTML table contents.
Beautiful!
Finally I will leave you with a snapshot of the full flow to show how it all fits together - it really is that simple! Happy HTML tabel-ing.
[1] Power Automate HTML table styling - https://ryanmaclean365.com/2020/01/29/power-automate-html-table-styling/