Author: bjorn

In X++ it is possible to write code that inspects the structure of other X++ code. This is sometimes called reflection or meta-programming.

You may wonder why you’d want to do that. Reflection is a very powerful tool and opens a wide array of possibilities. Without it some things would be very hard or even impossible to implement. Just look at the code behind table browser, the unit testing framework, or the data export and import tools.

There are several ways to do reflection in X++. I’m going to show an example using the Dictionary. It involves classes whose names start with Dict and SysDict. The latter are subclasses of their respective Dict-class and can be found in the AOT.

The goal

Suppose you need to analyze the performance of an existing application. You could set up monitoring but you need an indication where to start. The largest tables in the database, i.e. those with most records, are potential performance bottlenecks. For large tables it is important to have the right indexes that match the usage pattern of the customer. We’re going to make a simple tool to find those tables. You could also check if new tables from customizations have indexes and so on.

Getting started

First we need to get a list of tables in the application. The kernel class Dictionary has the information we need. It tells us which classes, tables and other objects are defined in the application. To iterate over the list of tables we can use something like this:

static void findLargeTables(Args _args)
{
    Dictionary      dictionary;
    TableId         tableId;
    ;

    dictionary = new Dictionary();

    tableId = dictionary.tableNext(0);

    while (tableId)
    {
        info(int2str(tableId));

        tableId = dictionary.tableNext(tableId);
    }
}

The tableNext() method gives the ID of the table following the given ID. So we start with the non-existant table ID 0 and get back the first table in the system. For now we’ll just print the result to the infolog.

Weeding out the junk

If you scroll through the infolog you’ll notice it also includes things we aren’t interested in, such as temporary tables, (hidden) system tables, views, and table maps. We need to skip these.

Enter the SysDictTable class. Whenever possible you should use the SysDict version of any class in the Dictionary API because they contain very useful additional methods. You’ll see an example in a minute.

static void findLargeTables(Args _args)
{
    Dictionary      dictionary;
    TableId         tableId;

    SysDictTable    dictTable;
    ;

    dictionary = new Dictionary();

    tableId = dictionary.tableNext(0);

    while (tableId)
    {  
        dictTable = new SysDictTable(tableId);

        if (!dictTable.isMap() && !dictTable.isView() &&
            !dictTable.isSystemTable() && dictTable.dataPrCompany())
        {
            info(strFmt('%1 - %2', tableId, tableId2Name(tableId)));
        }

        tableId = dictionary.tableNext(tableId);
    }
}

Some methods tell us what kind of table we’re dealing with and any special case is ignored. For this example I’m only interested in a single company.

Counting

Now need to know which tables have the most records. SysDictTable can count the records for us. To keep track of the results we’ll use an array. The index indicates the number of records and the value is a container of table names. This is a simple data structure that doesn’t require any new tables or classes. The results are ordered and it can deal with several tables having the same record count. The only catch is we need to keep in mind that not all array indexes will have a value.

It’s easier than it sounds. First we take out the info() in the loop and put in some real logic.

        if (!dictTable.isMap() && !dictTable.isView() &&
            !dictTable.isSystemTable() && dictTable.dataPrCompany())
        {
            currCount = dictTable.recordCount();
            if (currCount > 0)
            {
                if (recordCounts.exists(currCount))
                {
                    tables  = recordCounts.value(currCount);
                    tables += dictTable.name();
                }
                else
                {
                    tables = [dictTable.name()];
                }

                recordCounts.value(currCount, tables);
            }
        }

We ignore empty tables and then check if we need to add our table to an existing container or create a new one.

After inspecting the tables we can print the top 10.

    printed = 0;
    i = recordCounts.lastIndex();
    while (i > 0 && printed  0 )
    {
        if (recordCounts.exists(i)
          && conLen(recordCounts.value(i)) > 0)
        {
            info(strFmt("%1 - %2", i, con2str(recordCounts.value(i))));
            ++printed;
        }
        --i;
    }

What’s next?

To make it more useful you could add more checks. I included some of these in the XPO.

• cacheLookup() : to check if a good cache level is set.
• indexCnt(), clusterIndex() and primaryIndex() : if you want to know if the table has indexes. For large tables a good set of indexes can make a big difference.
• tableGroup() : for filtering out transaction tables, which are often the ones that need most tuning. Or to find all those Miscellaneous tables that should be in another group.
• fieldCnt() : counts the number of fields. Tables with a lot of fields take up more space and require more round trips between AOS and database when fetching data. So don’t go overboard when adding new fields. It’s a good idea to check the field count every now and then when developing.
• recordSize() : tells you how big a single record is in bytes. This depends on the number of fields and the data types.

There’s a lot more you can do with the Dictionary classes. To get an idea of the possibilities you can check how dictionary classes are used in the standard application.

In later posts I’ll give more examples how to read (and modify) the AOT structure in X++.
XPO for Dynamics Ax 4.0.

The table browser is a great tool but it can be hard to find the fields you’re looking for in the grid. Standard Ax either shows all fields or the fields in the AutoReport field group.

Sometimes I like to use another field group so I added that option to the table browser. Now I can use any field group defined on the table. Filtering out the irrelevant fields makes browsing the data a lot easier.

It wasn’t too hard to implement. Standard Ax code can already handle any field group but there is no way to choose one. On the form SysTableBrowser I replaced the radio button with a combobox and then modified the class SysTableBrowser to use that instead of the radio button.

XPO for Dynamics Ax 4.0