Background / The Problem

For many companies a long awaited new feature introduced with Dynamics GP 2010 was the ability to email SOP (Sales Order Processing) invoices directly from GP. Many of our customers have started to offer emailing of invoices to their customers and are seeing an increased number of requests for emailed invoices. However, something that we have heard many users complain about with GP’s new emailing options is how cumbersome it is to deal with both emailing and printing invoices. It’s easy if invoices are all printed or all emailed, but anything in-between requires a lot of manual sorting through records in GP or sorting through printed invoices to determine what needs to get mailed out.

 The Solution

We have come up with a really great solution for this predicament using GP Reports Viewer and I wanted to share it in the hopes that others would find this useful. I will go over how to set this up in the section following this one, but once set up, here are the steps the user would take to email and print invoices without any additional manual work or conflicts (please click on images to see them bigger if they are unclear):

  1. Open Sales Batch Entry window and select your invoice batch
  2. Click the Print icon
  3. Choose to include Invoices, Blank Paper for the Format, Send Documents in E-mail (and Reprint Previously Printed/Sent just in case): 
  4. Click Print – this will email all the invoices that can be emailed and if there are any invoices in the batch that could not be emailed, you will get an exception report with 2 possible types of Exceptions: 
    • If a customer is set up for emailing but there was no valid email address, you will get a message saying “A To, Cc, or Bcc address could not be found”. For these customers you just need to fix the email address and re-email the invoices individually. There are typically not too many of these and you could prevent this by using my SOP email setup view ahead of time to check for valid email addresses.
    • If a customer is not set up for emailing, you will get a message saying “This document type cannot be sent in e-mail for this customer”. You will see one of these messages for each invoice that needs to be printed and mailed. Here is where this gets very cumbersome out-of-the-box: if you then simply print your entire invoice batch to the printer, you would need to go through all the printed invoices one by one to pull out only the invoices you need to physically mail. While for small batches this may not be a huge deal, imagine if your batch had 250 invoices and you needed to mail 50 of them! Instead, to reprint only the invoices that need to be mailed, follow steps 5 through 8 below.
  5. Open Sales Batch Entry window and select your invoice batch
  6. Click the Print icon
  7. Choose to include Invoices, Short Form for the Format (or any other format you want to set up for this, just not Blank Paper), Print Document and Reprint Previously Printed/Sent: 
  8. Click Print – this will print only invoices for customers that are not set up for emailing invoices.

Setup / Recipe

This solution relies on the following ingredients:

  1. Microsoft Dynamics GP 2010
  2. Flexible Solutions’ GP Reports Viewer
  3. Two versions of a Crystal or SSRS report for SOP invoices:
    • One version that prints for all customers (replacing the Blank Paper format of the SOP invoice)
    • Another version that is a copy of the first, but only prints for customers that are not set up for emailing (replacing the Short Form format of the SOP invoice)

To complete this solution you will need to set up GP Reports Viewer to replace your SOP invoices with your two reports by mapping them to the Blank Paper and Short Form formats. More information on setting up SOP replacement in GP Reports Viewer can be seen in this demo video and detailed step by step instructions are in the GP Reports Viewer User Guide.

The only task left is to create a new version of your Crystal or SSRS report that only prints for customers not set up for emailing invoices:

Add data that determines whether each customer is set up for emailing

Often a Crystal or SSRS report for SOP invoices will be based on a SQL view or stored procedure. In that case, to capture the information that determines whether the customer is set up for emailing SOP invoices, you will need to add the SY04905 table which holds the details for which documents are enabled for emailing for each customer and vendor. (More details about email tables are on my Company/System Tables page and if you’re looking for more detail on email setup, please take a look at my SOP email setup view.)  Below is sample code that can be used to link the customer e-mail setup for SOP invoices to your sales data (in table S in this example):

LEFT OUTER JOIN SY04905 EC --email card documents
ON S.CUSTNMBR = EC.EmailCardID AND EC.MODULE1 = 11 AND EC.EmailSeriesID = 3 AND EC.EmailDocumentID = 3

You will also need to add a field to the data you are selecting in your stored procedure or view, for example:

case coalesce(EC.EmailDocumentFormat,0)
 when 0 then 'Not Enabled'
 else 'Enabled'
 end Email_Invoice

If your invoice report is pointing directly to SQL tables, you can add a link to my  SOP email setup view.

Add a restriction to your report so that it only prints for customers not set up for emailing invoices

In Crystal Reports you can add the following to your Record Selection formula:

and {view_Customer_SOP_Email_Setup.Email_Invoice} = "Not Enabled"

(Note that the example above is pointing to my SOP email setup view, if you are using your own view or stored procedure, please change the name accordingly.)

In SSRS you can add the following to the WHERE clause for your dataset:

and (view_Customer_SOP_Email_Setup.Email_Invoice = "Not Enabled")

(Note that the example above is pointing to my SOP email setup view, if you are using your own view or stored procedure, please change the name accordingly.)

Conclusion

The end result of this is a much happier and more productive invoicing staff. :-)

For more information about GP Reports Viewer, please take a look at the GP Reports Viewer demo video.

Disclaimer: GP Reports Viewer is an add-on product for Dynamics GP that is created and sold by my company, Flexible Solutions. I may be slightly biased, but I think it is the best thing since sliced bread.


Filed under: Crystal Reports, Dynamics GP, GP 2010, GP Reports Viewer, SOP SQL code Tagged: Dynamics GP, featured, GP 2010, GP Reports Viewer, Sales Order Processing