Quick Tip: Resolving Error Retrieving Teams via Fetchxml with List Records Action in CDS (Current Environment) Connector

I ran into a rather odd error recently when I was trying to use a FetchXML query to retrieve a list of Teams using the List Records action with the Common Data Service (Current Environment) Connector in a Power Automate flow, so to save you any future headaches I’ve outlined the issue below and the solution to the problem.

The Problem

In this instance I needed to retrieve a list of Teams related to Accounts that matched a specific N:N relationship. As this involved an N:N relationship, there are mulitple levels of link-entities and therefore we cannot use the normal OData filter query parameters.

The FetchXML query I created, using Jonas Rapp’s FetchXML Builder, was:

<fetch>
  <entity name="team" >
    <attribute name="teamid" />
    <link-entity name="account" from="accountid" to="regardingobjectid" >
      <link-entity name="cn_cn_practice_account" from="accountid" to="accountid" >
        <link-entity name="cn_practice" from="cn_practiceid" to="cn_practiceid" >
          <filter>
            <condition attribute="cn_name" operator="eq" value="Investment" />
          </filter>
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

I tested this query and it returned a valid list of results:

I then took the FetchXML query and added it to my List Records action and ran my Flow, so imagine my surprise when it failed!

The important part of the error details that were returned is below:

[Key property 'ownerid' of type 'Microsoft.Dynamics.CRM.team' is null. Key properties cannot have null values.]

The only problem with this error is that there is no “ownerid” property on a Team…

The SOLUTION

I was banging my head off my table trying to solve this issue and I was stumped, so I reached out to the community to see if anyone else had any ideas; fortunately for me Rob Dawson stepped up to the table with a genius idea; he suggested faking the ownerid as an alias for the administratorid attribute. This would literally never have occurred to me, so I’m extremely grateful for his help!

I added the new FetchXML query, with the ownerid alias for the administratorid attribute and, hey presto, it worked!

Conclusion

This was actually a relatively simple workaround for what appears to be a bug in the List Records step, but hopefully you’ll find it useful too! I’m always grateful to be part of a community that can help me when I’m stuck, and scenarios like this show how important it is to have a great network of peers to help you see solutions that wouldn’t otherwise occur to you

Converting Time zones with Power Automate

If you’ve ever had to convert a timezone with Power Automate then you’re hopefully aware that there is a “Convert Time Zone” action that is designed to make this process easier for you. If you’ve had to convert one time to multiple different time zones all at once then it very quickly becomes a pain to create a Flow as it requires a new action for each conversion.

Did you know that Microsoft supports 250 different time zones? If you wanted to see your local time represented in all of these different time zones all at once then it would be really inefficient to create a Flow for this, so how could we do it?

Note: if you’d like to see how to achieve the same functionality using a Select action instead of an array then read Pieter Veenstra’s excellent post on the topic.

The Solution

If you’ve read any of my recent posts, you’ll be aware that I’m a big fan of arrays, and this is a perfect opportunity to use one. In the Flow below I’m going to create an array of time zones using the data from the table on the Windows Default Time Zones page I linked above, and then I’ll convert the current UTC time to each of those to generate an output.

1. Trigger – for the purposes of this demo I’m using a manual trigger, but you could of course use any trigger that gives you a time and date you want to work with.

2. Initialize ConvertedTimes string variable – we’ll create an empty string variable called ConvertedTimes that we will use to capture the outputs when we loop through the time zones later in the Flow

3. Intialize TimezonesArray array variable – next we’ll create an array variable that we will populate with our array of time zones for conversion. I’ve included an extract of the array below:

[
  {
    "Timezone": "Afghanistan Standard Time",
    "UTC Offset": "(UTC+04:30)",
    "Description": "Kabul",
    "Country": "Afghanistan"
  },
  {
    "Timezone": "FLE Standard Time",
    "UTC Offset": "(UTC+02:00)",
    "Description": "Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius",
    "Country": "Åland Islands"
  },
  {
    "Timezone": "Central Europe Standard Time",
    "UTC Offset": "(UTC+01:00)",
    "Description": "Belgrade, Bratislava, Budapest, Ljubljana, Prague",
    "Country": "Albania"
  },
  {
    "Timezone": "W. Central Africa Standard Time",
    "UTC Offset": "(UTC+01:00)",
    "Description": "West Central Africa",
    "Country": "Algeria"
  }
]

The array contains information on the Time Zone including the name, it’s UTC offset, a description of some of the cities where it has effect, and the country of origin.

The full array is included in the Flow that you can download by clicking the link at the bottom of this post.

4. Parse JSON – next we run a Parse JSON step on the TimezonesArray variable so we can access the array item values for use in our Apply to Each loop. The schema that we use for the Parse JSON step is:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Timezone": {
                "type": "string"
            },
            "UTC Offset": {
                "type": "string"
            },
            "Description": {
                "type": "string"
            },
            "Country": {
                "type": "string"
            }
        },
        "required": [
            "Timezone",
            "UTC Offset",
            "Description",
            "Country"
        ]
    }
}

5. Apply to Each – we use the Apply to Each condition to loop through each of the time zones from our Time Zones Array. Inside the loop we only have one Append to String Variable action, and we’re using this to convert the time to the Time Zone for the array item and capture the output.

To convert the time zone we use the convertTimeZone expression. As you can see from the reference, the convertTimeZone expression uses the following format:

convertTimeZone(‘<timestamp>’, ‘<sourceTimeZone>’, ‘<destinationTimeZone>’, ‘<format>’?)

I’ve constructed the expression as follows:

convertTimeZone(utcnow(),'UTC',items('Apply_to_each')['Timezone'], 'dd MMMM yyyy hh:mm tt')
  1. timestamp – I’m using the utcNow() expression to get the current date and time, but of course you could pass in any value from your trigger to here
  2. sourceTimeZone – I’ve hardcoded this to be ‘UTC’ in my Flow, but you could get the current User’s timezone in your own scenario to set it dynamically, or set it to any other timezone
  3. destinationTimeZone – for the destination Time Zone I’m using the Time Zone from the current array item, so it will be set dynamically for each time zone
  4. format – I’ve created a custom date format to show me the day, month as a full word, full year, and then the time as a 12 hour display with an AM/PM designation

For the rest of the String I’m also including the Country, UTC Offset and Description from the array for each item, and then including a line break, so the output will look something like:

30 January 2020 06:34 AM Barbados (UTC-04:00) Georgetown, La Paz, Manaus, San Juan

6. Compose – The final step is just a compose step to let me see the output of the Apply to Each loop. Once you have all the times converted you could use them in whatever way you please

Based on the inputs above, the output from the Flow will look something like:

Conclusion

In this flow I wanted to demonstrate how we could take an array of timezone inputs and convert a specified time to all of them in a quick and efficient manner; in my demo I’ve used an array of 250 time zones but the good thing with an approach like this is that it doesn’t matter how many values are in your array.

I realise it’s unlikely that you’d want to convert a time to 250 different time zone values (and I’m also aware that there is crossover between time zones, so there are multiple time zones sharing the same UTC offset), but I’ve worked in organisations that have worldwide operations so it’s always useful to be able to convert deadlines to local times in each office. Similarly, I know that there are lots of virtual events that have attendees from all over the world so it is handy to be able to see the local time for each attendee.

I think this flow could be expanded even further to make it more dynamic, but I hope it has provided some useful food for thought. If you’d like to download it and have a play with it yourself then please click here

Power Automate HTML Table Styling

If you’ve been using Power Automate flows to send notification emails to people in your organisation then there’s a fair chance you’ve run into a requirement to send a table of results in the email. This is a regular occurence in my organisation, and I’ve run into some limitations with the standard “Create HTML Table” action so in this blog I’ll explore them and demonstrate how I’ve managed to work around them.

The Situation

For the purposes of demonstrating this Flow I’m going to use the scenario where I’ve created a Quote for my customer and I want to email it to them using a Power Automate flow.

The Solution

There is an action in Power Automate called “Create HTML Table” and it will probably suffice for most usage scenarios. In the extract from my Flow below I’ve covered the key steps to create a table from a list of CDS records:

1. List CDS records – we use the List Records action to retrieve a list of records from CDS. Similarly, you could retrieve records from SharePoint (or any other data source).

2. Select – we use the Select action to specify matching key:value pairs from the returned records from the List CDS records step above. The easiest way to think about this is that the first column is the “Headers” for the table, whilst the dynamic values in the second column are the “rows” for the table

3. Create HTML Table – the final step to create the HTML table is to use the Create HTML Table action. As we’ve specified the Columns in the Select action above, we set the Columns to automatic.

Now that we have a HTML table generated, we can insert it into an email, or a Teams message, or create a PDF with it, or any other approriate action you can think of.

For the purposes of this demo I’m going to send it in an email, and I’ll include some of the details from the parent Quote record. The Send an Email action looks like this:

The output from this action looks like this:

As you can see, it works but the styling leaves a bit to be desired so let’s see if we can pretty it up a bit.

INjecting CSS Styling

One way we can do some styling is to add a CSS stylesheet to our HTML. We can do this by prefixing the output from the Create HTML Table step with some additional HTML using a Compose step. You can use internal CSS in HTML by using the <style> element, and we can do that in the compose step like this:

The HTML style element with the CSS that i used is below if you want to copy and paste it:

<style>
table {
  border: 1px solid #1C6EA4;
  background-color: #EEEEEE;
  width: 100%;
  text-align: left;
  border-collapse: collapse;
}
table td, table th {
  border: 1px solid #AAAAAA;
  padding: 3px 2px;
}
table tbody td {
  font-size: 13px;
}
table thead {
  background: #1C6EA4;
  border-bottom: 2px solid #444444;
}
table thead th {
  font-size: 15px;
  font-weight: bold;
  color: #FFFFFF;
  border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
  border-left: none;
}
</style>

I used the table designer at http://divtable.com/table-styler/ to give me inspiration for the CSS, and the output with the styling added looks like this:

I think this looks much better, it’s more readable and a bit friendlier, but I still think it can be improved on. One of the things I experimented with was alternate colours for rows, but unfortunately Outlook seems to ignore this CSS styling. In order to get alternate rows, and to include the information at the bottom the email in the table, we’re going to have to get creative!

Advanced StylinG

The good news is that we can achieve some quite complicated styling using a mix of HTML and CSS. The bad news is that it’s slightly more complex and therefore we can’t use the simple “Create HTML Table” action. The output from my advanced styling looks like this:

I think this is a huge improvement, and looks really professional. The Flow to create this table is slightly longer, but I think it is pretty straightforward to follow. I’ve outlined it below and I’ll go through each step in turn

1. Trigger – in this Flow I’m using “when a record is updated”, but of course you can use any trigger

2. Initialize RowNumber Integer Variable – the first action is to create a new Integer variable called RowNumber. We’ll use this as part of our Apply to Each loop later in the Flow, but for the time being we’ll default it to 0

3. Initialize TableHTML String Variable – the next action is to create a new String variable called TableHTML. In this we’re going to set the Table Headers. We can also use some inline CSS style elements to set the table style and to set the table header style. We can also set the width of each column.

4. List Records – in this scenario I want to send a quote in an email to a client, so I’m going to get the related Quote Lines. In your own scenario you may wish to retrieve different records, or items from a SharePoint list

5. Apply to Each – next we’re going to loop through the items that are returned from the List step above. There are two actions inside the loop

5A. Increment RowNumber by 1 – in this action we’ll increase the RowNumber variable by 1 so we can identify which loop we’re on, and we’ll use this in the next step to identify if we’re in an even or odd row

5B. Append Product Row to TableHTML – in this step we’re using an Append to String Variable action to add more HTML table rows to the TableHTML. The HTML code we’re using is below:

        <tr style="background-color:@{if(equals(mod(variables('RowNumber'),2),0),'white','#e1e1e1')};">
            <td>@{items('Apply_to_each')?['productdescription']}</td>
            <td>@{formatNumber(items('Apply_to_each')?['priceperunit'],'C','en-GB')}</td>
            <td>@{items('Apply_to_each')?['quantity']}</td>
            <td>@{if(equals(null,items('Apply_to_each')?['manualdiscountamount']),formatnumber(0,'C','en-GB'),formatnumber(items('Apply_to_each')?['manualdiscountamount'],'C','en-GB'))}</td>
            <td>@{formatnumber(items('Apply_to_each')?['extendedamount'],'C','en-GB')}</td>
        </tr>

There are some key elements to consider here:

  1. First, we’re setting the background colour of the row by using some inline CSS within the <tr> element. We have an IF condition in here as follows
    “if(equals(mod(variables(‘RowNumber’),2),0),’#ffffff’,’#e1e1e1′)”
    This works by using the MOD expression to check if the RowNumber variable is divisible by 2. The MOD expression returns the remainder from dividing two numbers. If this equals zero we know we’re on an even row, while if it returns any value other than zero we’re on an odd row. We can then use the IFTRUE and IFFALSE to set alternating row colours.
  2. We use the FormatNumber expression to set the currency values to the appropriate currency, e.g.
    “formatNumber(items(‘Apply_to_each’)?[‘priceperunit’],’C’,’en-GB’)”
  3. We won’t have a discount applied to each line item, so we need to check if the returned value is null and, if so, set it to 0, i.e.
    “if(equals(null,items(‘Apply_to_each’)?[‘manualdiscountamount’]),formatnumber(0,’C’,’en-GB’),formatnumber(items(‘Apply_to_each’)?[‘manualdiscountamount’],’C’,’en-GB’))”

6. Append Footer to TableHTML – in the first two examples I demonstrated above the values from the parent record weren’t included in the table, which looked a little bit messy. In order to include them we can append some more HTML to the TableHTML variable. We also know that we want the rows containing subtotals and totals to be bold and to have a different colour background, so we can include some more inline CSS styling for these.

The table we have created above has 5 columns but the final elements in the footer only use two columns, so we can also use a HTML colspan element to make the row labels span 4 columns, thereby ensuring the row values are in column 5

If you want to copy the HTML above, then it is below:

<tr  style="text-align:right; background:#c5c5c5; padding:3px">
    <td colspan="4" ><strong>Detail Amount:</strong></td>
    <td><strong>  @{formatnumber(triggerOutputs()?['body/totallineitemamount'],'C','en-GB')}  </strong></td>
</tr>
<tr style="text-align:right; background:#ffffff;padding:3px">
    <td colspan="4" >Less Discount %:</td>
    <td>@{formatnumber(div(triggerOutputs()?['body/discountpercentage'],100),'p','en-gb')} </td>
</tr>
<tr style="text-align:right; background:#ffffff;padding:3px">
    <td colspan="4">Less Discount:</td>
    <td>@{if(equals(null,triggerOutputs()?['body/discountamount']),formatnumber(0,'c','en-GB'),formatnumber(triggerOutputs()?['body/discountamount'],'C','en-GB'))} </td>
</tr>
<tr  style="text-align:right; background:#c5c5c5; padding:3px">
    <td colspan="4">
        <strong>Pre-Freight Amount:</strong></td>
    <td><strong>@{formatnumber(triggerOutputs()?['body/totalamountlessfreight'],'C','en-GB')}  </strong></td>
</tr>
<tr style="text-align:right; background:#ffffff;padding:3px">
    <td colspan="4" > Freight Amount:</td>
    <td>@{if(equals(null,triggerOutputs()?['body/freightamount']),formatnumber(0,'C','en-GB'),formatnumber(triggerOutputs()?['body/freightamount'],'C','en-GB'))} </td>
</tr>
<tr style="text-align:right; background:#ffffff;padding:3px">
    <td colspan="4"> Tax Amount:</td>
    <td>@{formatnumber(triggerOutputs()?['body/totaltax'],'C','en-GB')} </td>
</tr>
<tr  style="text-align:right; background:#c5c5c5; padding:3px">
    <td colspan="4"><strong>Total Amount:</strong></td>
    <td><strong>@{formatnumber(triggerOutputs()?['body/totalamount'],'C','en-GB')}  </strong></td>
</tr>
</tbody>
</table>
<br/>

7. Send an Email – we can use the Send an Email action to send the HTML table we created in an email, or we can use the Teams – Post a Message [STEP 8 above] to post the HTML table in a Teams channel.

Conclusion

This post demonstrates 3 different ways to create and display HTML tables using Power Automate flows:

  1. Simple – using a combination of the Select and Create HTML Table actions
  2. Intermediate – using a combination of the Select and Create HTML Table actions, and adding internal CSS using a Compose action
  3. Advanced – using manually created HTML tables with inline CSS styling, colspan elements and Flow expressions to add formatting

I found this an interesting challenge, and I think it demonstrates how we can get creative with Power Automate to achieve advanced functionality. If you found it useful, or if you have any questions or comments then please let me know!

If you’d like to learn more about HTML tables then I’d highly recommend reading the W3 Schools tutorials.

Calculate Working Days in a time period using Power Automate

I’ve been working on a Flow recently that requires either the total amount of days between two dates, or the amount of working days (i.e. total days minus weekends and holidays), and the lack of a proper DateDiff expression in Power Automate has been a bit of a headache for me to work around.

If you’ve ever worked with Excel then you’ll know that the networkdays formula would be perfect for this scenario, so I wanted to see if I could replicate this in a Flow.

The Setup

The networkdays formula takes three parameters:

  1. Start Date
  2. End Date
  3. Holidays (an array containing a list of holiday dates)

We need to include the same parameters in our Flow, so you’ll need to ensure that your trigger contains this information, or that you can gather it within your Flow.

The Solution

The Flow that I created is below, and I’ll go through each step in turn to explain what I’ve done

1. Trigger – For this Flow I’m using a manual trigger that takes the three parameters I outlined above. Of course you could also get this to work from a CDS record or any other trigger. The Holidays are required to be input in a “yyyy-MM-dd” format, separated by a comma. I’ve also experimented with retrieving the holidays from the Gov.UK Bank Holidays API, the important thing is to be able to construct an array of Holiday days.

2. Initialize HolidaysArray – The first action is to take the comma-separated list of Holidays from the trigger and convert it to an array so we can use it later in the Flow. We do this using the Split expression; this will convert a comma-separated list such as 2019-12-25,2019-12-26,2020-01-01,2020-01-02,2020-04-10 into an array that looks like:

[
"2019-12-25",
"2019-12-26",
"2020-01-01",
"2020-01-02",
"2020-04-10"
]

3. Compose StartDateTicks – in Excel dates are stored as sequential serial numbers starting with 1 for January 1 1900. Unfortunately, Power Automate doesn’t do the same thing, so we need to be slightly more creative in order to calculate the number of whole days between two dates; the ticks function gives the nanosecond interval for a specified datetime. For example, the date 10 December 2019 has a tick value of 637115328000000000. We use the compose action to convert the input start date to it’s representative tick value

4. Compose EndDateTicks – as above, use another Compose action to convert the input end date to it’s representative tick value

5. Initialize FullDays integer Variable – Now that we have the tick values for the start date and the end date, we can subtract the start date from the end date then divide the result 864000000000 to convert the result to the number of days. We also add 1 to this result to give us the total number of whole days between the start date and end date, inclusive of the end date. The expression we use is

add(div(sub(outputs('EndDateTicks'),outputs('StartDateTicks')),864000000000),1)

Note: The next 7 steps in the Flow (Step 6 – Step 12) will help us to calculate the number of working days in the total number of days we had returned above.

6. Initialize WorkDays Integer Variable – now that we have the Full Days calculated, we’ll initialize another Integer Variable to calculate the Working Days. When we initialize it we’ll set the default value to the FullDays value and we’ll decrement it in subsequent steps

7. Decrement WorkDays by 2 for each Full Week – we know that in any given 7 day period there will be two weekend days, so we need to remove these from the total number of days returned above. The way we do this is to divide the total number of days value by 7, then multiply it by 2, and then subtract this result from the total number of days.

For example, if you had 14 days total, then you could divide this by 7 (giving 2), then multiply it by 2 (giving 4), and subtract this from 14 (giving 10). This tells you that in a 14 day period there are 10 working days and 4 weekend days. The expression we use to calculate this is:

mul(div(variables('Days'),7),2))

8. Decrement WorkDays by 1 if Start and End are the same day on a weekend – we need to account for situations in which the Start Day and End Day are the same day of the week (i.e. it starts and ends on Sunday). If it is midweek then we don’t need to do anything, but if it’s a weekend then we need to ensure we’re decrementing the WorkDays value by 1. The reason for this is that if we started and finished on a Sunday, this would be 8 days in total. The calculation at step 7 would remove 2 days for each full week, but we’d also need to ensure we’re removing an additional 1 day to account for the weekend.

if(equals(dayofweek(triggerBody()['date']),dayofweek(triggerBody()['date_1'])),if(or(equals(dayofweek(triggerBody()['date']),0),equals(dayofweek(triggerBody()['date']),6)),1,0),0)

9. Decrement WorkDays by 2 if Start Sat & End Midweek – in this step we’re checking if the time period we’ve selected for the Flow starts on a Saturday and ends on a midweek day. If so, we want to decrement the WorkDays variable by 2

if(and(equals(dayofweek(triggerBody()['date']),6),not(or(equals(dayofweek(triggerBody()['date_1']),0),equals(dayofweek(triggerBody()['date_1']),6)))),2,0)

10. Decrement WorkDays by 1 if Start Sun & End Midweek – this step is almost the exact same as above, but this time we’re checking if the time period starts on a Sunday and ends on a midweek day. If so, we want to Decrement the WorkDays variable by 1

if(and(equals(dayofweek(triggerBody()['date']),0),not(or(equals(dayofweek(triggerBody()['date_1']),0),equals(dayofweek(triggerBody()['date_1']),6)))),1,0)

11. Decrement WorkDays by 1 if Start Midweek & End Sat – this action is the inverse of Step 9; we’re checking to see if the time period selected starts on a midweek day and ends on a Saturday. If so, we want to Decrement the WorkDays variable by 1

if(and(not(or(equals(dayofweek(triggerBody()['date']),0),equals(dayofweek(triggerBody()['date']),6))),equals(dayofweek(triggerBody()['date_1']),6)),1,0)

12. Decrement WorkDays by 2 if Start Midweek & End Sun – as above, this is the inverse of Step 10; we’re checking to see if the time period selected starts on a midweek day and ends on a Sunday. If so, we want to Decrement the WorkDays variable by 2

if(and(not(or(equals(dayofweek(triggerBody()['date']),0),equals(dayofweek(triggerBody()['date']),6))),equals(dayofweek(triggerBody()['date_1']),0)),2,0)

NOTE: it would probably make more sense to have the decrement actions above contained within a Switch action to make your Flow more efficient

13. Initialize HolidaystoRemove – for this step we’re initializing another integer variable that we’ll use in the Condition in Step 14 to count the number of Holidays that occur in the selected time period

14. Check if the Date Range contains any of the holidays – for this step we’re going to iterate through the Holidays array we created in step 2 and check if that date is in between the Start Date and End Date of the time period we selected, using an “is greater than or equal to” and “is less than or equal to” condition respectively. If the holiday is in the time period, we’ll increment the HolidaystoRemove variable by 1.

15. Decrement WorkDays by HolidaystoRemove – Once we’ve iterated through all the holidays, we then Decement the WorkDays variable by the HolidaystoRemove variable, and this will give us our final amount of Working Days in our selected Time Period

16. Response – the final step in my Flow is a Response action. I’m going to be calling this Flow as a child flow from another one, so I need the response to return the information. In my response I have 3 outputs:

  1. Total Days
  2. Working Days
  3. Holidays Removed

Conclusions

This Flow was a bit more frustrating than I’d expected to try and pull together, and it involves some hefty expressions, but I’ve tested it pretty thoroughly and its worked in all the scenarios I’ve thrown at it, but I’d love for other people to do more testing.

I would love to get your feedback on whether you think this is useful, or if you think I’ve missed anything or made any mistakes!

If you’d like to download a copy of this Flow please click here