Valid Time State Tables in Dynamics AX 2012 [ValidTimeStateFieldType]

Friends,

In DAX 2012, we can let the system manage data relationships that are valid only during a specified date range.

This can be achieved by setting “ValidTimeStateFieldType” property on the table.The system then adds the ValidFrom and ValidTo columns that track a date range in each row. The system guarantees that the values in these date fields remain valid by preventing overlap among date ranges, based on the primary key value.

Let me explain with an example. Let us create a new table with a new string field “Name” which extends Name EDT.

Right click on the table and set the ValidTimeStateFieldType property to utcdateTime as shown below

After setting this property, system will automatically create ValidFrom and ValidTo fields in your table as shown below

Create a new Index by name NameIdx and drag drop Name, ValidFrom, ValidTo fields on to the index and set the following properties on your newly created index as shown below.

You can set the NameIdx as replacement Key on the table.

Now open the table and start entering the data. you will notice that ValidTo will be automatically defaulted to maxdate() 12/31/2154[Never] and validFrom to system date and time now as shown below.

Now let us select all the records in the table starting from min date to max date with the new keyword validTimeState in the SELECT statement as shown below. [Note, Give your own from date time and To datetime Ranges and it work perfctly fine by filtering only those records within the range]


static void SR_ValidState(Args _args)
{
SR_ValidTimeState sr_validTimeState;
utcdatetime minDateTime = DateTimeUtil::minValue() , maxDateTime = DateTimeUtil::maxValue();

While SELECT
validTimeState(minDateTime, maxDateTime)
* from sr_validTimeState
order by sr_validTimeState.ValidFrom
{
info(SR_ValidTimeState.Name);
}
}

we can also achieve the same thing by using Queries. The following code example uses the Query class to read all of the rows from a valid time state table. Use a new validTimeStateDateTimeRange method to achieve this.

utcdatetime minDateTime = DateTimeUtil::minValue() , maxDateTime = DateTimeUtil::maxValue();
qry = new Query();
qry.validTimeStateDateTimeRange(minDateTime, maxDateTime);

There are 4 methods intotal which are introduced new in the query framework.
validTimeStateDateTimeRange
validTimeStateDateRange
validStateAsOfDate
validStateAsOfDateTime

The same concept will be used on the forms to show only relevant records based on the system date and time if the ValidState is enable on the tables. [I believe so – will post in detail soon]

Happy Dax6ng,
Sreenath

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: