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. You may also have pathnames in things like Posting Setup for sending posting journals to file. There are many other examples once you get into ISV (third party) products.

Depending on the kind of testing that you’re doing, you don’t want those pathnames to be the same in Production and in Test. Consider EFT, and creating a test EFT batch and accidentally sending the file to the bank? Hopefully, you have controls in your 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 you may script this in, but you 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 you still have to remember to do it!)

Examples of what to look at

1. Next Note Index

If you use notes (window notes, record notes), and don’t update your test company very often, 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 you 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.

Here’s an old blog post from Michael Johnson (link removed, no longer valid) which talks about how to call a system stored proc to update this if you need to have the system “find” the next note index for you instead. (I.E. if you’re refreshing a separate environment, you can’t necessarily just update SY01500 cross server if it’s not set up).

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 you replace with the word TEST if you are testing EFT files. That field is stored in the CM00104 table, although the fields and location will differ based on your 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 contains the word PROD for production files and you need to use 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!

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 you didn’t know what it did.

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.