4 Replies Latest reply on Oct 29, 2008 4:12 PM by swc

    Duplicating a record that is linked to several tables



      Duplicating a record that is linked to several tables


      I have a work-order system that links two tables together...the main table links to customer info (one to one link); parts info line-item (one to many link). That is three tables total,



      => Line Items 

      => Customer details.


      I can not find a way to duplicate a workorder when a job repeats, meaning I need to duplicate one record in the main table, no duplication in the Customer table, and I need to duplicate a range of records from the Line Item table.


      All of my scripting attempts have failed (scritping is still my one greatest FM weakness), are there any other ways to do this? Or can someone offer some tips on how to make my (weak) script work?


      Am I even making sense? Can someone recommend a solution ot point me in the right direction?

        • 1. Re: Duplicating a record that is linked to several tables

          in other word, you'd like to duplicate related records.

          I have an example of that somewhere on the site where I usually collaborate (experts-exchange.com), see if you find it there, otherwise I'll try to locate it on my disk and post it to you.

          • 2. Re: Duplicating a record that is linked to several tables

            It is a big pain.  I wish FMP had an option in the relationship definition that helped with this.  Currently, you can select to delete when the related record is deleted so that you avoid orphan records, which is good.  If you could select to duplicate when related record is duplicated, that would simplify matters.


            If you just have the relationship you describe, and not ones that are multiple levels deep, you can script this.


            In Workorder:

             - Get to the record you want to duplicate.

             - Show all records.

             - Omit record.

             - Show omitted only.  // Now you are only showing the record you want to duplicate.

             - Duplicate & commit record.

             - Store the record_id in a variable - $$new_rec_id

             - Omit the new record, leaving you on the original record.


             - Go to related records (line items), show only related, on appropriate layout.

             - Go to first record

             - Loop

             - Duplicate record, and set work_order_rec_id = $$new_rec_id

             - Commit record.

             - Omit record. - // Drops the new line item linked to the new work order

             - Go to first record.

             - Omit record. - // Drops the original line item linked to the original work order

             - Exit loop if record count = 0

             - End loop.

             - Go to original layout, optionally perform find based on rec_id = $$new_rec_id to take you to the new work order.


            • 3. Re: Duplicating a record that is linked to several tables

              Cool! This worked wonders!


              And I even understand the script.


              Can I impose upon the group-brain here and ask how this would work if you wanted to duplicate multiple records in multiple tables. Same relation as the initial question..... One master table, one-to-many record relations to two tables, one for notes and one for line-items. The script example given works for one table, how could you do it for two tables? Will the variable pass to a sub-script if I break up the loop portion into a smaller script that gets called twice with the different relations specified?

              • 4. Re: Duplicating a record that is linked to several tables

                If you are only dealing with one master record and all of the records you need to duplicate are one hop away, it is trivial to add additional related groups of records.  Just repeat that second block, only applying it to the other relationship(s). 


                Entirely up to you whether you are comfortable just flat out repeating the block of script steps or would rather put it in a seperate script and call it.  If you break it out, you could either pass the variable in as a parameter to the script, or you could leave it as is.  If you prefix the variable with two dollars signs, as in my previous example, the variable will remain set in the subscripts.