Quick Tip: Accessing Third Party Table Buffers using anonymous tables

I have been spending a lot of time recently working on consulting engagement in Malaysia. This project involves a large amount of custom Dexterity code, some of which involves accessing tables in other third party product's dictionaries.

In the past, I have covered Cross Dictionary Dexterity Development techniques and the usual method I recommended for accessing data from third party tables is to use the execute() function to execute code in the third party product to set key fields in a table buffer, get the table record and then return the required data.

Below is an example of that technique to retrieve data from Fixed Assets:

The method above is great for getting a single record (1 to 1 relationship), but how can you move through a range of records in a third party table (1 to many relationship)?

Here are some methods I have used previously for handling moving through a range of records:

Using similar code to the example above, you can pass in the parameters to set the range and the previous values for the key fields (blank will get first). Then issue a get next table command and return the key fields and any other additional data needed. If the EOF (End of File) is reached then clear the table buffer so that the returned parameters are blank. This code can be called once to get the first record and then from inside a while loop to get the next record. Each time we are passing back the parameters for the range and the previous records key fields. This method is good for reading values, but not so useful if you need to manipulate/save data or access every field in the table.

Create a global procedure in your dictionary which has the parameters for the fields you need from the third party table. Then use an execute() function to create the range on the third party table, get the first record and then loop through the table using a while loop with a get next table statement. Inside the while loop use a call with name <procedure> in dictionary <id> command to call back to your dictionary. This code will then loop through the table and call back into your code for each record.

Not Recommended: Before we had the execute() command, you could create a duplicate table definition in my dictionary which could access the physical table at the database level. This technique has issues if the original table structure gets changed. It does not work for temporary tables and will bypass Dexterity table triggers (if any) on the original table.

So, now I want to introduce a new method that I worked out that allows full access to the third party table almost as though it was actually a table in your dictionary. The concept is execute() some code in the third party dictionary to pass back the table buffer you want as an anonymous table. It requires a procedure in your code which can be called from the execute() statement (similar to method 2 above).

Once you have the anonymous table in your code you can perform any action you like on the table. The only exception is that you should use the column() function to refer to the columns or fields. If you require other parameters to be passed, you can add them to your callback procedure and then pass them via the execute() statement, such as the IN_Key parameter in the example above.

Note: This technique will not work for TEMP tables as it will just create a new instance of the temporary table with zero records. Also, don't expect the Dexterity Script Log to be able to identify the anonymous table passed to the callback script... it will probably show in the logs as [Not Found]

In our project we used a cross dictionary trigger on a Fixed Assets procedure to capture one table buffer that was passed as a parameter. We then used a variation of the above technique to pass the captured table buffer as an anonymous table parameter through to the execute() statement, which in turn passed it back to our callback procedure as a second anonymous table parameter along with the additional table buffer from the execute() statement itself.

You missed one method: pass-through SQL with Dex. Still very effective to reach multiple rows in a table, supports temp tables and so long you don't use a "SELECT *" in your pass-through queries, you should be covered for upgrades.

Using Pass through SQL can work well, especially for large "set" based changes. SQL Temporary Tables can be used if you pass through the table name captured from Table_GetOSName().

The method in this article is excellent when you want to move through the table record by record or what the functionality of a table buffer which can "hold" values while changes are made to other values and then be saved.