Creating Records With Set Field – The Efficient Way?Posted: June 30, 2012
Demo 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: