Creating Records With Set Field – The Efficient Way?
Posted: June 30, 2012 Filed under: record creation, Technique explanations | Tags: create record, Filemaker, set field, techniques, tips 20 CommentsDemo file: MagicKey.zip
I’ve just spent some time getting my head around the technique of forcing the creation of records using a global field in the parent table as part of the “create” relationship. When this field is set to [null] (or any other value which definitely won’t have a match in the related table), doing a Set Field on any other related field will cause the creation of a new record. Note that this generally requires an additional Table Occurrence, just for the purposes of record creation.
As an example, a table of Programme records has a related set of Sessions (i.e. a Programme has several Sessions). In the “normal” relationship between the tables, we simply relate the primary key of Programmes to the foreign Programmes key in Sessions.
In the “create” relationship, we use a global field in the Programmes table, gMagicKey, related to the primary key of Sessions. With gMagicKey set to “”, there will never be a match, so that setting a field in the “create” Table Occurrence will force the creation of a related record.
Here’s the relationship graph:
And here’s the “create” relationship:
So far, so good. But the clever bit of the Magic Key thing (and the bit that Filemaker Inc. don’t document, but which seems to be a reliable feature), is that when you create a record in Sessions in this way, gMagicKey is populated with the primary key value of the record that you’ve just created. because you now have that value automatically in the parent table, it makes the relationship between Programmes and Programmes_Sessions~create valid, so that when you set further fields, the record that you’ve just created will be updated.
You can also use a multi-predicate key for the “create” relationship, to cause fields to be automatically populated. The obvious candidate in the above example is the foreign key to Programmes in the Sessions table. Include that in the equi-join relationship to create a child record without explicitly having to set the foreign key field:
This can be expanded as much as you like. For example, if you are creating a series of related Session records with different dates, you can incorporate the date in the relationship too. If you collect the required date in another global field, gDate, creating a record is simply a case of setting the date in the Sessions table. The relationship now looks like this:
And a script to create a related record could look like this:
A Filemaker Miscellany 
Reading your post inspired me to run a quick performance test comparing different methods of creating related records. Creating 1000 records took:
– 3:56 using the New Window method
– 5.4s using the Go to Layout method (window frozen)
– 0.5s using the related record method
With the related record method, the performance difference between committing once for all 1000 records vs. once for each record was ~0.06 seconds. I was testing on a local file, but I imagine the difference would be bigger for a hosted file, especially a file hosted over WAN.
This raises another interesting advantage to creating records via this method: it’s transactional. If you do it right, when you detect an error you can revert the creation (or editing) of multiple related records, even without a portal.
Jeremy,
Thanks for sharing this extra information. That is a remarkable difference, and certainly constitutes a major benefit.
Dave.
Very nice, Dave. I especially like how this removes the windowing overhead. Re Jeremy’s performance tests: It would be interesting to compare times using this technique vs. SQL INSERT statements…
This is a very interesting technique that I had not heard of before! Thanks for sharing.
One question: what is the reasoning behind adding the global field? Shouldn’t it also work if the relationship was between, say, the CHG_Desc field and the CUP__kp_ID field? As long as it doesn’t ever find a match, this should still work.
Hello Alex,
There may well be a way of achieving this without the global field, but I’ve just got into the habit of defining this “create global” for all tables. Saves having to think about which field to use, and makes the record creation scripts more re-usable.
Dave.
Trying to use your technique to create a set of related records quickly. But can’t seem to get it working, even with just one record. Wondering if you wouldn’t mind revisiting this technique with me via email?
Jeff – I wrote this a while ago, and, I think, in a hurry, because it was rather misleading! I’m sorry if that caused you to waste time. I’ve now amended it so that it hopefully makes more sense. I’ve also tried to email you a demo file, but it seems that the email address associated with your account is not valid – give me another email address and I’ll gladly try again.
Dave.
[Deleted]
Jeff,
I’ve followed your instructions and think I must be missing something as I get the following error message:
[This field cannot be modified until “gMagic_Key” is given a valid value]
Here is my script where gMagic_Key is a global text field & PRIM_REF_ID is a plain text field:
>Set Field [Primary Key Test::gMagic_Key; “”]
>Set Filed [Secondary Key Test::PRIM_REF_ID; Primary Key Test::gMagic_Key]
>Commit Records/Requests [Skip data entry validation; No dialog]
>Close Popover
I’m on a Windows 7 Server running FileMaker Pro 13.
Thank you in advance (y)
-garrett
Hello Garrett,
I’ve added a demo file (magic_key.zip) to the Box over on the right – hope fully that will clarify things a bit. The way it works is that, in the “create” relationship, you relate:
gMagicKey to the primary key of the child table
primary key of the parent table to the foreign key in the child table
as many other data items as you wish (e.g. date, in the example)
It’s the setting of the other items in the child table which forces the creation of the child record. And the creation happens with the keys automatically set – the primary key is set according to the auto-enter options that you’ve specified for the field, and the foreign key is set to the value of the parent record that’s forcing the creation.
The added usefulness comes from the fact that, after the creation, gMagicKey contains the primary key value of the newly created child record, which you can then use for other things (or not – maybe you don’t need to).
Hope that helps,
Dave (not Jeff!)
Can you please also send me a copy of the demo file.
Thanks in advance & best regards,
Khanchi
Hello Knanchi,
I’ve sent the demo file via email. Hope it helps!
Dave.
Dave:
Love this technique. Could I get a copy of your demo file. This looks like something I’d love to put in the old “toolbox”.
Dom
This technique is not working for me for single record creation. I would like to use this for an access log and not have to navigation to layouts. Nothing I have done works. I you have a sample file I would appreciate if you can send this to me. Thanks.
Hello Robert – I’ve sent you a simple demo – hope it’s useful!
Dave.
Hello Dave, could you please share the same simple demo wth me as well? I am experience a similar problem.
Thank you very much.
Hi Dave, I am also not able to follow, but, it sounds exactly like what I need to finalize a solution I am working one. So, if it is still available is it possible for you send me a copy of the demo file also.
Many Thanks in advance & best regards for sharing you work!
Chris
Chris,
I’ve sent a link to the demo file via email, and updated the blog post to include the same link.
Dave.
Hi Dave can u also send me a demo file? I’m not able to follow! thanks
Hello Yollev, The demo file name is now a direct link to the demo file, but I’ll send it via email as well. Hope it helps,
Dave..