An Afternoon Stroll with Temp Tables in AX 2009

How to initialize and transfer temp tables in AX 2009

In Dynamics AX 2009 there are two ways to create and initialize a temporary table:

1) Create new table and set Temporary property to Yes:

Table Import Temporary

2) Call the setTmp() method in X++ code for any existing table in AOT:

AOT dynamics ax 2009

Temporary tables store data the same way as regular tables, except that they keep data only when the object exists during code execution. In other words, if the object is finalized, then all data from the temp tables will be dropped.

There are two common situations where temp tables can be applied:

  1. As a Datasource for a form or report, where the original data is too complex to be easily queried.
  2. As temporary storage during complicated processing, to hold the results midway through the process.

Temporary tables can be defined by the following ways:

  1. Set the table’s Temporary property to Yes.
  2. Call the setTmp method in X++ code.

A temporary table is held in memory until its size reaches 128 KB. The dataset is then written to a disk file. The disk file for a temporary table has the naming convention $tmp<nnnnnnnn>.$$$.

Best practices requires to infix temporary table names with Tmp. This improves readability in code.

Here is an example of how to declare, initialize and use temp tables on the fly based on already existing CustTable:

static void testInitTmp(Args _args)

{

    CustTable   custTable; 

    CustTable   custTableTmp; 

    Counter     totalRec;

    ;

 

    //The setTmp method is used to create a temporary table based on CustTable

    custTableTmp.setTmp();

    custTableTmp.recordLevelSecurity(true);

 

    //Show all records in CustTable filtered by CustGroup

    while select custTable where custTable.CustGroup == “Test”

    {

        info(custTable.AccountNum);

    }

    //Show number of all records in CustTable

    info (strfmt(“Total records %1”, (select firstonly count(RecId) from CustTable).RecId));

 

    // Insert a subset of custTable records based on the filter on CustGroup

    while select custTable where custTable.CustGroup == “Test”

    {

        custTableTmp.data(custTable.data());

        custTableTmp.doInsert();

    }

 

    //Show all records in temporary table.

    while select custTableTmp

    {

        info (custTableTmp.AccountNum);

    }

 

    //Find all records in temporary table

    select firstonly count(RecId) from custTableTmp;

 

    totalRec = any2int(custTableTmp.RecId);

    info (strfmt(“Total records %1”, totalRec));

}

Please note that calling insert() method on temporary table executes logic on standard CustTable.insert(), so in that case I would recommend to use CustTableTmp.doInsert();

Result:

infolog dynamics ax 2009

We have two records of CustGroup: Test in CustTable (Total records 39). All these two records from custGroup: Test were successfully copied in CustTableTmp, so now it has only 2 records.

AOT temporary table:

static void TableTmpInsertRecord(Args _args)

{

    TestTableTmp  testTableTmp

    ;

    testTableTmp.AccountNum = “1000”;

    testTableTmp.Name = “Name”;

    testTableTmp.Group = “10”;

    testTableTmp.insert();

    

    testTableTmp.AccountNum = “2000”;

    testTableTmp.Name = “Name2”;

    testTableTmp.Group = “20”;

    testTableTmp.insert();

    

    while select testTableTmp

    {

        info(strfmt(“%1, %2, %3”, testTableTmp.AccountNum, testTableTmp.Name, testTableTmp.Group));

    }

}

Result:

infolog 2 dynamics ax 2009

Using Temporary tables in different classes

Let’s talk about another common issue, when we need to initialize temporary table and pass it between different classes. In this particular case we will create a new class: ImportDataTmp to initialize data in the ImportTmp table and then pass and display it in our job: ImportTmpClass

Class:

public class ImportDataTmp 

{

    TestTableTmp testTableTmp;

}

 

public void importData()

{

    ;

    

    testTableTmp.AccountNum = “1000”;

    testTableTmp.Name = “Name”;

    testTableTmp.Group = “10”;

    testTableTmp.insert();

 

    testTableTmp.AccountNum = “2000”;

    testTableTmp.Name = “Name2”;

    testTableTmp.Group = “20”;

    testTableTmp.insert();

}

 

public testTableTmp parmTestTableTmp (TestTableTmp _testTableTmp = testTableTmp)

{

    ;

    

    testTableTmp = _ testTableTmp;

    

    return _ testTableTmp;

}

 

void showAllRecords()

{

    while select testTableTmp

    {

        info(strfmt(“%1, %2, %3”, testTableTmp.AccountNum, testTableTmp.Name, testTableTmp.Group));

    }

}

Job:

static void importTmpClass(Args _args)

{

    ImportDataTmp   importDataTmp;

    TestTableTmp        testTableTmp;

    ;

    

    importDataTmp = new ImportDataTmp();

    importDataTmp.importData();

    

    testTableTmp = importDataTmp. parmTestTableTmp ();

    importDataTmp.showAllRecords();

    

}

Result:

infolog 3 dynamics ax 2009

In this example we created two records in our temp table during our class execution and returned reference of this temporary table directly to the job. Please note that when you pass a temporary table into a method call, it is passed by reference. In other words, we don’t make a new copy of the importTmp table.

Let’s consider an example to demonstrate such behavior:

static void importTmpClass(Args _args)

{

    ImportDataTmp   importDataTmp;

    ImportTmp       importTmp;

    ;

    

    importDataTmp = new ImportDataTmp();

    importDataTmp.importData(); //Initialize temporary table with data

    

    importTmp = importDataTmp.parmImportTmp(); //Pass reference 

   

    //Create new record

    importTmp.AccountNum = “3000”;

    importTmp.Name = “Name3”;

    importTmp.Group = “30”;

    importTmp.insert();

    

    //Show all records in the temp table

    importDataTmp.showAllRecords();

}

New record has been created in the job, and you can see it from the class:

infolog 4 dynamics ax 2009

Let’s summarize: when a variable is passed by a reference, only a pointer to the object is passed to the method. When a variable is passed by value, a new copy of the variable is passed to the method (Ex: Integer, String, Container, etc)

How to create a copy of a temporary table.

If we do something like this:

TalbeTmp2 = TableTmp1 

Then all it does is creating a reference to the TableTmp1 table.

 

Let’s consider an example:

static void copyTmpTable(Args _args)

{

    ImportTmp       originalTmp;

    ImportTmp       duplicateTmp;

    ;

 

    originalTmp.AccountNum = “1000”;

    originalTmp.Name = “Name”;

    originalTmp.Group = “10”;

    originalTmp.insert();

 

    originalTmp.AccountNum = “2000”;

    originalTmp.Name = “Name2”;

    originalTmp.Group = “20”;

    originalTmp.insert();

 

    duplicateTmp = originalTmp;

 

    while select originalTmp

    {

        info(strfmt(“Original: %1, %2, %3”, originalTmp.AccountNum, originalTmp.Name, originalTmp.Group));

    }

 

    while select duplicateTmp

    {

        info(strfmt(“Duplicates: %1, %2, %3”, duplicateTmp.AccountNum, duplicateTmp.Name, duplicateTmp.Group));

    }

}}

Result:

infolog 5 dynamics ax 2009

It looks like both tables have the same data, however, as mentioned above, duplicateTmp table is just a reference to originalTmp table. Therefore, if you drop data from the original temp table, the second one won’t show any data. This is demonstrated in the next example:

static void copyTmpTable(Args _args)

{

    ImportTmp       originalTmp;

    ImportTmp       duplicateTmp;

    ;

 

    originalTmp.AccountNum = “1000”;

    originalTmp.Name = “Name”;

    originalTmp.Group = “10”;

    originalTmp.insert();

 

    originalTmp.AccountNum = “2000”;

    originalTmp.Name = “Name2”;

    originalTmp.Group = “20”;

    originalTmp.insert();

 

    duplicateTmp = originalTmp;

 

    while select originalTmp

    {

        info(strfmt(“Original: %1, %2, %3”, originalTmp.AccountNum, originalTmp.Name, originalTmp.Group));

    }

 

    //drop data

    originalTmp = null;

 

    while select duplicateTmp

    {

        info(strfmt(“Duplicates: %1, %2, %3”, duplicateTmp.AccountNum, duplicateTmp.Name, duplicateTmp.Group));

    }

}

Result:

infolog 6 dynamics ax2009

This is what happened.

  • We declared two temp tables: OriginalTmp and DuplicateTmp.
  • Inserted two records in the OriginalTmp, so the system allocated memory for those two records and set pointer to OriginalTmp.
  • Then copied a reference for the allocated records in memory to the duplicateTmp. So now the two variables represent the reference to the same allocated memory.
  • Looping through records of the originalTmp we see records in that temp table.
  • Now we delete these records from that originalTmp table, i.e. we delete those records from memory.
  • Finally, looping through the duplicateTmp table shows no records.

Note: if we want to create a new copy of originalTmp, then we need to make sure that each record is inserted into duplicateTmp table separately:

static void copyTmpTable(Args _args)

{

    ImportTmp       originalTmp;

    ImportTmp       duplicateTmp;

    ;

 

    originalTmp.AccountNum = “1000”;

    originalTmp.Name = “Name”;

    originalTmp.Group = “10”;

    originalTmp.insert();

    

    buf2buf(originalTmp, duplicateTmp);

    duplicateTmp.insert();

 

    originalTmp.AccountNum = “2000”;

    originalTmp.Name = “Name2”;

    originalTmp.Group = “20”;

    originalTmp.insert();

    

    buf2buf(originalTmp, duplicateTmp);

    duplicateTmp.insert();

 

    while select originalTmp

    {

        info(strfmt(“Original: %1, %2, %3”, originalTmp.AccountNum, originalTmp.Name, originalTmp.Group));

    }

 

    delete_from originalTmp;

 

    while select duplicateTmp

    {

        info(strfmt(“Duplicates: %1, %2, %3”, duplicateTmp.AccountNum, duplicateTmp.Name, duplicateTmp.Group));

    }

}

infolog 7 dynamics ax 2009

Using temporary tables in X++ is a powerful and useful tool, but since data in such tables is saved in memory only (or disk), you need to be very careful while using it to avoid unforeseen results.

Prior to Dynamics AX 2012, only one type of temporary table was available. In AX 2012, however, the Temporary property on tables was replaced with a new property: TableType, which has three possible values:

  • Regular – a standard physical table
  • InMemory – the type of temporary table which existed in the previous versions of Dynamics AX.
  • TempDB – a new option in AX 2012. They are “physical” temporary tables held in the SQL Server database, but this is a topic for another discussion.

Have other Microsoft Dynamics AX inquiries or needs? Visit our Help Desk for AX page to see how our experts can help! 

You can also contact Ellipse Solutions, LLC by email at info@ellipsesolutions.com or by calling (937) 312-1547.

  Codes, Codes, Codes… Commodity Codes in Dynamics 365!