Date Time Behavior and Format in Power Platform/Dynamics 365 CRM

Date Time field in power apps has behaviors and formats which control how the data will be stored in CDS (Common Data Service) and how it will pe presented to different users from different time zone on retrieval. Here I will try to explain the same in a simple way with examples for each combination of behaviors and formats.

Behavior: User Local

1. Format: Date Only
   • User’s provided date gets converted to UTC before it gets saved in CDS.
   • As there is no time part in user input system will take midnight as input time for conversion.
   • Stored data in CDS will have both date and time part.
   • System will convert UTC time to requesting user’s time zone while displaying data in UI.
2. Format: Date and Time
   • User’s provided date and time gets converted to UTC before it gets saved in CDS.
   • Stored data in CDS will have both date and time part.
   • System will convert UTC time to requesting user’s time zone while displaying data in UI.

Behavior: Date Only

1. Format: Date Only
   • User’s provided date gets saved in CDS as it is.
   • Same date value gets displayed to all users irrespective of their time zone settings.

Behavior: Time-Zone Independent

1. Format: Date Only
   • User’s provided date gets saved in CDS as it is, and system append midnight in the time part.
   • Same date value gets displayed to all users irrespective of their time zone settings.
   • Stored data in CDS will have both date and time part (time part will always be 00:00:00).
2. Format: Date and Time
   • User’s provided date and time gets saved in CDS as it is.
   • Stored data in CDS will have both date and time part.

Example:

I took this example to illustrate how values are stored in CDS and how it gets converted to different time zone based on user’s time zone settings.

• For easy understanding I used all times in 24hr format.
• This example shows what is the impact of user’s time zone on insert/ update/retrieval of date values,

Field Behavior	Field Format	Example
		Insert by User 1 (Time zone – GMT + 4:00).		Value seen by User 1 on UI (Time zone -> GMT + 4:00).	Value seen by User 2 on UI (Time zone -> GMT – 10:00).	Value seen by User 3 on UI (Time zone -> GMT + 13:00).
		Value entered in UI	Value stored in DB (UTC)
User Local	Date Only	6-Sep-20	9/5/2020 20:00	06-Sep-20	05-Sep-20	06-Sep-20
	Date and Time	05-Sep-2020 06:00:00	05-Sep-2020 02:00:00	05-Sep-2020 06:00:00	04-Sep-2020 16:00:00	05-Sep-2020 15:00:00
Date Only	Date Only	4-Sep-20	04-Sep-2020.	04-Sep-20	04-Sep-20	04-Sep-20
Time-Zone Independent	Date Only	3-Sep-20	03-Sep-2020 00:00:00	03-Sep-20	03-Sep-20	03-Sep-20
	Date and Time	02-Sep-2020 10:30:00	02-Sep-2020 10:30:00	02-Sep-2020 10:30:00	02-Sep-2020 10:30:00	02-Sep-2020 10:30:00

Here is some important point to note

• Once a date time field behavior is set to either “Date Only” or “Time-Zone Independent” it cannot be changed.

  

• Date time field with behavior “User Local” can be changed to “Date Only” or “Time-Zone Independent”. You must evaluate the impact before you make this change. Also, you must review all process like BPF/Workflow/Power Automate etc. and re-activate it to reflect field behavior changes in the process.

  

• If you change date time field behavior “User Local” to “Date Only” or “Time-Zone Independent”, it will only affect new data modifications any existing data stored in this field will not get affected.
• Within a behavior one can switch the format at any time. You can change format from “Date Only” to “Date and Time” and vice versa.

  

• “Behavior: Date Only, Format: Date Only” and “Behavior: Time-Zone Independent, Format: Date Only” behave similarly except one thing that later have time part (always 00:00:00) when it is saved in CDS.

Thanks for reading, hope you find it helpful.

Cheers

Use column comparison in D365 CE/Power Apps/CDS queries

Microsoft has finally given option to compare two columns while querying data from D365 CE/Power Apps/CDS. Here I am sharing my experiences and examples of how it can be used from various channels

1. Power Apps UI
2. Web API
3. Fetch XML
4. SDK API (.net)

Power Apps UI: This feature is not yet available in PowerApps UI (Advance Find), hope It will be available soon

Web API: This is a sample example of how you can compare columns of various data types in web API

1. String field to String field: here is the sample where I compared “firstname” column with “lastname” and “mobilephone” with “telephone1″ and it worked like a charm <a rel=”noreferrer noopener” href=”https://<https://<<YourOrganization>>/api/data/v9.1/contacts?$filter=firstname eq lastname and mobilephone eq telephone1&$select=firstname,lastname
2. Other data types: I tried other data type field comparisons as mentioned below
   • DateTime – ex. createdon – modifiedon
   • Whole Number (Integer)
   • Option Set (Picklist)
   • Two Options (Boolean)
   • Floating Point Number (Double)

But Web API is giving error message “The right side of the \”Equal\” operator must be a constant value”, seems like Microsoft has not yet enabled this feature fully in Web API yet.

Fetch XML: This is a sample example of how you can compare columns of various data types in Fetch XML. I tried to create one query which covers following comparison scenario and it worked smoothly

1. Text to text comparison
2. 1 column ‘telephone1’ being compared with 2 columns ‘mobilephone’, and ‘fax’.
3. Float to Float comparison
4. DateTime to DateTime comparison
5. Optionset to optionset comparison
6. Optionset to whole number comparison
7. Lookup to Lookup comparison
8. Field comparison in a link entity

Query:

Response:

CRM Contact Entity which was returned in above response:

For more details on fetch XML you can refer Nishant’s Blog.

SDK API: Microsoft has shared a new API for this, you can use this to compare two column in query

public ConditionExpression
(
  string attributeName,
  ConditionOperator conditionOperator,
  bool compareColumns,
  object value
)

Example Query Expression:

var query = new QueryExpression("contact");

query.ColumnSet.AddColumns("firstname", "lastname");

query.Criteria.Conditions.Add(new ConditionExpression("firstname", ConditionOperator.Equal, true, "lastname"));

var results = client.RetrieveMultiple(query);

For details explanation on how to compare columns in query expression, I suggest you refer Debajit’s Blog.

Here is some observation I had while testing this feature and limitation of this feature:

1. You can only compare columns within a single entity. IE you cannot compare column from contact entity to column in its parent account entity.
2. As per Microsoft documentation only 2 columns can be compared at a time but it did not says how many condition can be added. I tried with 12 column comparisons (9 conditions) and it worked so I guess there is no limitation on number of conditions.
3. Multi-value condition operators are not supported (i.e., “in”).
4. Extended condition operators are not supported (i.e., “creditlimit > spends+1000”).
5. Only column of compatible data type can be compared, like
   • Whole Number to Whole Number
   • Text to Text
   • Whole Number to option set
6. If you try to compare two incompatible data type columns you will get below error (below is the error while comparing Text to Whole Number).
   • SDK Error Message: “Attributes of types “System.String”, ” System.Int32″ are not supported for comparison”
   • Web API Error Message: “A binary operator with incompatible types was detected. Found operand types ‘Edm.String’ and ‘Edm.Int32’ for operator kind ‘Equal’.”
7. You can user column comparison in nested queries.
8. In Web API field comparison is only working for string data type only.
9. While comparing option set, option set numeric values are being compared not the Label (which is how it should be).

Disable Export and Print button in Dynamics 365 CE (CRM) report viewer.

In one of my recent project, there was a requirement to disable/remove export button from report viewer (Only for certain set of users).

To achieve this, we had to remove below two permissions from all roles assigned to those users. You can remove one or both the permission to match your need.

1. Export to Excel (to remove download option from report viewer)
2. Print (to remove print option from report viewer)

Before removing permissions

After removing permissions

Thanks

References:

1. https://www.powerobjects.com/2016/08/22/how-to-disable-exporting-or-printing-reports-in-dynamics-crm-2016/

Create XrmToolBox Plugin – Part 1: Set-up Visual Studio and create a basic plugin

Recently I created my first XrmToolBox plugin User Security Manager and successfully released this to the plugin store. I followed XrmToolBox for developers and the other resources available on the internet but creating a plugin was not so smooth for me, so I thought of writing my experiences and detailed step by step guide.

I will detail the entire process in 4 different blogs this is my first blog in this series.

1. Create XrmToolBox Plugin – Part 2: Debug XrmToolBox Plugin
2. Create XrmToolBox Plugin – Part 3: Package your plugin
3. Create XrmToolBox Plugin – Part 4: Setup build definition release fo CI and CD

Prerequisites:

1. Visual Studio 2017

Step 1: Download and install XrmToolBox Plugin Project Template: download VS 2017 project template and install on your dev machine.

Step 2: Open VS 2017 and go to File -> New -> Project…, it will open a new project window.

1. Click on “Installed” (1) template and search for “Xrm” (2). You will see the above-installed plugin (3).
2. Give a meaning full name to your plugin (4) and click OK(5).

1. Click on “Trust” if you get following warning message.

Step 3:
Update Plugin Metadata open file “MyPlugin.cs” and update the following metadata details

1. Name: Name of your plugin. It will appear in XrmToolBox plugin list and plugin store.
2. Description: Short description of your plugin. It will appear in XrmToolBox plugin list and plugin store.
3. SmallImageBase64: 32*32 logo of your plugin, to generate a base64 string of your plugin logo you can use this URL.
4. BigImageBase64: 80*80 logo of your plugin, to generate a base64 string of your plugin logo you can use this URL.
5. BackgroundColor: background color of your plugin
6. PrimaryFontColor: Primary font color of your plugin
7. SecondaryFontColor: Secondary font color of your plugin

Step 4: Build your plugin logic At this point you are all set to start building your logic into the plugin. You can create UI and add code logic but still, you cannot run/debug it. In my next blog I will talk about debugging, but before we go there let’s elaborate on the various components of this project, do’s and don’ts. (below is how default project structure will look like)

1. MyPlugin.cs: This file contains all necessary metadata that defines your plugin, please make sure you have provided all the values in this file as described above.
2. MyPluginControl.cs: all your plugin logic goes here or gets invoked from here.
   1. If your plugin logic is simple and does not requires much coding you can put everything in this class file (this is just my opinion).
   2. If your plugin logic is complex, and you are using some design pattern then this is the class where you will initialize your child components, controls.
   3. MyPluginControl_Load: You can put your initializing logic here. Like configuring the plugin as per the user settings, or resuming the plugin last used state etc.

      

   4. MyPluginControl_OnCloseTool: Your plugin close logic (if any) goes here.

      

   5. UpdateConnection: If you want to configure the behavior of your plugin on change of CRM connection, then you need to inject your logic here. For example, if you want to reload data from new CRM organization on XrmToolBox connection change, you can invoke that logic from this function.

      

   6. If your plugin needs multiple organization connections (one such example is if you are creating a plugin to move data from once CRM org to another CRM org), then you need to inherit “MultipleConnectionsPluginControlBase” class and implement “ConnectionDetailsUpdated” method. By default “MyPluginControl” class inherits “PluginControlBase” base call which provides access to only one Organization connection at a time.

      

      

      If you inherit “MultipleConnectionPluginControlBase” then you need to implement “ConnectionDetailsUpdate”

      

3. Do’s and Don’ts:
   1. To ensure that Organization service is available when you execute your logic use “Service.Execute(<<your function name>>());” instead of invoking you function directly.

   

   1. Your plugin logic should be asynchronous, it should not block the tool itself. Use function “WorkAsync” and class “WorkAsyncInfo” to invoke your logic asynchronously, for details see here.

   

   1. Add proper logging to your plugin. “PluginControlBase” class provides 3 helper methods that you can use.

   

   1. Do not use early bound classes with your plugin.
   2. Your plugin should open with or with or without a connection available in XrmToolBox.

Hope It helps. In the next blog, I will explain how to debug your plugin in visual studio.

User Security Manager 1.2018.7.16 – Added features to manage Field Security Profile and view and export User security report.

I am excited to share that latest version of our XrmToolBox plugin “User Security Manager” is ready and available to download in XrmToolBox plugin store.

User Security Manager

We have implemented the following enhancement in the latest version

1. Mange user’s field security profile, along with user security roles and teams in a single window.
2. View user security report, sort and filter report for selected users.
3. Download user security report (user to role mapping, users to teams mapping, users to fields security profile mapping), in csv format.

For more details of tool’s latest feature and user guide, please click here for complete user guide of this tool click here

We love to hear from you, please update/install it use it and share your feedback