Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Working with Temporary Tables in Dexterity

Working with Temporary Tables in Dexterity

  • Comments 8

David Meego - Click for blog homepageDexterity 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.

  • David,

    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!

    Leslie

  • Posting from Vaidy Mohan at Dynamics GP - Learn & Discuss

    www.vaidy-dyngp.com/.../temporary-tables-in-dexterity-david.html

  • Posting from Leslie Vail at the Dynamics Confessor Blogspot

    dynamicsconfessions.blogspot.com/.../autocomplete-data-for-gp-its-not-really.html

  • Great article David, but what about Memory tables with a physical name of TEMP?

  • Hi Tim

    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.

    David

  • Posting from Mark Polino from DynamicAccounting.net

    msdynamicsgp.blogspot.com/.../working-with-temporary-tables-in.html

  • 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.

  • Hi Syed

    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.

    See

    support.microsoft.com/.../888884

    David

Page 1 of 1 (8 items)
Comments Information

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.

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post