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.
Whenever Dynamics accesses a table (or stored procedure) the following steps occur:
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:
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.
Posting from Mark Polino at DynamicAccounting.net
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.