Simple view with computed columns for date functions

In a recent development project we had to add query ranges and grouping on the week, month or year number dynamically. Initial thoughts were to use expressions in query ranges combined with date functions like wkOfYr. However after a few attempts it was clear that this was not the best solution to our problem. That is when we decided to use a view with computed columns.

We created a view and added the original table as a datasource. The idea was to add the week of year, month number and year number as computed columns and then use them in the query as normal fields.

The following are the steps you need to do to add a computed column for the week number:

  • First create a new integer computed column for the week number. Right-click on fields under the view and select new > integer computed column. Let’s call it MyComputedColumn for our example.
  • Then create a method on your view which will calculate the value for the computed column. The method must be set to private static of type string. For AX 2012 you should set the method to run on server. The method below is self-explanatory, it gets the SQL field name from the view and returns an SQL select statement to convert the date to week number using the SQL function DATEPART.
private static str myDateWeek()
{
        DictView dictView = new dictView(tableNum(MyView));

        str myTransDate = dictView.computedColumnString(
            identifierStr(DataSourceName),
            fieldStr(DSTableName, TransDate));

        return "SELECT DATEPART(WEEK," + myTransDate +")";
}
  • Next set the “View Method” property on the computed column you created earlier.
  • Finally build and db synch your code. Remember the view is created in the database under the views section. Here you can see the result of the computed column.
(CAST ((SELECT DATEPART(WEEK,T1.MyTransDate)) AS INT)) AS MyComputedColumn

For the month and year you can create additional computed columns with their respective methods.

"SELECT MONTH(" + myTransDate +")";

"SELECT YEAR(" + myTransDate +")";

Remember that a computed column works by adding SQL query syntax to the view. Therefore you need to use SQL functions like DATEPART(WEEK,%1) and not D365 FO/ AX functions like WkOfYr in the returned select statement. You can easily find SQL syntax functions on the web such as this one at w3schools.

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

Products and attachments in D365 Enterprise

Some newbies in the Dynamics world struggle to clearly understand the difference between products, product masters, product variants and released products and how these are saved in the data model. This post attempts to explain these differences.

The Product Information Management (PIM) module shows a list of menu items with different list pages to see products. Some are actually using the same list page EcoResProductListPage with different queries because they use the same tables.

The following table summarizes the main differences. Important to note that the main product information (not company based) is stored in EcoResProduct. Release products are in the InventTable per company (DataAreaId).

Products Menu Item

Description

Per Company

Data Model

All products and product masters

Distinct and master products

No

EcoResProduct where field ‘INSTANCERELATIONTYPE’ = DictTable::getRelationTypeFromTableName(tableStr(EcoResProductMaster)) OR DictTable::getRelationTypeFromTableName(tableStr(EcoResDistinctProduct))

Products

Distinct products that have no variants

No

EcoResProduct where field ‘INSTANCERELATIONTYPE’ = DictTable::getRelationTypeFromTableName(tableStr(EcoResDistinctProduct))

Product masters

Master products that have variants e.g. Women top (colour, size, style)

No

EcoResProduct where field ‘INSTANCERELATIONTYPE’ = DictTable::getRelationTypeFromTableName(tableStr(EcoResProductMaster))

Product variants

The different variants of the Product Master e.g. Women top Blue Medium

No

EcoResProduct where ProductMaster field is not empty

Released products

Released products per company (legal entity)

Yes

InventTable

You can see the actual query ranges when opening the interaction class of the EcoResProductListPage list page. The field “InstanceRelationType” is filtered to distinguish between distinct and master products.

EcoResProductMaster, EcoResDistinctProduct and EcoResDistinctProductVariant are all extending the EcoResProduct table. Therefore if you had to look up for this information directly from SQL in a development environment you will find all the fields in the EcoResProduct table as shown in the example below. Note the differences in the ProductMaster and InstanceRelationType fields/columns. I added the first column Product_Type to make it more clear.

File attachments

I included this section because its interesting to see how attachments are linked to Products using the DocuRef table. The DocuRef table uses the fields RefRecId and RefTableId to link to the respective product table with the attachment (file, image etc) . In the example below you can see the end result after uploading the same file ‘Speaker010720.jpg‘ to different product types.

Note that images were attached whilst the user was in the USMF company so all images have the ActualCompanyId field as ‘USMF’, but only the released product and released product variant have ‘USMF’ in the RefCompanyId field. The product master and distinct product both have ‘DAT’ company since they are not saved per legal entity.