Copying Records

[nutthick]

I'm working on a simple quote and invoice database. I create a quote record and then add items to the quote, by adding records from a quote_item table. The layout has a portal to show the appropriate items associated with the quote. I also have an invoice section set up identically. What I want to do, when a quote is accepted, it to create a new invoice record (easy) and then copy all the associated quote_item records to the invoice_items table. This is where I'm stuck, as I can't work out how to copy the data across. Sometime a quote will only have one item, sometimes more. Can any please help, as I can't figure out how to write a script to perform this 'dynamic copy'?

Thanks

QuotesandInvoices.zip

[transpower]

One way would be just to change a status field from "Quote" to "Invoice"--so you don't need a separate invoice file. It seems like you have four tables, when two would do (unless you really want to keep quotes and invoices totally separate).

If that won't work for you, just add the quote number to the invoice file and relate that field to the quote_item table; you could have a flag in the quote_item table indicating that the quote_item is now an invoice_item.

[nutthick]

transpower: I did think of that, but often invoices and quotes look different. Extra things have to be bought, extra work is needed, or sometimes items are dropped from the quote. In general the majority of the quote is the same.

[-Queue-]

Why not Go to Related Records [show only related; quote_items] and then Import the found set into invoice_items?

You can simplify this by combining your parts and labour item tables. You only need a field to flag whether a record is parts or labour, two calculation fields (or auto-enter calculation fields) in the parent table with the same flag result, and a relationship from each calc field to the related flag calc. Then it's easy to keep the data combined yet separate.

[nutthick]

Queue: The importing is working great! However, I have a field in the Invoice_Parts that holds the Invoice_ID of the the parts relate to. Obviously this field isn't getting filled. How would I get all the imported records to take the Invoice_ID for the invoice record just created?

[-Queue-]

Set the id into a global and loop through the imported records, setting the id with the global.

In the vein of minimizing tables, you can easily have a quote_id field and an invoice_id field in the same table and display the correct related records for each one. It wouldn't be difficult to have all quote items default to the newly created invoice id, then use a button on a portal to remove the related item from the invoice by using Set Field [relationship::invoice_id; ""], thereby breaking the relationship for the selected related item.

[nutthick]

I've been trying looping a global since I last posted, but I'm struggling to get FM to jump out of the loop. So far I've got

Go to Record/Request/Page [First]

Loop

Set Field[invoice_parts_Items::Invoice_ID; GlobalID]

Go to Record/Request/Page [Next]

Exit Loop If[] <--- Stuck here

End Loop

How would I get the loop to exit when it has reached the last record in the Set?

In regards to minimising tables, I toyed with the method you mention about Quote_ID and Invoice_ID in the same record, and that's probably the way I will go eventually. I think I'm still going to come across this scripting problem several times, which is why I'm trying to crack it now.

[-Queue-]

Select the 'Exit after last' option that appears when you select 'Next' on the Go to Record/Request/Page step.

[nutthick]

Great, thanks for your help. That'll keep me going for now