Dexterity developers often need to use temporary tables to perform tasks, such as displaying data in a different order, consolidating or totalling data, to store data while it is being edited, or to combine data from tables so it can be sorted using an index using fields from multiple tables.

Working with temporary tables in Dexterity can be a little difficult some times as you need to always pass the table buffer when calling functions or procedures. This is because if the table buffer is not passed, a new instance of the table buffer will be created and a new instance of a temporary table will actually be a completely new temporary table with no data in it.

This becomes more complex if you want call a procedure in the background.  Procedures which are called in the background cannot have a table buffer as a parameter because it would be possible for the form or script that created the table buffer to be closed while the background procedure is still in the process queue. In this situation, you will have to use a "permanent" temporary or a memory table (see below for details).

Following is a description of the different types of temporary tables:

• Temporary (SQL): This is a table with the Physical Name of "TEMP" and a Database Type of Default or SQL. The table will be created in the SQL TempDB database. This type of temporary table should be used if the data set will be very large or the data will be used in the Report Writer.  This is to ensure we don't get out of space errors, performance issues and so the Report Writer can use SQL optimized queries.
   
• Temporary (Ctree): This is a table with the Physical Name of "TEMP" and a Database Type of Ctree. The table will be created in the current user's temporary (Temp) folder using the naming convention TNT####.dat, TNT####.idx and TNT####.tmp. This type of temporary table is very fast to work with small to medium data sets as it is all local to the workstation, however you would not be able to use any SQL optimized commands like range table where. Even though Ctree is no longer a supported primary database platform, it is still used within Dexterity for many temporary tables.
   
  Note: Problems with Ctree Temporary tables can occur when the files are left in the temporary folder, see these posts for more information:  An open operation on table '[TEMP Table]' errors and Unexplained Temp Table Errors.
   
• Memory (Ctree): This is a table with a Database Type of Memory. Originally, these tables were actually stored in RAM, but as this sometimes caused out of memory errors, they were moved to physical Ctree tables stored in a TNT#### subfolder in the current user's temporary (Temp) folder. While not a permanent physical table, these tables remain in existence while the current instance of the application is running. They can be accessed in procedures and functions without having to pass a table buffer.  Again they should not be used for large data sets as the local workstation could run out of hard disk space and they cannot be used with SQL optimized commands.
   
• Permanent (System Level): This is a table with the Database Type of Default or SQL and the Series of System.  Usually the Physical Name is numbered in the 50000's. It is a SQL table stored in the DYNAMICS system database. To allow it to contain temporary data without clashing with data from other users or companies, it must include the Company ID and User ID fields in the table and these two fields should be the first fields of all keys. Before use, a range should be set for the current user and company and the range removed.  The data should also be removed or "cleaned up" after use.  This sort of temporary can be used without needing to pass a table buffer and so works well for background processes.
   
• Permanent (Company Level): This is a table with the Database Type of Default or SQL and the Series of Company (or other company level series).  Usually the Physical Name is numbered in the 50000's. It is a SQL table stored in the company database. To allow it to contain temporary data without clashing with data from other users, it must include the User ID fields in the table and this field should be the first field of all keys. Before use, a range should be set for the current user and the range removed.  The data should also be removed or "cleaned up" after use.  This sort of temporary can also be used without needing to pass a table buffer and so works well for background processes.

One of the common uses for temporary tables in the past was to allow for data to be sorted into an order that is not one of the keys on the table.  This is no longer needed as it is now possible to create Virtual Keys using the assign as key command. See the Dexterity documentation for more information.

If you need a temporary table with the same definition as an existing table, you no longer need to create a duplicate table definition and change the Physical Name to "TEMP". Instead you can use the Table_OpenAsTemp(table table_name) command. See the documentation for details.

[Edit] There is also a new undocumented command which can create a new table buffer based off an existing table buffer or reference to a table buffer. The command returns the reference to the table buffer, the syntax is new_reference = Table_OpenNewBuffer(table(table_reference)). As this is currently undocumented, use of this is at your own risk. That said it is used extensively in the code that handles Word Template integration from the Report Writer.

I hope this explanation of the different types of temporary tables and when each type should be used is of value.

David

26-Oct-2010: Added information about Table_OpenNewBuffer command added in Dexterity 11.0. Also added links to Temp table error posts.