invoicing direction

[Duane Pederson]

Morning

I seem to be going in so many circles here trying to figure this out if someone could please clue me in.
My understanding of making an invoice system for charge accounts is to do a summary operation of the line items sold and then post that result to a previous balance field in the customer table.
My problem with this concept is all of my - discount, local tax, federal tax, ship charge fields are part of the header table which doesn't get summarized, hence not posted.
I have made a fairly large expression in the summary operation which works well to calculate all these extra fields, but it won't post because it is too many characters long and the post setup truncates the expression.
Do I need to do all the calculations in the items table or browse so each line item has it's own value of discounts and taxes? Then the summary op will collect the final amount, maybe plus shipping and the post should work.??

Thanks
Duane

[Jeff Moses]

RE: invoicing direction

Duane, Calculating an invoice can be done about a thousand different ways. We've spent the last three years tuning ours. Here are some roadblocks we ran into and some solutions. First of all you have to decide how far you want to break down taxes, discounts, returns, etc. For example, if you want to be able to determine if an individual line item is or is not taxable when the item is entered in the original table. If each customer is able to be marked tax exempt or eligible for a fixed discount at the time of the customer�s entry. If you need a checkbox on the invoice to make the whole transaction tax exempt from the start. If you give a general discount on the invoice but always have some items that are non-discountable. How to handle returns. How to charge to another department. This list could go on for another page. If you think you will need to be very specific as your app grows then by all means determine the final extension value of each line item and then general header calcs on top of the line item extensions. You can kill a number of birds with one stone if you add another field we call "entry_type". The user fills this field for each line item and it determines how the item is handled. Most line items would have an entry_type of "Sale". Others might include, "Return", "NonDisc", "Quote", "LayAway", and the list goes on. If you just doing simple invoicing, header calcs would be ok. If you choose to calculate by the line item a good way to do it is create a second extension field that the user never sees but we call the calulated_extension. This value would differ from the basic extension shown to the user, which is quantity times price. The calulated_extension would have all other calcs figured in such as tax, etc. You would then have another hidden field called calc_extension_total that would be the total of all the line items after they have been calculated. This figure could then have any final general calcs you might want to make. NOW TO FINALLY ANSWER YOUR QUESTION! We then take the final results, load them into variables and post them to wherever we need them to be. In our system we allow multiple payments, or deposits on each invoice. Most of our data gets stored on the OnWrote action when leaving the deposit amount field in the payment browse after each payment is received. A script runs and posts all values where they belong and informs the user via a message box that the payment has been posted. I have attached a .bmp of our invoice form that displays a number of issues you will want to think about when designing an invoicing system. You may want to open it up and read this again while looking at it. If you look at it closely you�ll see most of the things I�ve discussed up to now. We also have a Repair Order that is much more specific than the invoice and allows quotes, dept charges, etc. I can send a sample of that if you like. This is more info than you asked for but it sounds like your still in design mode and this could save you allot of time down the road.
Jeff

[Jeff Moses]

RE: invoicing direction

Duane, In the bitmap attached to the last post you will see a couple of line items in red. This indicates to the user that that item is out of stock.
Jeff

[Duane Pederson]

RE: invoicing direction

Jeff

Thanks for your in-depth reply.
And yes My head has been spinning with what if's.
Consequently, the what if's blew me away as to which way to turn for this. I ended up putting the tax and discount rates on each line item and calculating the amounts in the field rules as some items will get diff tax and disc. Then making a summary table with a summary operation. Then I can append all this data to a transaction table to use for statements and tax reports, I hope.
And yes, everytime I open this thing up, another 22 issues seem to pop up.
Thanks for the file. I will open it up tonight. And read your first post a few more times.

I'll be back!! (for sure)

Thanks again
Duane

[Jim Chapman]

RE: invoicing direction

Hi Duane,

I've done quite a bit of invoicing and as Jeff said, there are a ton of issues. I don't summarize or post except in the individual line item records to their parent invoice header record. I make sure I've got a good invoice header with all the info I need for each transaction. The invoice header is the heart of my systems. Now I can link the invoice header to the customers or not as I wish and let my reporting script and report do the work. I can then report based on customer, invoice header or line items. When a customer pays my system does a dynamic lookup on the spot figuring what is owed etc. I don't depend on 'old' posted records. In my systems I apply payments to the invoice header, allowing oldest invoice to be paid first (customer centric), or if needed to manually apply a payment to a specific invoice (invoice centric). I try to keep my data structures simple and posting to a bare minimum and let scripts, functions and reports give me the values I need when I need them.

I handle the taxation and discounting of items in the same manner, put all summation data in the invoice header record. Then I can report till the cows come home.

Good luck,

Jim

[Jeff Moses]

RE: invoicing direction

Hi Jim, Maybe we learn from each others experience? We are slowly trying to get away from posting as much as possible but can't seem to get over a few issues. Maybe you can help. Are your payments entered on the invoice or in a seperate receive payments screen? How do you apply more than one (unlimited) payments to a single invoice and then show all the activity while paging through the invoices? In doing this we are using a child (one to many) deposit table. Each payment is added to the last, calculated into the header total and then calculates the amount to be applied the invoice. In amount applied I mean does the customer get any change. A gift certificate may be used and the customer will receive change. Many times a user enters the wrong amount and the error in not caught until the last payment, in which case the whole transaction needs to be recalculated to correct amount applied for each deposit, or payment, hence re-posting values. Second, if an invoice is marked to have a general discount via the header, how do you exclude a single non-discountable line item from being discounted in the total and then figure the tax? These are only a few issues our invoice has to be able to deal with. Our customers use this at a cash register. Our Repair Order is another animal all together! Any info you can offer would be greatly appreciated. I would be happy to share any of our notes as well.
Jeff

[Jeff Moses]

RE: invoicing direction

Jim, When I say posting, I mean xbaisic scripts to keep things up to date. We don't use the A5 posting operations for anything but simple QIS (Quantity In Stock).
Jeff

[Jim Chapman]

RE: invoicing direction

Hello Jeff,

>>Are your payments entered on the invoice or in a seperate receive payments screen?>How do you apply more than one (unlimited) payments to a single invoice and then show all the activity while paging through the invoices?>Second, if an invoice is marked to have a general discount via the header, how do you exclude a single non-discountable line item from being discounted in the total and then figure the tax

[Duane Pederson]

RE: invoicing direction

Morning Jim

Thanks for the reply. It gives me confidence to continue on with this even though my A5 knowledge is minimal

Duane