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.

AIF Many to One External Codes Value Mapping and Reverse View Extension

I do not see any reason why we are not allowed to map many external codes to one internal for AIF inbound port value mapping.

In fact, this is just a question of one additional table, which can be easily created as a copy of the exting one, and a slight change to three classes and AIF related forms.

For the demo's sake it is implemented for Customer and Units only, but you can add the same to any AX externally enabled table.

 Please download and use this extension to the standard AIF in AX 2012.

Another valuable feature of this project is the External codes Reverse View.


Any time I saw something like depicted, I dreamt to have a way to look into this halo in reverse.



The Reverse view enables you to find any existing relation between 1:1 and N:1 external and internal codes.

Filter by any column, export them to Excel, and go directly to the internal table by Edit or double-clicking.

Besides aforementioned, there are examples of using the powerfull AX objects, like:
- table map;
- Data Dictionary operations for scalability;
- set;
- InMemory and TempDB usage in Form and joins.