Tag Canvas Apps

Choosing The Right Data Source for Power Apps & Power Automate

AaronKGumbs Leave a comment

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.

Reduce Columns Created in a Collection in Canvas Apps

AaronKGumbs 1 Comment

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

AaronKGumbs Leave a comment

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!

Delegation in Canvas Apps

AaronKGumbs 1 Comment

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

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

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

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

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

What is Delegation?

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

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

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

Cause

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

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

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

Solution

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

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

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

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

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

Expect Dataverse Deployments To Fail First Time

AaronKGumbs Leave a comment

Whilst the process of deployment hasn’t changed too much since the days of Dynamics CRM, one thing that has changed significantly is the volume of possible components that can be included in a solution file.

Not only is this due to an increase of readily-available functionality from Microsoft, but also by the ability for end users to install their own components, which in turn creates more dependencies on (what we think) is our small solution of configuration changes to be deployed from one environment to another. This can increase the number of failures that can occur during delivery, and often, the end user error isn’t very helpful.

A generic error provided by the Power Platform when trying to deploy a solution.

Solution deployment failures don’t have to be a problem, in fact, we should expect them.

In this blog post I will help you understand how to troubleshoot a failed deployment so that you can solve the issue in an informed way.

Step 1: Download A Code Editor

We want to ensure that the output from the failure is in a readable format, and for this we need a code editor that recognises XML formatted files. My preference as a functional consultant who needs to open the occasional file is Notepad++. It’s free, and it has an XML Tools plugin which allows you to ‘pretty format’ any XML files. You can also use Visual Studio or Visual Studio Code – I suspect some of you reading this will already have one of these installed!

Step 2: Download The Solution’s Log File

Whenever someone approaches me with a failed deployment, the first thing I ask them for is the log file. When you open this file in Notepad++, use ctrl+alt+shift+B, which will ‘pretty format’ your XML file. It’ll look something like this:

A screenshot of Notepad++ with XML Tools plugin installed. The file shown here is using 'pretty format' to make the code readable.

It looks difficult to decipher to the untrained eye, but we can quickly start to understand why the solution is failing with a few tips when we break down the file.

Step 3: Understand The Dependency

Let’s take a look at the first dependency, defined by the <MissingDependency> XML tags.

A snippet of code showing a missing dependency.

You’ll notice a <Required> line and a <Dependent> line which both include a Type. This, alongside the schema name, is the most important part of the dependency, as the two combined tell us what we’re looking for.

Fortunately we don’t need to remember all of the types as Microsoft provide a handy reference guide here.

We simply need to cross-reference the numbers in our dependency, and we now know that to complete the deployment we need to include the “Offering” entity (table) for the “Service” System Form.

Step 4: Modify Your Solution

We have two choices here:

  1. Remove the Service System Form from the solution, or,
  2. Add the Offering entity (table) into the solution.

In this particular instance it would make more sense to add the Offering into the solution, but sometimes you may challenge whether the component is really needed within your deployable solution, in which case, you’d remove the System Form.

Step 5: Rinse & Repeat

Not all dependencies will be resolved within one solution modification, but that’s ok, and you may need to repeat steps 3 & 4 multiple times before you have a solution file that can be successfully deployed. The key is to remember that failures can be expected, and that they don’t always have to be a problem!

How To Enable ModelDrivenFormIntegration for Existing Canvas Apps

AaronKGumbs 3 Comments

Earlier this week I found myself trying to embed an existing Canvas app into a Model-Driven app to better present information relating to the record using the flexible UI controls, but as an infrequent user of such a feature, I struggled to understand how I could reference the current Model-Driven app record’s data within the Canvas app!

All over the web I could find resources pointing towards a special type of control called ModelDrivenFormIntegration and how it works, but I simply couldn’t see it within my solution. This is where it should be:

A screenshot of a Canvas app, highlighting the ModelDrivenFormIntegration control that appears after embedding within a Model-Driven app.

The new Power Platform interface has improved so many of the existing controls and added fantastic new features, but unfortunately at the time of writing, embedding a Canvas app via the new user interface needs a little more work, and it doesn’t quite complete the job in all circumstances. There are two reasons for this:

  1. You have to associate the control with a field on the Form, and when adding the Canvas app via the new User Interface, it doesn’t configure the field control automatically leading to errors when loading.
  2. The functionality available via the ‘Customize’ button is not available in the new User Interface, which is partly the reason for this blog post!

As some of you will be aware, sometimes it’s just better to head over to the classic user interface to complete your configuration, and although I’m finding myself using this user interface less these days. In this post I attempt to bridge the gap and I’ll explain how to get things working.

What is ModelDrivenFormIntegration?

As Microsoft have explained in detail here, this control allows us to bring contextual data from the Model-Driven app that the Canvas app is utilised in.

There are a significant number of benefits to doing this, primarily because it provides you with the ability to dynamically change your Canvas app content based upon the record you’re currently viewing in the Model-Driven app.

Enable ModelDrivenFormIntegration

Step 1: From https://make.powerapps.com, navigate to the correct environment and choose the relevant Table’s Form within your solution file. Use the ‘Switch to Classic‘ button from the navigation bar straight away.

A screenshot of a Dataverse Table's Form, highlighting the 'Switch To Classic' button.

Step 2: Navigate to the required field you’ve configured for your Canvas app control and double click. You’ll notice a pop up window which provides the ability to navigate to the Canvas app control configuration. Click on ‘Controls‘ and press the ‘Customize‘ button.

A screenshot of the classic user interface that was inherited from Dynamics CRM, showing the Properties of a Mandatory field on the Controls tab, highlighting the 'Customize' button.

Step 3: Your Canvas app will now open, and you’ll notice that ModelDrivenFormIntegration is now available as the first control on the list for the first time! We’re not done yet though, we need to tell the control which Table we are using within the Model-Driven app by editing the ‘DataSource‘ Property.

A screenshot of a Canvas app, highlighting the ModelDrivenFormIntegration control's Property called 'DataSource'.

And finally the ‘OnDataRefresh‘ Property.

A screenshot of a Canvas app, highlighting the ModelDrivenFormIntegration control's Property called 'OnDataRefresh'.

We can use the plural label/friendly name of the Table here, instead of having to look up the logical name, which is a great touch in a lot of Power FX’s abilities.

Conclusion

And we’re done!

The configuration for this isn’t difficult, but finding out why the issue exists can often be challenging, especially if you’re an infrequent user of this functionality.

Remember to ‘Save‘ and ‘Publish‘ your Canvas app changes when you’re ready, but you don’t need to publish your Model-Driven app as this was simply a route into enabling the functionality with no configuration changes.

There are a few pointers to be aware of when generally configuring embedding Canvas Apps:

  1. Microsoft advise that associating your embedded Canvas app control is tied to a mandatory field, so that you can guarantee the operation of the Canvas app.
  2. Remember to share your Canvas app or make other users Co-Owners as appropriate before releasing the functionality to end users.
  3. The classic user interface is due to be deprecated in an upcoming release nearer the end of the year. I suspect this feature will still work for a short period of time, but we will have to watch and wait until such a time, and I’ll update this post if/when this happens.

Power Apps ModelDrivenFormIntegration Control

Embedded Canvas App Guidelines & Troubleshooting