Bonus points if it can also display a dialog box to allow the user to modify a specific data point that shows up on all three tables, such as product name.
Product name shouldn't be in all three tables in the first place. It should only exist in the products table. The same goes for any other fields expect those Foreign keys that match to primary keys. If you have the same data in two tables, chances are you need to remove a field from one of the two tables.
To see a script that duplicates a parent record and a related set of child records, take a look at the very last script example found here: Duplicating Bill Of Materials (duplicating portal line items)
Thank you for this answer! It seems to be exactly what I need. I'm working on implementing it now.
Hi that script is fantastic, and worked out perfectly for me to copy a record within a table.
However just wondering what is the best way to edit the script it so I can copy a record and it's portal items to another table?
Or is this possible?
Thanks a million
That's not what this script is designed to do. That is more easily done with an Import Records script step--which can copy a set of records from one table to another, even when the two tables are in the same file.
But there are many reasons for such a mass copy of records from one identical table to another and few are really "good" reasons.
I am trying to create an invoice from a quotation, of which there might be many on one job, however I tried using the same table and having an invoice layout as suggested in other posts, but I want to avoid people editing the quotes and affecting the created invoice. I am still not sure of the safest and cleanest way to achieve this TBH.
Often the invoice is altered from the final quote so there would need to be a 'new' instance of it, but again having read lots of threads I know that copying records isn't advisable. So I am stuck and not sure what avenue to go down!
Copying records often results in redundant data. But what you describe is one situation where copying records is advisable and an expected process. But the original duplicate script can do the job. You can certainly keep quotes and invoices in the same table and this is simply a case of duplicating a quote record along with its related line items records, relabeling the parent record created from the Quote as an "invoice" record instead of a Quote.
But if you prefer to keep invoices and quotes in separate tables, then you can use Import records to copy sets of records from one line items table to the other. (And it's even possible to keep both sets of line item records in the same table...)