I need help in planning forms and linking tables (1 Viewer)

[ptaylor-west]

I have a form which raises a Quotation which grabs information from the main Customer table and I enter prices and quantities and it totals everything up with taxes etc.

I now want to generate an Invoice which contains exactly the same information as the Quotation but store it in an Invoice table, I want to press a control button on the Quotation which would generate the Invoice and assign an auto number, I then need to enter additional information on the Invoice such as serial and registration numbers and store that information in a Serial Number table with the customers name.I also need a seperate costing sheet where costs are entered and then taken from the sales price to give me profitability........would I need another table for this ?

The whole picture is so that apart from generating invoices I can produce reports on Quotations to Invoices ratios, Total Values, profit per unit and also look up specicifc products sold to any customer based on the serial numbers.

I don't need specifics I just need guidance on how I lay it all out, I don't know why I have a mental block on this but I know the value of planning your applications only too well and don't want to waste time creating forms which then don't give me the results.


[This message has been edited by ptaylor-west (edited 10-08-2001).]

[This message has been edited by ptaylor-west (edited 10-08-2001).]

 

[Chris RR]

Let me just comment on a little part. If you have standard costs for an item, then you will need a table to store those costs.

I would make sure that any costs that you store have effectivity dates on each record (possibly the beginning date and certainly the ending date that a particular cost is valid for a part). In every system that I've worked on with costs, effectivity is the first thing that users ask for.

You may also need to let users override these costs for a specific invoice. Then you would need to store the costs for an invoice (if only standard costs are allowed, you can always look the costs up.)

 

[ptaylor-west]

Thanks Chris, in this case costs will probably always be variable because of current market price ie: old stock/oldprice so I agree I will need a table but should I put this in the Invoice Table as the costing sheet needs to be like a backing sheet to the Invoice and therefore costed at the time of sale ?

 

[Rich@ITTC]

Hi Paul

OK here is some advice.

Create a table of current prices, with product ID, descriptions, etc. This table is used as a lookup table to give you a default price when creating your quotation and/or invoice.

Create a table for storing overall quotation/invoice details - customer ID, date, person taking order, etc.

Create a table for the invoice items. This table stores the invoice ID, product ID, actual cost, quantity.

In the form that you design for drawing up a quotation/an invoice have a combo box of products. On the After_Update event of this combo box use DLookUp to check the current price and add this as the default actual price (you can't just have the current price as the value, as prices can change due to inflation, special offers, etc and then your quotation/invoice would change restrospectively).

To change a quotation to an Invoice I would not use an AutoNumber (controversial stuff, I know!). The problem with an AutoNumber is that you can lose numbers if a user cancels out when halfway through entering data ... and then you have a missing Invoice number sequence (which is not good accounting practice). I would have a button that converts a quotation automatically to an invoice ... locking the invoice (so it can't be altered once issued), allocating the next sequential number from a query showing the max value of invoice numbers already issued.

Does that give you something to work on?

HTH

Rich Gorvin


[This message has been edited by Rich@ITTC (edited 10-09-2001).]

 

[ptaylor-west]

It certainly does give me ideas, can you expand on:

"I would have a button that converts a quotation automatically to an invoice ... locking the invoice (so it can't be altered once issued)"

If I can get this bit right the rest of what you suggest is straight forward.

Paul

 

[Rich@ITTC]

Hi Paul

Sorry, I obviously followed your original post too literally ... "I don't need specifics I just need guidance on how I lay it all out ... "

OK - what I do is have a field that indicates the staus of the Quotation/Invoice (eg. 1 = Quotation, 2 = Invoice with an Options Group display, or a tick/check box - with Triple State set to No - with unticked/No for Quotation and ticked/Yes for an Invoice).

The command button that converts a quotation to an invoice does a number of things (usually I make this a button for actually printing out an Invoice), including changing this Status value from 1 to 2, or No to Yes. It finds the highest value for an existing Invoice and generates that number+1 as the new Invoice number. It then sets AllowEdits to False (so the Invoice is longer editable) ..

Me.AllowEdits = False

I also have the OnCurrent coding include an If/Then/Else condition, checking each Quotation/Invoice and what the Status is ... if it is a quotation then AllowEdits = True, if it is an invoice, AllowEdits = False. This means that an Invoice is "locked" (though strictly speaking this term is usually used for individual controls).

HTH

Rich Gorvin




[This message has been edited by Rich@ITTC (edited 10-11-2001).]

 

[ptaylor-west]

This looks spot on, I will try it out at the weekend.

Many thanks once again.

Paul

 

[Rich]

Curious Rich, how do you cope with any extras that may have evolved since the original quote?

 

[Rich@ITTC]

Hi Rich

Well ... now I am getting into the nitty-gritty stuff (as much to do with "business defined rules" as database design work). Much of what I have described to Paul is based on the databases I have designed for English language schools (EFL) - a School Administration Database. Often in this line of business a quotation (in the case of this line of business a Pro Forma) is "open to change". The client comes back, asks for extras to be added, changes dates of their course of studies and so on. This can all be accommodated within a Pro Forma/quotation as the form is still editable and the Pro Forma/quotation number does not enter the accounting system (so a Pro Forma can even be deleted if a booking never materialises in the end).

Once payment has been received an Invoice is drawn up and issued/printed (though in the system I am describing this only involves the user in clicking one button and all the data pertaining to the Pro Forma/quotation is instantly linked to an Invoice - i.e. an invoice number is assigned to all this data).
At this stage invoices, as far as users are concerned, are fixed/uneditable in terms of altering items, quantity, price etc. Payments are a different matter... full or partial payments can be accepted against the invoice at any stage (though payments against a Pro Forma are not allowed ... as once monies have been received for a service an invoice must be issued, so the Pro Forma has first be converted to an invoice and then payments can be cross-referenced against the invoice number).

If a client changes his/her course after paying (leaving earlier, changing number of lessons per week, etc) then the user can at the press of another command button change all those details from an invoice status to a credit note - so the initial invoice is "removed from the system" ... in fact it is all still there, but as a credit note now. The user then has to draw up a new replacement Pro Forma which is immediately converted to an invoice. This may seem rather laborious, but in a language school this is necessary as the invoicing for courses is linked to class attendance records (you can't be in class without having had a Pro Forma drawn up for the type of course you are on ... so the possibility of upgrading an invoice by £20/$30 a week to take account of a change from one course to another is not possible).

Complicated? Some aspects, perhaps! But it does work well in practice, as well as in theory, in the type of organisation I have described. Once last thing to mention ... there is an administrator password protected feature that will allow the head of registration to go in and revert an invoice to a pro forma (when, for example, the user has pressed the wrong button or realised that the data was entered incorrectly) ... but where I work we have only had to use that on 2-3 occassions in over 2 years and over 1,000 invoices.

Does that satisfy your curiosity, Rich?

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 10-12-2001).]

 

[ptaylor-west]

Tried your solution and it works really well, I have also been playing about setting up an Invoice Table and Cost Table and using a query append to transfer the quotation to the Invoice Table from the Quotation Form which means that as your pro-forma that it can only be changed at the quotation/pro-forma stage.

From an accounting view my process is less critical because it is an order form that is passed on to accounting for processing in another package (Sage or the like) and what I am doing is quotation/order ratios and profit ratios from the order/cost tables which I am now able to achieve from all your very helpful advice and guidance.

Thanks once again.

Paul

 

[Rich]

Mmmm many ways to skin this cat eh

 

[pcs]

nah, rich...you won't suck me in with the cat-skinning

but seriously, this is one of the better threads i've seen on this forum!
'business rules' can get very complicated! and there is a lot of good info here!

my $.02 worth:
i never allow modifications to a quotation or an invoice...any changes require a new quotation/invoice sequence, with perhaps a credit memo (invoice) to correct any billing errors in the original invoice.

in any case, some very well thought-out replies from chris and the Rich's!

al

 

[Rich]

Interesting to see how others approach this problem, in my case I have to be able to edit the invoice for a variety of reasons although I give warnings once the invoice is issued. I lock it when it's paid.
Sorry al I forgot about your furry companion. But didn't "cats" in your youth mean something else?

 

[pcs]

rich,

what youth? i don't remember it, but pretty certain it was 'misspent'.

you're right about the various approachs...business rules are often quite hazy...even when you look at GAAP, you find a lot of conflicting info. since the US has gotten so damned litigious, i take a fairly hard-line. when a document, for example, is sent to a customer (electronic or hard copy) i disallow any changes. but, that is probably over-kill for most applications. i do a lot of stuff for clients who have some pretty scary product liability issues. CYA is real important to them.

al