Never a dull moment when working with Ax, right? Today I ran into an interesting problem involving table caching in AX2012.

Table caching in AX2012

This story starts with the request to create a new company in an existing AX environment. This company should have all general data from the old company, but none of the transactions. So far, so good. It is at times like this that one really learns to appreciate the DMF module.

For whatever reasons it all had to be done in a jiffy so we decided that real men don’t test and when it compiles it works. Besides, it was a simple and straight-forward request. What could go wrong?

When I released the new company and customer service people eagerly started to enter sales orders, I immediately received feedback that they got an error message when entering an item on the sales line. “Reservation hierarchy and unit sequence group ID are not set up for the item”.

Well no. This company does not use warehouse management, so why would I set these up? Come to think of it, the company the data originated from did not use WMS either. I checked the storage dimension group, which was definitely not WMS controlled.

Huh?

Cache!

Debugging the error message learned that the item validation checks for mandatory WMS fields if all of the following are true:

• The item has an item number (duh!).
• The WHS/TMS configuration key is enabled.
• The item exists in table whsInventEnabled.

Not much I can do about the first two, but a quick peek in the database learned that DMF had decided to fill the whsInventEnabled table for all items in the new company. How is that for artificial stupidity?

So I purged the table and felt sure that this would resolve the issue. Only one small problem….it didn’t. The error message persisted and AX continued to complain about missing WMS setup.

Because I am oh so clever and not easily fooled, I immediately jumped up and shouted: “Cache!” so loud that it startled the other people in the room. Then I did what I would advice any user to do in a similar situation. I logged out…and logged back in. Once again I felt sure that I had resolved the issue and once again I had to face disappointment when the same error message popped up in my face the moment I entered an item on the sales line.

Ummmm….

Schrödinger

Now what? The cache must be on the server side or it would have renewed when I restarted my client. Bouncing the AOS is not an option since the current live company is hosted there as well.

The table comes up empty when I read it from the AOT. Strange, because obviously AX is holding back values for it somewhere, somehow.
I wrote a quick job to confirm that I’m not as crazy as they say.

As you can see, the first query (select firstonly from…) returns a value, where as the second (while select…) does not. Does Schrödinger’s uncertainty principle apply to Dynamics AX as well? Can a record exist and not exist at the same time? It sure looks that way.

CacheLookup property

If you look at the properties of the whsInventEnabled table, you will see that the cacheLookup is set to FoundandEmpty. This is an example of so-called ‘Single-record caching’. According to Microsoft this means:

Records are cached for all unique indexes when all the following criteria are met:

• The table is cached by having its CacheLookup property set to NotInTTS, Found, or FoundAndEmpty.
• The select statement that selects the records uses an equal operator (==) on the caching key. The fields in the where clause of the select statement match the fields in any unique index.

So lo and behold, the type of select statement determines whether the application decides to make a trip to the database or not. When you think about it, which in these cases is almost always too late, it is really nice to have such an option. You can either get a quick’n dirty value from cache, or as long as you’re going for the whole enchilada anyway, you’ll get a freshly baked dataset straight from the reservoir.

Caching is a beautiful thing, but every now and then it can be a source of headaches rivaled only by Transnistrian Vodka.

Links to this article

A very good blog on the subject of table caching in AX2012
One from the chaps in Redmond
MS Developer library