Create a Rolling Calendar Year in Canvas Apps

Earlier this week I had a requirement to create a screen that would help others forecast an activity for the next 12 months. Whilst I was happy working with the data model, ensuring that the year was based on this month and the next eleven was more difficult than I anticipated. I suspect that this is because of my previous work with Dynamics 365 Customer Engagement & model-driven apps, where ‘Next X Months’ is a common and native reporting query, so I haven’t really had to think about it too much.

Here is a step-by-step guide on how you can create your own, and feel free to adapt this to use days or years instead!

Add a Gallery Control to your Screen

As calendars are visual, let’s add a Gallery control to the screen as this will enable us to create user interface effects that you’d typically see within a calendar-style control. At this moment in time, we won’t add a data source.

A screenshot of a Canvas App with a Horizontal Gallery control using the CustomGallerySample list of Items

I would recommend a Horizontal Gallery control for this example so that we can see the next 12 months across the screen with no scrolling necessary.

Create a Collection to Store Months

Now this is where the real work starts! We’re going to need to create a Collection which recognises today’s date and then shows the proceeding 11 months. This may initially look complicated, but it’s just two distinct lines of code and then a copy of the second line with very small amendments for each month.

ClearCollect(MonthsList, {Name: Text(Today(),"mmmm"), Month: Month(Today()), Year: Year(Today())}, 
{Name: Text(DateAdd(Today(),1,Months),"mmmm"), Month: Month(DateAdd(Today(),1, Months)), Year: Year(DateAdd(Today(),1, Months))},

As you can see from the above partial Power Fx code, we need to:

  • Collect a list of months called MonthList.
  • Store the name of the month by converting the month’s value to text using the Text format “mmmm”.
  • Store the date’s month’s value (January = 1, February = 2, etc.) using Month.
  • Store the date’s year value using Year.
  • Create the next item in the Collection by wrapping the code above in the DateAdd Power Fx code, incrementing by one each time.

💡Whilst it may not seem purposeful at the moment, storing the month’s value will help us if we need to query data when selecting one of the gallery items and mitigate the risk of delegation!

A screenshot of a Canvas App with a new collection called MonthList which collects values for this month and the proceeding 11 months.

Here’s the full code snippet:

ClearCollect(MonthsList, {Name: Text(Today(),"mmmm"), Month: Month(Today()), Year: Year(Today())}, 
{Name: Text(DateAdd(Today(),1,Months),"mmmm"), Month: Month(DateAdd(Today(),1, Months)), Year: Year(DateAdd(Today(),1, Months))},
{Name: Text(DateAdd(Today(),2,Months),"mmmm"), Month: Month(DateAdd(Today(),2, Months)), Year: Year(DateAdd(Today(),2, Months))},
{Name: Text(DateAdd(Today(),3,Months),"mmmm"), Month: Month(DateAdd(Today(),3, Months)), Year: Year(DateAdd(Today(),3, Months))},
{Name: Text(DateAdd(Today(),4,Months),"mmmm"), Month: Month(DateAdd(Today(),4, Months)), Year: Year(DateAdd(Today(),4, Months))},
{Name: Text(DateAdd(Today(),5,Months),"mmmm"), Month: Month(DateAdd(Today(),5, Months)), Year: Year(DateAdd(Today(),5, Months))},
{Name: Text(DateAdd(Today(),6,Months),"mmmm"), Month: Month(DateAdd(Today(),6, Months)), Year: Year(DateAdd(Today(),6, Months))},
{Name: Text(DateAdd(Today(),7,Months),"mmmm"), Month: Month(DateAdd(Today(),7, Months)), Year: Year(DateAdd(Today(),7, Months))},
{Name: Text(DateAdd(Today(),8,Months),"mmmm"), Month: Month(DateAdd(Today(),8, Months)), Year: Year(DateAdd(Today(),8, Months))},
{Name: Text(DateAdd(Today(),9,Months),"mmmm"), Month: Month(DateAdd(Today(),9, Months)), Year: Year(DateAdd(Today(),9, Months))},
{Name: Text(DateAdd(Today(),10,Months),"mmmm"), Month: Month(DateAdd(Today(),10, Months)), Year: Year(DateAdd(Today(),10, Months))},
{Name: Text(DateAdd(Today(),11,Months),"mmmm"), Month: Month(DateAdd(Today(),11, Months)), Year: Year(DateAdd(Today(),11, Months))}
);

Technically speaking, we’re not actually working with Months and Years directly here, we’re adding one month to the current date for every item. Whilst we don’t see it on the screen, if today’s date is the 13th November 2022, then the calculation for the second item in the Collection is actually splitting values from the 13th December 2022, and so on. This doesn’t matter though, as we aren’t manipulating or using Day values anywhere in this example.

Associate the Gallery and the Collection

This is relatively straight forward, the hard work is now done. Head over to your Gallery and replace the Items Property with MonthsList, and then run the OnStart Property from your App control in the Tree View.

A screenshot of the Canvas App showing the dynamic data associated with the controls provided by the Horizontal Gallery.

Depending on how you’ve adapted this example, you may see errors on the screen or your may see unnecessary controls. This is ok. This is the canvas trying to associate everything that you have with the controls provided for the custom data.

Get Styling!

  • Removed the Image control.
  • Replaced Subtitle2’s data with the date’s Year value.
  • Added a thin Rectangle control to separator data.
  • Added the month’s value underneath the separator just to show how you could display more data.
  • Added a ‘fill’ for the selected Gallery Item so that you can visually identify which month has been clicked by the user using the code below within the Fill property of a Rectangle:
If(ThisItem.IsSelected, RGBA(255, 191, 0, 1), RGBA(0,0,0,0))
A screenshot of the Canvas App with the finished rolling calendar view, showing all of the data that we collected earlier in a styled Horizontal Gallery control.

Final Thoughts

And there we have it, a fully dynamic month selector that will change based on the month we are currently in. There are several ways that you could possibly adapt this to either add more dynamic complexity, by creating a second gallery below that is controlled by the selector we’ve just produced – this is actually what I had to do for the client, but I can’t show you that as the data was far too specific!

I’ve also discussed the creation of this collection with a few colleagues this week and I couldn’t find anything more efficient to dynamically calculate the rolling months, so I would be really keen to hear your suggestions in the comments below if you have any.

Visualise Your Day’s Meetings in Canvas Apps

With Microsoft expanding their suite of apps every month, it can be difficult to create the right view of data for you to personally consume without context switching. Recently I have been exploring the Office365Outlook Connector in Canvas Apps to bring a day view of my calendar into a Power App alongside information from other meeting & task related content that I consume on a regular basis, and here are a few tips on how I created the solution.

Collect the data.

Now, I’m breaking all of the rules here. This is not low-code and it requires a relatively complex collection in order to gather the correct data, and time zones can be a pain too. Let’s break it down into the Power Fx formula that we’re going to focus on:

  1. ClearCollect: We need to create a new collection for our data and treat Office365Outlook.GetEventsCalendarViewV3 as the source for everything that we need.
  2. Office365Outlook.GetEventsCalendarViewV3: Given that the Office365Outlook.GetEventsCalendarViewV3 data requires a start and end, we need to define our duration of appointments. Now in my tenant unfortunately my time zone is offset by one hour compared to the data stored against the calendar entry, but I really want to avoid any appointments for the next day so I’m going to need to bear this in mind when creating my filter by adding 22.5 hours. I am deliberately leaving this in my solution, because I am sure that there are alternatives to resolving this issue, but this is the reality with working within constraints that you don’t have full control over.
  3. SortByColumns: We will also want to sort our data in ascending order to ensure that we see the correct flow of information.
  4. ShowColumns: Finally, we want to limit the data initially retrieved too, as this data set can be quite large and include columns that you are very unlikely to use. More information on this can be read in my previous post here: Reduce Columns Created in a Collection in Canvas Apps

In order to achieve all of the above in these particular circumstances, we need to write the following Power Fx code in the OnStart property of the App.

ClearCollect(
    MyMeetings,
    (SortByColumns(
        ShowColumns((Office365Outlook.GetEventsCalendarViewV3("Enter Your Calendar's ID here.",
            Text(
                Today(),
                LongDateTime
            ),
            Text(
                DateAdd(
                    Today(),
                    1350,
                    Minutes
                ),
                LongDateTime
            )
        ).value),"start","end","showAs","isAllDay", "subject"),
        "start",
        Ascending)))

You can then ‘Run OnStart’…

A screenshot of Power Apps showing the 'Run OnStart' button within the App.

…and then navigate to your Collection to prove that you’re seeing the correct data from the three dots on your command bar.

A screenshot of the MyMeetings Collection showing data from Outlook.

Display the data on the screen.

Now that we are sure that we are collecting the correct data, we can now move towards adding this information into a gallery.

A screenshot of the CustomGallerySample on the Power App's Screen with two available data sources.

Remember that when you’re choosing your data source, you need to choose the “MyMeetings” Collection and not the Office365Outlook connection. This will ensure that you’re loading all of the filtered data from your OnStart formula.

A screenshot of the out-of-the-box attempt to display our Collection's data.

Visualise!

As you can see from the previous image, the attempt at showing our Collection’s data doesn’t exactly provide any benefit or meaning, and it doesn’t look like a calendar at all. Let’s change that with the following requirements:

Show the Outlook image and a count of the items being displayed on today’s calendar.

We’re going to source the Outlook logo and also add a Label control that counts the rows within our Collection by using the following code:

//X meetings today
Concatenate(CountRows(MyMeetings), " meetings today:")
A screenshot of the Canvas App after adding the Outlook logo and a count of how many meetings we have today.

Show the time of the meeting or whether it’s an All Day Event.

Let’s get rid of that placeholder image and make use of the space that we have. To alternate between All Day Events and the time itself if it’s not all day, we need to write some conditional logic in a Label based on our Collection’s data.

First of all we need to understand whether the isAllDay value is set to true. If it is, we simply need to show the words “All Day”, if it’s not, then urgh! We need to visit time zones and time values again. For this particular example I had to carry out some logic to show the times within a format that looked correct based on my tenant’s time zone, the time zone value set against the meeting, and the local time zone of where I was using the app. This results in the following formula which concatenates “start” and “end” if the isAllDay value is false:

If(ThisItem.isAllDay = true, "All Day",Concatenate(Text(TimeValue(DateAdd(DateTimeValue(ThisItem.start),TimeZoneOffset(Now())*-1,Minutes))), " ",Text(TimeValue(DateAdd(DateTimeValue(ThisItem.end),TimeZoneOffset(Now())*-1,Minutes)))))
A screenshot of the Power App's "All Day" identifier within the Gallery.

Indicate the meeting’s status.

Now this is my favourite part of the solution. We could show the status using words as shown in the Gallery so far, or why don’t we assign an indicator a specific colour based on the status?! This nested If statement allows us to check for the values within the “status” column and set a colour based upon it. If the “status” is null, then the indicator will be black.

If(ThisItem.showAs = "free", RGBA(0,128,128,1), If(ThisItem.showAs = "busy", RGBA(230,0,0,1), If(ThisItem.showAs = "oof", RGBA(102,51,153,1), If(ThisItem.showAs = "tentative", RGBA(255,192,0,1), If(ThisItem.showAs = "workingElsewhere", Gray, Black)))))
A screenshot of the new visual indicator for Outlook meeting status next to the time indicator.

Add the final touches.

From here it’s entirely up to you how you style your calendar view. Personally, I would like to format the main body of the row and add the description, and then make a few changes to the styling of the Gallery. If you want to use any of the other available data from this action, just ensure that you add that specific column in your ShowColumns formula within the ClearCollect statement in your OnStart.

To finalise the solution, I then carried out a series of visual changes with very little code:

  • Removed the chevron.
  • Removed the original “showAs” label.
  • Replaced the “end” label with “description” by changing ‘ThisItem.end’ to ‘ThisItem.subject’.
  • Shrunk the height of each row in the gallery.
  • Adjusted the alignment of each component.
  • Changed the colour of the separator.
  • Renamed the controls that hadn’t already been modified earlier.
  • Adjusted the size of the logo and count of meetings.
A screenshot of the finished calendar view within the Canvas App after adding some finishing touches.

And there we have it! In this example we have just explored building a calendar, but think about the important information you evaluate to prioritise, and you could further expand upon this to include Planner, To Do, and many more pieces of data!

Choosing The Right Data Source for Power Apps & Power Automate

One of the biggest challenges that we face when building apps and automations is the decision on where to store my data. Sometimes this choice may be dictated to us based on licensing and architectural factors, however, if you have a choice of options then this blog post is for you.

Think about the way that cars are advertised. Most car manufacturers have a super mini, family hatchback, cross-over, and SUV offering, and each one from an adoption perspective acts like a steppingstone towards the next model up next time as your wants and needs become more sophisticated! Great for us, but also great marketing for the supplier!

We all want the Audi RSQ8, but right now we might only be able to afford the Audi S1, or we might not want to commit the investment of the most expensive one right now. Note: Other car manufacturers are available of course!

Anyway, back to the technology, the most used data sources for Power Apps and Power Automate are usually Excel, SharePoint, Dataverse for Teams, and Dataverse, so let’s compare the options and understand how our needs can be met within Microsoft 365.

Microsoft Excel, the super-mini.

I have an ‘I 💖 Spreadsheets’ mug for my morning tea, and for some reason the world just can’t get enough of spreadsheets! Many organisations around the world are run on spreadsheets and nothing else. It was revolutionary at the time it was released.

A screenshot of Excel being used to create a shopping list.
A shopping list swiftly created via Excel.

Microsoft Excel is a hugely popular and fulfilling software tool, providing us with a quick way to format lists, calculate information, and visualise data. It’s formula functionality is so successful, that Power Fx, Microsoft’s language for developing Power Platform components, was inspired by it!

Excel is not a relational database though, and unless you have already defined a digital adoption strategy, spreadsheets are still saved locally on team member devices leading to a loss of business data over time.

When to use spreadsheets:

  • Quick lists
  • Personal recording of information
  • Extraction of data from another system into a universal format

When to seek one of the alternatives in this post:

  • When data is shared across multiple people or departments
  • When data repeats the same information multiple times, such as contact details

Lists (SharePoint), the hatchback.

Microsoft Lists has become a more prominent feature of SharePoint and has been rebranded as such to position the product as a feature primarily for use within Microsoft Teams. Lists combine the familiarity of Microsoft Excel, whilst also introducing concepts from relational databases and centralising of information to help increase quality by a significant proportion in comparison.

A screenshot of Microsoft Lists on the mobile and on a tablet device.
Microsoft Lists has the same features available regardless of what device you use.

Microsoft Lists can allow categorisation, links to users, and formatted fields with extraordinarily little effort. They won’t solve every problem, but they will help to keep sight of business data, and you can even generate apps and automation from Lists directly too.

Microsoft Lists is a well-received solution to our hybrid working scenarios where Microsoft Teams plays a huge part in operations.

When to use Lists:

  • Track information and progress within a team
  • Organise work and assign owners
  • Indirect benefit of preparing our business for modern cloud solutions that integrate across all of Microsoft 365

When to seek one of the alternatives in this post:

  • When sensitive data requires better security considerations
  • When your data needs to flow into another process in another system, or with another department

Dataverse for Teams, the crossover.

Following the release of Dataverse (previously the Common Data Service, or the on-premise Dynamics CRM SQL database to some of us older folk!), Microsoft also released Dataverse for Teams. This has been a fantastic middle-ground, offering organisations a step into the world of relational databases within the Power Platform, without having to initially commit to a licensing investment. The benefits of taking a relational database approach for this are huge.

A screenshot of creating a Power App in Teams, which will lead to the creation of a Dataverse for Teams environment.
Creating a Power App in Team will lead to the creation of a Dataverse for Teams environment.

There are some significant caveats in comparison to Dataverse, but you can set up your own database within a Microsoft Team, and build apps and automations on top of it, to service your end users. Remember, this is entirely free!

When to use Dataverse for Teams:

  • Small operational processes that require a team scope that can be defined within a Microsoft Team
  • When you need to build appetite for further Power Platform delivery in the future to demonstrate the art of the possible with little investment
  • When you plan to invest in Dataverse in the future, as the upgrade path from Dataverse for Teams to Dataverse is much more seamless than a migration project

When to seek Dataverse as an alternative:

  • When you need to retrieve data from sources outside of the Microsoft Team your Dataverse for Teams environment lives in
  • When you need to deliver Application Lifecycle Management (ALM) and utilise the concept of ‘development’ and ‘production’ for your solutions.
  • When you want to start utilising Dynamics 365 apps using the same database as your custom solutions.

Dataverse, the SUV.

We now look towards our final data source for review. I love describing Dataverse as the SUV. We see a nice car on the motorway with all the extras, we look up to it for inspiration on our next purchase, and one day we can finally make it to buy this dream car and it just works.

Dataverse is the same, it’s a full database offering with a comprehensive list of functions that require no expertise in SQL, just a theoretical understanding of relational databases and normalisation.

A screenshot of a Developer Dataverse environment.
A standard database environment configured for developer use.

Dataverse helps us to create a single source of the truth, and it helps us to share data from one record and relate it to others. Over time as multiple users build upon the quality of the data, you gain a significantly better understanding of how your business operates, which will help you to further improve your efficiency and services in the future. It’s worth the investment, and it’s worth setting up a free developer account to explore the possibilities if you haven’t already.

Conclusion

As we’ve discovered, there are so many tools at our disposal, even just within the Microsoft 365 stack when we’re delivering apps and automations.

My recommendation would always be to ‘climb down’ rather than ‘climb up’. We all know how easy it is to set up a spreadsheet and often we talk ourselves out of using another tool.

If we step back for a moment and consider our audience, our data model, and the impact across the organisation, it may be far better to rule out Dataverse first rather than having to justify its purpose 3-levels away from our currently proposed ‘easy’ solution which could cause maintenance issues in the future.

Consistent Y-Axis in Model-Driven App Charts

This week I faced a very old school problem with model-driven apps from the days of working on-premise with Dynamics CRM, I needed to show two different series on the same graph, but every time I would view the chart it would show me two different scales on the same Y-Axis!

As this chart was for the purpose of comparing values, this makes the out-of-the-box chart meaningless as in some respects, smaller numbers look bigger than their counter part as shown below with test data in my development environment.

A screenshot of a model-driven app chart with two different Y-Axis scales for the same type of data.
Note that the y-axis reference on the left has a higher increment for each bar in comparison to the right y-axis.

We can resolve this with a few steps by editing code, and here’s how.

Step 1: Back up your environment

Before we get started, please note that Microsoft have gone a very long way to make solutions a no-code option for deploying components and that it is not recommended to edit solution files unless the requirement cannot be fulfilled any other way, and you are absolutely confident in how solutions are composed and deployed.

Always back up your database before significant operations, and seek support from peers if you are uncertain. It’s also worth considering how impactful this change is, and whether the effort vs. benefit stacks up in the correct way.

Step 2: Create a temporary solution file

In order to make sure that our changes persist from development to production, we will need to re-import our code changes back into the source environment once complete. This is so that the changes are recognised every time we export from source and deploy to target in the future, otherwise you would need to make this change every time you deploy to a new environment in the future which carries risk due to the frequency of this activity.

A screenshot of a user creating a new temporary solution file.
This helps us to target the components that we need to change that otherwise live in another solution, and therefore reduces risk considerably.

Step 2: Add your chart(s)

When we add existing components, we’ll need to locate the Table and its associated Chart components for change.

A screenshot of a user selecting multiple existing charts to add to their solution.

Again, let’s make sure that we only add what we need here.

Step 3: Export an unmanaged copy of the solution

As we need to edit the code that sits within the solution, we must export it so that we can make the changes and re-import it here later.

A screenshot of a user selecting the Unmanaged option for the solution export.

By selecting Unmanaged, we retain full control over the customisations once the solution is re-imported, which is important as we probably want to keep the changes but remove the solution file later on.

Step 4: Unzip your file and make the change

Solution files download as .zip files, so we need to extract the files before we can work on them. When you extract the files, you’ll see three files:

  • [Content_Types].xml
  • customizations.xml
  • solutions.xml

We now need to open up customizations.xml in our favourite code editor, preferably Notepad++ or Visual Studio Code. Search for the word ‘Secondary’ within your code, and remove the YAxisType variable. You will have one of these tags for each chart that you’ve added with multiple Y-Axis, and in this instance I have two due to the two chart components that I selected earlier.

A screenshot of a user editing the customizations.xml file in Visual Studio Code.

Original code:

<Series ChartType="Line" IsValueShownAsLabel="True" BorderWidth="3" MarkerStyle="Square" MarkerSize="9" MarkerColor="37, 128, 153" MarkerBorderColor="37, 128, 153" YAxisType="Secondary" />

Amended code:

<Series ChartType=”Line” IsValueShownAsLabel=”True” BorderWidth=”3″ MarkerStyle=”Square” MarkerSize=”9″ MarkerColor=”37, 128, 153″ MarkerBorderColor=”37, 128, 153″ />

Step 5: Zip up the files and re-import

Now this is where we need to be extremely careful, we need to select the three extracted files and compress into a .zip file.

At this point in time, your Windows device will ask for a name for the .zip file. You should make sure that the name of your file within this folder is exactly the same as the original file name exported from your PC. As long as you zip these files up anywhere other than the same location that you downloaded the .zip file too, you will have no problems doing this, and you’ll then be able to go back to your browser to import the newly compressed .zip file.

A screenshot of the newly modified .zip file being uploaded to the environment.

And there you have it! I have mentioned this just a few times before, but remember that this is a relatively complex and risky operation that should be executed with focus and confidence. I have the luxury of working on on-premise versions of Dynamics “CRM” well before some Power Apps developers were out of secondary school, but if you aren’t so sure, please do reach out to me or to someone else who may be able to help with the more technical elements of this activity.

A screenshot of the final result, showing one y-axis for both lines in the graph.

Now that you’ve seen the results, you are safe to carefully remove each component from your temporary solution, before finally removing the solution itself.

Reduce Columns Created in a Collection in Canvas Apps

One of the first lessons when getting to grips with Canvas Apps was that you should always use Collections where possible to reduce the number of calls to the original data source, and with any luck, you may see a performance increase as a result too. However, I often find that the data source I’m using always collects a number of columns that I am never going to use in the Canvas App itself.

Let’s take the example of listing Account records from Dataverse using a simple Power Fx statement:

ClearCollect(ListOfAccounts, Accounts);

As you can see below, there are a significant number of columns that I don’t plan to use relating to various relationships across the Dataverse database.

A screenshot showing a Collection in Canvas Apps returning all fields from the data source.

These columns are extremely important for the database and we shouldn’t underestimate their criticality, but these are not necessarily important for me when building a Canvas App as I just want to retrieve the Account Name and the Account ID.

We can make a small change to the original Power Fx statement, by expressing exactly which columns to use, such as:

ClearCollect(ListOfAccounts, ShowColumns(Accounts, "name", "accountid"));

Which in turn produces a Collection that is much more refined, shown below.

A screenshot showing a Collection in Canvas Apps returning a more defined list of columns based on my needs for the app.
This won’t necessarily make a difference to the code that you write within your app, other than the collection’s size itself, however, when you start to write Power Fx within your components you’ll see a much shorter and more defined list of available attributes when trying to retrieve data from your collection!

Keeping Calm Whilst Troubleshooting Software Bugs

As a manager of a team of consultants, I no longer have my own configuration problems anymore, so troubleshooting is a theme that plays into every single working day.

When I started 10+ years ago, seeing an error would fill me with dread, sweat, and stress that I just couldn’t shake, and today I see technical bugs as a little bit of fun to try and solve the unknown! Some will think I’m strange, but everyone has their niche.

My thought process has changed dramatically over the years and it’s not something that you can teach, it’s all experience. I used to panic particularly about the reaction of everyone around me.

  • I’m an awful consultant.
  • I bet the client/end user is going to be so angry.
  • How do I tell people that something is wrong?!
  • What if I don’t know how to fix it?

Today it’s entirely different and it’s all about the tech, and here are just a couple of provocative sentences for to consider when troubleshooting technical issues to slow down the pace of thought and to focus on the outcome. I can assure you there is a good explanation!

100% pass rates during testing are terrible.

If test scripts produce a pass rate of 100% then it is very likely that we haven’t considered all of the possible paths that the end user may take, and we might have focussed on the happy path too much.

We want to break things during testing, as it means that they’ll be fixed or known about with recommended workarounds by the time the feature moves to the production environment with real users.

Taking a Power Platform example, when testing a requirement such as ‘Entering data into the Contact Table’, perhaps we should consider:

  • What happens if I upload instead of creating from the Form? Are all rules respected?
  • What happens if I create the Contact from another record, does the Form behave the same way as it would if I were creating the Contact standalone.
  • What if I create many versions of the same Contact to try and confuse the system? Does it change the way that the system behaves in other areas?

These tests may seem extreme, but remember, we can never guarantee that an end user will work in the way that the system is built, which often highlights the result of a potential gap between UI and UX.

There are some fantastic examples out there describing this difference which you can find online. Think of the glass bottle of ketchup that we place on its lid when it’s running out so that we can get every single last drop, and how we used to bang on the bottom of the bottle or use a knife to try and help the flow through the thinner neck at the top. It’s no coincidence that today’s bottles are plastic built and have the lid at the bottom, with a width the same across the entire length of the bottle, as well as a squeezy mechanism to help with flow. These are all features that were created because of user experience rather than user interface.

Another notable example I can also almost guarantee that everyone reading this post has been guilty of – The great corner-cutting mud patches that we find in parks or woodlands where two paths meet at a 90° angle. The paths always look nice, but we are built to try and be efficient, and watching someone follow the path in this case can often look unusual. In our local area we’ve had five new-build estates pop up and they all feature non-linear paths. I wonder why?!

Work out who’s to blame.

Wait what? I thought we were all on the same team here. You’re absolutely right, but there are so many parts to any software delivery that you really do need to identify which supplier of the technology is causing the issue.

One of the most interesting examples I saw of this recently was a scaling issue in a Power App that was being loaded through the Website tab in Teams (for good reason, it had an appended URL!). Out of context, the Power App would render fine with no issues functionality or cosmetically whatsoever, but then it occurred to me that this is a 4-level hierarchy of display settings!

  • The person accessing the app is using a desktop monitor with a resolution and scale.
  • Teams on the desktop also has its own scaling percentage.
  • The Website tab, just like web browsers, is mobile responsive.
  • The Power App also has its own scaling and ratios built in as well as orientation.

In this setting, we had control over the Website Tab configuration and the Power App configuration, but we can’t ask end users to use a specific monitor resolution and scale, nor can we change their Teams environment. Rather than ‘fixing’ the perceived issue, we have to work with it, and in this case the ‘fix’ that worked for us was the ‘Lock Aspect Ratio’ setting on the Power App’s Settings so that it rendered in the way that we wanted it to irrespective of other scaling factors that we can’t control.

Final Thought

Bugs will always occur in software delivery, we see it from every single software supplier in the world. So, let’s not be afraid of them and let’s tackle them head on and ask ourselves some thought provoking questions in the process!

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.