3. Email Invoice

Preparing the IMan dataset for Emailing

In this step we will add a number of fields to the IMan dataset so they can be used to dynamically populate contents of the email.

Setup the Customer Lookup

We will create a lookup to retrieve some key customer data from the Customer record.

  1. Save the integration and go to the Setup area, then click Lookups, and double click the top row to create a new Lookup.

  1. Enter the values as per the following:

  1. Lookup ID
    • SAGE200INV
  2. Description
    • Sage200 Customer by Invoice Lookup
  3. Lookup from IMan DB
    • Unchecked
  4. Select Clause
    • A.CustomerAccountNumber, A.CustomerAccountName, C.ContactName, A.AccountBalance, CV.ContactValue
  5. From Clause
    • SOPInvoiceCredit I innerjoin SLCustomerAccount A on I.CustomerID = A.SLCustomerAccountID innerjoin SLCustomerContact C on A.SLCustomerAccountID = C.SLCustomerAccountID innerjoin SLCustomerContactRole ContRole on ContRole.SLCustomerContactID = C.SLCustomerContactID innerjoin SYSTraderContactRole SYSContRole on SYSContRole.SYSTraderContactRoleID = ContRole.SYSTraderContactRoleID innerjoin SLCustomerContactValue CV on CV.SLCustomerContactID = C.SLCustomerContactID
    1. Yes, this is long, but it’s the only way to obtain the default contact details.
  6. Where Clause
    • SYSContRole.Role ='Account'and ContRole.IsPreferredContactForRole ='True'and SYSContactTypeID = 2 and I.DocumentNo ='%1'

Setup the Map Transform

  1. Return to the Designer, load the integration.
  2. Open the Transform setup tab.
  3. Add a Map transform to the SOP Print Post Invoice connector created in the last section.

  1. Click the Field Mapping tab and add three new fields as per the following:
  1. Name
    • ContactName
  2. Field Type
    • Text
  3. Enable Script Evaluation
    • Checked
  4. Formula
    • Dim Result
      Result = Lookup("SAGE200INV", "ContactName", %InvoiceCreditNo, True)
      If Result = "" Then
      Result = Lookup("SAGE200INV", "CustomerAccountName", %InvoiceCreditNo, True)
      End If
      Result
  1. Name
    • Balance
  2. Field Type
    • Text
  3. Enable Script Evaluation
    • Checked
  4. Formula
    • Lookup("SAGE200INV", "AccountBalance", %InvoiceCreditNo, True)
  1. Name
    • Email
  2. Field Type
    • Text
  3. Enable Script Evaluation
    • Checked
  4. Formula
    • Lookup("SAGE200INV", "ContactValue", %InvoiceCreditNo, True)
  1. Press Refresh.

  1. Press Apply to save then close.

Using the Email Task

In this step you will learn how to setup the email task.

  1. Click on the Tasks group on the left hand of the designer and connect an Email task to the end of the Map transform.

  1. Expand the Email Addresses group and enter the following:
  1. From Address
    • Replace with your email address.
  2. To Address
    • Enter your address.
    • Typically you would dynamically populate this with a value from your dataset, i.e. %INVOICE.Email

  1. Collapse the Email Addresses group.
  1. Enter/select the following values:
  1. Send Individual Emails
    • Checked
    1. With this check every address in the To address will send a separate email.
  2. Generate Separate Emails Per
    • SOPPostInvoice
    • This will generate an email for each SOPPostInvoice record i.e. for each invoice.
  3. Subject
    • Demo Data Ltd Invoice - %SOPPostInvoice.InvoiceCreditNo
  4. Use IMan Styling
    • Checked
    • The email will be formatted using the internal IMan styling used on the audit report. If this is unchecked it allows you to create an email using your own styling and HTML.
  5. Embed MIME/Images into Email
    • Checked
    • Any images referenced in the email will be embedded into the contents of the email as opposed to being referenced as an URL or local file path.

  1. Expand Email Body options.
  2. Enter the following; after each line press the Add button:
  1. Line 1
    • Dear %[SOPPostInvoice.ContactName],
  2. Line 2
    • Please find attached your latest invoice %[SOPPostInvoice.InvoiceCreditNo]. If there are any queries please contact our accounts department quoting the invoice number.
  3. Line 3
    • Your current balance is £ %SOPPostInvoice.Balance and we would appreciate it if it is kept up to date.
  4. Line 4
    • Thank you for your custom.
  5. Line 5 – In this line we add the company’s logo using some html.  It is important to replace the path of the logo to the actual path.
    • <a href="http://www.sage.co.uk/sage-200"><img src="C:\IMan\InputData\SampleLogo.png" /></a>
  6. Line 6
    • Demo Data Ltd.

  1. Collapse Email Body and expand Attachments
  1. Enter the following and press the Add button.
    • %SOPPostInvoice.InvoiceExportFile

  1. Press Refresh to generate the invoices, if successful the Generation Status should go complete and three invoices will appear in your inbox.

  1. Press Apply to save then close and finally save the integration.
Homework >