Applies to

Spindle Professional v6.14 and later


Requires

Sage 200 2010 or later

Sage Pay account configured,

Spindle Professional v6.14 or later.

"Pay Them" licence key for Spindle Professional in addition to the standard Spindle Professional licence key.


Process

In order that the correct data is passed from Sage 200 to the SagePay website, you must ensure that the data fields are added to the Sage 200 document layout and that these map to the SagePay Integration service in Spindle Professional Tools. The integration service uses a standard set of Spindle Professional variables to pass the information through to SagePay.



Draycir supplies a pre-configured Invoice Layout which contains the correct expressions, This can be installed by adding the Sage add-on (SDBX) file appropriate to the version of Sage 200. For Sage 200 2010 and 2011, use the add-on located in: 


C:\Program Files (x86)\Draycir\Spindle Professional 6\3rd Party Integration\Sage 200\Layouts\PayThem


For Sage 200 2013 and later, use the add-on located in 


C:\Program Files (x86)\Draycir\Spindle Professional 6\3rd Party Integration\Sage 200 2013+\Layouts\PayThem


Install the add-on using the Sage System Administration utility


Manual configuration


If your Invoice has been customised and you do not wish to use the default layout provided by Draycir, or if you wish to add the PayNow button to Pro-Forma invoices or Statements you can add the expressions to the layout manually. The basic process is as follows:


Step 1

Add additional JOINs to the report to enable the required additional fields to be available to the report

Additional Joins can be added by selecting the menu items Data>Joins (Sage 200 2010 and  2011) or Report>Joins (Sage 200 2013+) in the Sage Report Designer, to display the Joins Editor window:


Click the button to launch the Raw Text Editor



Scroll to the bottom and add the specified lines to the bottom of the text


Step 2

Add the Expressions into the locations specified 



SOP Invoice (Single)


Append the following to the Join Editor

LEFT OUTER JOIN [SYSCountryCodes] AS [SYSCountryCodes1] ON SOPInvCredAddresses.Country = SYSCountryCodes1.Name
LEFT OUTER JOIN [SYSCountryCodes] AS [SYSCountryCodes2] ON SOPInvCredDelAddresses.Country = SYSCountryCodes2.Name
INNER JOIN [SYSCurrencyISOCodes] ON SYSCurrencies.SYSCurrencyISOCodeID = SYSCurrencyISOCodes.SYSCurrencyISOCodeID

Then add the following expression to the Report Header


"##VAR31 " + SOPInvCredAddresses.C_AddressLine1 + "##

##VAR32 " + SOPInvCredAddresses.C_AddressLine2 + "##

##VAR33 " + SOPInvCredAddresses.C_AddressLine3 + "##

##VAR34 " + SOPInvCredAddresses.PostCode + "## ##VAR35 " + SYSCountryCodes1.Code + "##

##VAR36 " + SOPInvCredDelAddresses.Contact + "##

##VAR37 " + SOPInvCredDelAddresses.AddressLine1 + "##

##VAR38 " + SOPInvCredDelAddresses.AddressLine2 + "##

##VAR39 " + SOPInvCredDelAddresses.AddressLine3 + "##

##VAR40 " + SOPInvCredDelAddresses.PostCode + "## ##VAR41 " + SYSCountryCodes2.Code + "##

##AMOUNT " + SOPInvoiceCredits.InvoicedGrossValue + "##

##CURRENCY " + SYSCurrencyISOCodes.Code + "##

##ACCOUNTREF " + SLCustomerAccounts.CustomerAccountNumber + "##

##DOCUMENTNO " + SOPInvoiceCredits.DocumentNo + "##

##ERPCOMPANY " + SYSCompanies.CompanyName + "##"


Lastly, at the position on the report that you want the "PayNow" button to appear, add


"##PAYTHEM##"


Please note that the ##PAYTHEM## command must appear below the other PayNow commands on the report. 


SOP Pro Forma (Single)


Append the following to the Join Editor


INNER JOIN [SYSCountryCodes] AS [SYSCountryCodes1] ON SLCustomerLocations.SYSCountryCodeID = SYSCountryCodes1.SYSCountryCodeID

LEFT OUTER JOIN [SYSCountryCodes] AS [SYSCountryCodes2] ON SOPDocDelAddresses.CountryCodeID = SYSCountryCodes2.SYSCountryCodeID


INNER JOIN [SYSCurrencyISOCodes] ON SYSCurrencies.SYSCurrencyISOCodeID = SYSCurrencyISOCodes.SYSCurrencyISOCodeID


Then add the following expressions to the Report


In header 1


"##VAR31 " + SLCustomerLocations.C_AddressLine1 + "## ##VAR32 " + SLCustomerLocations.C_AddressLine2 + "##

##VAR33 " + SLCustomerLocations.C_AddressLine3 + "##

##VAR34 " + SLCustomerLocations.PostCode + "## ##VAR35 " + SYSCountryCodes1.Code + "##

##VAR36 " + SOPDocDelAddresses.Contact + "##

##VAR37 " + SOPDocDelAddresses.C_AddressLine1 + "## ##VAR38 " + SOPDocDelAddresses.C_AddressLine2 + "##

##VAR39 " + SOPDocDelAddresses.C_AddressLine3 + "##

##VAR40 " + SOPDocDelAddresses.PostCode + "## ##VAR41 " + SYSCountryCodes2.Code + " ##

##AMOUNT " + SOPOrderReturns.TotalGrossValue + "## ##CURRENCY " + SYSCurrencyISOCodes.Code + "##

##NAME " + SLCustomerAccounts.ContactName + "## ##COMPANY " + SLCustomerAccounts.CustomerAccountName + "##

##ACCOUNTREF " + SLCustomerAccounts.CustomerAccountNumber + "## ##DOCUMENTNO " + SOPOrderReturns.DocumentNo + "##

##ERPCOMPANY " + SYSCompanies.CompanyName + "##"


In header 2


"##VAR31 " + SLCustomerLocations.C_AddressLine1 + "## ##VAR32 " + SLCustomerLocations.C_AddressLine2 + "##

##VAR33 " + SLCustomerLocations.C_AddressLine3 + "##

##VAR34 " + SLCustomerLocations.PostCode + "## ##VAR35 " + SYSCountryCodes1.Code + "##

##VAR36 " + SOPDocDelAddresses.Contact + "##

##VAR37 " + SOPDocDelAddresses.C_AddressLine1 + "## ##VAR38 " + SOPDocDelAddresses.C_AddressLine2 + "##

##VAR39 " + SOPDocDelAddresses.C_AddressLine3 + "##

##VAR40 " + SOPDocDelAddresses.PostCode + "## ##VAR41 " + SYSCountryCodes2.Code + " ##

##AMOUNT " + SOPOrderReturns.TotalGrossValue + "## ##CURRENCY " + SYSCurrencyISOCodes.Code + "##

##NAME " + SLCustomerAccounts.ContactName + "## ##COMPANY " + SLCustomerAccounts.CustomerAccountName + "##

##ACCOUNTREF " + SLCustomerAccounts.CustomerAccountNumber + "## ##DOCUMENTNO " + SOPOrderReturns.DocumentNo + "##

##ERPCOMPANY " + SYSCompanies.CompanyName + "##"


In the footer (at the location you wish the button to appear)

"##PAYTHEM##"



Sales Ledger Statement with Advice (Single)

Append the following to the Join Editor

INNER JOIN [SYSCurrencyISOCodes] ON SYSCurrencies.SYSCurrencyISOCodeID = SYSCurrencyISOCodes.SYSCurrencyISOCodeID


Then add the following expressions to the Report


In header 1


"##VAR31 " + SLHeadOfficeLocation.C_AddressLine1 + "## ##VAR32 " + SLHeadOfficeLocation.C_AddressLine2 + "## ##VAR33 " + SLHeadOfficeLocation.C_AddressLine3 + "## ##VAR34 " + SLHeadOfficeLocation.PostCode + "## ##VAR35 " + SYSCountryCodes.Code + "##

##VAR36 " + SLHeadOffice.ContactName + "## ##VAR37 " + SLHeadOfficeLocation.C_AddressLine1 + "## ##VAR38 " + SLHeadOfficeLocation.C_AddressLine2 + "## ##VAR39 " + SLHeadOfficeLocation.C_AddressLine3 + "## ##VAR40 " + SLHeadOfficeLocation.PostCode + "## ##VAR41 " + SYSCountryCodes.Code + " ##

##CURRENCY " + SYSCurrencyISOCodes.Code + "## ##NAME " + SLHeadOffice.ContactName + "## ##COMPANY " + SLHeadOffice.CustomerAccountName + "## ##ACCOUNTREF " + SLHeadOffice.CustomerAccountNumber + "##

##DOCUMENTNO Statement" + (DateTimeToFormattedString(SLSettings.AgeingDate, "ddMMyyyy")) + "## ##ERPCOMPANY " + SysCompanies.CompanyName + "## ##VAR42 " + SLSettings.AgeingDate + "##"



Header 2:

"##VAR31 " + SLCustomerLocations.C_AddressLine1 + "## ##VAR32 " + SLCustomerLocations.C_AddressLine2 + "## ##VAR33 " + SLCustomerLocations.C_AddressLine3 + "## ##VAR34 " + SLCustomerLocations.PostCode + "## ##VAR35 " + SYSCountryCodes.Code + "##

##VAR36 " + SLCustomerAccounts.ContactName + "## ##VAR37 " + SLCustomerLocations.C_AddressLine1 + "## ##VAR38 " + SLCustomerLocations.C_AddressLine2 + "## ##VAR39 " + SLCustomerLocations.C_AddressLine3 + "## ##VAR40 " + SLCustomerLocations.PostCode + "## ##VAR41 " + SYSCountryCodes.Code + " ##

##CURRENCY " + SYSCurrencyISOCodes.Code + "## ##NAME " + SLCustomerAccounts.ContactName + "## ##COMPANY " + SLCustomerAccounts.CustomerAccountName + "## ##ACCOUNTREF " + SLCustomerAccounts.CustomerAccountNumber + "##

##DOCUMENTNO Statement" + (DateTimeToFormattedString(SLSettings.AgeingDate, "ddMMyyyy")) + "## ##ERPCOMPANY " + SysCompanies.CompanyName + "## ##VAR42 " + SLSettings.AgeingDate + "##"


Footer:

"##AMOUNT " + Sum(SLPostedCustomerTrans.OutstandingValue) + "##"

and

"##PAYTHEM##"

Please note that the amount expression will default to being a Sum function in the report designer's Properties pane, This must be set to None, as the expression itself already contains a SUM function







Lastly, at the position on the report that you want the "PayNow" button to appear, add


"##PAYTHEM##"

Sales Orders


"##VAR31 " + SLCustomerLocations.C_AddressLine1 + "##

##VAR32 " + SLCustomerLocations.C_AddressLine2 + "##

##VAR33 " + SLCustomerLocations.C_AddressLine3 + "##

##VAR34 " + SLCustomerLocations.PostCode + "##

##VAR35 " + SYSCountryCodes.Code + "##

##VAR36 " + SLCustomerAccounts.ContactName + "##

##VAR37 " + SLCustomerLocations.C_AddressLine1 + "##

##VAR38 " + SLCustomerLocations.C_AddressLine2 + "##

##VAR39 " + SLCustomerLocations.C_AddressLine3 + "##

##VAR40 " + SLCustomerLocations.PostCode + "##

##VAR41 " + SYSCountryCodes.Code + " ##

##AMOUNT " + SOPOrderReturns.TotalGrossValue + "##

##CURRENCY " + SLCustomerAccounts.CurrencyISOCode + "##

##NAME " + SLCustomerAccounts.ContactName + "##

##COMPANY " + SLCustomerAccounts.CustomerAccountName + "##

##DATASETID " + SYSCompanies.CompanyNumber +  "##

##ACCOUNTREF " + SLCustomerAccounts.CustomerAccountNumber + "##

##DOCUMENTNO Order " + SOPOrderReturns.DocumentNo + "##

##ERPCOMPANY " + SysCompanies.CompanyName + "##

##VAR42 " + SOPOrderReturns.DocumentDate + "##"


Please note that the ##PAYTHEM## command must appear below the other PayNow commands on the report. 

Spindle Professional Tools configuration

Once the correct expressions have been added to the Sage 200 layouts, Spindle Professional Tools must be configured to make use of the data.

Open the tools and navigate to Integration Manager and add a new PayThem service. Give the service a name, and enter the Vendor Name and Encryption password as supplied by SagePay (Draycir have no visibility of this information)
Check Enable service to allow the button to be placed on the report.


Under Configuration, check that the fields are being mapped correctly, if you have used the default expressions given above then the fields will be correct.


Under Button, choose the preferred button style/colour



Finally add the PayThem service to the document automation



Related Product
Spindle Professional
Ref Number
KBA-01-02-01
Document Date
23/08/2016
Original Author
Zak Rangara
Document Version
v1.1
Last updated
30/11/2017
Update Author
Vince Hodgson