Invoice Due Date & Payment Terms

[hov333]

Hi all,

I have an invoicing set which links my customer table to the invoices table. When a new invoice is created the fields invoice_date and due_date are blank until the invoice is printed/submitted.
Customers all have different payment terms noted in the customer table record which I would like to use (Advance, Net 30, Net 60, Net 90, Net 120). Upon pressing a button on the invoice form I have the "set field values" script genie setting these two values. 'Invoice_date' would be date(), but I'm stuck with the due_date expression.

What would be the best (expression?) to make use of the "Net terms" field and convert that to a usable due date?

thanks!

[preston2]

Re: Invoice Due Date & Payment Terms

To add Net30 to the date.

dim idate as c
idate = date()

? CTOD(idate) + 30
= {01/02/2016}

[hov333]

Re: Invoice Due Date & Payment Terms

Thanks preston, but your code only applies to Net30 customers. What about the others?

The complication comes when trying to calculate based on the net30 vs net60 vs advance payment.

[preston2]

Re: Invoice Due Date & Payment Terms

Thanks preston, but your code only applies to Net30 customers. What about the others?

The complication comes when trying to calculate based on the net30 vs net60 vs advance payment.

Well I just used the net 30 as an example. I assume you have a field that stores the "Advance" "Net30" "Net60" and so on.

It would be real simple to do a case or if to replace the value to be added to the formula.

dim terms as n
terms = case(invoices->cust_terms = "Advance",0,invoices->cust_terms = "Net30",30,invoices->cust_terms = "Net60",60,invoices->cust_terms = "Net90",90)

dim idate as c
idate = date()

? CTOD(idate) + terms
= {01/02/2016}

[DaveM]

Re: Invoice Due Date & Payment Terms

John,
a script to look up the terms for the client is going to be needed in some way. If it is in a child of the client, it can be done. Then process similar to what Preston said and write to the field in a manner you are comfortable with.

I would need a lot more information to get the best way for you, but maybe others have it on the tips of their tongues.

[hov333]

Re: Invoice Due Date & Payment Terms

Great info guys, then I guess that's how we'll do it.
I wasn't familiar with the case() function, it seems more suitable than if() which I was not excited about using.

Thanks for the code Preston!

[preston2]

Re: Invoice Due Date & Payment Terms

Great info guys, then I guess that's how we'll do it.
I wasn't familiar with the case() function, it seems more suitable than if() which I was not excited about using.

Thanks for the code Preston!

Not a problem. Many have helped me here so I give back when I can.

[Mike Wilson]

Re: Invoice Due Date & Payment Terms

John,
What you are saying is that you have the terms as a value in the customers table in a field named "net_terms". There is no need to have the word "Net" in the value of the field. Make it simple. Put in the numeric value and use the value to add to the invoice date. Then you don't need a long and complicated case statement.

invoice->due_date = date()+val(customer->net_terms)

[Keith Hubert]

Re: Invoice Due Date & Payment Terms

Another way is to get the Net_terms when you do the lookup to the customer table. Nothing calculated about that at all.

[DaveM]

Re: Invoice Due Date & Payment Terms

Net_terms as numeric changes it to :

invoice->due_date = date()+customer->net_terms

It is so strange to me why one would make things so hard. Numeric works so much simpler in some instances. NOT all.

[hov333]

Re: Invoice Due Date & Payment Terms

I'd like to re-open this topic now since we have now progressed to another question. What would be the best way to handle terms that involve a discount?
Such as:
3.5% 15, net 120
3.0% 30, net 120
2.5% 45, net 120

I'd curious as to how you guys are implementing this into your own invoicing tables.

[Al Buchholz]

Re: Invoice Due Date & Payment Terms

I'd like to re-open this topic now since we have now progressed to another question. What would be the best way to handle terms that involve a discount?
Such as:
3.5% 15, net 120
3.0% 30, net 120
2.5% 45, net 120

I'd curious as to how you guys are implementing this into your own invoicing tables.

Define a table with each value as a field. ie description, %, days discount, days net.

Use as a lookup table to the customer and fill values in customer table.

Fill those values into invoice header table.

[GGandhi]

Re: Invoice Due Date & Payment Terms

my feeling is the customer is responsible to pay within certain time frame without incurring finance charge - terms. in your case it appears to be 120 days.
that is all there to be included in the customer table. the printed invoice. on the other hand will state the discount rates as
3.5% 15, net 120
3.0% 30, net 120
2.5% 45, net 120
and the customer may choose to exploit the discount or not. so the calculation only applies when they make the payment.
so when they make the payment you design to run a code that calculates the discount based on the date of payment or finance charge if still unpaid after 120 days against the invoice date and post it to the transaction table.
the transaction table will have charges and payments, including finance charges and discounts ( positives and negatives) and the monthly statement will list all the transactions for the customer and you will see the discounts and finance charges if any.
that brings me to another question, if the days are set at 120 do you still need that in the customer table?

[hov333]

Re: Invoice Due Date & Payment Terms

Gandhi, you are on the right track. I do have the terms stored for each customer since it does vary for each customer.
The challenge now is to figure out the posting process when they make that payment. As you mentioned, it needs to happen when the user is 'posting a payment'. While entering the payment details into a "Payments" table (via an xbasic enter record command), I want to at the same time run that calculation for the discount based on the date and the terms on the invoice. The question is where is that discount shown, I have a discount field so perhaps that can be updated. What expression should be used when updating the discount field to cover these terms...Should I have another "terms" table, and store these values in separate fields? (Discount%, Discount Day, Due Day)

3.5% 15, net 120
3.0% 30, net 120
2.5% 45, net 120

One final thing, I saw on here that people were saying to keep calculated fields such as 'Invoice Total' and 'Amount Due' at the Form/Report level instead of storing them in fields in the table. If that's the case, how would one calculate a discount which involved looking at the 'Invoice Total'. Wouldn't it be smarter to keep all that saved in the record so that I can run other calculations off that field? (discount%, invoice status, etc)

[GGandhi]

Re: Invoice Due Date & Payment Terms

i would think when you write your xbasic code to save the payment you save that as normal and calculate the date of payment and date of invoice if it falls in a certain range add another posting as positive or negative number