Today’s #TipTuesday is around email functionality in Microsoft Dynamics GP and some tips around how to test it. As you may have read in my post last week, I made some mistakes in testing that have led to this post. Long story short: I neglected to edit all the tables that had live email addresses when I did some testing. There are some other things I could have also done to prevent compounding the problem, and all of that is what I intend to cover in this post.
The background to this post is the standard non-Workflow related email for emailing documents to customers or vendors. While some of what I’m about to say may apply to Workflow emails, I am going to be explicit here and say I didn’t not document all of the places where Workflow may contain data and it’s not covered here. The only mention of Workflow is in relation to valid Series settings in the SY04902 table.
There are a couple of scenarios where you may be testing email functionality:
- Configuring new functionality and testing that configuration.
- Troubleshooting a production issue or training new staff in a test environment.
When configuring something new, by far the safest approach is to always use internal or test email addresses. Testing at this stage is about ensuring the configuration is valid and emails are sent/received, and you can use “fake” data for that, not real customer or vendor email information. I typically would use real email addresses for the business side of the configuration, i.e. reply to email addresses and things like that, but not for the receiving end of the configuration.
The complications arise when using a test environment to test email functionality, if it’s already configured for production. It’s too easy to accidentally send an email to a real recipient unintentionally. The rest of this post is around this type of scenario.
There is one primary table for storing email addresses for most of the email functionality built into Dynamics GP, but it may not be the only one to update if you need to test email functionality (& overwrite production email addresses).
- SY01200 – Internet info (including email)
- This is information at a Master Record + Address ID level
- This is a company table, not specific to a module so the fields are generic.
- Master_ID is customer ID, vendor ID, employee ID etc.
- Master_Type will be “CUS”, “VEN” or “EMP” for sales, purchasing and payroll modules, respectively.
- The EmailToAddress, EmailCCAddress and EmailBCCAddress are the specific ones used in the “new” Word template email functionality.
- INETxx fields are the other addresses that have been in this window for years, but they are not used in any out of the box functionality I’m aware of (even if you have populated email addresses there).
- RM00106 – RM Statements Email addresses
- This is pre-GP 2013 functionality around emailing customer statements, not the new functionality introduced later which included emailing other types of documents from Microsoft Word templates.
- The email addresses stored here are what is populated on the Customer card, under Options (“Send email statements” section).
- SY04906 – Email Card addresses
- *This* is the table I neglected to update when testing something last week. This table is populated if you have chosen the “Email Address based on DocType” option in the cards (Vendor Email Options or Customer Email Options windows specifically).
- What this is tracking is which email to use for which document type.
- Series 3 is Sales and Series 4 is Purchasing
- Module1 is as follows
- 9 = Receivables Management
- 11 = Sales Order Processing
- 12 = Purchase Order Processing
- 19 = Payables Management
That’s it for the tables where new transactions will be referenced; however if your goal is to obfuscate production data in a test environment or test company, there are additional tables you will want to update that contain the history of emailed transactions and/or setup information where internal reply-to email addresses are stored.
- SY04910 – Email Details
- This is one table that tracks the details of what was emailed including the message ID used, the email To, CC, and BCC, the reply to etc.
- SY04915 – Email History
- This is one table that tracks the details and more, including who triggered the sending of the email and date/time stamps not recorded in the SY04910 table.
- SY04901 – Message Setup
- This table tracks the individual message IDs and will contain reply-to email addresses if any are populated on the Message ID.
- SY04902 – Email Series Setup
- This table tracks the series setup for email functionality, including the Reply-To email address by series.
- This table contains some of the same Series as above, plus potentially more depending on what is configured:
- 3 = Sales
- 4 = Purchasing
- 2 = Financial (Workflow)
- 6 = Payroll (Workflow)
- 7 = Project (Workflow)
- 16 = Administration (Workflow)
- 99 = All (Workflow)
Some of the tables identified in this section are in release GP 2013 R2 and newer. To my knowledge, the two tables that existed prior to that are SY01200 and RM00106, and the rest are new.
Training staff scenario
In the scenarios I outlined originally, if you are training new staff on functionality that is email-related, even if using existing production data copied to a Test company/environment, consider creating net-new customers or vendors to test with. By starting with data completely made up from scratch, the likelihood of mistakenly emailing a live vendor or customer is minimal. Of course, this entails also entering the documents from scratch but if you are training, this isn’t a hardship.
Reproducing an issue scenario
If attempting to reproduce an issue from production and using existing production data after copying it to Test, you’ll want to overwrite the settings so as not to send test emails to real vendors or customers. Assuming their actual email address isn’t the issue, it will not affect the testing to do this. (I say this as if that’s never been the issue in the past but if it’s a badly typed email address, hopefully it’s identified before going to the trouble of refreshing a test environment/company!). The safest route is to update the data on the vendors/customers you wish to test with directly in Dynamics GP. I know from experience that it’s not always something you have time for, so updating the tables may make sense.
If you went the SQL route, before doing any testing: spot check the vendors you want to use. Look up the vendors you want to use in SY04906 before testing anything. If any of the vendors appear here, make sure the email address is your test email, not a real production email.
It’s always a good idea to spot check within Dynamics GP too, just in case you mistakenly are checking a different company database than you’re testing as I’ve seen that happen to (though thankfully I haven’t made that mistake myself!). Spot check both the Internet Information window and the Email Options window in the customer or vendor you are testing, the second one you’re looking for vendors or customers that have “Email based on DocType” enabled.
General testing advice
Other than the suggestions above for “before you send an email” testing, the other thing I highly recommend is start small with testing batches. Test a small batch, then immediately check your Sent Items folder (in Outlook) to see where the test emails went. With each batch, check your Sent Items to ensure that nothing went to a different email than expected.
If you are testing email functionality, you will also want to check that the emails were received to the email address that you intended to send them to. The comments above about checking your sent items is precautionary, so you are aware of where the sent items went. Once you know the emails were not sent to places you didn’t want them to go, then you can check the receiving end. If you are using your own email for testing and see a bunch of emails coming in from testing, it’s far too easy to assume that all of them came in! (Ask me how I know! LOL…)