AX 2012 table not showing all records (aka new feature in AX 2012)

There is a new feature in AX 2012 which only shows records in AX UIs if the current date is between the Valid From and Valid To Date and Time: Valid Time State Tables and Date Effective Data [AX 2012] . It is a property that you can set on a table in the 'ValidTimeStateFieldType'.

This can throw people off coming from previous releases of AX as it will appear some records just flat out don't exist. The records do exist in the SQL DB but the AX kernel filters them out for the user in forms and table browser. If this property doesn't exist for the table you're interested in, you may have bigger issues...

 

Figure 1 - The 'ValidTimeStateFieldType' table property

Much like the modified and created by/date fields, when this property is set fields are automatically added to the table. In this case, its 'ValidFrom' and 'ValidTo'. These can either be a date alone or UTC if time of day is required.

There is built in validation to make sure that a primary key combination doesn't have any overlapping date and/or time intervals. Basically, there can be multiple records for the primary key with a date/time range which must not have any records overlapping even for a second. This is base and there is nothing the developer can do to override this. If its needed, this should be developed separate and this feature shouldn't be used.

About the properties:
Table Keys: Surrogate, Alternate, Replacement, Primary, and Foreign [AX 2012]

Property	Description
AllowDuplicates	No means that the combined fields of the index must together make a value in each record which no other record has.
AlternateKey	Yes means that other tables can create foreign key relations that reference this key, as an alternative to referencing the primary key. Indexes with two or more fields cannot have their AlternateKey property value set to Yes.
ValidTimeStateKey	A key that is marked as a valid time state key is not a candidate key for child tables to reference in their foreign key relations. Instead, this key is meant for managing date effective data in its own table. The default is No. This field can be Yes only if the ValidTimeStateFieldType property is Yes on the table. Yes means this key contains the ValidFrom and ValidTo fields. The ValidTimeStateKey property cannot be set to Yes when the AlternateKey property is set to No.

FYI, in Microsoft Dynamics AX, an AOT node under MyTable > Relations represents a foreign key. For more information, see the previous Relations section in this topic.

Figure 2 - Index properties that need to be set