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!

“in” operator in D365FO

Yesterday Michael Fruergaard Pontoppidan published a brief blog post New capability in X++ : The In operator. He mentioned that this feature went unnoticed by most, which is my case too. And I didn’t find anything even when I explicitly looked for more information.

Anyway, such an operator can be very handy and I’m quite sure that everybody who knows IN() operator in T-SQL sometimes missed it in X++.

In short, it allows you to check if a field value is in a set of expected values. For example, if I want to find all sales orders with status either Delivered or Invoice, I can do this:

SalesTable st;
container statuses = [SalesStatus::Delivered, SalesStatus::Invoiced];
select from st
    where st.SalesStatus in statuses;

The generated code is what you had to do when there was no ‘in’ operator – it uses OR:


I wanted to know what else I can do with this operator and because I didn’t find any documentation, I tried a few things by myself. Note that my environment has platform update 20; it might behave differently in different versions.

I wondered if I can’t use a container directly, instead of putting it to a variable. It would make things simpler if the set is known at design time.

select count(RecId) from st
    where st.SalesStatus in [SalesStatus::Delivered, SalesStatus::Invoiced];

The editor didn’t show any error, but compilation blew up completely:

Abnormal termination with unhandled exception. Exception key: a59d89f7-fe80-4ab3-a420-9a6ba9a30bca. System.NullReferenceException: Object reference not set to an instance of an object.

Hmm, let’s try something else. Which data types can I use? What about a set of string values, which is a common scenario?

container ids = ["S01", "S02", "S03"];
select st
    where st.SalesId in ids;

This fails already in the editor. The compilation error is: Types ‘str’ and ‘container’ are not compatible with operator ‘in’. 🙁

The last thing I tried was using ‘in’ operator outside a query. I didn’t expect it to work and it indeed doesn’t. This doesn’t compile; it fails with ‘)’ expected.

if (highestStatus in statuses)

The ‘in’ operator is useful and I’m glad it’s been added. But I see space for improvement, especially the ability of using it with string fields would be very useful.

Throwing managed exceptions from X++ in D365FO

What I really miss in X++ is the ability to throw exception objects. If you throw an exception in X++, it’s just a number defining what kind of exception it is, which usually says just “Error” (Exception::Error). You also typically add a message to infolog, but the message in infolog and the exception don’t have any link.

Other object-oriented programming languages (such as Java, C# or Python) do it in a much better (object-oriented) way. You throw an object, which contains a lot of information about the error – the type (such as FileNotFoundException), a message, extra details such as the argument name of ArgumentNullException, a stack trace showing which sequence of calls led to the error and so on.

What I consider the most important is the type. This is necessary for meaningful recovery from errors. For example, you’ll go back to user when an error says that an input is invalid, while you may wait a while and try a request a bit later if you know that the error is about a network failure. If all you know is that there is an error, you can’t handle different errors in different ways; you can either stop execution when an error occurs or you’ll ignore all errors. Obviously, neither is ideal.

And other information besides type are is useful too. You may want to log stack trace of where the exception was thrown (not caught), you may want to know which parameter has a wrong value and so on.

I mentioned several times in previous blog posts that D365FO runs on the .NET (CLR) platform and you can catch exceptions objects of CLR exceptions (see Catching exceptions in AX 7). While this is useful, you can’t throw such exceptions from X++, therefore it alone can’t solve our problem.

But making it possible isn’t difficult. Let me show you my proof of concept.

What I wanted to achieve:

  • Ability to throw “normal” .NET exceptions from X++. For example, I want to throw ArgumentNullException rather than just error(“Wrong parameters specified”).
  • Ability to define and throw custom exceptions, specific to the business domain of D365FO.
  • Ability to catch these custom exceptions in the usual way.
  • Simple syntax for throwing these exceptions.

Throwing .NET exceptions from X++ can be easily done with a little C# class library. We can throw exception objects from C# and we can call C# methods from X++, therefore we can  instantiate an exception, pass it to a C# method and throw it from there.

This is the class in C#:

public class ExceptionHelper
    public static void ThrowException(Exception ex)
        throw ex;

You could call it from X++ like this:

ExceptionHelper::ThrowException(new ArgumentNullException("_name");

While this works, it doesn’t look natural. I wanted something similar to throw error(“…”). We can’t throw exception objects with throw keyword in X++, but we can do this:

throw exception(new ArgumentNullException("_name"));

exception() is a global function calling the exception helper. It’s implemented in an extension of Global class:

final static class Global_ManagedExceptions_Extension
    public static Exception exception(System.Exception _ex)
        // The return statement is never called because an exception is thrown above,
        // but it makes the method compatible with the throw statement.
        return Exception::Error;

The fact that it returns Exception::Error makes it usable in the throw statement. Calling just exception(…) has the same effect as throw exception(…), but the latter is nicely consistent with throw error(…).

When we can throw exception objects, why should we limit ourselves to existing exception classes? We can easily define our own, specific to our needs in D365FO.

For demonstration, I’ve implemented FieldEmptyException, where you can provide information about the field and the record in question. Later you can use this information for logging, for highlighting failing records or anything you like.

For example, here I’m checking if a record has a value in the Email field and I throw a FieldEmptyException if not.

SysUserInfo user = ...
if (!user.Email)
    throw exception(new FieldEmptyException(fieldStr(SysUserInfo, Email), user));

Then we can catch FieldEmptyException and react to it, instead of catching all errors by the universal class catch (Exception::Error). We also have all details available when we catch the exception, as demonstrated here:

This infolog was generated by the following catch clause:

catch (fieldEmptyEx)
    if (fieldEmptyEx.Record)
        setPrefix("We can log all these details:");
        info(strFmt("Exception type: %1", fieldEmptyEx.GetType().Name));
        info(strFmt("Message: %1", fieldEmptyEx.Message));
        info(strFmt("Table: %1", tableId2Name(fieldEmptyEx.Record.TableId)));
        info(strFmt("Field: %1", fieldEmptyEx.FieldName));
        SysUserInfo user = fieldEmptyEx.Record as SysUserInfo;
        if (user)
            info(strFmt("Data from the table: user ID %1, RecID %2",, user.RecId));
        info(strFmt("Stack trace: %1", fieldEmptyEx.StackTrace));

But if you want to use the usual catch (Exception::Error), you can! I’ve implemented FieldEmptyException as a specialization of ErrorException, therefore all logic working with the normal X++ errors still applies. This is important – you can start using these custom exceptions without worrying that they would stop being handled in existing code.

The complete source code (with examples) can be found on GitHub. It’s under MIT license, therefore you can do virtually anything with it, such as modifying it and including it in your commercial, closed-source solutions.

Find D365FO labels in Powershell

You can use Dynamics 365 > Find labels… in Visual Studio to find D365FO labels, but its capabilities are quite limited. Sometimes a better option is searching through the underlying text files directly and you can easily automate it with Powershell.

The following script looks at all label files for US English and finds all labels containing either PowerBI or Power BI. It skips label comments, because you’re typically interested only in labels and not their comments.

$packagesDir = 'C:\AOSService\PackagesLocalDirectory\'
$labelFiles = ls $packagesDir\*\*\AxLabelFile\LabelResources\en-US\*.txt
$labelFiles | Select-String -Pattern '^(?! ;).*=.*Power ?BI' | select Line, Filename, LineNumber

You might think this approach will be slow, but it actually returns result very quickly.

First chance exceptions

Dynamics 365 for Finance and Operations runs completely in “.NET”, which has some interesting implications. The runtime and tools working with it (such as Visual Studio) offer many new options that may be very useful.

One of these options is the ability to catch first chance exceptions. When an exception is thrown, it’s considered a first chance exception. Visual Studio won’t break the program execution, because your program still has a chance to catch the exception and do something about it. Only if it doesn’t do that and the exception is unhandled, Visual Studio will break the program. It doesn’t happen in D365FO at all, because it has a default handler for all exceptions.

Therefore if I call code like this and never catch the exception, D365FO will still catch it and put a message to infolog.


This is the result:

Now what if I you want to debug the code to see what happened? This error message contains a stack trace, therefore you can put breakpoint to the method throwing the error and run the logic again.

But often you don’t have the call stack; you have only the exception type and message:

System.IO.FileNotFoundException: Could not find file 'c:\nothing_here'.

This happens when there is an exception handler in the application that handles the error and merely puts a message to infolog. Or if it’s an X++ exception, because D365FO doesn’t show stack trace for them.

You can’t use breakpoints because you don’t know where you should put them. Wouldn’t it be great if Visual Studio interrupted execution immediately when the exception is thrown, i.e. at the first chance exception? Fortunately there is a way!

Go to Visual Studio and open Debug > Windows > Exception Settings. Expand Common Language Runtime Exceptions, find FileNotFoundException (you can use the search box on the top) and tick the checkbox.

When you run the code again (with debugging), Visual Studio breaks the program when the exception is thrown and shows the dialog which is normally used for unhandled exceptions:

The example above used a .NET exception, FileNotFoundException. But can we use something like that with native X++ exceptions? Yes, we can!

I’ll use the following piece of code for my example:


If I run the code, I’ll get the following messages in infolog.

I can’t merely put try/catch to my code, because there is no unhandled exception. The exception is already handled in some code called from run().

But I can ask Visual Studio to break on first chance exceptions.

X++ doesn’t use objects for exceptions; throwing an error means throwing an enum value (throw Exception::Error). But D365FO uses CLR (“.NET runtime”) and must play but its rules, therefore every X++ exception is converted to an instance of an exception class under the hood. The class is Microsoft.Dynamics.Ax.Xpp.ErrorException.

If you open Debug > Windows > Exception Settings again, you’ll notice that Microsoft.Dynamics.Ax.Xpp.ErrorException isn’t in the list of Common Language Runtime Exceptions. But it doesn’t matter, because you can add it there.

Select Common Language Runtime Exceptions node and click the green plus button:

Type in Microsoft.Dynamics.Ax.Xpp.ErrorException

and press Enter. The exception is now in the list and Visual Studio will break every time when an X++ error exception is thrown.

When you run the demo code again (with debugging), Visual Studio will stop at the throw statement in createGrid() method.

Finding the place where an exception is thrown may be time consuming, especially if it’s handled somewhere but you don’t know where. This little trick may save you a huge amount of time.

It also shows that there is a lot to gain from the new platform and from Visual Studio, if you use them creatively.

Note that things like this are also applicable to AX 2012, if X++ code is executed as CIL (which, unlike in D365FO, happens only in some cases). I sometimes intentionally ran code in CIL just to get access to features that aren’t available in the X++ debugger.