Blanking out date fields in Dynamics 365 using a Flow

Dynamics 365 supports a number of base content types naively that can be set via Flow.  However, date fields are a bit finicky.  When trying to set a date field, you can provide the date in a string format.  But what do you do if you need to clear out an existing value in a date field in a Dynamics 365 record?

Turns out that the Dynamics 365 connector is not behaving the way most of us would expect.  Leaving the field blank will not change the value.  If you add any value into the date field other than a date field, the action will fail.  

The way to do it is by using the following steps:
  1. Add a Convert time zone action.  If you're not actually converting the time, then set the Source time zone and Destination time zone to be the same.
  2. Assign the Base Time you want for the field.  If you want to clear it out, set it to the null expression.  null will cause the time conversion operation to fail, but still creates a time object.
  3. In the action immediately following the Convert time zone action, set the Configure run after to run after success and failure.

You will need to do this for every date field that you may want to reset.

Building Responsive(ish) PowerApps

Providing users with the ability to access content on their mobile devices has become common place and somewhat of a necessity today.  In the best case, it would be good to have a single solution that can serve multiple devices and orientations.  This article discusses how you can achieve this for PowerApps.




App Settings

Screen size + orientation

Start with creating a PowerApp Canvas App using a Phone layout and orient it to landscape.  On the Screen size + orientation layout page, make sure you disable Scale to fit.  Disabling this setting will allow your app controls to remain the same size regardless of the resolution.  Also disable the Lock orientation so your app will work in both portrait and landscape orientation.  You will notice near the top of the page that the resolution of the app is set to 1136 x 640.   Remember this number as we'll get back to it soon.



Advanced Settings

Next, go to the Advanced Settings tab and enable the Try the enhanced Group control.  Not only does this control help with accessibility, but it also allows you to position your controls on the screen  as a group.


At this point, you will be able to build an app that will have consistent component sizes.  On to the fun part. 

Dealing with screen size and orientation

Each screen has two properties that can be used to determine its size and orientation - Width and Height

Determining how to deal with screen sizes

When you query the Width and Height, it's easy to determine what orientation you are in.  If the Height is greater than the Width, then the orientation is portrait.  Otherwise, it's landscape.  For simplicity, you can store the orientation in a context variable for reference.

If(Screen1.Width < Screen1.Height,
UpdateContext({orientation:"portrait"}),
UpdateContext({orientation:"landscape"}));

Now to the device and screen size.  Responsive design uses something called Break Points and Media Queries to determine how to render a web page based on the browser size.  We can do something similar in PowerApps.  In my example, I decided that anything with a Width or Height greater than 2000 should be considered a tablet while anything smaller is a phone.  The exception is that if the width is exactly 1136 and the height is 640 then it's a Desktop.  The reason for this last scenario is that PowerApps renders the app using these dimensions in the browser.  So, I determined the device type as follows:

If(Or(Screen1.Width > 2000,Screen1.Height > 2000),
UpdateContext({device:"tablet"}),
If(And(Screen1.Width = 1136,Screen1.Height = 640),UpdateContext({device:"Desktop"}),UpdateContext({device:"phone"}))


This approach is conceptual and you can define other rules to determine how the app should render. 

What happens when a device changes orientation

So far, I've discussed how to determine the orientation and device type.  But when are these determined?  One way is to set them using the OnVisible property for the screen.  This will work, but will not change when the phone or tablet is changing orientation.  What you can do to address this is add a timer to your screen and add these settings to the OnTimerEnd property.  Set the Duration to be short (e.g. 100 ms) and make sure the AutoStart and Repeat properties are set to true so that the timer will begin as soon as the screen is displayed and will continue to check these settings.

Organizing your controls on a screen

Group components

Earlier in the article, I mentioned that the experimental Group components control should be used.  This control is great for developing responsive apps as it allows you to easily move groups of controls around on the screen rather than treating each one separately.

Make everything relative

Every control has a number of properties that are used to determine where it is positioned on a screen and how big it is.  As well, some controls that are text based have settings to indicate the font family, size, and weight.   When you add controls to a screen, these settings have default fixed values.  In order to allow you to better control the overall layout of your app, its better to use relative values.  This way, when one control changes, all other controls relative to it will change.  For example, in the image below, I am forcing the left group control to be 10px from the top and left of the screen border.  Likewise, I'm setting the Device and Screen Width labels to be 10px from the top and left inside their Group components controls.  All other controls are set to be spaced 10px apart across and down the page.  

For the Group components control on the right, I'm determining its location based on the orientation.  If it's a landscape orientation, then I set the X positions to be 10px after the end of the right Group components control.  Otherwise, I set it at 10px and below the right group control.  


So, for the example above, the Group component on the left is grp1 and the one on the right is grp2.  The positions for grp2 are determined as follows:

  • X: If(orientation = "portrait",10,grp1.X+grp1.Width+10)
  • Y:  If(orientation = "portrait",grp1.Y+grp1.Height+10,10)
Building on this approach, I also defined context variables for the font size and control widths and heights so that smaller devices will have larger fonts and boxes to make them easier to read.  The full expressions for the resizing in my case are:

If(Screen1.Width < Screen1.Height,
UpdateContext({orientation:"portrait"}),
UpdateContext({orientation:"landscape"}));
If(Or(Screen1.Width > 2000,Screen1.Height > 2000),
UpdateContext({device:"tablet",fontsize:30,height:50,width:300,width2:200}),
UpdateContext({device:"phone",fontsize:40,height:70,width:400,width2:300}));
If(And(Screen1.Width = 1136,Screen1.Height = 640),
UpdateContext({device:"Desktop",fontsize:14,height:25,width:140,width2:100,orientation:"landscape"}))

Conclusion

Using the techniques discussed above can, you can build apps that will resize their controls based on the device used and the way it's oriented.  The approach can be further expanded on to show/hide certain controls.

Working in different languages in Office 365



Sometimes, there's a need to work in a different language.  Often, the driving factor is to support  users in different locales.  So, how do you configure the language in for your favourite Office 365 apps?  Turns out the answer is not so simple.

In my tests, I have narrowed down the language configuration to four areas:
To see where things are set, I have configured my environment as follows:
  • Operating System - English
  • Browser - Spanish
  • Office 365 - Hebrew
  • Delve - German
While not exhaustive, I have summarized my findings of which configuration sets the language for which app below.

App OS Browser Office 365 Delve
Admin Center Yes
Delve Yes Yes
PowerApps Studio Yes
PowerApps Language() Yes
SharePoint Site Collections (Default) Yes Yes
SharePoint sub sites (default) Yes Yes
Excel Online Intro Page Yes
Excel Online Workbook Yes
Word Online Intro Page Yes
Word Online Document Yes
Yammer Yes
Teams Online Yes Yes
Teams App Yes
OneNote Online Intro Page Yes
OneNote Online Document Yes
Outlook Online Yes Yes
OneDrive for Business Yes
Flow Studio Yes Yes
Planner Yes
Calendar Yes
Staffhub Yes
My Analytics Yes

Considerations

There are a few considerations to mention around the language selection that may impact how you manage languages for your needs.

SharePoint Site Languages

When you create a new site collection, by default, no languages are selected and the site defaults to the Operating System language.  However, when a sub site is created, all languages are by default selected.


Cascading Settings

In some cases, setting more than one configuration can impact the language in which an app is displayed.  For SharePoint-based apps (SharePoint sites, Groups, OneDrive for Business), the language is controlled by two settings - Office 365 and Delve.  In the case, where both are set, these apps will take the Delve language configuration.  When not set, the apps will revert to the Office 365 language.  So, in my case, the Site Collections were showing up in English (operating system language), while the sub sites were showing up in German (Delve language).  Once I enabled the alternate languages in the site collections, then those sites also appeared in German.

Delayed Settings

Some settings don't take effect immediately.  In my test, it took as long as 15-20 minutes in some cases to see these changes.  So, when you're making changes and want to see if it works, give it some time. 

The order in which you set the language also matters.  In my testing, when setting the Office 365 language after the Delve language, I noticed that the Delve language is overwritten by the Office 365 language.

Conflicting Settings

In some apps, I noticed that different language configurations were impacting the apps at once.  In the case of Sway, the browser setting was taking over the general UI, while some of the default content was appearing using the Office 365 language.


Tracking your Agile project progress using Azure DevOps and Flow - The Sprint Backlog

This is the second post that discussed how information tracked in Azure DevOps, such as the sprint burndown chart and current sprint backlog can be exported to SharePoint using Flow.  This scenario applies to times when you want to share such information with clients without giving them direct access to Azure DevOps.
If you're new to Agile project management, I recommend you have a look at a book I recently published on this topic called Agile Office 365.  Besides providing you with an overview of Agile project management using the Scrum methodology, it provides you with lots of information on how to manage your Office 365 projects using these methods. 

To learn about ways to generate a sprint burndown chart, check out Part 1:  The Sprint Burndown Chart of this two-part series.

The Sprint Backlog

A product backlog is a tabular list that is used to capture the work that the team will be working on.  Throughout the life cycle of the project, the backlog gets re-prioritized over and over to ensure that the team's focus is on the scope that is most important to the business.  In Azure DevOps, the product backlog is typically broken down into 4 levels:

  • Epic
  • Feature
  • User Story / Bug
  • Task

During sprint planning, the top items from the product backlog are further broken down and scoped out to form the next sprint backlog.





Microsoft Flow includes 13 actions that allow you to query and update the items.  You can use those existing actions and leverage the Azure DevOps Analytics extension to make your life a little easier.  

The Ingredients

To build out the solution, I used 3 technologies from the Office 365/Azure stack

Azure DevOps

For those new to Azure DevOps, I recommend reading Introducing Azure DevOps.  One key assumption in my process was that all the tasks within a User Story or Bug would be completed within a single sprint.  During my sprint planning meeting, the User Stories/Bugs would be assigned to a specific sprint.

In order to build the sprint backlog with the User Stories and Bugs assigned to the current sprint, I created a custom query.  From those work items, I was able to retrieve all the tasks underneath them well as the parent Feature and Epic they belonged to.  To get the results, I had to compose the following query:

(Work Item Type = Bug) OR (Work Item Type = User Story) AND (Iteration Path = @CurrentIteration)


To build the proper hierarchy and report on progress, I needed to pull the ID, Work Item Type, Title, and State in my query. 


This query, which I called Current Sprint Activities is a key component to my Flow that I'm discussing further down in this article.

SharePoint List

The goal of the SharePoint list was to reconstruct the hierarchical nature of the epic/feature/user story or bug/task relationship.  To do so, I leveraged a custom SharePoint list with the following fields:

  • Title - to display the User Story or Bug name
  • Feature - to display and group the User Stories and Bugs by Feature
  • Epic - to display and group the Features by Epic
  • Status - to  display the current status of the item

Flow

Flow is used to populate the SharePoint list with the sprint backlog items.  The flow is broken down into 4 steps:
  1. Delete all existing list items
  2. Get the information from Azure DevOps
  3. Update the SharePoint list
In my case, step 2 was done by a separate Flow as Azure DevOps was on a different tenant.


Get the information from Azure DevOps

To make it easy to pass back the Azure DevOps information to the calling Flow, I was generating a JSON object that contained the information using the following structure:

[
  { "User Story Title" : "US1",
    "User Story State" : "S1",
    "Feature" : "F1",
    "Epic" : "E1"
  },
  { "User Story Title" : "US2",
    "User Story State" : "S2",
    "Feature" : "F2",
    "Epic" : "E2"
  },

...

  { "User Story Title" : "USn",
    "User Story State" : "Sn",
    "Feature" : "Fn",
    "Epic" : "En"
  }
]


The first step involved calling the query to get all the User Stories and Bugs in the current sprint.  



Next, I leveraged the Azure DevOps Analytics extension to make it easier to get the ID, title, and work item type of the parent item (Feature or Epic).  I could have used the Send an HTTP request to Azure DevOps action to get this information ,but would have required a bit more manipulation of the results to parse out the values.


By looping through the items from the first, I was able to compose the JSON object discussed earlier.



As a safeguard, I'm using replace() to escape any double quotes and avoid invalid JSON.  For example, for the title, I used replace(items('Apply_to_each')?['System.Title'],'"','\"')

As well, I used coalesce() in case the Feature or Epic were not set for a User Story or Bug.

Update the SharePoint list

Now that I have the Epic, Feature, User Story or Bug names and Status for each item, I can create new list items. In this case, I have chosen to use other Status terms (hence the if-expression)



To get the hierarchical view, I created a list view that is grouped by Epic and Feature.


The flow was originally set up to update nightly.  However, it could also be set up to run as soon as any changes are made to the backlog.

Conclusion

Using this method, I was able to share the current sprint backlog with my clients without giving them direct access to Azure DevOps.  This approach can be used to share other information from DevOps without giving any access beyond the project team.

Employee Check-in Simplified using Power Automate

Company employees who move between offices are often faced with a tedious process of having to check-in at the security desk to get a tempor...