How to convert UTC into Your Local Timezone in Canvas Apps

One of the technical challenges we have in the UK is that for half of the year we are in the UTC time zone that we’re all familiar with, and the other half we’re in British Summer Time (BST). Those lucky few that keep the same time zone all year don’t know how easy they have it!

It can be quite confusing, as some digital solutions (including Dynamics 365) host UTC and our local time as separate time zones but call both UTC, but others don’t always make this distinction, and you may have seen data that you just submitted appear with a date stamp of ‘1 hour ago’. This is easily done if you’re non-technical. Why would you ever consider having to change your time zone if you can already see ‘UTC’ in the dropdown?

This doesn’t have a major material impact until you’re working with date values without times, particularly if the solution you’re using only allows you to control the date entry from the front end, and not the time entry. The difficulty we face in this scenario is that an application could even show yesterday’s date!

Yesterday’s date? Are you sure?

Well submitting data at 2pm during your workday doesn’t cause too much of an issue, you might see data entry from 1pm instead. But what if you submit a ‘date only’ value, or, (hopefully you’re not working at this time) but at some time between 00:00 and 00:59?! In this instance, the application can often confuse the user and present the data back as yesterday’s date instead!

How do I prevent this?

Fortunately we don’t have any problems submitting data as these will always be submitted in UTC and convert appropriately.

The issue we face occurs when we are trying to retrieve data from a data source, where (for example) the database stores the date as 30/07/22 00:00:00, but our Canvas App reads this from the data source as 29/07/22 23:00:00 due to the database storing our submitted date in UTC.

I discovered this when using the Outlook Tasks Connector to pull in today’s To Do items into a Collection, rather than using the Today() function to compare dates.

Check out the example below:

DateAdd(DateTimeValue(DueDateTime.DateTime),-TimeZoneOffset(),Minutes)) = Today()

“Add the negative of my local timezone offset in minutes to the local date, and then show me all of the To Do Items where the DueDateTime.DateTime value is equal to the newly calculated date.”

Note: For this particular connector I needed to explicitly specify DateTimeValue as the format, but you don’t need to do this for all Connectors.

That’s all. Fortunately Power Fx allows us to grab the time zone offset for the time zone I am currently in, but we must be aware that this value is a negative, and therefore we need to negate the negative in order to add the correct number of minutes. I’ll be using this in every Canvas App I build now, particularly as I work in an organisation that spans multiple time zones!

Modify An Owner’s Connection References in Power Automate

No matter how amazing an organisation may be, unfortunately there will always be the possibility of someone leaving the organisation. When it comes to Power Automate, this means that you can be stuck with the original Owner of the Cloud Flow having left the organisation, where Connection References eventually error, and lead to an automated process failing which may be critical to business systems.

Referenced Forever?!

At present, there is no way for you to delete the original Owner of a Cloud Flow even if you manage to establish yourself as a Co-Owner. Connection References cannot necessarily be deleted either!

Workaround

In this example I’ll use the Centre of Excellence Starter Kit environment that I inherited from a previous colleague, and for demo purposes I’m going to modify the Dataverse Legacy Connector as it is currently in the correct state to demo.

Let’s make our Connection References valid, and eventually fix the Cloud Flow by following the below steps for each Connection Reference:

A screenshot of a Power Platform environment, looking at Connection References within the Default Solution.
Your screen should look similar to the above screenshot at this stage.
  • Open the Connection Reference that you wish to modify. Hint: Filter by Owner to get to your reference quickly if you have many to search through.
  • Click on Edit.
  • Select the Dropdown with the existing Connection and re-point it to an existing valid Connection or create a new one.
  • Repeat those same steps for every invalid Connection Reference.

But Wait!…

Now there are some caveats to this approach which you should consider during this process:

  1. This does not remove the Owner from the flow, but it stops the Owner’s account from being used as a Connection Reference when using a data source in your Cloud flow.
  2. In my instructions I asked you to navigate to the Default Solution. For the consultants among us, with great power comes great responsibility. Be careful here, and use the original Unmanaged solution if you can. In most circumstances, you will be presented with Managed solutions and will be forced to use the Default solution.
  3. To ensure that you can see the full scope of your solution and automations, you ideally need to be a System Administrator to complete this exercise.

Tip: Find Hidden Personal Microsoft To Do Capability in Power Automate

Way before Microsoft had a fully-fledged Outlook and Microsoft To Do app for iOS and Android, there were two apps that tightly integrated with each other to form an absolute machine in productivity – Sunrise and Wunderlist.

Tasks would show as ‘All Day’ items at the top of your calendar, with ticks next to each one completed as a frequent reminder of progress as you check your calendar for the seventeenth time during the working day.

A digitally produced image of Sunrise Calendar with Wunderlist Integration on an iPad
Sunrise Calendar with Wunderlist Integration on an iPad

Microsoft bought both of those products and that’s how we arrived at Microsoft’s eventual Outlook Tasks replacement and the ability to add third party calendars to our Outlook with ease, but not all features were migrated easily, and I have always wanted a replacement, but never found one.

By using the Power Platform, we now have the ability to bring together the capabilities of personal Microsoft To Do with Outlook, and any other service is hidden within the Outlook Tasks Connector within Power Automate!

Simply search for the Outlook Tasks when creating a flow, and once you’ve chosen your trigger or action, you’ll be able to see your tasks.

A screenshot showing the selection of a Microsoft To Do list in Power Automate via the Outlook Tasks Connector
Selecting a Microsoft To Do list in Power Automate via the Outlook Tasks Connector

I’m unsure on when exactly this feature became available for personal accounts, but Microsoft To Do with business accounts has been available for a while under it’s own Connector.

What’s the catch?

As with a lot of early Connectors that have since had iterative updates in Power Automate, not all actions are built consistently.

A screenshot showing a list of some of the available Actions within the Outlook Tasks Connector.
A list of some of the available Actions within the Outlook Tasks Connector.

We also have to bear in mind that Microsoft To Do and Outlook Tasks are built on entirely different architectures where functionality has merged over the years, and therefore there are several fields available that may not directly align to what you expect, particularly when trying to use the data you’ve received in another Connector.

Having said all of the above, once you have established the correct Dynamic Values and the correct Actions to use, the connector is extremely reliable and hasn’t failed me yet in any working examples.

References

Microsoft Docs: Outlook Tasks Connector

Microsoft Docs: Microsoft To Do (Business) Connector

Delegation in Canvas Apps

A couple of weeks ago I found an empty slot in my diary, and I (dangerously) thought “I know, I’ll brush up on my Canvas app skills!”.

In my role I find myself looking across multiple Dynamics 365 apps, Excel spreadsheets, and Power BI reports daily, and I set myself the task of bringing all of this together into one place so that I could access all of the data I need with one or two clicks instead of manually transforming data and keeping several browser tabs permanently open.

This was going great, until I saw the dreaded ‘delegation’ warning that all Canvas app novices will see very quickly in their career.

“Delegation warning. The Filter part of this formula might not work on large data sets.”

When you expand the warning, you get the following detail:

What is Delegation?

Simply put, delegation is an instruction from the target application to the data source, to carry out a query before returning the subset of results that are wanted by the target application itself.

This means that we only ever receive the desired data in the target application, and in turn, performance is increased as a result.

When you compare the processing required in this scenario compared to retrieving every piece of data and then filtering it in the target application, you see a measurable performance increase by using delegation, and you’re also increasing technical debt by pulling back data into the target environment that you want to throw away immediately.

Cause

The wording for this warning can be considered a little misleading. The warning is actually telling us that there will be a lack of delegation in the data source. In this instance, the data source does not have the ability to carry out the condition logic with its capabilities, and therefore it needs request that the Canvas app carries out the query instead.

For example, Power Fx provides the ability to retrieve a day, month, or year value from a Date field, but Dataverse cannot do this! Dataverse can only query date ranges such as ‘on or before [Date]’! When querying a ‘month’ in this scenario, you would receive the delegation warning as the delegation cannot happen.

As a result, the full data set from the data source has to be retrieved by the target application, only for the target application to filter the data once it has all been received. This lowers the performance of the app, but it could be worse than that – if you exceed the definition of ‘large data set’, the data set may not return at all, leaving you with incomplete results with no error and a low quality solution.

Solution

The biggest lesson learned whilst working on delegation recently was from a colleague – there is always a workaround.

Whilst you can’t “fix” the warning with the same piece of code, you can use combinations of delegated conditional logic in order to achieve the same results.

A classic example steps back into using dates in Canvas Apps. In Power Fx I can express “Month = 1”, but Dataverse only allows date ranges so the Canvas App needs to bring back the full data set to work out whether “Month = 1”. As a result, I can’t quite express “in January this year” using delegated logic, so instead I need to combine two ranges using something that Dataverse can recognise. In this example I would combine “Created On must be on or after 1st January 2021”, and “Created On must be on or before 31st January 2021” to obtain the right data at source.

Some examples can be more complicated than this, but a top tip for Dataverse specifically is that if you can achieve it using Advanced Find, then you can be certain that the logic can be delegated!

Have you worked in this space before and found any cool workarounds? Leave a comment below!

Tip: Quickly Enable Migrated Power Apps Portal Configuration

Microsoft’s documentation goes to great lengths in order to explain how we can migrate Power Apps Portal data from one environment to another by using the Configuration Migration Tool, but it doesn’t quite go as far as explaining how to re-point the already-provisioned portal to your newly migrated data upon first deployment.

Follow the below steps once you’ve moved your data in order to see your changes come to life!

1a. Locate via Dataverse

Navigate to Apps and find your Portal app from the list. Click on the three dots, and choose ‘Settings‘.

A screenshot of make.powerapps.com highlighting Apps and Administration.

Select the ‘Administration‘ option which will open a new tab.

1b. Locate via Power Platform Admin Centre

Navigate to the Resources tab which will expand to show a Portals option, and find your Portal app from the list.

A screenshot of the Power Platform admin centre, highlighting the Portal and Manage options.

Click on the three dots, and choose ‘Manage‘.

2. Update Portal Bindings

A screenshot of the Power Apps portals admin centre, showing the Update Portal Binding option.

Stay on the ‘Portal Details‘ tab and scroll down to ‘Update Portal Binding‘ and choose the newly migrated Website Record from the list.