Invoice Due Date & Payment Terms

[GGandhi]

Re: Invoice Due Date & Payment Terms

calculated fields such as 'Invoice Total' and 'Amount Due' at the form/report level not on the table

i agree with that statement, calculated fields do not belong in the table since you can calculate on the fly, with one exception. if the data will change with change in time then the calculated field should also be saved.
for example if the price of an item will change over time then the calculated amount paid for that item should be saved so as to prevent a new calculation done with new data will be different than what has happened awhile ago.

[hov333]

Re: Invoice Due Date & Payment Terms

I got the discount to work by creating a Payment Terms table which specifies the terms, discount %, discount days, and net days. This info is looked up and then the discount is calculated using a calculated field 'Balance' and entered into my discount field when a payment is being posted. The trouble I find about having calculated fields calculating on the fly is that they dramatically slow down my form because that is where I show a lot of data. If this was all at the table-level, then my form would work much smoother...any suggestions on this?

[CharlesParker]

Re: Invoice Due Date & Payment Terms

I store the total as Ghandi suggests for the same reason. I also like to use the totals in reports later based on products and services rendered without the need to calculate which is no longer necessary on historical data, not that it couldn't be recalculated. My issue with calculated fields in general is that they work every time, and in some cases where you might want to make an adjustment with out the calculations being enforced. I cannot think of an example off the top of my head but I do recall a time or two where the calculated field was working against what I was trying to do.
On the point of a calculated field slowing down a form, that seems like it could be more the fact that you may be using an Ajax call back to get the info from the drop down table values rather than the actual calulation because client side usually works instantly. Try your form with explicit values rather than a lookup to another table for the discount, I would bet it's instant.
Just a guess though.

[MoGrace]

Re: Invoice Due Date & Payment Terms

A calculated field needn't become part of your table - or form, until you need it to. I often use vars to hold the calc value which is updated by a button or event, at which time the value is saved in a table field. Invoices in particular work against the rule of normalization for the very reason that this is historical data and should always match the printed invoice sent to the customer. If ever a correction does need to be made, some note should appear that the invoice has been revised and a copy was sent to the customer.

So when you set the terms for a customer, you should probably copy that info to the invoice table in case the terms for that customer are changed at a later date - then it won't change past records.

[hov333]

Re: Invoice Due Date & Payment Terms

Charles, I'm very interested in learning more about the explicit values. What's really slowing things down is that I'm listing all the customers with their balances, as well as their invoices with the totals, there's a lot of calculations being done. Could you suggest another way to go about it?

current = dbsum("invoices","current",invoices->customer_id,"balance")
over30 = dbsum("invoices","Over30",customer_id,"balance")
over60 = dbsum("invoices","Over60",customer_id,"balance")
over90 = dbsum("invoices","Over90",customer_id,"balance")
balance_tbi = dbsum("invoices","BalanceTbi",Customer_Id,"Balance")
invoice_count = count(invoices->invoice_number,GRP->customer)
items_count = count(Th_Main_Database->Work_Number,GRP->invoices,GRP->TH_MAIN_DATABASE)
inv_total = total(Th_Main_Database->Total,GRP->invoices)+invoices->Adjustment
customer_balance = dbsum("invoices","Cust_Bal",Invoices->Customer_Id,"balance")-total(Payments->Payment_Amount,GRP->customer)
inv_balance = calc->INV_TOTAL+Invoices->Adjustment-Invoices->Total_Paid_Amount
list_sum_of_invoices = total(Invoices->Balance,GRP->customer)

Invoice Form.jpg

[Al Buchholz]

Re: Invoice Due Date & Payment Terms

Charles, I'm very interested in learning more about the explicit values. What's really slowing things down is that I'm listing all the customers with their balances, as well as their invoices with the totals, there's a lot of calculations being done. Could you suggest another way to go about it?

current = dbsum("invoices","current",invoices->customer_id,"balance")
over30 = dbsum("invoices","Over30",customer_id,"balance")
over60 = dbsum("invoices","Over60",customer_id,"balance")
over90 = dbsum("invoices","Over90",customer_id,"balance")
balance_tbi = dbsum("invoices","BalanceTbi",Customer_Id,"Balance")
invoice_count = count(invoices->invoice_number,GRP->customer)
items_count = count(Th_Main_Database->Work_Number,GRP->invoices,GRP->TH_MAIN_DATABASE)
inv_total = total(Th_Main_Database->Total,GRP->invoices)+invoices->Adjustment
customer_balance = dbsum("invoices","Cust_Bal",Invoices->Customer_Id,"balance")-total(Payments->Payment_Amount,GRP->customer)
inv_balance = calc->INV_TOTAL+Invoices->Adjustment-Invoices->Total_Paid_Amount
list_sum_of_invoices = total(Invoices->Balance,GRP->customer)

[ATTACH=CONFIG]42280[/ATTACH]

The way I read your first 5 calculated fields and customer_balance, you are reading the database 6 times for records you already have.

Slowness is from extra data retrieval more than the number of calculated fields.

The other calc fields use count() or total() and use the data already retrieved.

I would try using total instead of dbsum.
You would need 4 more calculated fields combos like:

iOver30 = if(<ourover30filter>,balance,0)
Over30_total=total(iOver30,GRP->customer)

Then a like combo for current,over60,over90

From the looks of it you have filtered indices for current,Over30,Over60,Over90 that would not be needed then and would speed up your writes to the table.

I bet it improves performance with these changes.

dbsum() is good for data from a table that is not already in the set and it's data is already retrieved.

[hov333]

Re: Invoice Due Date &amp; Payment Terms

thanks for the suggestion Al, yes I have filtered indexes here's the one for Over30:
ISNOTBLANK("Invoices->Invoice_Date").AND.BETWEEN(Invoices->Invoice_Date,DATE()-60,DATE()-31)

I will try to incorporate what you suggested, but
1) the index filter as well as your suggested <iOver30 = if(<ourover30filter>,balance,0)> both rely on this 'balance' field which is calculated and stored in the table. So rather than calling out the table's field, I should be using calc->inv_balance, correct?
2) I don't really have speed issues when writing to the table. Its the form itself that is very slow i.e. navigating and selecting records which I would like to fix.

[MoGrace]

Re: Invoice Due Date &amp; Payment Terms

Hi John,
Forgive me for my unsolicited opinion, but it looks like you are trying to do too much on this form.

For example:
1. You are showing a list of customers with open balances on the right, and yet your invoice table is not showing anything that indicates which invoices are included in the open balance. Instead, that list appears to be open orders that have not been invoiced.

2. The Invoice items list then is a detail view of an open order.

3. Therefore - why are payments being shown here when your orders are not billed yet?

If it were me, I would have a form that concerns the open orders, and another that is only about the invoices and their payments. Or I suppose you might use a tabbed object if you need to show both.

[Al Buchholz]

Re: Invoice Due Date &amp; Payment Terms

thanks for the suggestion Al, yes I have filtered indexes here's the one for Over30:
ISNOTBLANK("Invoices->Invoice_Date").AND.BETWEEN(Invoices->Invoice_Date,DATE()-60,DATE()-31)

I will try to incorporate what you suggested, but
1) the index filter as well as your suggested <iOver30 = if(<ourover30filter>,balance,0)> both rely on this 'balance' field which is calculated and stored in the table. So rather than calling out the table's field, I should be using calc->inv_balance, correct?
2) I don't really have speed issues when writing to the table. Its the form itself that is very slow i.e. navigating and selecting records which I would like to fix.

1. yes

2. it may not seem that way, but it will be faster.

3. and yes Robin it is a busy form, but should work

[GGandhi]

Re: Invoice Due Date &amp; Payment Terms

Charles, I'm very interested in learning more about the explicit values. What's really slowing things down is that I'm listing all the customers with their balances, as well as their invoices with the totals, there's a lot of calculations being done. Could you suggest another way to go about it?

i am not charles, but have the pleasure of working with him.
what is slowing down is, you are bringing all the customers with their balances and then identify the one customer and display all the outstanding invoices whether billed or not and then display all the open items for a given invoice. this is lot of work for the computer not to mention to you and the person looking at the screen.
what i would do is take a path similar to quickbooks.
display a field ( in my world it is a textbox) and get the name of the customer, and then filter the data only to display data pertaining to that customer. similar to what you have done in your form but with a dropdown or something akin to that in the desktop side.
again taking clue from quickbooks(which displays the same kind of busy data) display main customer info in the middle and the invoices on the right side top and payments on the right side bottom with little but more valuable information. and when the person looking at the screen wants to do something, direct them with some mechanism. most people when they look at a screen on the web side spend around 15 seconds looking at the data anything more they are not interested in it they will move away. i do not know about desktop side. but less eyeful but important information will be more welcome than not in my opinion.

[hov333]

Re: Invoice Due Date &amp; Payment Terms

Good idea G, I actually started off trying to mimic the quickbooks form but eventually moved away from it without realizing.
With that in mind, I'd like to revise the "customer balance" calculation. Looking at QB, each customer is listed with their balance next to the name, then there are child records for each customer showing the transactions which make up the balance. I'm guessing I will need to figure out how to replicate a similar table...I have one for invoice headers and another for payments... Could someone point me in the right direction?

[CharlesParker]

Re: Invoice Due Date &amp; Payment Terms

Just to tossing this in, it would be a really good "feature" to add in an additional table to capture collection attempts. Date reason for not paying, last collection conversation, etc.
Another "feature" would be to add in a button to see any previous invoices for the same customer. Many times, people who do service work may quote a higher/lower price for a service on a per customer basis - reasoning aside, it happens and it comes up often where you may create an invoice and want to recall what you charged that person before 2 years ago for a similar problem.
Again just food for thought...

[MoGrace]

Re: Invoice Due Date &amp; Payment Terms

To replicate the QB model you might try a mapped table. Also for payments you would need both a pmt_hdr and a linked 1:N pmt_items table, since 1 payment may be applied to multiple invoices. I use a 3rd party program to import and export data in QB. It would be interesting to see what a payment export looks like...