D365 HR FinOps CDS Integration : Resolving date time conversion issue

The integration between Microsoft Dynamics 365 Human Resource (Talent) and Finance & Operations (FinOps) is pretty much out of the box. Talent entities are instantly synched with the Power Platform entities (read more about it here), and the integration from Talent to FinOps can be configured from the Power Platform admin portal (more info on the official Microsoft docs). There is an issue up till now April 2020 (FinOps v10.0.9) that causes the date fields to be formatted wrongly in FinOps if you are not working with en-us date format and UTC time zone.

Date and Time in UTC

When Talent writes a date and time value to the database, it stores the data in UTC and en-us format. This allows users to see any Date and Time data relative to the time zone defined in their user options. As per MS docs, HR > Understand Date and Time fields, when using external sources like DMF to view or author data, it is important to keep in mind that the Date and Time values are considered by default to be in UTC regardless of the time zone of the user’s computer or their current user time zone settings.

The date conversion issue

The en-us date format and UTC time zone cause an issue if you are using a date time zone other than UTC/en-us. The data integration between CDS and FinOps takes the UTC date and converts it to the locale format and FinOps DMF settings converts it again. This dual conversion is causing the issue.

The following is an example using the en-GB date format and UTC+1 time zone for the Job Position entity (activation and retirement date fields).

We first create a job position 000729 in Human Resource with duration 2nd March 2020 to 7th May 2020 in UK date format.

Human Resources – Job Position 000729

The dates for job position 000729 look 1 hour earlier due to UTC time zone in CDS, therefore the user sees them from 1st March 23:00 to 6th May 22:59.

The problem occurs in FinOps because the resulting date range after the integration with CDS is 4th January to 6th June 2020. Not only there is a time zone difference but also the day and month values swapped which creates completely wrong dates.

FinOps – Job Position 000729 showing wrong dates in UK format after integration with CDS entities

Issue resolution

Until there is an official fix for this this, fortunately you can resolve it with a configuration in DMF data source properties. If you look at the DMFEntityWriter class it uses the DMFDataSourceProperties table in FinOps to convert the date time. We therefore need to tell DMF that the date format we need is en-us & GMT time zone to avoid the second conversion.

Let’s first check which source data format is the CDS integration using. By looking at the import projects job history you can see that the source data format used for the standard CDS to FinOps integration is CSV-Unicode.

By double-clicking on the source data format (or from Data Management workspace > Configure Data Source tile > select CSV-Unicode source data format) you will open the DMF data source properties form. Change the timezone to Coordinated Universal Timezone and Language as En-US to avoid the second conversion from regional settings as shown below.

In summary, to resolve the issue :

  • Open D365 FinOps
  • Open the Data Management Workspace
  • Click on the “Configure data source” tile
  • Select and click on CSV-Unicode
  • Change the Timezone to Coordinated Universal Timezone and Language as En-US in the regional settings tab

Extending Talent: Custom fields in CoreHR

D365 for Talent Architecture

No development is required to add custom fields in Talent. In fact, a user or consultant can easily do this too using the provided personalisation functionality in Talent and the Power Platform. The process is very similar to adding user defined fields to D365 for Finance and Supply Chain.

Before we go ahead it is best to explain some points on the Talent architecture. Talent comes in 3 parts: CoreHR, Attract & Onboard. CoreHR was built on top of the same HR stack as D365 Finance and Operations (FinOps). It has a subset of data entities (tables) from the FinOps platform. These entities are then pushed to the Common Data Service (CDS) platform automatically using a Data Sync service. On the other hand, Attract and Onboard data is saved directly in CDS entities through OData calls.

Talent Architecture (Microsoft Business Applications Summit June 2019)

Till this date (November 2019), adding custom fields is only available in CoreHR and not in Attract or Onboard. You can add custom fields of different types including text, numbers, dates and checkboxes but you cannot add lookups.

The following list of entities in CoreHR are in CDS and can be extended:

  1. Benefit entities
  2. Worker entities
  3. Position entities
  4. Job entities
  5. Leave and absence entities
  6. Organization entities
  7. Payroll entities
Core HR entities (Microsoft Business Applications Summit June 2019)

Refer to https://docs.microsoft.com/en-us/dynamics365/talent/corehrentities for the updated list of entities as these can change from time to time.

To explain the process of adding custom fields we will go step by step through a real example of how to add a new field to the worker to store the “Number of years of work experience”. This means a new numeric field “WrkExp” on the HcmWorker table in CoreHR which will then be synched to the cdm_worker entity in CDS.

Adding a custom field

Sign-in to Talent and go to the form where you would like to add the field. You can either:

  1. Right-click on the control (e.g. grid) of the form and select add column from the menu, or,
  2. You can also click on the Options tab from the Action Pane, click on Personalize this form, click on the “+ Add a field” button and select the control where you want to add a column.

In both cases this will open the “Add columns” dialog slider.

From the “Add columns” dialog click on the “+ Create new field” link. This will open another dialog to create the new field. Here you will specify table name, the name of the field (Name prefix + “_Custom”), choose the type of field (numeric in our case), enter the label and help text.

This will add the new custom field to the form that you just selected. At this stage the new field is only visible to the user currently signed.

Sharing custom fields with other users

To share the new field with other users we will need to use personalisation. An Admin user needs to go the System Administration module > Setup > Personalisation.

Select all the users that should see the new custom field and apply the same personalisation settings from the selected user/form.

Sync with CDS

To sync the custom field with the CDS database (from HcmWorker to cdm_worker) go to System Administration > Setup > Custom Fields using an Admin account. When the form opens, select the table (HcmWorker in our case) and custom field that we just created. In the entities tab enable the CDS entity which starts with the “cdm_” prefix. Click save and apply changes. It can take up to 5 mins to see the custom field in the CDS entities.

To verify, open the PowerApps platform from http://powerapps.microsoft.com/ (make sure you have the PowerApps Plan 2 license on your Office365 account). Go to the Entities tab under Data and find the cdm_worker entity. Select the correct environment (Sandbox/Prod) to see the data and select All entities from the search bar.

When you open the open you should see the custom field created earlier. Custom fields can be easily identified as they will have the “_custom” suffix.

Article written for Bluefort Malta