Today's #TipTuesday is not an application tip but a technical one.

Most Dynamics GP clients have one or more test companies or test environments. I'd be willing to wager that many have some standard scripting that is run when a test company is refreshed and may even automate it in a SQL stored procedure or job. There are at least 2 standard Microsoft scripts that should be run all the time: changing the db_owner to 'DYNSA' and the standard script to update company database references in the refreshed company db.

There has been a proliferation of modules and add-on products over the years that have pathnames in them, which theoretically should be changed in the test company. Modules that come to mind are EFT for Payables, EFT for Receivables, and Safepay. There may also be pathnames in things like Posting Setup for sending posting journals to file. There are other examples in ISV (third-party) products.

Depending on the kind of testing that is being done, those pathnames shouldn't be the same in Production and Test. Consider EFT, creating a test EFT batch and accidentally sending the file to the bank. Hopefully, there are controls in the processes to avoid it but one customer I have worked with used to have an FTP process that automatically uploaded the EFT files to the bank without a user touching it. If they don't change their Test company pathnames, that could be a serious gaffe!

What I'm describing below are examples where it could be scripted, but admins could also just set up a post-refresh checklist for someone to manually go through various setup windows in GP and update these things. (That could be time-consuming and they still have to remember to do it!)

Examples of what to look at

1. Next Note Index

If there are notes (window notes, record notes), this is something that should be updated because the next note index for the test company could be quite different from the production company. The "next note index" is stored in the SY01500 table by company. In many cases, this may not matter but I recommend updating the Next Note Index to whatever the value is from the company it was copied from. At times I see things like notes on records that don't make sense (re-use of a number) and occasionally odd errors when a note index is being assigned when it already exists.

2. EFT Chequebook Setup

The CM00101 table stores the pathnames for the EFT files in the DomPmtsFile, ForeignPmtsFile and EFTPMPrenoteFile fields. I typically use the REPLACE command to search and replace the pathname value with a \TEST folder extension for example.

3. EFT File Format Setup

The bank we use at my company has a File Number that could be replaced with the word TEST when testing EFT files. That field is stored in the CM00104 table, although the fields and locations will differ based on the specific bank's EFT file. Query the table to find the value to change.

In my case, since the next number usually increments and I have to change it to a static value, my update statement contains several fields to override the fields that are affected (in my case, EFTMapsTo, EFTSourceTable, FieldName and EFTDataValue are what I need to update or clear out as the case may be, for a specific EFTLineType and EFTField Number.

Our bank also had a header record that contained the word PROD for production files and TEST for test files. That is in the same table and is a simple one-field replacement in my case, using REPLACE.

4. Safepay pathnames

We use Safepay so we also want to update the pathnames for that product. The ME123501 table stores what I need to update and it's the DLFILAPTH field.

Sidebar: who named these tables and fields? Crazy field names!

How do we do this?

I've gone a little medium-tech on this and set up the various steps in a single SQL job. I could have created a stored procedure to do this all for me but what I wanted was a method that was easy to update if changes are needed in the future. I also wanted our IT department to understand the moving parts instead of having a "black box" stored procedure where they don't know what it does.

For our production server, and test companies, there is one Job per Prod-Test company relationship and I put in separate steps for each type of script so I can see clearly what they are doing. I also make it clear where the script comes from, i.e., Microsoft or internal. We have some permissions for reports we also update and those are purely internal scripts, nothing to do with GP itself.

Example: for "Refresh Prod A to Test A", my steps look something like this:

  • Restore a specifically named .bak file to the test company. (In my case, I want the control over which backup to restore so I don't have this backing up Prod and then restoring from that).
  • Regular Microsoft scripts (DYNSA, fix company ID references)
  • Individual steps for each script with clear Step Names so I know exactly what is happening.

We have a few production companies and not all of them use all of the modules so I have fewer steps in the jobs with the companies without EFT, Safepay etc.

It works like a charm and makes refreshing a test company quick and painless, as well as "hands-free" for the most part.