1

Date Effective framework

Many business scenarios require the organization to analyze how data changes over time. To cater this need, AX 2012 has introduced the Date effective framework.

An organization could be interested in knowing, how the data changed over a period of time. How it was in the past, what the current value is, and what should be the future values. A typical example to consider would be that of an employee.  For example, you might be interested in knowing the current salary of a particular employee, or you might want to find out what was the salary one year ago? Or possibly, you want to explore the different positions a particular employee has hold within the organization in his entire career within the organization? Or you want to update the price of a product, while still maintaining the historical data of previous prices? To fulfill such needs, all you need to do is to empower your organization by making use of the date effective framework in AX 2012.

In this blog post, we are going to see how to make use of this framework in our development.

Development demo

The date effective framework is implemented at the table level.

Scenario: We have a company called “AXPulseConstructions”.  We have four categories of workers: Carpenter, Cleaner, Plumber, Painter. We want to store the hourly wage of each worker type and also enable to update wages while still maintaining the history of wages against each category.

For this we have added a table in the AOT called : “AXPWorkerWagesTable”. To keep things simple, we would just add two fields:

WorkerType this stores the type of worker
HourlyWage this stores the hourly wage of the worker type

Enabling the date effective framework:

  • Go to table properties, and set the “ValidTimeStateFieldType” property to “date”. This is what our table looks like as shown in the figure.

1

 

There are two values available for the “ValidTimeStateFieldType” property.

Date tracking records at the day level. Time is not taken into consideration.
UtcDateTime this is more granular and also considers the time. This also allows multiple values on the same day differentiated with respect to time.

The choice depends on the business needs.

As soon as you change the “ValidTimeStateFieldType” property, two fields are added in the table:

ValidFrom Starting date
ValidTo Expiration date

The data type depends on whether “ValidTimeStateFieldType” property is set to “Date” or “UtcDateTime”.

  • After doing this, we must add an alternate key index in the table. This is known as the “validtimestate key” in the date effective framework. You have to enable this by setting the “ValidTimeStateKey” property to “yes”. Let’s name this index “WorkerTypeIdx”. This index must fulfill the following requirements
    • It must be a unique index
    • Must have the “ValidFrom” field and one other field OTHER than the “ValidTo” field. This would allow date effective framework understand how to track data. For example, in our case, I have added the “WorkerType” and “ValidFrom” fields in the index. This tells the framework, that we are tracking change in data against “WorkerType”. In our case, we are tracking how the hourly wages are changing against a worker type. We’ll see an illustration later in this blog to make things clear.
    • There is another interesting property called “ValidTimeStateMode”. The available values are: “Gap” or “NoGap”. If its set to”NoGap”, it means we don’t allow gaps in the date ranges. For example, we define an hourly wage for “Carpenter” from January 1st to April 1st. Then we want to define another wage for “Carpenter” from June 1st to December 1st. It would not allow this. As there is no record of wage for the “Carpenter” for the month of April and May. The framework would automatically set the “ValidTo” property of the former record to May 31st so as to make sure there is no gap.

2

 

Let’s see how it works:

We have the following data in our table:

HourlyWage

WorkerType

ValidFrom

ValidTo

35

Plumber

12/25/2013

12/31/2154

40

Painter

12/25/2013

12/31/2154

30

Cleaner

12/25/2013

12/31/2154

30

Carpenter

12/25/2013

12/31/2154

 

Now let’s update the hourly wage of “Carpenter” from $30 to $35. We do so by adding a new record in our table, setting the “WorkerType” to “Carpenter”. This tells the framework that hourly wage for “Carpenter” is being updated. Set the “HourlyWage” to 35 and insert this new record.

 

3

 

Alright so, the framework gives us this dialog asking for the confirmation. Click “yes” and bingo! There you are: notice that the date effective framework has automatically updated the “ValidTo” property the older record and set it to one day before the “ValidFrom” property of the new record.

 

4

 

That’s all for now. My name is Bilal Khan and I am an associate technical consultant at AxPulse. I hope you enjoyed reading this blog post. In the next blog, I would be discussing how we can retrieve the date effective data in AX 2012. Good day!

Leave a Reply

Recent Comments

    Archives

    Categories