Cross Linked Companies, Pathnames and Everything

A long time ago, in an ERP system far far away, data was stored in tables which in turn were stored as files in folders.

Yes, before Great Plains Dynamics became Great Plains Dynamics C/S+ for SQL (version 3.15/3.17), we had to specify pathnames to dictate where the Ctree or Btrieve files for each series (Financial, Sales, Purchasing, Company, System, etc.) and for each product / dictionary would be located. There was also a location translation system which allowed the paths to be mapped when a workstation required a different drive letter or machine name to access the data.

With Dynamics running on SQL Server and especially when it became the only supported platform (version 8.00), the use of pathnames and location translations were pretty well forgotten about.

So why am I bringing this up again now? And why am I using Star Wars and Hitch Hikers Guide To the Galaxy references?

I will answer the second question first because it will be quick.... Because I am a geek and know that the answer to Life, the Universe and Everything is 42.

Now back to Pathnames....

The reason that I am bring them up is because they are still important and are still used by the Microsoft Dynamics GP system and recently I had a case where a problem with the pathname settings caused cross linked data between companies.

The situation was with a 3rd party product which had been set up in one company. When the customer logged into a second company, all the data from the first company was showing.

While this seems a bit confusing, it makes sense once you understand how pathnames work and what was happening.

Theory Bit

Whenever Dynamics accesses a table (or stored procedure) the following steps occur:

• If the table belongs to a table group (logical file grouping), the resource ID and series of table group is identified as well as the dictionary ID of the product the table belongs to.
   
  Using the currently logged into company ID, dictionary ID, file series and logical file ID, the SY_Pathnames (SY02100) table is consulted to locate the data pathname. Note: Individual data pathnames for each table group are not usually used.
   
  If no record is found, the system will look again this time with a value of 0 (default) for the logical file ID.
   
• If the table does not belong to a table group, the series of the table itself is identified as well as the dictionary ID of the product the table belongs to.
   
  Using the currently logged into company ID, dictionary ID, file series and a logical file ID of 0 (default), the SY_Pathnames (SY02100) table is consulted to locate the data pathname.
   
  If no record is found, the system will look again this time with a value of 0 for the dictionary ID, ie. Product = Microsoft Dynamics GP.
   
• The resulting data pathname is checked against the SY_Pathnames_Translate_MSTR (SY03600) table for the Work Station Type defined in the Workstation Dex.ini setting. If there is a match the data pathname will be modified to adjust the final path. As the SY_Pathnames_Translate_MSTR table is empty in a SQL system, there should be no location translations occurring.
   
• The final path is now used to access the table at the SQL Server.
   
• If the path is not blank, the table will be located in the database specified.
   
• If the path is blank and a default database is specified in the ODBC (Open Data Base Connectivity) DSN (Data Source Name) settings, the table will be located in this default database.
   
• If the path is blank and no default database is specified, the table will be located in the master system database.

So now you can see that if the data pathname information is missing it is very likely that the table will end up in the wrong database .... AND that the database won't change depending on the company you are logged into.

The bottom line is that when data appears to be cross linked with the same data appearing in more than one company, the cause is quite possibly related to pathnames.

Practical Bit: How to Fix

The user interface for pathnames is still in the Dynamics GP product, just not directly available from the navigation menus. To open it follow the following steps:

1. Right mouse click on the Shortcuts area.
2. Select Add >> Add Window.
3. In the Available Windows tree, select Microsoft Dynamics GP >> Company >> Pathnames.
4. Click Add.
5. Click Done.

The shortcut called Pathnames should now be available, click on it to open the Pathnames window.

From this window, you can check and set the pathnames for the current company for each product and series.

The pathnames for System series should be DYNAMICS\dbo\ and for all other products should be the company database name followed by \dbo\. For example: TWO\dbo\.

Back to our case, when we searched for the tables for the misbehaving product, we found them in the master database. We fixed the data pathnames to add the required records into the SY_Pathnames (SY02100) table for the addon product. We then ran Dynamics Utilities which created the tables in the correct databases and then we dropped the tables from the master database. We could have taken an extra step to copy data from the master database before dropping the tables, but decided that the data was corrupted due to the cross linking and that it was safer to start again.

Advice to Developers

It is best practice to add your tables to table groups based on logical groupings of related tables. It is also best practice to populate the SY_Pathnames (SY02100) table for your product id and series for the table groups you have used... especially if using Project series or 3rd Party series as these are not populated by default by Microsoft Dynamics GP. 

The easist method is to read the value for Microsoft Dynaimcs GP (Dictionary ID = 0) Company Series (File Series = 8) or System Series (File Series = 7) and save the entries for your product and series using the same pathnames. If you add this code before your table creation code, you will make sure your tables are created in the correct locations.

Hope you find this helpful.

David