Tip #1205: Local time in Flow using Common Data Service

Remember the times when you wake up and struggle to figure out where you are or what time it is? Of course, you don’t. I personally can’t recall those because lack of space and time awareness goes hand in hand with the selective amnesia. We did create a flow to help you on those occasions.

That flow worked using trigger tokens (and Bing Maps API) that work when you actually push the button on a mobile device. Sometimes you don’t have a button to push, or time is unrelated to the flow itself. I am talking about datetime fields in Common Data Service, of course. Those are UTC as well so how do we convert the values into the realm of a user? It could be the user who pushed another button, or user who invoked a flow from their model-driven app, or simply the user who created the record in the first place.

Why does it matter, apart from the reasons we described in tip #1141? Imagine sitting on a beach in Hawaii, doing the numbers and, bingo!, you close that elusive sale in the evening on September 30, just in time to make your quota for the quarter. Except that you didn’t because it was already October 1 in the UTC timezone so here goes your quarterly bonus.

Since we don’t have the user location, we’d have to use user options that actually exist precisely for the purpose of localizing datetime values:

image

Getting from a user to the timezone in the format we need is not a one-step operation so these are the steps: (and remember I promised to cut down on the screenshots so here we are):

  1. Manually trigger a flow. Self-explanatory. Could be When Record is Selected CDS trigger, could be something else.
  2. Get my profile action. Gets O365 profile of the current user running the Instant Flow (i.e. triggered manually).
  3. List Records action on Users (systemuser) entity to get the list of user records for the current user (i.e. list of exactly 1 record). We could filter records by email provided by the manual trigger but that will fail miserably for B2B guest users.
  4. Initialize variable action to declare ExecutingSystemUserID variable and set it to the current system user id. Keep your flow tidy.
    Note: step 2-4 are courtesy of my bad masterclass crony, David “Xrm.Tools” Yack. They apply to Instant Flows when you have the executing user. For automatic flows you would use createdby or ownerid , or some other field containing the target user id.
  5. List Records action on User Settings (usersettings) entity to get user settings include mysterious timezonecode.
  6. List Records action on TimeZone Definitions (timezonedefinition) entity to get the name of the timezone.
  7. Initialize variable action to declare User Timezone variable. Again, keep your flow tidy. Now we are ready!
  8. List Records on account entity. Completely optional action extracting first active account owned by the user just to demonstrate the technique.
  9. Convert date time action to get the local value for createdon.

Result:

image

Since we have limited number of pictures, you can download, import, and dissect the flow in your own environment. You’ll need to provide your own Office 365 Users and Common Data Service connections.

(Cover photo by Heather Zabriskie on Unsplash)

Leave a Reply

Your email address will not be published. Required fields are marked *