Valid time state type in ax

on

Valid time state type in ax 2012


A valid time state table property provides 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.
  1. Each valid time state table depends on an alternate key index.
  2. The “ValidFrom” and “ValidTo” columns can both be of the date data type, or can both be of the utcDateTime data type.
  3. 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 “RRValidTimeStateType” and create two fields “Id” and Reason like figure 1.


After, we’ll set table property’s “ValidTimeStateFieldType – Date”. Then the 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 Id identifier and the two date fields.

On “Idx” index set the 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.


Now open the table and add a new record. Notice how the record defaults to ValidFrom with today's date, and ValidTo set to "never". 


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


Comments

Post a Comment