Convert qoutation to invoice

[Freddie81]

Hi everyone,



I have a database for issueing qoutatins and invoices that I nee help with. I have 4 tables, QoutesT,QouteDetailsT,InvoicesT and InvoiceDetailsT. I have a datasheet with Qoutes where the user clicks on the qoutation number to open a new form containing the qoutation info. What I would like to do is to have a button on this form that the user can click to "accept" the qoutatiion and convert it to an invoice in th InvoiceT table along with the details in InvoiceDetailT. After the qoutation has been converted it needs to delete the qoutation from the QoutesT table and QutesDetailsT table.

Do I Dim a couple f variables for each of the fields and copy the values from one table to the other or what is the most effective wayto acomplish this?

[CJ_London]

if quotes are the same as invoices, why copy to another table? just have flag field which says whether the record is a quote or an invoice - which the user can change the status of with the button click event - the flag field might be the invoice number - if it is null, it is a quote, if not it is an invoice.

[Freddie81]

The Invoice tabl has ields that are dffernt than th r qoutes. so I woul like t keep the tables seperate.

[orange]

Freddie,

Please explain

I woul like t keep the tables seperate.

I agree with ajax based on the info you provided. If you step back, it seems you could abstract quote and invoice to document.
Then you would need some sort of flag to identify a document as a Quote or Invoice.

What are the fields in your tables? Why do they have to be different? Is there something you haven't told readers?

[Freddie81]

Ive attached the relatioships for my tables for you to see. The invoice has fw extras in it like Order number, Vat amount, Terms,Paid.

[CJ_London]

Just looking at the headers - vat and vat amount should not be stored in the header. VAT is calculated at a line level so should be stored there.

depends on your business but I would question the paid flag - what if the customer only part paid? or pays in advance? this data should be in a separate table.

terms - won't these be standard across the business? (or perhaps a variety based on customer type) - and wouldn't it form part of the quote anyway?

discount - why in the quote header? you have it in the line details

what will be different between a quote note and an invoice note?

So far as I can see, there is no reason not to combine the tables although you could have an invoice table that just points to the quote table if you have many quotes and few invoices

Other question to ask - what happens if you quote for say 10 items and the customer only orders 8?

[Freddie81]

The Vat fied is a dropdpwn list wher user chooses 0 or 15 % and then the orm calculated the vat and enters it into VatAmount.

You have a very good pint regarding the paid field...Ill have to modify the database for this. That will definately be a next step for the application

The terms I made a list to pick one from...either COD, Account or SLA. I have clients that are cash only but business clients get a account if they need to and then there are SLA clients who only pay one fixed amount on a monthly basis and not every call out.

QDiscount is for the line entry only...so if a client takes a data recovery for example they can get 25% discount and then th value for this discount goes into the qoute table into QouteDiscount.
Regarding the notes there will be no differnce at all. Both are Long text.

That is why I wanted a seperate table for invoices and qoutes. If the client accepts only part of the qoute the entire qoute gets converted to th invoice table but the user can still modify the detauls fields on the form to remove the data that is not relevant.

It seems I have sme more planning to do lol. Could the user select the items from the details subform to convert to the invoice subform section?

Sorry If I seem stupid with this...it is my first database attempt

[CJ_London]

Could the user select the items from the details subform to convert to the invoice subform section?

yes. You have provide a reason for keeping them separate, but I still feel in your situation it is not required - as with my comments about the invoice header, the same can be applied to the invoice lines.

I note you do not have an invoice number field - so the implication is you are using the autonumber field. Be aware that this is generally a bad idea because you have no control on its value - autonumber's only purpose is to provide a unique ID for a record, it should not be seen by the user and should not be given any real life significance.

However if that is the route you wish to go, you will need 3 queries

The first to append the quote header to the invoice table
The second to find the ID of the newly created record
The third to then append the line records by linking invoices to quotes to quotelines

The second one is the tricky one, particularly if you have multiple users. I would recommend you include the quoteID field in the invoice field, then you can find the new invoice by using the quoteID as a criteria.