Temporary tables in Axapta

Temporary tables are used for non-persistent storage in Microsoft Axapta.

They are useful in two common situations

1. As the 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.


Contents
[hide]

* 1 Scoping rules for temporary tables
* 2 Creating temporary tables
o 2.1 In the AOT
o 2.2 Making an existing table temporary
* 3 Temporary tables in forms
* 4 Temporary tables in reports
* 5 Temporary table performance
* 6 Indexes on temporary tables
* 7 Security on temporary tables
* 8 Database transactions (tts) on temporary tables

[edit] Scoping rules for temporary tables

In general, each instance of a temporary table, and it's associated data, will only exist while the buffer variable used to access it is in scope.

You can point multiple buffer variables to the same instance of a temporary table by using either the .setTmpData() method or by directly assigning the buffers to each other, identically to normal tables. In this way, even if your original buffer variable goes out of scope, your data will be retained while one of the other referencing variables remains.

Be aware that static table methods - such as find() - will not work with temporary tables unless you pass through the buffer variable to the method.

For example, this method will not work on a temporary table, as the tempTable variable used is newly created and will always contain no records.

// This won't work on temporary table
public static TempTable find(AccountNum _accountNum, boolean _forUpdate = false)
{
TempTable tempTable;
;

if (_accountNum)
{
tempTable.selectForUpdate(_forUpdate);

select firstonly tempTable
where tempTable.AccountNum == _accountNum;
}

return tempTable;
}

If you want to have a find() method on your temporary table, then you will need to modify it slightly to pass through a reference to our populated temporary table.

// Use this pattern instead
public static TempTable find(AccountNum _accountNum, TempTable _tempTable, boolean _forUpdate = false)
{

if (_accountNum)
{
_tempTable.selectForUpdate(_forUpdate);

select firstonly _tempTable
where _tempTable.AccountNum == _accountNum;
}

return _tempTable;
}

Some examples of populating and using temporary tables can be found in Image:TRG TempTablesGeneral.xpo project.
[edit] Creating temporary tables
[edit] In the AOT

Set the Temporary property to Yes to create a table which will always be temporary.

Note that any existing data will be permanently deleted if you do this!

Of course, you can no longer use the Table Browser to check the data, as the data is stored only per scoped instance of this table.
[edit] Making an existing table temporary

You can convert a normal table to a temporary table in code. For example, if you wish to create a temporary copy of the inventory table:

InventTable inventTable;
;

inventTable.setTmp();

Doing so will remove all data from the temporary copy of the table. If you wish to create a populated temporary copy of a standard table, you can do the following:

InventTable inventTable;
InventTable inventTableTmp;
;

inventTableTmp.setTmp();
while select inventTable
{
inventTableTmp.data(inventTable.data());
inventTableTmp.doInsert();
}

You can now add, modify or delete data from the table without affecting the real contents stored in the database.
[edit] Temporary tables in forms

Using temporary tables in forms requires the use of the .setTmpData() method.

For example:

The temporary table data is populated in a static class method (running server side), which is called from the form and returns the populated table. We could populate a form-level buffer with the temporary data if needed, or else just call the populating method directly from the setTmpData() call as shown below.

In the form datasource init(), we use .setTmpData() to instruct the datasource query to use our temporary table. Our datasource name in this example is TempTable.

public void init()
{
super();

TempTable.setTmpData(tmpTableClass::populateTmpData());
}

See Image:TRG TempTablesForm.xpo for an example of a working form based on a temporary table.

It is also possible to add a table to a form which is not a temporary table, but the data that is shown must be temporary. Compared to the previous example there are not a lot of changes; just make sure the table is made temporary using .setTmp(). As in the previous example the temporary table data is populated in a static class method (running server side), which is called from the form and returns the populated table.

In the following example the InventTable is used.

public void init()
{
super();

InventTable.setTmp();
InventTable.setTmpData(InventTableClass::populateTmpData());
}

[edit] Temporary tables in reports

The correct method of using temporary tables in reports is slightly different from that of forms.

The most important difference is the use of .setRecord() instead of .setTmpData(). A simple example follows:

public boolean fetch()
{
boolean ret;
;
this.queryRun().setRecord(tmpTableClass::populateTmpData());

ret = super();

return ret;
}

As there is often already a supporting RunBaseReport class being used to run the report, it is easy to integrate the population of the temporary data into that existing class. This is particularly useful if you need the data in the temporary table to be dependent on information entered into the report dialog prompt by the user.

See Image:TRG TempTablesReports.xpo for an example of using a RunBaseReport class to run a report based on a temporary table.
[edit] Temporary table performance

Data being stored in temporary tables is stored in a temporary physical file in the file system. The file itself is created when the first record is being inserted in that particular instance of the temporary table. Hence, in a 3 tier environment, the file will be maintained on server or client side, depending on where the first record is inserted. From a performance standpoint this is a concern when using temporary tables.
[edit] Indexes on temporary tables

As with normal tables, indexes can be created on temporary tables. When a temporary copy of a normal table is used with .setTmp(), then the existing indexes will also be created on the temporary version. For new temporary tables (with the Temporary property set to Yes), you must create any desired indexes through the AOT in the normal way.

Indexes have a substantial effect on temporary table performance. For temporary tables with a lot of records you will experience major performance limitations when searching on non-indexed fields.
[edit] Security on temporary tables

You can assign a SecurityKey to a temporary table, like any other. The security key will work well, limiting access. However, temporary tables never show up in the tree for assigning permissions, so it's not possible to actually enable them for users. Therefore it's important not to put a security key on any temporary table or users will never be able to use it.
[edit] Database transactions (tts) on temporary tables

Temporary tables are not included in Dynamics Ax's normal transaction processing capabilities. If you include population of a temporary table inside a ttsBegin/ttsCommit which then aborts, changes made to the temporary table will not be aborted.

To activate transaction capabilities on temporary tables, use the local ttsBegin and ttsCommit methods on the temporary table buffer themselves. These work as expected.