Oct 5, 2011
Filed under: #daxmusings #bizapps

AX 2012 features a new framework called the date effective framework, or valid time state tables. There are many easy examples of date effective data. The easy example is some sort of rate table, where rates become effective and expire at certain times. Other examples could be bills of material or revisions of items that become effective or expire at certain dates. In previous versions of AX, it was up to the developer to implement the logic for querying and validating the date ranges. AX 2012’s date effective framework takes care of the ground work for you. In this article, we’ll walk through the setup, and show you the automatic functionality that comes with it.

Date effective comes in two flavors. One uses regular date fields, the other UtcDateTime data type. Obviously, the UtcDateTime gives you more granularity to the second (and the nice timezone support that comes with UtcDatetime). For this example, we’ll just stick with an easy example using regular dates.

First, we create a new table and we’ll call it RateTable. We’ll give the table a RateID field which identifies the different rates, and a price for each rate.

On the table’s properties, we’ll set the ValidTimeStateFieldType to “Date”. This will automatically create two new date fields called “ValidFrom” and “ValidTo”.

Next, we’ll add an index on the table, containing our RateID identifier and the two date fields. The index needs to be unique, and set as an alternate key.

On the index’ property sheet, we’ll set ValidTimeStateKey to “Yes” and ValidTimeStateMode to “NoGap” (should default to NoGap when you set timestatekey to YES). The NoGap value tells the framework we do not allow gaps in the date ranges. For example, for the same RateID, NoGap will not allow one record January 1 to February 1 and a second record of March 1 to April 1, since there would be a gap between February 1 and March 1. We can easily test this once our table is set up.

That is pretty much it. Let’s open the table browser by right-clicking and selecting “Open”. Create a new record by hitting CTRL+N on your keyboard. Notice how the record defaults to ValidFrom with today’s date, and ValidTo set to “never” (if you put your cursor on the field, you’ll notice how the “never” value is actually “12/31/2154”). Give it a RateID of “DAXMusings” (yeah!) and save the record (CTRL+S).

Now, if you create another new record (CTRL+N), it will again default in the same date values. If you enter the RateID “DAXMusings” again and try to save the record (CTRL+S), you will get the error “Insert not supported with the values specified for ‘Effective’ and ‘Expiration’. New record overlaps with multiple existing records”.

So, it obviously doesn’t allow this overlap of the same dates. So, change the ValidFrom field to TOMORROW’s date, and save the record (CTRL+S).

If you click yes, you will notice 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 (if you follow the example, your first record should now contain today’s date in both ValidFrom and ValidTo fields).

That was pretty easy. Stay tuned, we’ll look at how to query this table next.

 

There is no comment section here, but I would love to hear your thoughts! Get in touch!