Freigeben über

Quick Tip: Accessing Third Party Table Buffers using anonymous tables

  • Artikel

David Meego - Click for blog homepageI 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: 

 

MBS_FA_General_Information_MSTR_ByIndex Function

function returns integer OUT_Error;
in long IN_Asset_Index;
out string OUT_Asset_ID;
out integer OUT_Asset_ID_Suffix;
out string OUT_Asset_Description;

local text code;
local string compiler_error;

pragma(disable warning LiteralStringUsed);
if MBS_FA_Installed() then

 {Build the pass-through sanScript code.}
clear code;
code = code + "in long IN_Asset_Index;";
code = code + "out string OUT_Asset_ID;";
code = code + "out integer OUT_Asset_ID_Suffix;";
code = code + "out string OUT_Asset_Description;";
code = code + "out integer OUT_Error;";

 code = code + "clear table FA_General_Information_MSTR;";
code = code + "'Asset Index' of table FA_General_Information_MSTR = IN_Asset_Index;";
code = code + "get table FA_General_Information_MSTR by number 1;";
code = code + "if err() = OKAY then";
code = code + " OUT_Asset_ID = 'Asset ID' of table FA_General_Information_MSTR;";
code = code + " OUT_Asset_ID_Suffix = 'Asset ID Suffix' of table FA_General_Information_MSTR;";
code = code + " OUT_Asset_Description = 'Asset Description' of table FA_General_Information_MSTR;";
code = code + "else";
code = code + " clear OUT_Asset_ID;";
code = code + " clear OUT_Asset_ID_Suffix;";
code = code + " clear OUT_Asset_Description;";
code = code + "end if;";
code = code + "OUT_Error = err();";

 

 {Execute the code.}
 if execute(MBS_FA_PROD_ID, code, compiler_error, IN_Asset_Index, OUT_Asset_ID, OUT_Asset_ID_Suffix, OUT_Asset_Description, OUT_Error) <> 0 then
  {A compiler error occurred. Display the error.}
  warning compiler_error;
 end if;
 
else
 clear OUT_Asset_ID;
 clear OUT_Asset_ID_Suffix;
 clear OUT_Asset_Description;
 OUT_Error = -1;
end if;
pragma(enable warning LiteralStringUsed);

 

 

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:

  1. 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.
     
  2. 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.
     
  3. 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).

  

Execute Code Example

local integer l_key;

local text code;
local string compiler_error;

l_key = 1;

pragma(disable warning LiteralStringUsed);

if MBS_FA_Installed() then
{Build the pass-through sanScript code.}
clear code;
code = code + "in integer IN_Key;";
code = code + "local anonymous table Master_Table;";
code = code + "open table Master_Table with name technicalname(table FA_General_Information_MSTR);";

 code = code + "call with name " + QUOTE + technicalname(script MBS_FA_General_Information_MSTR_Callback) + QUOTE+ " in dictionary " + str(Runtime_GetCurrentProductID())
+ ", table Master_Table, IN_Key; ";

 {Execute the code.}
if execute(MBS_FA_PROD_ID, code, compiler_error, l_key) <> 0 then
{A compiler error occurred. Display the error.}
warning compiler_error;
end if;
end if;

pragma(enable warning LiteralStringUsed);

 

 

MBS_FA_General_Information_MSTR_Callback Procedure

inout anonymous table FA_General_Information_MSTR;
in integer IN_Key;

get first table FA_General_Information_MSTR by number IN_Key;

warning column("'Asset ID'") of table FA_General_Information_MSTR;

 

 

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.

I hope you find this technique useful.  

David

14-Feb-2013: Added Notes with additional information.

Comments

  • Anonymous
    February 05, 2013
    Very cool, I like it.

  • Anonymous
    February 06, 2013
    Very cle-va! 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.

  • Anonymous
    February 07, 2013
    Hi Mariano 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. David

  • Anonymous
    February 18, 2013
    Posting from Mark Polino at DynamicAccounting.net msdynamicsgp.blogspot.com/.../quick-tip-accessing-third-party-table.html

  • Anonymous
    January 05, 2015
    I want to access third party temporary table buffer values. How do we achieve the same, because it is a temporary table I am not getting table buffer on a field change event. I have a request to add additional field sort by option in “Time and Materials Billing” window, it is a project accounting window. Can you please help.

  • Anonymous
    January 30, 2015
    Hi Shyam You need to use the technique in the Pushing the Limits conference session materials to capture the reference to the temporary table. blogs.msdn.com/.../cross-dictionary-dexterity-development.aspx David