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!

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.

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.

Translating Unknowns into Tangible Requirements

For me, the most exciting part of a project is the challenge of figuring out exactly what a client is asking for based on a very short brief provided in an introductory call.

This challenge is increased in my industry when you move from Dynamics 365 based projects to pure Power Platform projects, because you move away from a functionally built system, to a set of tools that enable the capability. Not only do we now have to qualify the tool, but we also need to qualify the business process at an earlier stage than we typically used to, as well as the full data model.

For example, a “helpdesk replacement tool” screams Dynamics 365 Customer Service, and consultants in the industry typically understand the core operational processes before they speak to a customer. On the Power Platform, however, no two ‘self-serve chatbot’ projects would ever be the same, and there’s no functional process that you can align to this.

So how do we quantify projects with so many unknowns when we need to fully design the data model, the user interface, and the functional process? One way to start is to look for three themes:

  • Trends
  • Assumptions
  • Caveats

The first consideration I make is whether there are any repeatable components for any given high level requirement.

Whilst this doesn’t necessarily give us the full requirement ready to build, it does give us an idea of the size of the scope in contrast to a solution that is easier to estimate. Let’s take the idea of implementing a chat bot for a client on their website.

As a website user, I want to be able to engage with a chatbot, so that I can easily find out store opening times and current stock levels.

Within the industry I work in, we know that a configurable Power Virtual Agent for Teams solution that only uses Entities is relatively straight forward, and doesn’t require code. The interface used to build the solution is entirely controlled by Microsoft, so we also have confidence that it works! Let’s now put our original requirement into context by using known unknowns:

  • We know that the client cannot deploy this through Teams, but we don’t necessarily know exactly how to deploy it through a website that we don’t control just yet.
  • We are not being asked to build their website and we don’t know what their data source is, but we do know that we can take advantage of data and automation services that we can control to make this easier, perhaps Microsoft Dataverse with some sort of movement of data via Power Automate?

We now have broken down the requirement into tangible considerations and we can justify risk and complexity based on what we do know and what we can control, so we should factor this in to our estimate right from the beginning.

As a website user, I want to be able to engage with a chatbot, so that I can easily find out store opening times and current stock levels.

Trends:

1. Power Virtual Agents for intelligent chatbot functionality.

2. Power Automate to drive dynamic data interactions between end user and data source.

3. Dataverse to assist with controlling data where necessary.

Assumptions

Next up, assumptions. We are often taught that making assumptions is a bad thing, and in most cases that is correct, but assumptions can be extremely powerful when defining a requirement if used correctly.

Taking our earlier example of a chatbot being deployed via a client’s website, we really don’t want to be developing the website in unfamiliar territory, nor do we want run into any bumps if their data source isn’t fit for purpose. For now, we can set assumptions against our requirement to portray what we would typically expect within the client’s landscape, and if any of these are found not to be true, then we can justify a change in direction for a requirement through a change of scope, estimate, and change request!

As a website user, I want to be able to engage with a chatbot, so that I can easily find out store opening times and current stock levels.


Assumptions:
1. Assumes that the client’s existing data model is fit for purpose, and if any changes should be made, the client will take responsibility for these.

2. Assumes that the solution can be deployed using a embedded HTML code snippet, as per Microsoft’s standard approach.

Caveats

And last but not least, we have caveats. Clients may see these as the supplier creating ‘get out of jail free’ cards, but in reality, these are to ensure that everyone involved understands what should happen in the event that one of these factors occurs. Caveats are usually based on assumptions, but can extend further than this to cover typical project factors too.

As a website user, I want to be able to engage with a chatbot, so that I can easily find out store opening times and current stock levels.


Caveats:
1. If the data source should change after delivery, the client will be responsible for a change request for any errors that may occur with this solution if they wish to continue using the functionality.

2. If the client’s website cannot support HTML snippets for any given reason, the project may need to be delivered via a Power Apps Portal, which would incur extra cost to ensure the delivery is built to the correct standard.

Summary

When I describe this way of working with my team, I reference a phrase that may be familiar to some – It’s about the journey, not the destination. Imagine you have a 100 mile journey to make with no map functionality, digital or print. What would be your first move?

Success isn’t just the destination, or the solution in this case, it’s the route to it and the service provided along the way that counts. This continues to be a significant theme throughout the whole lifecycle of the project, and it can make or break the final engagement with the software.