Adding Dataverse Record Links in HTML Tables in Power Automate
I want to send a weekly summary of Open Opportunities & Activities to all the sales users to help them manage workload - nothing new or novel here (e.g. this post). I also needed to include a clickable link for each row in the emailed table. This included Opportunities, Activities and also the ‘Regarding’ record to the activity. So that the receiver of the email can quickly jump directly into the app.
In terms of creating HTML tables with CSS formatting in Power Automate - I always use this blog post (thanks Ryan!). Plus how to create a single dynamic URL for a dataverse record - I always use this blog post (thanks Linn!). This post will focus on creating dynamic URLs for different dataverse tables into a HTML table using Power Automate:
Opportunities
Activities (Task, Email, Appointment, SMS etc.)
Regarding (anything enabled for activities e.g. Contact, Account, Opportunity, Lead, User etc.)
Create this guy for later
Add this action at the top of your flow, we will use and reuse it later. You could declare a string variable also but I’m lazy and this allows me one or two less clicks.
Opportunities
1.1) Search for Opportunities
Using the Dataverse List Rows action and using the OData filter to retrieve only the relevant opportunities for each user.
1.2) Did you find any Opportunitties?
If the user is a goody two shoes (or just a really bad salesperson) and they don’t have any opportunities due to close, then there is no point sending an empty table. Using a Condition action we can use the following formula to see if any rows were returned from the search.
empty(body('OppDueThisMonth')?['value'])This returns either true or false, if its false then the search has successfully found at least one opportunity - proceed to table creation.
1.3) Create the HTML table
Use the Create HTML Table action, where the ‘From’ input is the search results from the Opportunity list rows action above. Select the custom option to map the columns yourself. We are going to make the title into a clickable link by combining the a href compose outputs from above, uriHost, Opportunity ID and Opportunity Name like so ->
<AHREF OUTPUTS="https://URI HOST/main.aspx?pagetype=entityrecord&etn=opportunity&id=OPPORTUNITY ID">OPPORTUNITY NAME</a>
AHREF OUTPUTS
See above “Create this guy for later“ - if you type this section ‘a href’ it seems to loose the space between a and href, which then breaks the link
URI HOST
Using uriHost ensures that when you move this flow between environments, it will always be creating the correct link without modifying any environment variables or the flow itself. Like so:
uriHost(item()?['@odata.id'])More info on uriHost -> this blog post
OPPORTUNITY ID & OPPORTUNITY NAME
Select them from the Dynamics Content list or use the below syntax as an expression:
item()?['opportunityid']item()?['name']The rest is just text and HTML tags - copy pasta (paste) away!
BONUS TIP
If you type directly into the Header column the name of the column, it will not allow you to add any spaces. BUT if you copy the column names from elsewhere and paste them in - you can have a space - yay!
1.4) Make the HTML table look pretty-ish
This step is entirely optional and just allows you to style the HTML table before dropping it in the email. More info on HTML table CSS styling -> this blog post.
1.5) Make the links clickable
If you skip this step - the HTML above will not be interpreted and instead your link will be surrounded by <a> tags and totally non-clickable. This step is ugly but its pure copy & paste, just replace CSS STYLED OUTPUTS with the outputs from the step above using the dynamics content builder.
replace(replace(replace(CSS STYLED OUTPUTS,'<a href="','<a href="'),'">','">'),'</a>','</a>')This converts the HTML tags into actual tags rather than HTML interpreted symbols
Finito - your opportunity links are ready to go. Next up the troublesome ones - ‘Activities’ and ‘Regarding’.
Activities & Regarding
Step 2.1) and 2.2) are the same as step 1.1) and 1.2) above, but with Activities instead of Opportunities
2.1) Search for Activities
Using the Dataverse List Rows action and using the OData filter to retrieve only the relevant openn Activities for each user.
2.2) Did you find any Activities?
No open activities = no table. Using a Condition action we can use the following formula to see if any rows were returned from the search.
empty(body('OpenActivities')?['value'])This returns either true or false, if its false then the search has successfully found at least one activity - proceed to table creation.
2.3) Create the HTML table - Activity link
Follow all the same steps for 1.3) above, just using Activity instead of Opportunity List Row results. We are going to make the Subject into a clickable link by combining the a href compose outputs from above, uriHost, Activity Type, Activity ID and Subject like so ->
<AHREF OUTPUTS="https://URI HOST/main.aspx?pagetype=entityrecord&etn=ACTIVITY TYPE&id=ACTIVITY ID">SUBJECT</a>
AHREF OUTPUTS & URI HOST
Same as 3) above
ACTIVITY TYPE
A new piece of Gymnastics for this post - and it’s a beauty! There are many different types on activity (Task, Email, Appointment, SMS etc.). This impacts what follows etn= in the link. Fortunately this is passed as part of the Activity List Rows results in activitytypecode@OData.Community.Display.V1.FormattedValue. Then we need to convert it to lowercase, using toLower() as the URL is case sensitive
e.g. Activity type=Task -> URL Text=task
toLower(item()?['activitytypecode@OData.Community.Display.V1.FormattedValue'])More stuff like this -> Power Automate Gymnastics Reference Guide
ACTIVITY ID & SUBJECT
Similar to 1.3) above - select them from the Dynamics Content list or use the below syntax as an expression:
item()?['activityid']item()?['subject']The rest is just text and HTML tags - copy pasta (paste) away!
2.4) Create the HTML table - Regarding link
Finally our good friend ‘Regarding’ - it is a similar premise to the activity URL that it has many different types. Se we use all of the stuff above in 2.3). However, ‘Regarding’ is an optional column for an activity record, not all Activities are linked to another table using ‘Regarding’. The function toLower() doesn’t handle null values, causing the rest of the flow to fail.
If ‘regarding’ is blank - do nothing. Otherwise combine all the pieces of the regarding URL using concat(). Anything that is text must be wrapped in single quotes (‘), each item of the string is connected with a comma (,).
if(empty(REGARDING, null,concat('<',outputs('ahref'),'="https://',URI HOST,'/main.aspx?pagetype=entityrecord&etn=',ACTIVTY TYPE,'&id=',ACTIVITY ID,'">',SUBJECT,'</a>'))Yikes!
2.5) Make the HTML table look pretty-ish
Optional HTML table CSS styling, same as 1.3) from above
2.6) Make the links clickable
If you skip this step - the HTML above will not be interpreted and instead your link will be surrounded by <a> tags and totally non-clickable. Same as 1.4) from above.
Create and Send the Weekly Summary Email
Finally we need to check how much of a good two shoes our user really is - if they have no pending Opportunities or Activities - they don’t need an email. Combine the expressions from the two ‘Did you find any Opportunities/Activities’ steps into a condition.
empty(body('OppDueThisMonth')?['value'])empty(body('OpenActivities')?['value'])
If they have any Activities and or Opportunities, we need to send them an email. Use the outputs from steps 1.5) and 2.6) where the links become clickable and the tables are ready to go to create the email. Then perform a bound action to send the email from dataverse.
Clickable links in HTML Table for Opportunities, Activities and Regarding
Congratulations for completing this gymnastics tutorial of HTML Tables and clickable links - I hope you survived! Questions comments etc. always welcome 😊
