Introduction

A frequently requested configuration is to print an invoice run from the ERP application, and create a single email for each customer which has all relevant invoices attached.

Adapting this guide to other report writers

To adapt the expressions to other ERP report designers is simply a matter of understanding how to build a text string out of fixed text elements and database field values.

Applies to

Spindle Professional 2009 and later

Spindle Document Distribution

Solution

This requires Spindle Document Distribution to be configured to do two things:

1. The Invoices must be archived to a PDF Archive in an orderly manner, using a field at document level, such as the Invoice number, as the split variable. 
2. The Emails are generated using a field at Customer account level, for example the Customer account number, as the split variable. 

In this guide we will be using the Invoice Number as stored in ##VAR1## and the Customer Account Name as stored in ##COMPANY##. 

Overview of the process

The process can be broken down into four stages, 

1. Print from the ERP
2. Sort by customer field
3. Split by document field and archive
4. Split by customer field and email

Step 1 – Create the Invoice Archive

When processing the Invoices, please ensure they are saved into a location derived from the data available, for example:

This will create a folder structure inside C:\Archives\Invoices that looks like :

Step 2 – Create the Archiving Operation

We need to create a Document type that will perform at least two operations on each document, For this to work, we need the documents to be sorted by account number or company name.

In Spindle Document Distribution tools we want our first operation to archive the invoices to a folder location. For this we set the Split Variable to be VAR1 (the invoice number) in the Settings Tab.

In the Distribution tab we set all three distribution methods to NONE, uncheck the option "Warn if no distribution found" and check Enable Archiving Mode.

In the Archive Settings, select the archive configuration we set up in step 1.

Spindle will now save all the invoices into this folder structure.

Step 3 – Add the Attach commands to the report design

The invoice layout will contain expressions setting Spindle Document Distribution variables to the Invoice number, the Customer Account number and so on. We will use these values to tell Spindle Document Distribution which files to attach to our emails.

We will need to add an ##ATTACH…## (and optionally an ##ATTACHDIR…##) command to our report so that when it is printed, it is rendered as for example

##ATTACH C:\Archives\Invoices\Abbey Retail\Invoice 00123.pdf##

Note, we cannot use ##COMPANY## and ##VAR1## that are used to populate ##COMPANY## etc. Instead we use the variables for those fields as used by the ERP report writer. In Sage 200 the expression might look like this:

"##ATTACH C:\\Archives\\Invoices\\" + SLCustomerAccounts.CustomerAccountName + "\\Invoice " + SOPInvoiceCredits.DocumentNo  + ".pdf##"

If you end up with a document that looks like it will attach itself, then you have configured it correctly.

Please be aware that this operation will display warnings that the attachment cannot be found:

This is to be expected.  

Step 4 – Create the Email operation

Back in Spindle Document Distribution Tools, add a second operation under the document type that will simply email the documents to the customers. In this operation we want to set the split variable to be either the Customer account number or customer name, so that each recipient gets only one email.

In the distribution tab we set the method to Email, and in Email Settings>Advanced we set the option to not send the attachment (because the ##ATTACH...## command on the report will handle that).

Warning: Because the above process will add all relevant invoices to the mail, please exercise caution that you do not exceed the allowed mail message size limits set by the mail administrator, either on site, or at the recipient end.