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