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:
