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:
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.
26-Oct-2010: Added information about Table_OpenNewBuffer command added in Dexterity 11.0. Also added links to Temp table error posts.
This was a very good and enlightening article. I've often wondered specifically about the different types of temporary tables and if I was using the right kind. Usually, the query would pass through my brain and I forged ahead. Now, it all makes sense!
Thank you David!
Posting from Vaidy Mohan at Dynamics GP - Learn & Discuss
Posting from Leslie Vail at the Dynamics Confessor Blogspot
Great article David, but what about Memory tables with a physical name of TEMP?
Good Question. as a Memory table is a Ctree table, I would say that a Temporary (Memory) table will behave the same as a Temporary (Ctree) table. You could always test it out if you want.
Posting from Mark Polino from DynamicAccounting.net
How to find the name of the temp table used in GP report, and how to edit / add one more column in that temp table. Pls provide steps for request.
To obtain the OS name or path to a temporary table from Dexterity you can use the Table_GetOSName() function. This function is often used after creating a temp table to pass the table name through to pass through SQL or a Stored Procedure so that SQL code can populate the table.
You would have to use Dexterity triggers (maybe in the three trigger technique) to capture the reference to the temporary table's table buffer to be able to use the Table_GetOSName() function.
However, as with all tables in Microsoft Dynamics GP, you cannot add columns to them at the SQL level, otherwise the SQL table will no longer match the table definition and Dexterity will complain.
If you need additional data, then you would either need a parallel table (which shares the same primary key fields and then has your extra fields), or for reports, I would suggest using Report Writer functions to allow additional data to be pulled onto the report.
You can either create an alternate report (if the original was in Dynamics.dic) or use the RW function placeholder scripts so the RW function calls can be added using Report Writer.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.