5 Replies Latest reply on Oct 23, 2015 11:48 PM by erolst

# Calculation needed please for invoice

I can't seem to get this to work. I need to add a field 'discount' to an invoice where it returns "-20.00" when certain criteria is met. Customers will get a £20.00 discount if they pay within 62 days, but only applies to sales from 1/10/15. So the date in 'Invoice Date' must be from 01/10/2015 AND the day count in 'days'  must be between 1-62 days. I also would like the 'discount'  field left blank if the 'payment date' field is blank.

The 'payment date' field is used to calculate the 'days'  field. The sum works out number of days from the 'Invoice date' date subtracted by the 'payment date' field.

I hope this makes some sense.

• ###### 1. Re: Calculation needed please for invoice

if ( Invoices::Invoice Date > "10/01/2015" and Invoices::days < "63" ; "-20" ; "" )

This would calculate your discount. But it's kind of odd to store a discount on something AFTER a payment has been made.

assuming you are using UK formatted dates since your invoices are in GBP.

• ###### 2. Re: Calculation needed please for invoice

It sounds like there may be an underlying an issue with the model you describe, in that it seems to only allow for one payment per invoice. Surely, a customer will sometimes partially pay an invoice, or will send in a payment for more than one invoice?

We generally have a Payments table, and a Payments Line Item table that's a join between Payments and Invoices.

The main reason I mention this is that, for discounts based on payment behavior, such as "2% 10 Net 30", we use an auto-entry calculation to enter calculate applicable discounts and apply them in the Payments Line Item table. That discount is effectively a "payment" applied, but by discount ("coupon", if you will) instead of cash. So the amount due on the invoice is the original amount, minus cash applied, minus discounts applied.

Not sure if this directly addresses your question, but hopefully provides some food for thought. I end up getting a lot of repair and rebuild work on solutions that, among other things, have payment fields in the invoices table. It seems "simpler" at the outset, but almost inevitably leads to unnecessary limitation and dissatisfaction in the long run.

Chris Cain

Extensitech

• ###### 3. Re: Calculation needed please for invoice

Hi guys, thank you for that.

Maybe I need to explain it better. Clients are paying for a course in advance. If they pay early and in full before 62 days, they get a £20.00 early bird discount. This system only started on 1st October.  If they part pay or/and pay after 62 days from invoice date, they do not receive the discount.

Thank you Mike, but your calculation still returns -21 when the 'payment date' field is empty (this field is only entered when full payment is made), and on records prior to 'Invoice date' field of 1st October.

Jo :-)

• ###### 4. Re: Calculation needed please for invoice

So then just add a parameter to the calculation so that it only returns the discount when the Payment Date field isn't empty?

I created a data table with four fields: Invoice Date, Payment Date, Days and Discount. The first two fields are in date format, and the latter two fields are calculations with the result being a number.

This calculation successfully entered a Discount of -20 only when the Invoice Date is after October 1, the Payment Date is present, and "Days" is less than 63:

If ( Invoice Date < "2015-10-01" or IsEmpty ( Payment Date ) ; "" ; If ( Days < 63 ; -20 ; "" ) )

- RG>

• ###### 5. Re: Calculation needed please for invoice

realgrouchy wrote:

This calculation successfully entered a Discount of -20 only when the Invoice Date is after October 1, the Payment Date is present, and "Days" is less than 63:

If ( Invoice Date < "2015-10-01" or IsEmpty ( Payment Date ) ; "" ; If ( Days < 63 ; -20 ; "" ) )

Or be totally explicit about it (and save one field):

Let ( [

invoice = Invoice Date ;

payment = Payment Date ;

isPaid = not IsEmpty ( Payment Date ) ;

qualifies = invoice >= Date ( 10 ; 1 ; 2015 ) ;

isEarlyBird = payment - invoice < 63

] ;

Case (

isPaid and qualifies and isEarlyBird ;

20

)

)