WINDOWS Live Search

To contact me for any clarifications regarding any posts / information, please mail me at arijit [dot] basu [at] gmail [dot] com


Saturday, December 2, 2006

Temporary tables

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.
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.
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.
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 project.
Creating temporary tables
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.
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.
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());
}

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.

No comments: