Duplicating Related Record Sets
FileMaker provides us with the wonderfully simple Duplicate menu option right there within its Records menu. The problem, however, with a relational database, is the fact that many times you're in need of duplicates of both a master record and any of its child records.
This video presents one of the many possible solutions to this exact issue.
You can, for example, export a set of related records and then reimport them while reassigning key fields. You can also fully script the whole process using all of the original data and simply assign new key values as you walk across a portal.
In this video, I walk through one of my favorite methods for creating related records - the Obligatory Create method. This method has a number of advantages and adds only a few extra table occurrences.
As with any technique for solving a specific problem, you must assess if the solution fits your environment and the given circumstances. Doing this comes with experience and exposure to the various possible methods. This method is just one of many.
Comments
AppleScript method
If you are on a Mac and the use of AppleScript is feasible, Bruce Robertson demonstrated a very simple way to do this. It does lock you down a bit in that you need a special layout, but if you have some specific situations in your dB where you want to regularly dupe a record and its related records, this is very cool.
From Bruce:
You need to create a layout with the desired parent/child fields displayed , but no calc fields should be displayed and the key fields should not be displayed. The parent to child relation(s) behind this layout have to allow creation of related records. If you've got that set up, then switch to the special layout and use Perform applescript in your script with the following one line. It is almost instant.
"create new record with data (get data current record)"
I have a sample file from him, if you are interested.
Doug
Sample file
Hi Doug, i just saw your comment describing Bruce's applescript method. Would you mind sending me a copy of your example file?
Thanks!
Tom
trubane@gmail.com
Very nice tip!
Ooh, this is very nice. I would expect nothing less from Bruce. Thanks to you and Bruce for the tip!
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Had it and lost it
Well, I am really excited about this. I have been trying to accomplish this very thing for weeks and here was the answer all along. Thanks Matt!! I integrated it into my solution and the first time it worked perfectly. The second and every time after I get some strange results though. Now, the child records are being doubly duplicated, half of them never get the lookup match field set, and only the first child record that gets related to the parent has the lookup data. all of the other records are there in the correct number, but these are the ones without the lookup data. The remaining new child records with the lookup data are just orphaned in the child table. I might be able to trouble shoot this better If I understood more about how the List function worked and how this script advances through them when creating the records.
Try a Commit step
Many times, when I create files based on using FileMaker's ability to create related records through a relationship, I just add the Set Field script step. I haven't looked at this file lately, but if you try a Commit step, that often solves the type of problem you seem to be talking about.
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Import, without Export, from found set -- drawbacks?
Hi Matt,
What would be the downside of the following (is there a compelling reason NOT to use it)?
1. Pull up the related records in the child table, using Go To Related Record (show only related records)
2. Go back to original (parent) table -- duplicate record (make any adjustments in desired fields) -- copy ID key
3. Return to child table -- perform a self-import of found set -- set ID key for parent record
4. Return to parent table
??
Would this not work in a two-file (no join table) or three-file (with join table)?
IS there a compelling reason NOT to use this approach?
Thank you.
Experienced beginner / rudimentary expert
Obligatory Create not working on FMP 19.3 under macOS 11.4
Hi Matt,
I've been using obligatory create for years. I recently created a new file and implemented the technique, but it is not working reliably.
The first time the technique is used after the file is opened, it works fine; but every time after that it fails – because even though the global field has been cleared and set to the empty value "" (verified in the data viewer and in a window open to the relevant table) the relationship to the first new record is intact and the subsequent attempts to create new records merely overwrite the first record.
I tried deleting and re-creating the relationship between the two tables, but that didn't help. I'm scratching my head on this one.
Obligatory Create not working on FMP 19.3 under macOS 11.4
Hi Matt,
I've been using obligatory create for years. I recently created a new file and implemented the technique, but it is not working reliably.
The first time the technique is used after the file is opened, it works fine; but every time after that it fails – because even though the global field has been cleared and set to the empty value "" (verified in the data viewer and in a window open to the relevant table) the relationship to the first new record is intact and the subsequent attempts to create new records merely overwrite the first record.
I tried deleting and re-creating the relationship between the two tables, but that didn't help. I'm scratching my head on this one.