Creating Records With Set Field – The Efficient Way?

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:

magic_key1

And here’s the “create” relationship:

magic_key2

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:

magic_key3

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:

magic_key4

And a script to create a related record could look like this:

 

magic_key5

 

Advertisements

16 Comments on “Creating Records With Set Field – The Efficient Way?”

  1. Jeremy says:

    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.

    • Dave says:

      Jeremy,

      Thanks for sharing this extra information. That is a remarkable difference, and certainly constitutes a major benefit.

      Dave.

  2. Brian Schick says:

    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…

  3. Alex Taylor says:

    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.

    • Dave says:

      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.

  4. Jeff says:

    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?

    • Dave says:

      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.

  5. Dave says:

    [Deleted]

  6. Garrett J. LaFrance says:

    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

    • Dave says:

      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!)

  7. Khanchi says:

    Can you please also send me a copy of the demo file.

    Thanks in advance & best regards,

    Khanchi

  8. Dom says:

    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

  9. Robert Ward says:

    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.

    • Dave says:

      Hello Robert – I’ve sent you a simple demo – hope it’s useful!
      Dave.

      • Luis Sardinha says:

        Hello Dave, could you please share the same simple demo wth me as well? I am experience a similar problem.
        Thank you very much.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s