Dataverse: Update Time zone adjustment from Time zone Independent to User Local and Date time Behavior

Hello everyone! I’m sharing my observation regarding Dataverse Date time (again). Previously, I updated several DateTime attributes from Time zone adjustment “User Local” (which is the default value) to “Time zone independent“. Then, my functional reported that the Date Time seemed weird as the Created On of the record vs the “Time zone independent” value has a gap and requested me to update again to “User Local“. But, from UI operation being blocked (I believe this couldn’t be done as MS doesn’t want to deal with data issues). But, before we go to how to change back, I also want to share things I learned about DateTime.

Once set as “Time zone independent“, we can’t set it back to “User Local“

First, I created the below table (as you can see, I have User Local Date, TZ Independent Date, User Local Date Only, and TZ Independent Date Only) and inserted the data from UI:

Data from UI vs SQL 4 CDS by Mark Carrington

As you can see, the TZ Independent Date-related values will be saved into DB without converting to UTC.

BTW, In SQL 4 CDS, we also have a feature to show the DateTime as UTC times/Local times (by default will be “UTC times”):

SQL 4 CDS setting to Treat date/time value as UTC times/Local times

And, for example, you have a Console application/WebAPI that will deal with inserting/updating data. You need to make sure to always set the Date as your current user TimeZone and convert it to UTC Time (in the below code, I’m using Betim Beja’s method that you can check here):

var userId = new Guid("F9E7905C-CF7A-EE11-8179-002248201668");
var userTimeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneDefinitionQueries.GetTimezoneByUserId(userId, service));

var result = service.Retrieve("tmy_transaction", new Guid("2ce5c827-af14-ef11-9f89-7c1e5218d596"), new ColumnSet("tmy_userlocaldate", "tmy_tzindependentdate", "tmy_userlocaldateonly", "tmy_tzindependentdateonly"));

var updated = new Entity("tmy_transaction", result.Id);
updated["tmy_userlocaldate"] = DateTime.UtcNow.AddHours(-4); // around 5 AM
updated["tmy_tzindependentdate"] = userTimeZone.ConvertToLocal(DateTime.UtcNow).AddHours(-4); // around 5 AM and using DateTime.Now as my local value same with my settings
updated["tmy_userlocaldateonly"] = userTimeZone.ConvertToLocal(DateTime.UtcNow).Date.ToUniversalTime();
updated["tmy_tzindependentdateonly"] = userTimeZone.ConvertToLocal(DateTime.UtcNow).Date;
service.Update(updated);

And here is the result from UI and SQL 4 CDS looks like:

UI vs SQL 4 CDS

Back to the main topic of how to set the “Time zone independent” to “User Local“. FYI, this operation modifies the Customizations XML directly, it is not an official way to do it (do it at your own risk). First, you need to export the Unmanaged solution of your Dataverse table. Once you get the .zip, extract the zip and you can inspect the XML.

In the below screenshot, I’m comparing the DateTime “User Local” vs “Independent Time zone“. As you can see, the difference is just in the tag “Behavior” whose values of 1 being “User Local” and 3 being “Time zone independent“.

User Local vs Time zone independent

So, to change the behavior back to “User Local“, you just need to change the value from 3 and set it to 1. Once done, you can zip and import it to your Dev environment > Publish All Customizations.

Here is the result of it:

TZ Independent Date updated to “User Local”

And if you are wondering what it looks like in the UI, here is the result of the existing data:

Result

Hope you are learning something today! Happy CRM-ing!

Dataverse – Convert UTC Time to User Timezone in Plugin

Today, I’ll share a quick tip on converting the UTC date to the User’s Timezone settings in the Plugin (once you read the logic, you can also implement this logic into Power Automate/Custom API). In Dataverse, we can set our timezone in the below UI (go to Settings > Personalized Settings > on General Tab, you can select “Set the time zone you are in“):

Set the user’s time zone

[Low Code] Send Translated Message to User using App Notification

Today we will learn how to create a Dataverse Instant plug-in using the AITranslate function. The purpose of AITranslate is to translate a message to the targeted language. For today’s scenario, we will learn how to query the selected Dataverse User’s UI Language and send the translated message using XSendAppNotification. FYI, 2 years ago I once wrote a similar topic that consumed the Azure Cognitive Service and used it in Power Automate Flow which you can read here. Since then, we have had the option to use it directly in Dataverse which is exciting! 😎

Build a Dataverse Plugin and use AIClassify to simplify business process

For those who don’t know, now we can call AI functions within Dataverse which helps to simplify lots of business processes. One of the scenarios that we will learn today is regarding Case/Feedback/Incident creation where users can just put minimal information such as “Title“, “Description“, and “Customer” and let the AI help us to set the correct “Subject” to route the case to the specific department (this is another process which not being demonstrated in this blog post). For example, we have “Problem“, “Billing“, “How To“, and “Licensing“. Then, if none of the categories are found, we can route the Case to “Default Case” which the team in this Department helps to clarify/route the Case to the correct Team.

Dataverse: Create an API to update Base Currency

This time, I’ve got a request from Trung Dũng Nguyễn where he wants to update the base currency based on the updated Currency inputted into the Transaction Currency table. When I check on the system behavior, the Currency (Base) information will be updated as long as we update at least one “Currency” attribute. Please note that because we need to update the Currency attribute, it might trigger the plugin step you set on that entity.

Dataverse: About Masking Rule 💎

I found an undocumented feature in Dataverse while exploring a topic for today’s blog post. The feature basically helps us to mask the value if the value matches with the RegEx (Regular Expression) that we are set. This feature can be handy if the customer has a strict requirement to not allow anyone to read some type of value. For example, you want to hide Email, Social Security Number, etc.

First, I create a new string attribute named “tmy_demostring” and set the “Enable column security” to check:

Let’s learn about the Dataflows

Have you ever heard about Dataflows? Dataflows is an automation to copy (from various data sources) > Transform (Power Query Transformation – which leverages the same technology with PowerBI) > and paste (to Dataverse/Power BI workspace/Azure Data Lake Storage Account). I will not re-invent the wheels with the diagram that I took from the documentation (which is already self-explanatory):

The official documentation said that we selected more than 80 data sources. The image below is a sample of the data sources: