Introduction

In the Configuring PayThem article - KBA-14-02-002 - Configuring Draycir PayThem 2 we provide an example expression to provide the ##DUEDATE...## for an invoice. The method shown is a simplistic method, adding the payment terms days amount to the invoice date. This method does not account for any terms more copmplicated that days from calendar date.

With thanks to DB Computer Solutions, we provide a more sophisticated set of expressions that allow the due date to be calculated more accurately. 

Applies to

Sage 200 customers using PayThem v2

Process

We will need to add three named expressions to the Sage 200 invoice layout as 'helper' expressions, which will allow us to add the due date into the main Spindle Document Distribution command expression.

For each of the following, Add an expression (Toolbox>Add Expression), paste the expression listed into the expression window and click OK.

You must then name the expression with the names provided (later expressions refer to these by name), by showing the expression's Properties and updating the Name field, for example:

Expression 1 - BaseDueDate

(
SLCustomerAccounts.SYSPaymentTermsBasisID= 0?
//Calendar
( MonthFromDate(SOPInvoiceCredits.DocumentDate)=12?CreateDateTime(YearFromDate(SOPInvoiceCredits.DocumentDate)+1,01,DayFromDate(SOPInvoiceCredits.DocumentDate)):DaysInMonth(YearFromDate(SOPInvoiceCredits.DocumentDate),MonthFromDate(SOPInvoiceCredits.DocumentDate)+1)<DayFromDate(SOPInvoiceCredits.DocumentDate)?CreateDateTime(YearFromDate(SOPInvoiceCredits.DocumentDate),MonthFromDate(SOPInvoiceCredits.DocumentDate)+1,DaysInMonth(YearFromDate(SOPInvoiceCredits.DocumentDate),MonthFromDate(SOPInvoiceCredits.DocumentDate)+1))
:
CreateDateTime(YearFromDate(SOPInvoiceCredits.DocumentDate),MonthFromDate(SOPInvoiceCredits.DocumentDate)+1,DayFromDate(SOPInvoiceCredits.DocumentDate))
)
:
SLCustomerAccounts.SYSPaymentTermsBasisID= 1?
//Start of Month
CreateDateTime(YearFromDate(SOPInvoiceCredits.DocumentDate),MonthFromDate(SOPInvoiceCredits.DocumentDate),01)
:
SLCustomerAccounts.SYSPaymentTermsBasisID= 2?
//End of Month
CreateDateTime(YearFromDate(SOPInvoiceCredits.DocumentDate),MonthFromDate(SOPInvoiceCredits.DocumentDate),DaysInMonth(YearFromDate(SOPInvoiceCredits.DocumentDate),MonthFromDate(SOPInvoiceCredits.DocumentDate)))
: SOPInvoiceCredits.DocumentDate
)

Expression 2 - DaysInDueMonth

DaysInMonth(YearFromDate(BaseDueDate+1), MonthFromDate(BaseDueDate+1))

Expression 3 - DueDate

This expression uses the previous two expressions to calculate the final due date. There are two possibilities depending on how the customer interprets the business terms. 

Option 1

SLCustomerAccounts.SYSPaymentTermsBasisID<>2?(BaseDueDate+Days(SLCustomerAccounts.PaymentTermsInDays)):SLCustomerAccounts.PaymentTermsInDays<>30?(BaseDueDate+Days(SLCustomerAccounts.PaymentTermsInDays)):(BaseDueDate+Days(DaysInDueMonth))

This is used if the customer interprets 30 days end of month to mean "the last day of the following month"

Option 2

SLCustomerAccounts.SYSPaymentTermsBasisID<>2?(BaseDueDate+Days(SLCustomerAccounts.PaymentTermsInDays)):(BaseDueDate+Days(SLCustomerAccounts.PaymentTermsInDays))

This is used if 30 days end of month is a strict 30 days

Update the command Expression 

With the above three expressions entered onto the document, we now need to use the DueDate expression in the main Spindle Document Distribution expression:

"##DUEDATE " + DateTimeToFormattedString((DueDate),"yyyy-MM-dd") + "##"

Optimise the layout

The three 'helper' expressions do not need to be printed, so we can select all three and set their Suppress Printing property to True:

KBA Details