Developing for Dynamics GP

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

Dex - How to work with a passed anonymous table without changing table buffer contents

Dex - How to work with a passed anonymous table without changing table buffer contents

  • Comments 5

David Meego - Click for blog homepageThis cool technique comes out of an issue that I was working on as part of a support case.  I will not go into specifics, but will explain what was happening.

We have a Dexterity window which reads a table and displayed information from that table to the window.  However, not all the fields in the table have associated window fields, so some of the fields are read and changed using the table buffer.  When the record is saved, the table buffer changes are written down to the physical table.  All sounds fine.

What we found is that when the browse buttons on the window were used, the scripts that ran called a generic procedure.  The window's table buffer was passed anonymously to this generic procedure.  Inside that procedure, the script used a get table command to check if the record passed in the table buffer actually exists. Due to the get table command, side effect of this generic procedure is that the contents of the passed in table buffer are populated with the data stored in the physical table on SQL Server. 

So, what is the issue?  Well, the call to the generic procedure occurs before the call to save the record is made.  As this window is relying on the data in the table buffer, it means that any changes made to the table buffer were lost when the browse buttons were used and save was selected from the "Do you want to save?" dialog.  Clicking the save button worked fine as this does not call the generic code.

Below is some code that shows the situation:

{ Example Procedure Code }
in anonymous table tbl;
out boolean found;
 
get table tbl; 
if err() = OKAY then
  found = true; 
else
  found = false;
end if;

As the generic procedure is called from more that one location, I wanted to find a way to fix the procedure so that it could still look up a table record, but leave the original passed in table buffer untouched. Normally, you would say to use a function or procedure and don't pass the table buffer (just pass the key values), this creates a new instance of the table buffer and the original table buffer is untouched.  However, that method can not work for an anonymous table where we don't know the table or what are its key fields.

Below is some example code showing the changes made to make the code work as desired:

{ Example Procedure Code }
in anonymous table tbl;
out boolean found;

local anonymous table tbl2;

open table tbl2 with name technicalname(table tbl);
copy from table tbl to table tbl2;


get table tbl2
if err() = OKAY then
  found = true; 
else
  found = false;
end if;

This technique creates a second anonymous table buffer based on the technical name of passed in table and then copies the contents of from the passed in table to the second table buffer.  We can then perform whatever table operations we like on this second anonymous table without affecting the original table buffer passed as a parameter.

[Edit] I just tested this technique using 3rd party tables called from a 3rd party script and confirmed that it does work from any dictionary.

I hope you find this advanced Dexterity technique a useful addition to your arsenal.

David

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

  • Posting from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com/.../dex-how-to-work-with-passed-anonymous.html

  • Is it possible to check whether an anonymous table has been opened?  I had some code, and out of laziness wanted to just check whether it was opened but couldn't find a script or function to do that.  I also wanted to check if it's open before attempting to close it while inside a loop.  Thanks for any help you can provide.

  • Hi Scott

    I am not sure there is a method of checking if a table is open.  The act of checking would reference it, which would open it.

    Better not to open and close tables in a loop if possible as this does have performance overheads.

    David

  • Scott,

    I would agree with David in that I can't think of any way to check to see if a table is open.  You mention that you were going to check to see if it was open before you attempted to close it.  The good news is that you don't have to worry about that - using 'close table' on a non-open table won't hurt anything.  The call will essentially do nothing.

  • Got this working now with code changes below, found some older articles on some other of your posts that pointed me to the right direction - thanks.

    local integer status = '(L) Status';

    local integer prod_id = '(L) Product ID';

    local string ddReportName = '(L) Report Name';

    local string ddFilePath = '(L) File Path';

    local string FileExt = '(L) File Extension';

    local string sRestriction = '(L) Restriction';

    local anonymous field ret_val;

    local integer storagetype;

    local string compile_errors = '(L) Compile Errors';

    local text code;

    local string compile_message,fileExt,extErr;

    local integer error_count;

    local string tablename = '(L) TempTableRemove';

    {local anonymous table report_temp_table;}

    {close table report_temp_table;}

    {open table report_temp_table with name '(L) TempTableRemove';

    copy from table('(L) TempTable' of window EASI_RepForm of form EASI_RepForm) to table report_temp_table;}

    warning "going to remove the restriction for testing";

    sRestriction = "";

    code = code + "inout anonymous table report_temp_table;";

    code = code + "close table " + tablename + ";";

    code = code + "clear table " + tablename + ";";

    code = code + "copy from table report_temp_table to table " + tablename + ";";

    code = code + "warning column(""Document Number"") of table " + tablename + ";";

    code = code + "save table " + tablename + ";";

    code = code + "warning column(""Document Number"") of table " + tablename + ";";

    code = code + "run report with name """ + ddReportName + """";

    if not empty(sRestriction) then

    code = code + " with restriction (" + sRestriction + ") ";

    end if;

    {filepath in dictionary dictid;}

    fileExt = FileType_GetNameByName(FileExt);

    code = code + " legends ""0"", """", """", """", """", ""1""";

    code = code + " destination false,false,"+fileExt+",""" + ddFilePath + """" + " in dictionary " + str(prod_id) + ".";

    {code = code + "close table " + tablename + ";";

    code = code + "warning ""table closed"";";}

    error_count = execute(prod_id, code, compile_message, table('(L) TempTable'));

    if error_count = 0 then

    {Success. Return the value.}

    if missing(storagetype) = false then

    storagetype = datatype(ret_val);

    end if;

    status = error_count;

    else

    {Errors in the pass-through sanScript.}

    call EASI_Log_Error,compile_message;

    status = error_count;

    if missing(compile_errors) = false then

    compile_errors = compile_message;

    end if;

    end if;

    {close table report_temp_table;}

Page 1 of 1 (5 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 5 and 1 and type the answer here:
  • Post