Valid Time State Tables and Date Effective Data [AX 2012]
Updated: December 13, 2011
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
A valid time state table lets you simplify the maintenance of data for which changes must be tracked at different points in time. For example, the interest rate on a loan can be 5% for the first year, and 6% for the second year. During the second year you still want to know that the rate was 5% in the previous year. An application can combine logic and corresponding table designs to track such date effective data. But the valid time state feature makes this task much simpler in Microsoft Dynamics AX.
On a table in the AOT, you can set the ValidTimeStateFieldType property to make it a valid time state table. That causes the system to automatically add the ValidFrom and ValidTo columns which track a date range in each row. The system guarantees that the values in these date or date-time fields remain valid by automatically preventing overlap among date ranges.
The ValidTimeStateFieldType property cannot be set for any table that inherits from another table.
The ValidFrom and ValidTo columns can both be of the date data type, or can both be of the utcDateTime data type.
Each valid time state table relies on an alternate key index. For example, suppose the table manages the relationship between employees and the projects they work on. The table would have employeeID and projectID as foreign key fields. The alternate key index would have the employeeID and projectID fields, plus the ValidFrom and ValidTo fields. On the index you set the ValidTimeStateMode property to either enable or disable gaps between date ranges.
The X++ select statement has the validTimeState keyword to filter rows by date or date range. The Query class has methods that provide filtering by date range, such as validTimeStateAsOfDateTimeRange.
Some people search for Help topics about the valid time state feature by using the phrase date effectivity.
Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.