Record caching principle

Which statement will perform faster?

select inventTable
    join inventItemGroup
    where inventItemGroup.ItemGroupId == inventTable.ItemGroupId
    && inventTable.ItemId == 'A'; 

or
select inventTable
    where inventTable.ItemId == 'A';

    select inventItemGroup
    where inventItemGroup.ItemGroupId == inventTable.ItemGroupId; 

From the first point of view it seems obvious that the first one with join, since it will result in only one database call and the second will result in two. However, it is not always the case. For example, if records that are being looked up in the second example are cached on the server (or even on the client) there will be no database calls at all, and may be even no client\server calls! The code from the first example will never hit the cache since caching is not supported for the queries with joins.
Actually Dynamics AX caches records by their primary key value. So, if a query has no joins and has primary key in its where clause then it will hit the cache. Otherwise it won't.

Unit tests data setup and tear down

There are methods in AX to setup data before test execution and tear it down afterwards - SysTestCase.setup() and SysTestCase.tearDown(). Those methods will be executed before and after each test method in a unit test class. 
In AX 2009 two new methods were added - setupTestCase() and tearDownTestCase() which are executed only once per unit test class execution - before first test method and after last test method. But there is a problem with using them - by default each unit test method runs in a separate instance of the unit test class, so if you are setting up some data in the setupTestCase() method there will be no possibility to store a reference to it in the instance variable. 
To change this behavior useSingleInstance() method can be overridden to return true. In this case all test method as well as setupTestCase() and tearDownTestCase() method will be execute in one instance of the unit test class.

Surrogate keys in AX 2012

Data modeling enhancements were prioritized really high in AX 2012. I already mentioned one earlier – table inheritance, but there are a lot of other important features about data modeling.

One such feature is the support of surrogate keys. Surrogate keys recommended themselves as a very good solution for complex databases because of their beneficial properties, like immutability, robustness to redesign and performance. And now this best practice is coming to AX. It is the recommendation for AX 2012 to use surrogate keys for all new tables, unless there is a good reason not to do so.

Actually, previous versions of AX already have surrogates – RecIds. And it was possible to introduce surrogate key just by setting CreateRecIdIndex table property to Yes. But there is a problem with the inconvenience of working with surrogates in the UI. Imagine InventTable using surrogate key and all other tables that have relations to InventTable using its surrogate key as a foreign key. Then on any form where ItemId should be displayed (let’s say on the SalesTable) an extra join to InventTable will be needed to fetch the ItemId. And what if user can change the item? Then even more custom logic is needed to resolve the entered ItemId to the corresponding surrogate key value and write it to the SalesLine.

This inconvenience actually was a showstopper. But not anymore. AX 2012 got kernel support for surrogate key substitution. And not only in forms, but in Axd document services and even in the debugger.

Let me show an example of how it works:
UnitOfMeasure table uses surrogate key as a primary key. However, the user-friendly identifier of the UnitOfMeasure table records is the UnitOfMeasureSymbol field. This field is a part of the SymbolIdx index, which is specified as a replacement key on the UnitOfMeasure table.


This setup basically means that surrogate key values (RecIds) of the UnitOfMeasure table records will be automatically substituted in the UI with the replacement key values (UnitOfMeasureSymbols).
UnitOfMeasureConversion table has a relation to the UnitOfMeasue table through the surrogate key.


As you can see, on the UnitOfMeasureConversion form there are only UnitOfMeasureConversion datasources. And you can notice new ReferenceGroup control which is bound to the FromUnitOfMeasure field (which contains surrogate values).


And in the UI it will look just like UnitOfMeasureSymbol field is shown instead. And it can also be modified and that will be handled automatically, without a single line of code.


Actually ReferenceGroup control has much more interesting capabilities and thus deserves a separate post.

Unit of Work

After the post about surrogate keys I was asked the following question:
There is the following problem with using surrogate keys.
A record gets RecId value at the moment it is inserted into the database. What if we are doing bulk insert of a journal with many lines and the journal header contains some totals calculated based on its lines. It is impossible to insert lines before header since the value of the journal's surrogate key is unknown. But that would be so convenient because otherwise the header should be inserted first, then the lines and then the header should be updated with the calculated totals.

The answer is – use Unit of Work. It allows to perform create, update and delete operations without worrying about the order of those and without a need to specify surrogate key values. It will all be done automatically in the kernel!

Example:
Let we have the following tables:


The following job will insert the journal header with calculated totals and 10 lines. Note, that there is no place where surrogate keys are mentioned in the code. Business data only.


And here is the result.



Some more details about the unit of work feature:

1. All database operations happen only when saveChanges() method is invoked.
2. UnitOfWork class has insertOnSaveChanges(), updateOnSaveChanges() and deleteOnSaveChanges() methods for CUD operations.
3. Surrogate keys can be propagated automatically to related records if buffers are linked via navigation methods (AJournalLine.header() method in the example). Navigation methods can be created automatically from relations. I’ll write a separate post about them.

And the last but not least. AX client has the great support of the unit of work as well. This means that form datasources can be grouped into a unit of work, so all the benefits of simultaneous commit and surrogate key propagation will be available on forms. There are a lot of possibilities and tricks there, so this topic worth one or more dedicated posts as well.

How can I use polymorphism using X++?

Polymorphism is not the same as method overloading or method overriding. Polymorphism is only concerned with the application of specific implementations to an interface or a more generic base class. Method overloading refers to methods that have the same name but different signatures inside the same class. Method overriding is where a subclass replaces the implementation of one or more of its parent's methods. Neither method overloading nor method overriding are by themselves implementations of polymorphism.

polymorphism in the context of object-oriented programming, is the ability of one type, A, to appear as and be used like another type, B.

So, basically, any RunBaseBatch extension class (the ones you use to process stuff in AX) is an example of polymorphism in AX. Because they all call the methods of the base class as if they were their own.

 method overloading is not suppoorted in X++.

Instead, we define methods with different names and a specific argument set:

newFromInventTable(InventTable _it)

newFromPurchLine(PurchLine _pl)

newFromSalesLine(SalesLne _sl)

Import customer address in the Global Address Book

In AX 2012 the GAB (Global Address Book) has gone through some serious changes and now the address information for the customers and vendors is kept in the LogisticsPostalAddress,and LogisticsPostalAddressExt tables. Additional information is maintained in the LogisticsAddress* tables.

This is the simplest possible example of how to create new customer and populate the customer's address information in AX 2012. Note that you may have to modify this code in order to handle the creation of records in the LogisticsAddress* tables.

        CustTable                       custTable;
        AccountNum                      accountNum = 'ABC-123';
        CustGroupId                     custGroupId = '10';
        Name                            name = 'ABC';
        
        DirParty                        dirParty;
        DirPartyPostalAddressView       dirPartyPostalAddressView;


        ttsBegin;

        custTable.clear();
        custTable.initValue();

        custTable.AccountNum = accountNum;
        custTable.CustGroup = custGroupId;

        custTable.insert(DirPartyType::Organization, name);

        dirParty = DirParty::constructFromCommon(custTable);

        dirPartyPostalAddressView.LocationName = 'ABC-AUTO';
        dirPartyPostalAddressView.City = 'London';
        dirPartyPostalAddressView.Street = 'Dover Street';
        dirPartyPostalAddressView.StreetNumber = '123';
        dirPartyPostalAddressView.CountryRegionId = 'GBR';

        dirParty.createOrUpdatePostalAddress(dirPartyPostalAddressView);

        ttsCommit;

Form control type at run-time

This shows how to get the type of a form control at run-time. The code iterates through the controls in a field group and checks each control for it's type. 

    FormControl fc;
    int         i;

    // ...
   
    for(i=1; i <= FieldGroupName.controlCount(); i++)
    {
        fc = FieldGroupName.controlNum(i);
        if(SysDictClass::is(fc, classNum(FormStringControl)))
        {
            info(strFmt("%1 %2", fc.name(), fc.type()));
        }
    }

If you do this in Dynamics AX 2012 you will get the following warning:

The SysDictClass::is method is obsolete. Use the IS operator instead.


The new syntax is:

if(fc is FormStringControl)

Date Effective tables with ValidTimeState and Dynamics AX 2012

You may have noticed the new ValidTimeStateFieldType property on tables in AX 2012. The enum values are None, Date and UtcDateTime. This is part of the new & cool Date Effective Table framework that comes with Dynamics AX 2012. The idea is to minimize the effort for managing period gaps, overlapping periods, period validation, etc. So, let's get down to how it works.

• We create new table called DEV_ValidTimeState and we add new field named ItemId
• We set the ValidTimeStateFieldType table property to UtcDateTime. At this point AX automatically creates 2 new fields: ValidFrom and ValidTo.   
   
• Now we need to create new unique index, which should include the fields ItemId, ValidFrom, and ValidTo
• We set the following index properties  
• AllowDuplicates to No.
• Alternate Key to Yes.
• ValidTimeStateKey to Yes. 
• ValidTimeStateMode to Gap. 
• Now let's insert some records.

static void createValidTimeState(Args _args)
{
    DEV_ValidTimeState table;
    ;

    delete_from table;

    table.clear();
    table.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

    table.ValidFrom = DateTimeUtil::newDateTime(1\1\2012, 0);
    table.ValidTo = DateTimeUtil::maxValue();
    table.ItemId = '1000';

    table.insert();

    table.clear();
    table.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

    table.ValidFrom = DateTimeUtil::newDateTime(6\6\2012, 0);
    table.ValidTo = DateTimeUtil::maxValue();
    table.ItemId = '1001';

    table.insert();
}

• Here is how to select and update records from the table. Please note the use of the validTimeStatekeyword with select statements and there is a new xRecord method calledvalidTimeStateUpdateMode.

static void updateValidTimeState(Args _args)
{
    DEV_ValidTimeState table;
    utcDateTime fromDateTime, toDateTime;
    ;

    fromDateTime = DateTimeUtil::newDateTime(3\3\2012, 0);
    toDateTime   = DateTimeUtil::maxValue();


    select validTimeState(fromDateTime) table;

    info(table.ItemId);

    select validTimeState(fromDateTime) * from table;

    info(table.ItemId);

    select validTimeState(fromDateTime) ItemId from table;

    info(table.ItemId);

    select validTimeState(fromDateTime, toDateTime) ItemId from table;

    info(table.ItemId);

    ttsBegin;

    while select forUpdate validTimeState(fromDateTime) table
    {
        table.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
        table.ItemId = '1002';
        table.update();

    }

    ttsCommit;
}

• The query framework was updated to support the new Time Effectiveness feature. Here is the code sample:

static void queryValidTimeState(Args _args)
{
    DEV_ValidTimeState      table;
    utcDateTime             fromDateTime, toDateTime;       
    Query                   q;
    QueryRun                qr;   
    QueryBuildDataSource    qbds;
    ;

    fromDateTime = DateTimeUtil::newDateTime(3\3\2012, 0);
    toDateTime   = DateTimeUtil::maxValue();

    
    q = new Query();    
    
    qbds = q.addDataSource(tableNum(DEV_ValidTimeState));
    
    q.validTimeStateAsOfDateTime(fromDateTime);
    
    qr = new QueryRun(q);
    
    while(qr.next())
    {
        table = qr.get(tableNum(DEV_ValidTimeState));
        info(table.ItemId);
    } 
} 

To sum up, in AX 2012 we have new feature that allows us to manage the time periods associated with an entity. We get all the benefits of validation and period gaps management for free (that's sweet). In order to support the new feature some changes have been introduced:

• All tables now have new property called ValidTimeStateFieldType
• Table indexes now have new properties
• ValidTimeStateMode 
• ValidTimeStateKey
•  Alternate Key should be set to Yes
• The kernel class xRecord and all tables now have the validTimeStateUpdateMode method. 
• There is new system enum ValidTimeStateUpdate with the following values:
• Correction – the ValidFrom or ValidTo values of existing rows must be modified to keep the date effective data valid after the update completes. 
•  CreateNewTimePeriod – a new record is inserted into the table to maintain the validity of the date effective data after the update completes. 
• EffectiveBased – forces the update process to switch to CreateNewTimePeriod for each row that spans the current date-time; otherwise to switch to Correction. 
• The kernel class Query now has 4 new methods:
• validTimeStateAsOfDate
• validTimeStateAsOfDateTime
• validTimeStateDateRange 
• validTimeStateDateTimeRange

Insert_recordset, Update_recordset, and delete_from single transaction command.

In AX, you can manipulate a set of data by sending only one command to the database. This way of manipulating data improves performance a lot when trying to manipulate large sets of records. The commands for manipulations are insert_recordset, update_recordset, and delete_from. With these commands, we can manipulate many records within one database transaction, which is a lot more efficient than using the insert, update, or delete methods.

Lets discuss about these commands one by one.

• Insert_recordset
  
  A very efficient way of inserting a chunk of data is to use the insert_recordset operator, as compared to using the insert() method. The insert_recordset operator can be used in two different ways; to either copy data from one or more tables to another, or simply to add a chunk of data into a table in one database operation.
  The first example will show how to insert a chunk of data into a table in one database operation. To do this, we simply use two different table variables for the same table and set one of them to act as a temporary table. This means that its content is not stored in the database, but simply held in memory on the tier where the variable was instantiated.
  static void Insert_RecordsetInsert(Args _args)
  {
  CarTable carTable;
  CarTable carTableTmp;
  
  
  /* Set the carTableTmp variable to be a temporary table.
  This means that its contents are only store in memory
  not in the database.
  */
  carTableTmp.setTmp();
  // Insert 3 records into the temporary table.
  carTableTmp.CarId = “200″;
  carTableTmp.CarBrand = “MG”;
  carTableTmp.insert();
  carTableTmp.CarId = “300″;
  carTableTmp.CarBrand = “SAAB”;
  carTableTmp.insert();
  carTableTmp.CarId = “400″;
  carTableTmp.CarBrand = “Ferrari”;
  carTableTmp.insert();
  /* Copy the contents from the fields carId and carBrand
  in the temporary table to the corresponding fields in
  the table variable called carTable and insert the chunk
  in one database operation.
  */
  Insert_Recordset carTable (carId, carBrand)
  select carId, carBrand from carTableTmp;
  }
  The other, and perhaps more common way of using the insert_recordset operator, is to copy values from one or more tables into new records in another table. A very simple example on how to do this can be to create a record in the InventColor table for all records in the InventTable.
  static void Insert_RecordsetCopy(Args _args)
  {
  InventColor inventColor;
  InventTable inventTable;
  This material is copyright and is licensed for the sole use by ALESSANDRO CAROLLO on 18th December
  Chapter 6
  [ 169 ]
  InventColorId defaultColor = “B”;
  Name defaultColorName = “Blue”;
  ;
  insert_recordset inventColor (ItemId, InventColorId, Name)
  select itemId, defaultColor, defaultColorName
  from inventTable;
  }
  The field list inside the parentheses points to fields in the InventColor table.
  The fields in the selected or joined tables are used to fill values into the fields in
  the field list.
  
  
• Update_recordset
  
  The update_recordset operator can be used to update a chunk of records in a table in one database operation. As with the insert_recordset operator the update_recordset is very efficient because it only needs to call an update in the database once.
  The syntax for the update_recordset operator can be seen in the next example:
  static void Update_RecordsetExmple(Args _args)
  {
  CarTable carTable;
  ;
  info(“BEFORE UPDATE”);
  while select carTable
  where carTable.ModelYear == 2007
  {
  info(strfmt(“CarId %1 has run %2 miles”,
  carTable.CarId, carTable.Mileage));
  }
  update_recordset carTable setting Mileage = carTable.Mileage + 1000
  where carTable.ModelYear == 2007;
  info(“AFTER UPDATE”);
  while select carTable
  where carTable.ModelYear == 2007
  {
  info(strfmt(“CarId %1 has now run %2 miles”,
  carTable.CarId, carTable.Mileage));
  }
  }
  
  
  When this Job is executed it will print the following messages to the Infolog: 
  
  Notice that no error was thrown even though the Job didn’t use selectforupdate, ttsbegin, and ttscommit statements in this example. The selectforupdate is implicit when using the update_recordset, and the ttsbegin and ttscommit are not necessary when all the updates are done in one database operation. However, if you were to write several update_recordset statements in a row, or do other checks that should make the update fail, you could use ttsbegin and ttscommit and force a ttsabort if the checks fail.
  
  
• Delete_from
  
  As with the insert_recordset and update_recordset operators, there is also an option for deleting a chunk of records. This operator is called delete_from and is used as the next example shows:
  
  
  static void Delete_FromExample(Args _args)
  {
  CarTable carTable;
  
  
  delete_from carTable
  where carTable.Mileage == 0;
  }
  
  
  Thanks for reading the post, any comments or questions are welcomed. Keep visiting the blog.