Quick Tip: Using Third Party Tables to fill a Scrolling Window
My good friend, Mariano Gomez (The Dynamics GP Blogster) raised this issue recently. He wanted to use the method I discussed in my previous Quick Tip post: Quick Tip: Accessing Third Party Table Buffers using anonymous tables to gain access to the third party table so he could display it in a Scrolling Window on his form.
The reason why this will not work in its simplest form is that the anonymous table buffer that we have which points to the third party table only has the scope of the callback global procedure. Once that procedure completes access to the third party table buffer is lost.
So what are the alternative solutions?
Going back the Cross Dictionary Dexterity Development techniques, I previously would have suggested a duplicate table definition, however as discussed in point 3 of the previous article, this is not a recommended method anymore.
Note: I am assuming here that the data to be displayed is a small subset of the entire third party table and not containing thousands of records. If you want access to the entire table, the duplicate table definition method might be for you.
Now I would suggest the use of a temporary table to contain the data you want to display in your Scrolling Window. To make this work all you need to do is populate the temporary table before you issue the fill window command and if you are allowing edits and or adds, you must write back the data from the temporary table to the actual table when you have finished.
If the third party table is stored in SQL Server (database type: SQL or default), then make sure that your temporary table is also created in SQL (database type: SQL or default) and not a local ctree table. This means that we can leverage SQL optimization to transfer the data between the actual third party table and the temporary table.
Note: I would suggest adding extra boolean fields to the temporary table to allow you to track what records have been edited or added. You might also want to add keys to the temporary table with these boolean fields as the first segment in the index.This will help later when you need to write the data back to the original third party table.
Below are some suggested methods for transferring the data between the original third party table and your temporary table:
- Use the method described in the post: Quick Tip: Accessing Third Party Table Buffers using anonymous tables to capture the table buffer. You can then set a range and use range copy to move the data into the temporary table. The range copy command will use a single SQL insert statement if both tables are SQL tables (and there are no Dexterity triggers on the tables). To copy the data back, you can remove all records in the range from the original table and use range copy to replace the updated data (as long as your temporary table contained every column in the original so no data is lost). Otherwise you could use Dexterity to range on updated or added records and write back the data record by record.
- Use pass through SQL to create the appropriate statements to transfer data to and from the temporary table. Use the Table_GetOSName(table temp_table_name) command to obtain the physical SQL name for the temporary table. The boolean flags added to the temporary table can be used to control what records are inserted and what records are updated at the SQL level.
- You can also use a call to a SQL Stored procedure, but this will require the creation of the stored procedure at the SQL level and the creation of a Dexterity prototype procedure to call it. The advantage of stored procedures is that the code can be precompiled. However, in this case as the temporary table's name is not known, the code will have to be dynamically generated and then executed with the EXEC (@variable) command. Thus losing any precompiled benefits.... Forget it this method is not worth the effort.
I hope this post has provided some insight into how you can access third party data in your own scrolling windows.
Enjoy
David