Hi all!

I know that this kind of question has been asked before, but I'm sorry if I've missed an obvious thread somewhere in this forum...

My client wants to create an Invoice based on a Quote, and then be able to make changes to the Invoice without affecting the original Quote.

He also wants the Invoice's unique ID to reflect the Quote's, so that when Quote_ID is "Qu001", Invoice_ID becomes "In001".

Any ultimate solutions floating around out there?! Two separate Tables or not?!

Any ideas/suggestions highly appreciated

regards

Anders

Edited June 25, 200718 yr by Guest

Try taking a look at this thread.

Quotes to Invoices

Thank you for pointing that out, but when I had a look at the included QuoteInvoice file in that thread, I found out that any changes to the Invoice was also made to the Quote.

What I need is to set up a relationship (I guess?!) so when I create an Invoice it is based on the Quote - if any changes has to be made on the Invoice, the original Quote will remain intact.

What is the best way to go about creating a new record in another table that's basically a copy of the original?!

thanks in advance

Anders

You can use a Go to Related Record to get the related line items for the quote, then import the found set into the invoice table.

I'm sorry, but I'm afraid I'm not following you... can you please give some more details?!!

regards

Anders

In brief, what I have done is to have separate Quotes and Transactions files (or tables) but have a common Line Items file. This latter has fields both for 'Quote No' and 'Job No'. So, when you make a job out of a quote you stick the new 'Job No' in the quotes line items. To do this pass the 'Quote No' to the Transactions file and have a relationship from a global to 'Quotes file:Quote No'. Stick the 'Quote No' in the global and then you just go to related records and use a script to put the 'Job No' in the line items.

You might need to add other clean up tasks.

Thanks! I'll look into it first thing tomorrow morning!

Well, I got the Quote to Invoice working by using 2 scripts, the first isolates the

desired record (Show All, Omit, Find Omitted)

and then calls a script in the Invoice table that imports from the Quote table with matching field names.

Not sure if it's an elegant solution, but it seems to be working!

What I can't figure out is the calculation neede to create a serial number for the Invoice that is based on the Quote, ie "Qu001" becomes "In001".

regards

Anders

You can create your serial number to just be in the format of 00001. Then a second field for display purposes that is "QU" & pk_serialID. Then in your invoices, you could have another calc that does the same with "IN" & fk_serialID.

You could also substitute it out with substitute (ID; "Qu"; "In")

I also just wanted to point out that I dont really agree with your concept because what happens when there are 10 quotes but only one invoice? Why should the invoice number have to jump as well? IMO, each should have its own unique serial key.

Thanks for the suggestions.

Personally I agree with you about the serial numbers, but it's my client's idea that the numbers should be kind of identical!

Anders

For the numbers to be identical, it would also mean that:

1. One invoice per quote, one quote per invoice;

2. You cannot issue an invoice without a quote first;

3. The invoice numbers sequence will be be out of order and contain gaps.

If all that is acceptable, the implementation could be quite easy. Define a relationship between Quotes and Invoices based on a matching QuoteID field. Allow creation of records on the Invoices side. To create a new invoice from the current quote:

Set Field [ Invoices::QuoteID ; QuoteID ]

Note that if the invoice already exist, this will do nothing. The "invoice number" is the QuoteID (I would also have an auto-enter serial InvoiceID ready for the day when the client changes his mind).

Display is a simple matter of typing

Qu<>

 on the Quotes layout, and 

In<>

on the Invoices layout.

Edited June 26, 200718 yr by Guest