Customise Data Shown in Dropdown Controls in Canvas Apps

When you use a dropdown control within a Canvas App it can be surprisingly tricky to show exactly what you want as an option unless the data already exists as a value in your data source. I assume that the reason for this is that dropdown controls are most commonly used within forms, and therefore the options available need to be valid for your submission otherwise your data entry will fail.

Dropdown controls have other uses too such as filtering data within a Table or a Gallery, and in this situation, you may need the data displayed under your control based on processing within the Canvas App itself. Last week I needed to do exactly that!

The Challenge

I am currently building a small timesheet approvals app, and the dropdown I wanted to create needed to display a list of people with an indication to the end user of how many unapproved time entries there were for that person. Afterall, if 40% of people had no unapproved time, it would be really frustrating for the end user to endlessly click on people to find that they have no time entries awaiting approval. In my example, I was using data from a Collection that was getting data from a table in another data source holding delivery resources, and I could choose any of the following fields but I couldn’t do anything else as the Value Property is not available for me to modify with Power Fx.

A screenshot of a dropdown control within a Canvas App allowing me to pick any of the available fields from the Collection.

This may not be a problem in other use cases as the people with approved time could be disregarded from the Collection by filtering on Delivery Resources where ‘unapprovedcount’ is 1 or more, but this won’t work for me with the app’s requirements so I had to seek another solution so that I could show ‘name’ and ‘unapprovedcount’ as one dropdown item.

The Solution

The workaround for this was relatively simple, but trying to find resources on how to achieve it was proving tricky! Given that we have already created a Collection from the data source called ColDeliveryResources, we simply need to create a second Collection based on this to add a column to the existing data, and in this particular case I needed to also sort by the number of unapproved timesheet entries too. By doing this in the OnStart property of the app, we can use Power Fx here to define a new column that we can select within the Value property of the Dropdown control:

First Collection

//Collects all resources from the Delivery Resources table to use across the app.

ClearCollect(ColDeliveryResources, ShowColumns('Delivery Resources',"name","resourceid", "email", "unapprovedcount"));

Second Collection

//Collects the results from ColDeliveryResources and adds a column to each one combining 'name' and 'unapprovedcount'. For example, 'Aaron Gumbs (9)'. Once this has been achieved, we sort the names from highest number of time entries unapproved to lowest so that the end user can prioritise their workload.

ClearCollect(DropdownDisplay, SortByColumns(AddColumns(ColDeliveryResources, "displayname", Concatenate(name," (", unapprovedcount,")")),"unapprovedcount",Descending));

Final Result

Once I had run OnStart and my new collection was available, I simply needed to swap out the original Collection for the new one in the ‘Items’ Property of the Dropdown control, and ‘displayname’ became available to select, allowing me to finally show both ‘name’ and ‘unapprovedcount’ on the screen as shown below.

A screenshot of my timesheet approvals app, showing a dropdown control with 'Aaron Gumbs' written in it, indicating that he has 9 unapproved timesheet entries.

Calculate ‘Total Days This Month’ Using Power Fx

New year, new challenge!

This week I was asked to adapt one of our internal Canvas Apps that was used for an exercise challenge in December to track miles, so that the leaderboard resets to ‘0 miles’ at the beginning of each month. Our team’s goal is to log the miles run or walked within the month, with the aim to contribute at least one mile per day of the month.

One of the features I delivered was a progress bar, but I wanted this progress bar to change based upon the duration of the month. Amongst other dynamic calculations, the width of the progress bar I want should be “total number of days in the month multiplied by 10”, and it looks something like this:

A screenshot of Aaron's running progress for January, showing his profile picture, 15 miles of 31 miles complete in a progress bar, and a message saying "15 miles down, keep going Aaron!".

As we are all well aware, the length of months aren’t consistent, and in my app progress should be ‘higher’ in February if I have run 15 miles in 28 days, as opposed to running 15 miles in March which has 31days. In February the width should be 280px and in March it should be 310px.

To my surprise there wasn’t an existing Power Fx function that could achieve this but I was able to do this myself with the following solution. For someone who writes Power Fx regularly, I found this quite difficult to explain whilst writing the one statement, so hopefully the following explanation that breaks down the formula can help you too.

Constructing the Formula

I’m using the last day of the month in multiple places in my Canvas App, so I don’t quite want to create the final value (the multiplication by 10 for the width of the progress bar) just yet. For now, let’s start our formula by declaring a new variable called varDaysInThisMonth in the OnStart Property of the Canvas App.

//Create the variable varDaysInThisMonth with an empty string.

Set(varDaysInThisMonth,"")

We then want to identify the last day of the month, but there’s also no direct formula for this either! We can use a little trick to achieve this by finding out the first day of next month, and then take away one day from the value.

Let’s do this by constructing the date for the first day of this month, and then add one month to the value. Please note that we need to make the Year and Month value dynamic here, otherwise we’ll end up with bad data in later months.

//Given that the current date is in January, construct the first day of the month using Date (01/01/2023) and return the value "01/01/2023" by adding one month.

Set(varDaysInThisMonth, DateAdd(Date(Year(Today()),1,Month(Today())),1,Months))

We now know the first day of the next month, so now we just need to subtract one day from this value to find out the last day of this month by wrapping a new DateAdd formula around our existing DateAdd formula.

//Given that the output above is "01/01/2023", return the value "31/01/2023" by adding -1 days.

Set(varDaysInThisMonth, DateAdd(DateAdd(Date(Year(Today()),1,Month(Today())),1,Months)), -1, Days)

In this situation the last day of the month is returned, which may be enough for some of you trying to achieve a similar calculation. In order to finally turn this in to the total number of days in this month, we just need to wrap our DateAdd calculation in a Day formula, which takes the “31” from “31/01/2023”.

//Given that the output above is "31/01/2023", return the total number of days this month, which equates to "31".

Set(varDaysInThisMonth, Day(DateAdd(DateAdd(Date(Year(Today()),1,Month(Today())),1,Months),-1,Days)))
A screenshot of Power Apps studio showing the OnStart Property and the formula constructed in its final form.

And there we have it! Personally, I found this more difficult than I expected to. I know other similar languages used in Excel and Power BI allow for simpler calculations than this, and I’d be interested in whether you’ve found a quicker way to achieve this!

The last step for me was to simply change the Width Property of the grey rectangle acting as the background of the progress bar alongside the formula for the with of the actual progress shown in the green rectangle by using this constructed variable.

A screenshot of the Power Apps Studio showing the Width Property of the grey rectangle acting as the progress bar's background.

Fact check: In this article Aaron implied that he has run 15 miles in January. This is completely false, Aaron would never run 15 miles within one month!

Handling Missing User Profile Photos in Canvas Apps

Lately I’ve been spending a lot of time working with photos in Canvas Apps in order to create more of a visual impact in my user interface design. Often this photo can come from the Office 365 User’s profile photo upload but we can’t necessarily predict or control whether the end user will definitely have a photo uploaded.

In this example I wanted to use data from Shifts to show who is going to be on annual leave this week, and the code that I am using to retrieve the photo is as follows where ‘userId’ is the column to identify the user in the list of Shifts records.

Office365Users.UserPhotoV2(Text(ThisItem.userId))

The Power Fx formula tries to obtain the photo based upon the userId of the returned Gallery Item. Immediately we receive an error and depending on the way that you’re trying to identify the Office365User, this error can vary, sometimes showing ‘resource not found’ too!

A screenshot of a canvas app displaying an error due to an invalid error for the 'id' parameter.

The reason for this error is that the Canvas App doesn’t know what to show as the image if it can’t find the Office365Users’ photo. Whilst this doesn’t seem significant right now as other images are showing, it will prevent any proceeding functions from behaving correctly and your end users may have a degraded user experience when running the app.

A screenshot of the Canvas app showing errors on the canvas and in the notification bar explaining that the photo could not be found.

Fortunately we can fix this with some minor edits.

Use IfError to Control the Situation

The IfError function is a way to identify whether an error occurs and what the ‘fallback’ should be in that particular event. We use IfError as opposed to IsError as this formula is specifically designed to revert to an alternative if the error occurs, whereas if we use IsError, we would need to combine this with an If statement and repeat parts of our code because IsError only returns whether an error actually occurred. I have used IfError as shown below:

IfError(Office365Users.UserPhotoV2(ThisItem.userId),SampleImage)

By adapting our previous snippet of code and using SampleImage as the fallback, this guarantees that we will not see a system error for this particular reason moving forward, and that a placeholder image will be shown for any users that do not have a profile picture. All proceeding code can execute with no problems too.

A screenshot of the canvas app showing error handling and a placeholder image if the photo could not be found for the Office 365 User.

This also increases the perception of quality. Showing a placeholder image instead of a blank circle indicates that there is data that has been returned rather than giving the impression that something hasn’t loaded.

As you’d expect though, it’s difficult to tell who this person is with the placeholder image showing, so it’d be worth adding an additional identifier such as the Office365Users’ Display Name underneath the photo too. This can easily be done with the Office365Users Connector with the following code, but I haven’t shown it in this example as I was working with real data.

Office365Users.UserProfileV2(ThisItem.userId).displayName

References

Microsoft Learn | Error, IfError, IsError, IsBlankOrError functions in Power Apps

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!

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!

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.