Valid time state table in ax 2012

A valid time state table property is provide you maintenance of data for which changes must be tracked at different points in time. For example where benefits become effective and expire at certain times for employee.

On a table in the AOT, you can set the “ValidTimeStateFieldType” property to “Date” or “UtcDataTime” make it a valid time state table. When you set property system automatically will 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.

Note:

1. The "ValidTimeStateFieldType" property cannot be set for any table that inherits from another table.
2. Each valid time state table depend on an alternate key index.
3. The “ValidFrom” and “ValidTo” columns can both be of the date data type, or can both be of the utcDateTime data type.
4. In X++ select statement we can use “validTimeState” keyword to filter rows by date or date range. And in Query class we can use “validTimeStateAsOfDateTimeRange ()” methods that provide filtering by date range.

Now we take simple example for understanding this functionality. First we create a new table called “EmployeeTable” and create two fields “EmplD” and Benefits like figure 1.

Figure 1. Create Table.

After, we’ll set table property’s “ValidTimeStateFieldType – Date”. Then system will automatically create two new date fields called "ValidFrom" and "ValidTo" like figure 2. Next, we'll add an index on the table, containing our EmpId identifier and the two date fields.

Figure 2. Create index.

On “EmpIdx” index set property “AllowDuplicates – No” and “AlternateKey-Yes”. After this we'll set “ValidTimeStateKey – Yes” that will enable you to set “ValidTimeStateMode- NoGap/Gap” (default to NoGap) and we’ll set “NoGap”. The NoGap value tells the system that we do not allow gaps in the date ranges. For example, for first record Jan 1 to Feb 1 and a second record of March 1 to April 1,since there would be a gap between Feb 1 and March 1.

Figure 3. Set Index property.

Now open the table and add new record. Notice how the record defaults to ValidFrom with today's date, and ValidTo set to "never". Give it a benefits  of “Bonus”  and EmpId of “0031” and save.

Figure 4. Add Record.

Now, if you create another new record, it will again default in the same date values. If we enter the EmpId "0031" again and try to save the record (CTRL+S), we’ll get the error "Cannot create a record in EmployeeTable (EmployeeTable). Insert not supported with the values specified for 'Effective' and 'Expiration'. New record overlaps with multiple existing records”. 

Figure 5. Error.

It doesn't allow this overlap of the same dates. So, change the ValidFrom field another date, and when we save the record we’ll get option like “Effective dates for one or more records will be adjusted by this change. Do you wish to continue?” 

Figure 6. Add another record.

If you select yes, you will see your previously created record will be updated so that its “ValidTo” date will be changed from never to a date that connects to your new record “ValidFrom” to one day before.

Figure 7. Change date according to previously created record.