Monday, April 10, 2017

InMemory and TempDB in joins on forms

One of the tricky point of the previously announced project for AIF external code mapping and Reverse view is the usage of temporary tables in the latter's form.

As we know there are two different temporary table types in AX 2012: InMemory and TempDB.

In my project I needed to join a temporary table with internal values to the regular table with external codes.

"Cannot select a record in xxxx.
InMemory temporary tables must be the outer tables when they are joined to a TempDB table or permanent table."

How to avoid this famous error?

Brief, I need to populate the temp buffer at the server side and then to pass it to the form data source.

The easiest way to understand how they are processed by AX is switching the type for and debugging then the Reverse view form opening in Init and temporary table populating method. Seeing is believing.

This is how Reverse View regular and temp table are joined.

Let's start with InMemory type. The form considers it as the client tier based table.

In the server based populating method, we need to instantiate the local temp buffer and then set it to the argument buffer via setTmpData() method so that it was still on the server tier. Old school.

Then the same approach to set it to the caller data source. Our temp InMemory table is still on the server and can be joined.

Now, change the table type to TempDB and debug it again. As you can see the form determines it as the server based object.

This time we need to insert new records directly to the argument buffer so that it could be linked to the caller form data source via linkPhysicalTableInstance() method.

If do not have any special reason, the TempDB is recommended to use.

No comments: