How to access data from other companies from Dexterity
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:
- First check the SY_User_Company_Access_REL table to see if the user has access to the company.
- Lookup the location for the table from the SY_Pathnames table, using the Dictionary ID, Company ID and Series.
- Call the Translate_Pathnames procedure incase location translations are being used.... usually not for SQL systems.
- Disable Error Checking to stop table execptions being displayed and stopping the code execution.
- Open the table using the pathname read previously.
- Perform the required table access.
- Close the table.
- 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, https://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