String format options for utcDateTime

I’ve run into a problem that reminded me that while X++ types and corresponding CLR types (such as str and System.String) can often be user interchangeably, they aren’t the same.

I was trying to convert a utcdatetime value to the standard “sortable” format in D365FO, for which I wrote the following code:

utcdatetime currentDateTime = DateTimeUtil::utcNow();
str s = System.String::Format('{0:s}', currentDateTime);

What I expected was something like 2019-02-03T17:29:00, but I got a very different format – 02/03/2019 17:29:00. What was wrong with my code?

The format definition is all right… but I don’t have the right type! I can easily fix the problem by declaring currentDateTime as System.DateTime instead of utcdatetime.

System.DateTime currentDateTime = DateTimeUtil::utcNow();
str s = System.String::Format('{0:s}', currentDateTime);

When Format() method is called, it’s able to utilize special formatting options (such as the “s” format), if the type of the value has methods that knows what do to it with it. System.DateTime knows what to do, but utcdatetime is actually Microsoft.Dynamics.Ax.Xpp.AxShared.utcdatetime structure which isn’t able to handle these options. It has a single hard-coded format for converting the datetime value to a string.

While I’m still working with the same value in both utcdatetime and System.DateTime, they’re two different types with different behavior and a conversion may be needed if I need behavior specific to only one of them.

I tested this behavior on Platform Update 22 and 24.

Splitting .xpo files

I got an .xpo file from an older version of AX with some code of interest and because it had a few thousand lines, it wasn’t exactly easy to navigate. At least splitting it by object would make my life much easier.

Fortunately I looked at the internet and found exactly the right tool for this task: xpoTools.

It’s not well-documented, but what I needed didn’t require much anyway. After installation, I simply ran the following (Powershell) code:

cd c:\Temp\XPO\
ls *.xpo | Import-Xpo | Split-Xpo -Xpp

Import-Xpo parses xpo files to objects expected by Split-Xpo, which creates a file for every object. The -Xpp flag means that the result are not .xpo files, but rather more readable files with pure source code (without all those # characters and things like that).

This might be the last time I used this tool, but it did help me today. As often, a single search on internet saved me a lot of time.

Thank you, mazzy.

Event handler for multiple events

I’m not sure that everybody is aware that a single event handler method (in Dynamics 365 for Finance and Operations) can be decorated with several event handler attributes.

For example, a common method can handle both inserts and updates.

    DataEventHandler(tableStr(MyTable), DataEventType::Inserted),
    DataEventHandler(tableStr(MyTable), DataEventType::Updated)
public static void myHandler(Common _sender, DataEventArgs _e)

Or it can handle similar events from different sources, such as different tables:

    DataEventHandler(tableStr(InventItemSalesSetup), DataEventType::Inserting),
    DataEventHandler(tableStr(InventItemPurchSetup), DataEventType::Inserting),
    DataEventHandler(tableStr(InventItemInventSetup), DataEventType::Inserting)
public static void myHandler(Common sender, DataEventArgs e)
    InventItemOrderSetupMap inventSetup = sender;

If you want to run the same logic, simply using the same method makes good sense.

Cleaning up cross-reference DB

Development VMs for Dynamics 365 for Finance and Operations come with a cross-reference database that doesn’t match the actual code and metadata. There are many annoying references to Microsoft’s own test models, which aren’t included and therefore such references are useless and make finding real references more complicated. (The idea to get it fixed is quite popular and currently tagged as Planned).

I wanted a quick way to get rid of these references, therefore I simply deleted them from DYNAMICSXREFDB database:

    JOIN Names ON (Names.Id = [REFERENCES].SourceId OR Names.Id = [REFERENCES].TargetId)
    JOIN Modules ON Names.ModuleId = Modules.Id
    WHERE Module LIKE '%Test%' AND Module <> 'TestEssentials'

It ran for seven minutes on my local VM (with database recovery mode set to Bulk-logged).

Reference group and GROUP BY

This blog post explains a problem that you can run into when using reference group controls with grouped data – and a solution for this problem.

I have a table which stores references to workers. The field has HcmWorkerRecId data type, i.e. it stores record IDs from HcmWorker table. When I drop this field to a grid on a form, the system uses Reference Group control, which shows human-readable data instead of RecId numbers. By default, it shows worker names.

There are multiple records for the same worker, therefore if I want to show data summarized data for each worker, I add grouping to the query:

TableWithRef_ds.queryBuildDataSource().addGroupByField(fieldNum(TableWithRef, Worker));

But the result isn’t correct – the reference group doesn’t show anything.

The grouping works correctly; I can see the right record IDs if I display them thought an Int64 control instead of a reference group.

The problem is that we group only by the record ID, but not by Name. And because Name is neither used in GROUP BY nor it has an aggregation function applied, its value is undefined and there is nothing to show.

Let’s fix it. Go to the data source in AOT and add reference data sources. Because Name field isn’t directly in HcmWorker table, we’ll need one more data source – DirPerson. Like this:

You can add them by right-clicking the Reference Data Sources node, choosing New Reference Data Source and then setting properties Join Relation and Name.

Then we can easily add an extra field to group by, this time from DirPerson table:

DirPerson_ds.queryBuildDataSource().addGroupByField(fieldNum(DirPerson, Name));

Voilà, names are back!