Valid Time State Tables

What are we talking about?

A valid time state table helps you simplify the maintenance of data for which changes must be tracked at different points of time.

• Rows that are valid at different points of time
• Tracks historical changes
• Tracks changes that will be valid in future
• ValidTimeStateFieldType property. Once set and if there is data in the table, AX will not allow property to be made None.
• AX kernel prevents overlaps among date ranges of records keeping the data valid.
• ValidFrom and ValidTo columns are added automatically. Can be type Date or UtcDateTime.
• Records in this table cannot have multirow insert, delete or update operations. They are done one row at a time.
• A ValidTimeStateTable should also have an Index that will be used as an AlternateKey.

How to make a Valid Time State Table?

• Create a new table and set the property ValidTimeStateFieldType to Date or UtcDateTime.         
• Create a new index that will be used as an AlternateKey.
• Add the ValidFrom and ValidTo fields to the index
• Set the AllowDuplicates to No
• AlternateKey to Yes
• ValidTimeStateKey to Yes
• ValidTimeStateMode can be Gaps or NoGaps. NoGaps doesnt allow gaps between a ValidTo field of one record and ValidFrom field of the next record. 

Inserts

When inserting a new record, existing record's ValidTo field is updated and then the new record is inserted.

Selects

In case of select statements, all records effective in the date range provided are selected. Refer code below.

dateFrom = 01/01/1900;

dateTo = 31/12/2154;

while select validTimeState(dateFrom, dateTo) * from address

{

               info(address.address);

}

Updates

In case of updates, a new enum ValidTimeStateUpdate is introduced.

It has 2 possible selections.

1. Correction - ValidFrom and ValidTo values of existing rows are updated to maintain data validity after the update_recordset statement. No new records can be created during this mode.

2. CreateNewTimePeriod - new record is inserted to maintain data validity after the update_recordset statement.

table.ValidTimeStateUpdateMode(ValidTimeStateUpdate::Correction);

Deletes

Deleting a current record in a ValidTimeState enabled table, will update the previous record's ValidTo date to current record's ValidTo date.

Query classes

Methods added to the Query class enabling querying valid time state tables.

1. ValidTimeStateAsOfDate()

//returns records effective as of the date parameter specified
qry.validTimeStateAsOfDate(dateMax);

2. ValidTimeStateAsOfDateTime()

//returns records effective as of the dateTime parameter specified
qry.validTimeStateAsOfDateTime(dateTime);

3. ValidTimeStateDateRange()

//returns records effective between the two dates
qry.validTimeStateDateRange(dateMin, dateMax);

4. ValidTimeStateDateTimeRange()

//returns records effective between the two dates and times
qry.validTimeStateDateTimeRange(dateTimeMin, dateTimeMax);

Using this table as a DataSource in Forms

Two properties are provided on datasources

1. ValidTimeStateAutoQuery
Value AsOfDate returns records on that date and value DateRange returns all the records

2. ValidTimeStateUpdate

CreateNewTimePeriod - Existing records are not modified instead new records are created. If this value is selected, you cant select DateRange in ValidTimeStateAutoQuery.

Correction - Allows updates to existing records. New records are not created.

EffectiveBased - Records in the past cannot be edited. Records that are currently active are edited in a manner similar to CreateNewTimePeriod mode. Future records are edited in a manner similar to Correction mode.