Description

We needed a formula to determine a weekday for a macro trigger to send reminder notices to customers that their invoice is nearing the end of the agreed term and wanted the email to be sent on a normal weekday. Since it is based on Terms, the macro trigger needed code that would determine a weekday just before the end of the Term period. Since terms are rarely divisible by 7. adding the term (E.g. 30 for 30 days) to the invoice date could not assure that the result would be a weekday. Hard-coding the number of days to subtract from the Terms (E.g. 2 days from 30 day terms and 4 days from 60 day Terms) was not acceptable as users might add additional terms not hard-coded and the macro would then fail for those; so the formula had to accommodate any terms which might be created.

Basic Discussion of Approach

The number of days permitted in the customer's payment terms is the GracePeriod field in the PaymentTerms table. This table is linked to the Accounts table with the [Account.PaymentTermsID] field.

We divide the Terms [PaymentTerms.GracePeriod] by 7 and subtract the “remainder” from the Grace Period to determine a number we can add to the invoice date which will be divisible by 7. For example, if the Grace Period is 30 days, when 30 is divided by 7 the result is 4 with a remainder of 2. For 45 day terms, 45 divided by 7 is 6 with a remainder of 3 and, for 60 day terms, the result of 60 divided by 7 is 8 with a remainder of 4.

For this we used the SQL MOD function which returns the remainder of a division. In SQL the MOD function is the % character. For example 30 MOD 7 results in 2. The SQL code for this is 30%7 or, using the GracePeriod, [PaymentTerms.GracePeriod]%7.

The result of this trigger is that the Trigger date will always fall on the same day of the week on which the Order was Invoiced. The days between when the macro is triggered and the end of the Term will vary 1 to 6 days, depending on the Terms – E.g for 30 day terms this interval will be 2; for 60 day terms the interval will be 4.

This Trigger is intended for manually run macros run daily as part of a batch run. It is inserted in the SQL code field.

Warning or areas where the approach might not work as expected

This approach does not assure that the macro will not be triggered on a Holiday. We decided in the interest of coding simplicity to accept this risk.

Be sure to add whatever additional filters are required to assure that customers do not receive emails when they do not have an invoice nearing the end of the terms period – E.g. Invoices with $0 balance. In the example below we use a UDF, [TransHeaderUserField.Construction_Billing], to designate customers with special billing and collections procedures.

In our system invoices are sent by email or US Mail on the date they are marked Sale and all Terms commence on the date of the Sale. For companies where this is not the case, the formulas below would need to be modified to include the incremental days before or after the Cyrious SaleDate.

We do not use Service Tickets. For those companies which use service tickets and do not want them included in the macro or want the macro to apply only to Service Tickets, the code below would need to be amended with a filter for the [TransHeader.TransactionType] – E.g. AND [TransHeader.TransactionType] = 1.

1. Set up the SQL code with the Payment Terms table so that the GracePeriod field can be used in the formula.
2. In the Where Clause use the DateDiff function to match the number of days since the invoice date to the difference between the current system date and the invoice date. The SQL code for this is Datediff(day,Cast(SaleDate as Date),Cast(GetDate() As Date)) == (GracePeriod - GracePeriod%7)

--Code For Sequel 2014
Select
TH.ID,
TH.ClasstypeID
From TransHeader TH with(nolock)
Left Join Account AC with(nolock) on TH.AccountID = AC.ID
Left Join PaymentTerms PT with(nolock) on AC.PaymentTermsID=PT.ID
Left Join TransHeaderUserField TUF with(nolock) on TH.ID =TUF.ID
Where
(
StatusID = 3
AND TotalPrice > 0
AND Construction_Billing  1 --filter invoices with AIA billing
AND BalanceDue > 5 --filter invoices with minimal balances
AND
Datediff(day,Cast(SaleDate as Date),Cast(GetDate() As Date))
= (GracePeriod - GracePeriod%7)
)

Contributor: Steve Gillispie, Acorn Sign Graphics

Date: 8/17/2017

Version: Control 06.10.1708.0301