6 Replies Latest reply on Aug 8, 2012 5:44 AM by russbrad

    How To Duplicate a Record with Related Records


      I'm aware this is a common request, and I've seen some threads detailing how to do it in broad strokes, but I'm still lost as to the specifics.


      I have tables of Quotes, Components and Line Items, in descending hierarchical order. So a Quote has Components, and a Component has Line Items. The Quote building screen is actually a Component layout that views other Components through a self-join portal.


      What I want to be able to do is duplicate a Component on the same Quote with all of its Line Items.


      Now, I understand the broad strokes theory:


      1 Copy original Component id to variable

      2 Duplicate Component

      3 Create new id for Duplicated Component and store as variable


      4 Go to Original Component

      5 Get related Line Items

      6 Duplicate Line Item and copy the Duplicated Component id into the appropriate field, repeat until all Line Items are duplicated


      7 Go to Duplicated Component to view it

      8 Profit


      The problem is, I don't understand how to get back to the Original Component, or how to loop through the associated Line Items for the Original Component and duplicate them. Something about the logic of it just isn't clicking for me. Is there a standard way to do this that will help me?


      As a side note, I come from a programming background, and there people talk about the "pointer epiphany", when you finally get what pointers are and what they do, and then a lot of things suddenly make sense because you understand what's happening under the hood. I think there might be a similar epiphany that I have to have when it comes to databases, but I don't know what it is. I just don't seem to be able to synthesise solutions to problems in Filemaker the way I can when writing code, so if anyone can help me understand where to look for this insight, I would appreciate it.

        • 1. Re: How To Duplicate a Record with Related Records

          This old chestnut!


          Wouldn't it be great if this was a feature built into FileMaker Pro?


          Ok, here's my thoughts (I'm sure there are better ways of doing this, but this works)


          1. Store the orginal parent ID in a variable, that you know already. (Lets call it $Original_ID)


          2. In the Parent table, create a new record, grab the id of the new record into a variable. (Let's call this $New_ID) The get next record ID could also work in this situation, but I'd be concerned about the timing if you're in a multi-user system where lots of records are getting created simultaneously, I'd prefer to create the record and get the ID.


          3. Go back to the orignal parent record (you could easily find it using the ID stored in the variable $Original_ID)


          4. Go to related child records, export these


          5. Import these records into the child table, replace the linking (foreign key) parent ID with the $New_ID


          6. Go to the new parent record for viewing (find the record using $New_ID)


          that's it...!


          I'd like to know any better ways of doing this, this tequnique may be slow on iOS from what I understand.

          • 2. Re: How To Duplicate a Record with Related Records

            Okay, one thing I'm missing - how do I go back to the original record? (step 3 in your answer)  Exactly what steps would you perform to do this?  You've said to perform a find, but I've always found that this has unpredictable results.  I have a lot of trouble once I've done a find knowing whether I should Show All Records, or Unsort Records, or whether I can just use the record and forget about the found set. Basically I don't know how found sets work.


            If there's a good tutorial you know that covers these kinds of things, I'd be interested to see it, because like I said, I think there's some fundamental piece of Filemaker understanding that I'm missing.

            • 3. Re: How To Duplicate a Record with Related Records

              roughly, you can peform a find something like this


              Enter Find Mode, set your id field to a calculated result (being the variable), then perform find.


              The search should ALWAYS find the one record, but it is wise to set error capture on and check for found records after the peform find.


              If you want some help understanding found sets, think of it this way:


              Performing functions generally work based on a found set of records. Performing a find, then doing an export or a loop, sort etc will only work on the currently found set of records.


              When you perform a find and it returns just 1 record, you can then goto the related records and the new found set of records are just those related records.


              The best way to practice this is to perform some tasks manually. If you have FileMaker Pro advanced, use the debugger when performing scripts.


              If you're really stuck, I could throw together a file for you to take a look at the code.

              1 of 1 people found this helpful
              • 4. Re: How To Duplicate a Record with Related Records

                Okay, thanks, that's a good explanation.

                • 5. Re: How To Duplicate a Record with Related Records

                  As an aside, I always permanently populate a separate field (could be named "Original_ID") with that original ID in all the newly created records and line items. This becomes the user's path to sanity when, for example, a invoice gets multiple credit memos or a PO/Quote is altered multiple times. It then becomes easy to display a history of that transaction in any number of ways.

                  • 6. Re: How To Duplicate a Record with Related Records

                    Thanks, I was able to duplicate Line Items using Duplicate Record, then just Go To Record/Request/Page [Next; Exit after Last] worked well enough.  I didn't have to do Export/Import - although if that's a better way to do it for some reason, I'd like to understand why.


                    I think the main problem was I was using GTRR, but "Show only related records" wasn't checked, so my find didn't appear to be working.  It seems obvious now, but when I didn't know exactly what it was supposed to do, I couldn't tell what was going wrong.