Developing for Dynamics GP

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

How to access data from other companies from Dexterity

How to access data from other companies from Dexterity

  • Comments 7

David Meego - Click for blog homepageHave you ever had to develop Dexterity code that needs to read data from a company other than the current Microsoft Dynamics GP company? Well, with my latest updates to the Support Debugging Tool, I came across this requirement.

While developing the support for the Extender security objects, I realised that the Extender objects are stored on a per company basis and to obtain the descriptions for the various objects I would need to read the tables in companies other than the one currently logged into.

I knew the method and so was able to implement this functionality, but I thought that it would make a good blog post incase others need to achieve a similar goal.  Below is the concept followed by some example code:

  1. First check the SY_User_Company_Access_REL table to see if the user has access to the company.
  2. Lookup the location for the table from the SY_Pathnames table, using the Dictionary ID, Company ID and Series.
  3. Call the Translate_Pathnames procedure incase location translations are being used.... usually not for SQL systems.
  4. Disable Error Checking to stop table execptions being displayed and stopping the code execution.
  5. Open the table using the pathname read previously.
  6. Perform the required table access.
  7. Close the table.
  8. Enable Error Checking.

The example code below was tested using the Support Debugging Tool's Runtime Execute window to execute the code in the context of the core Microsoft Dynamics GP dictionary (DYNAMICS.DIC). 

Cross Company Demo Example Code

{ Demo to show first customer in each company }
local string l_pathname;
local text l_results;
local string l_tab = char(9);
local string l_crlf = char(13) + char(10);

clear table SY_Company_MSTR;
get first table SY_Company_MSTR;
while err() <> EOF do
 l_results = l_results + str('Company ID' of table SY_Company_MSTR) + l_tab
    + 'Intercompany ID' of table SY_Company_MSTR + l_tab;

 { Check Company Access for current User }
 clear l_pathname;
 clear table SY_User_Company_Access_REL;
 'User ID' of table SY_User_Company_Access_REL = 'User ID' of globals;
 'Company ID' of table SY_User_Company_Access_REL = 'Company ID' of table SY_Company_MSTR;
 get table SY_User_Company_Access_REL;
 if err() = OKAY then
 
  { Get Pathname location for product, company and series }
  clear table SY_Pathnames;
  'Company ID' of table SY_Pathnames = 'Company ID' of table SY_Company_MSTR;
  'DictID' of table SY_Pathnames = 0 {DYNAMICS};
  'File Series' of table SY_Pathnames = 3 {Sales};
  'Logical File ID' of table SY_Pathnames = 0 {Default};
  get table SY_Pathnames;
  if err() <> OKAY then
  
   { If no pathname found fall back to DYNAMICS, Company and Company Series }
   clear table SY_Pathnames;
   'Company ID' of table SY_Pathnames = 'Company ID' of table SY_Company_MSTR;
   'DictID' of table SY_Pathnames = 0 {DYNAMICS};
   'File Series' of table SY_Pathnames = 8 {Company};
   'Logical File ID' of table SY_Pathnames = 0 {Default};
   get table SY_Pathnames;
  end if;
  if err() = OKAY then
   { Once Pathname found, run through location translation }
   { This is not really used on SQL systems, but is best practice }
   l_pathname = 'Data Pathname' of table SY_Pathnames;
   call Translate_Pathnames, l_pathname;
   l_results = l_results + l_pathname + l_tab;
  end if; 
 end if;

 { If a pathname has been identified attempt to access table }
 if not empty(l_pathname) then
  { Disable error checking in case database missing  or inaccessible }
  Table_DisableErrorChecks(true);
  { Open table using specified path }
  open table RM_Customer_MSTR as DB_TYPE_TABLEDEFAULT, l_pathname;
  if err() = OKAY then
   clear table RM_Customer_MSTR;
   get first table RM_Customer_MSTR;
   if err() = OKAY then
    l_results = l_results + 'Customer Number' of table RM_Customer_MSTR
       + l_tab + 'Customer Name' of table RM_Customer_MSTR ;
   end if;
  end if;
  { Close table }
  close table RM_Customer_MSTR;
  { Enable error checking }
  Table_DisableErrorChecks(false);
 end if;

 l_results = l_results + l_crlf;
 get next table SY_Company_MSTR;
end while;

warning l_results;

{
// 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.)
}

The Support Debugging Tool Configuration Settings file for this Runtime Execute example code is attached to the bottom of this post.

Let me know if this is helpful. 

David

Attachment: Debugger Settings Cross Company.dbg.zip
  • Thanks David for sharing this information. I can imagine scenarios where it will be very handy.

    Rubal,

    http://dynamicsgphelp.com/

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

    www.vaidy-dyngp.com/.../access-data-from-other-companies-from.html

  • Interesting stuff! I had no idea you could do that without using inline SQL.

    Thanks David

  • Hi created a new dexterity window with search box and the scrolling window

    The search field in the scrolling window was index Non-Unique Index.

    Can somebody tell me where I am making mistake because it is not select the record

    if empty('(L) EmpSearch' of window EmployeeSearch) then

    warning "Please select a record first.";

    else

    set 'Frstname' of table EmpRate to '(L) EmpSearch';

       get table EmpRate by number 1;

    {run script field 'Redisplay Button' of window 'EMP_LOOKUP';}

       fill window SCR_Employee from current by number 1;

    focus 'Frstname' of window SCR_Employee  ;

    run script field 'Redisplay Button' of window 'EMP_LOOKUP';

    end if;

  • Hi

    Please only post comments that relate to the current post. You should ask questions like this on the forums, links in the navigation pane.

    David

    PS: Remove the extra call to 'Redisplay Button', that will remove the find you already did. You just need to set the key field, then a get table, fill window from current and a focus to make this work.

  • David,

    I'm wondering if there is a similar "OPEN" procedure in the VS Toolkit (DEX.net if you will) that accepts a pathname parameter. I need to open a table in another GP company within VS Toolkit, but cannot find an appropriate place to specifiy the path (database) that I would like it to use. Specifically, I need to get the ACTINDX from the GL00105 from another company (creating an IC JE). I used IcGetAccount in GP 10, but there is some type of bug in GP 2010 causing that method to not work.

    Microsoft Support suggested using GPConnNet, which would work, but I would rather avoid that as I am working on a "commercial" application that will be deployed to a number of sites.

    Ideas?

    Thank you for your contributions to the community!

  • Hi Brian

    I don't think the VS Tools supports the pathname option provided by Dexterity. So here are some ideas:

    1) Create the code in a Dexterity chunk which can be called from VS Tools.

    2) Use the Continuum library to run pass through Dexterity sanScript.

    3) Connect via SQL using GPConnNet to get the connection.

    The next idea is a little risky as it might affect other code running:

    4) Manipulate the 'Series Pathnames'[index] of globals variable for the series you need, open the table and then restore the original value back.  Make sure that this manipulation occurs immediately before you open the table and is restored immediately after.

    Hope this helps.

    David

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