How to access data from other companies from Dexterity

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Have 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 }
    { 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 }
  end if;

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

warning l_results;</span>

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, <a href=""></a>.)

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.


Debugger Settings Cross

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on

7 thoughts on “How to access data from other companies from Dexterity

  1. 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.";
    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;


  2. 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.
    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.


  3. David,
    I'm wondering if there is a similar "OPEN" procedure in the VS Toolkit ( 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.
    Thank you for your contributions to the community!


  4. 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.


Please post feedback or comments

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.