It takes two records and returns a container with the field IDs and the values from both records. For simplicity I used a flattened container instead of more complicated data structures. Feel free to replace it with nested containers or some kind of collection.

I added this to the class Global for easy access.

public static container compareRecords(Common _record1, Common _record2)
{
    SysDictTable    dictTable = new SysDictTable(_record1.TableId);
    SysDictField    dictField;
    FieldId         fieldId, extFieldId;
    container       ret;
    int             i, j;
    ;
 
    if (_record1.TableId != _record2.TableId)
        return conNull();
 
    for (i=1; i<=dictTable.fieldCnt(); ++i)
    {
        fieldId = dictTable.fieldCnt2Id(i);
        dictField = new SysDictField(_record1.tableId, fieldId);
 
        if (!dictField.isSystem())
        {
            for (j=1; j<= dictField.arraySize(); ++j)
            {
                extFieldId = fieldId2Ext(fieldId, j);
 
                if (_record1.(extFieldId) != _record2.(extFieldId))
                {
                    ret += [extFieldId, _record1.(extFieldId), _record2.(extFieldId)];
                }
            }
        }
    }
 
    return ret;
}

As you can see it only compares records of the same type and skips system fields (e.g. RecId). Special care is taken to handle array fields correctly.

Using it is quite straightforward.

static void demoCompareRecords(Args _args)
{
    CustTable   custTable1 = CustTable::find('1101'); // CEE demo data
    CustTable   custTable2 = CustTable::find('1102'); // CEE demo data
 
    container   con;
    int         i;
    ;
 
    con = Global::compareRecords(custTable1, custTable2);
 
    for (i=1; i<=conLen(con); i+=3)
    {
        info(strFmt("%1: '%2' <-> '%3'"
                   ,fieldId2Name(tableNum(CustTable), conPeek(con, i))
                   ,conPeek(con, i+1)
                   ,conPeek(con, i+2)
                   )
             );
    }
}

As mentioned there, the following code doesn’t compile.

    select extCodeTable
        where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo)
    join extCodeValueTable
        where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId;

The compiler chokes on the call to tableNum(). I was surprised to see this, as I could have sworn that I have used tableNum() in select statements before. As it turns out it does compile in some cases.

It compiles without the join.

    select extCodeTable
        where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo);

It also works if you add another where clause after tableNum().

    select extCodeTable
        where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo)
             && extCodeTable.RecId != 0
    join extCodeValueTable
        where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId;

And using a non-intrinsic function works too.

    select extCodeTable
        where extCodeTable.ExtCodeTableId == str2int("1234")
    join extCodeValueTable
        where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId;

This example doesn’t make much sense with regards to business logic but it does compile.

I’m guessing this is a bug in the compiler. As far as I can tell it only fails when you use an intrinsic function before a join clause. Until it’s fixed just use a variable or throw in another where clause to check for RecId != 0. Since all records have a RecId it won’t affect the results you get back. This happens in Ax 4.0 and 2009.

All intrinsic functions are listed on MSDN.

Getting data without declaring a table variable

It’s possible to get data from the database without declaring any table variables. It goes something like this:

static void Job3(Args _args)
{
    // No variables!
    ;
 
    print (select CustTable).AccountNum;
    pause;
}

Note the parentheses around the select statement. This tells the compiler to create a temporary, anonymous buffer to hold the record. The select statement uses the exact table name and is immediately followed by a field name to get a value from a field.

If you use this there’s no Intellisense to help you look up the field name because the editor doesn’t know the type of the anonymous buffer.

This technique is often used in exist() methods on tables.

Getting a value from a field if you only know the field ID

Sometimes you don’t know the exact field name but you do know a field ID. Even in cases like this it’s possible to write an X++ SQL statement without knowing any field names.

Take a look at this example.

static void Job4(Args _args)
{
    CustTable   custTable;
    FieldId     fieldId;
    ;
 
    fieldId = fieldNum(CustTable, AccountNum); // This could be passed as a method parameter
 
    select custTable; // Get first record
 
    print custTable.(fieldId); // Print account number
 
    select custTable
        where custTable.(fieldId) == '1101';  // Where clause based on field ID
 
    print custTable.Name;
    pause;
}

It’s possible to use field IDs to retrieve values as well as use them in a where clause. Again parentheses are the key. Instead of writing a regular field name, wrap the ID in parentheses and you’re done. This makes it possible create generic code to handle any table and field without knowing the types at compile time. If you dig around in the classes for importing and exporting data you’ll find some examples.

Ax has a built-in function for this but all too often I see people resorting to convoluted string conversions to achieve the goal. Please don’t, it’s harder to read, more error prone and less efficient. Use mkDate() instead.

So instead of writing something like this:

static void Job1(Args _args)
{
    int givenYear = 2009; // Value acquired somewhere outside the method
 
    date fromDate, toDate;
    ;
 
    fromDate    = str2date("1/1/" + int2str(2009), 123);
    toDate      = str2date("1/1/" + int2str(2009), 123);
}

Write this:

static void Job1(Args _args)
{
    int givenYear = 2009;  // Value acquired somewhere outside the method
 
    date fromDate, toDate;
    ;
 
    fromDate    = mkDate(1, 1, givenYear);
    toDate      = mkDate(31, 12, givenYear);
}

To me, the most important advantage of mkDate() is clarity. Using string conversions distracts from the actual task of constructing a date. Additionally because all arguments to mkDate() are integers it’s easy to do input validation or build a whole bunch of date values in a loop, e.g. every first of every month.

As you know X++ supports methods with default values. In Ax it’s possible to detect when such a default value was used at runtime with prmIsDefault(). To see this for yourself, create a class with a method like this:

void someMethod(int i = 1)
{
    ;
 
    if (prmIsDefault(i))
    {
        info('Default');
    }
    else
    {
        info('Value given');
    }
}

And call it from another method.

    o.someMethod();
    o.someMethod(1);

Even though the value is the same in both cases a different info message will be displayed.

Now you may be wondering how this can be useful. Some scenarios:

• Avoiding expensive operations if the default value was used
• Avoiding unwanted initializations
• Dealing with customizations that require extra parameters in standard Ax classes

If you do a search for prmIsDefault() in the AOT you will see it’s used often. Classes with the Ax prefix are the most obvious example. Because getters and setters are rolled into a single method it’s necessary to know if the method was used as a getter or a setter and keep track of modified fields.

I have a list of tables where I want to set the ClusterIndex to be the same as the PrimaryIndex.

I’ve been able to loop through an array of the table names, look them up using DictTable, and examine the PrimaryIndex and ClusterIndex values.

But I can’t figure out how I would say:
myTable.clusterIndex = myTable.primaryIndex;

The properties all appear to be read-only.

Any ideas?

Thank you for the question and, yes, I do have an idea.

Enter the TreeNode

The key to solving this is knowing that there is more than one way to do reflection. As Todd found out, the Dictionary API is read-only. But the TreeNode API is not.

TreeNode is exactly what its name says: a node in a tree.
I can hear you thinking: “Nodes? Tree? What are you talking about?”

I’m talking about the Application Object Tree. The AOT has a bunch of nodes and a TreeNode object can represent any one of those nodes, like Forms, a class method or a table.

Whenever you’re using the AOT, clicking on an object and setting properties you’re using TreeNodes. And the best part is you can do it in code too!

Getting the node

Enough talk. Time to show the code. First you need to get hold of a node. Let’s use CustTable as an example.

    TreeNode    node = TreeNode::findNode(@'\Data dictionary\Tables\CustTable');
    ;
    info(node.treeNodePath());
    info(node.AOTname());

As you can see, you need to enter the path similar to the way you open the nodes AOT. If findNode() fails it will return null.

Once you have the node the fun starts. In this case I’m just dumping something in the infolog to show you it works.

Properties

Now let’s do what we really want. We’re going to set the primary index as the cluster index.

    TreeNode    node = TreeNode::findNode(@'\Data dictionary\Tables\CustTable');
 
    str indexName;
 
    #Properties
    ;
 
    indexName = node.AOTgetProperty(#PropertyPrimaryIndex);
    node.AOTsetProperty(#PropertyClusterIndex, indexName);
    node.AOTsave();
 
    node.treeNodeRelease();
    node = null;

There are a couple of things to explain here. First of all the macro #Properties contains the names of all properties you can read and write. These are the same as what you get in the property window. Use the macro instead of literal strings, it’s safer and doesn’t violate best practices.

Next you can see there is a set of methods to get and set properties. After modifying anything you need to save the changes, just like you would when changing an AOT object in the property window. TreeNode works just like doing it manually.

Finally you need to release the memory held by the TreeNode. The garbage collector cannot clean up TreeNode objects by itself. It needs a little help from you by calling treeNodeRelease() when you’re done. In this example nothing bad would happen if you don’t. However, if you run this in a loop a few thousand times Ax will run out of memory. Not something you really want.

Fitting it in with DictTable

Todd already did a lot of work with DictTable to find the tables he needs to change. And now I tell him DictTable won’t cut it. Not really helping.

Not all is lost. DictTable has a method to get a TreeNode object. How convenient. If we change the above example to start with a DictTable object we end up with this:

    SysDictTable    dictTable;
    TreeNode        node;
 
    str indexName;
 
    #Properties
    ;
 
    dictTable = SysDictTable::newTableId(tableNum(CustTable));
 
    node = dictTable.treeNode();
 
    indexName = node.AOTgetProperty(#PropertyPrimaryIndex);
    node.AOTsetProperty(#PropertyClusterIndex, indexName);
    node.AOTsave();
 
    node.treeNodeRelease();
    node = null;

This can be adapted to fit in a loop over several tables and Todd’s problem is solved.

You can do a lot more fun stuff with the TreeNode API. I’ll cover that in another installment. I won’t forget this time. I promise :)

Also, if you have questions or ideas for articles don’t hesitate to let me know.

This usually leaves your Ax session in an unusable state that you can’t close properly. However, there’s no need to get out the big guns and kill the process with the Task Manager.

Instead, if possible, open the AOT and run this job:

static void resetTTS(Args _args)
{
    while (appl.ttsLevel() > 0)
        ttsAbort;
}

It simply rolls back any pending transactions until the TTS level is back at zero. Now, this doesn’t fix the cause of the problem but it makes life easier trying to iron out the bug.

Many data structures in X++ are serialized (packed) into containers before they’re sent across the network or stored in the database. The entire SysLastValue framework runs on it.

Unfortunately this kind of flexibility is often abused. It’s very easy to abuse containers and I see it happen too often. In this article I’ll go over some good practices for containers. I won’t go into basics of containers. You can find basic documentation on MSDN (methods starting with con).

Appending to containers

Most of the time you want to append something to a container. Typically I come across code like this:

for (i=1; i<=maxNum; i++)
{
    con = conIns(con, i, theValue);
}

Don’t do that. The conIns() function is slow because it copies the original container to a new one before returning.

The above should be rewritten as:

for (i=1; i<=maxNum; i++)
{
    con += theValue;
}

It’s a lot faster and easier to read as well.

Only use conIns() if there’s no other option, i.e. for inserting values not at the end. Even adding elements to the front of the container is slow because the existing container is still copied. If at all possible write your code in such a way that you only append elements using +=. This is the fastest way to do it as it modifies the original container in place. And it’s easier to read as well.

Initialising

Whenever you’re going to insert a fixed set of variables into the container you can add them all at once. A typical example when writing something to a file.

while(someCondition)
{
    con = conNull();
    con += 1;
    con += someValue;
    con += otherValue;
    con += "X";
    outFile.write(con);
}

This is works but there’s a better way. You can assign a bunch of values to the container in a single line. Try this instead:

while(someCondition)
{
     con = [1, someValue, otherValue, "X"];
     outFile.write(con);
}

This is a contrived example and often the complexity filling the container can be pushed into a separate method. Usually hiding details like that increases readability of the code. It isn’t always possible to do it like this but simplify your code whenever possible.

Extracting values

The above trick works the other way around too. You can write this:

a = conPeek(con, 1);
b = conPeek(con, 2);
c = conPeek(con, 3);

This can be done in a simpler way:

[a, b, c] = con;

Bonus points if you can explain this:

[a, b] = [b, a];

Passed by reference

Containers are a primitive data type in Ax, so they’re passed by value. That means containers will be copied when passed as a function argument. Keep that in mind when you’re dealing with containers that can become large. For small containers the performance hit is negligible but at some point you’re going to notice it.

Don’t mimic other data structures with containers

Too often I see a mess of containers that could be replaced by a much more appropriate data structure.

Although it’s very easy and tempting to use containers for everything, again, it’s not a good idea. Other developers may have a hard time understanding these ad hoc data structures. Even understanding your own code can be a problem if you haven’t worked on it for a while.

There are alternatives to containers. Take a look at the Foundation Classes. They’re somewhat like the .NET collections. You can use a List, Map, Set, Array and Struct. They can be packed into containers when necessary.

If all variables are of the same type, consider using a List (ordered) or Set (unordered). If you need to track related values use Maps (key/value pairs) or Structs (supports different complex types). Please don’t use nested containers or keep values together in several containers at the same index. It’s hard to read and easy to break.