FileMaker – Indirectly Add a Record
Indirectly Add a Record
I recently inherited a database for a local New Mexico college in Santa Fe, something I always enjoy. I like to learn, and I try to learn at least one new thing a day (that’s the absolute minimum, actually).
This database taught me something I had not seen in all my years as a FileMaker developer. And that made me want to share it.
The heart of this trick is simple: use a relationship set on the fly to add a new record to a related table. The twist: it’s not added through a portal, nor is it added through the other usual method: freeze the screen, go to the proper layout, make a new record, use variables to set key values, etc.
So How Is It Done?
Instead, this system set a key value to match the other side of the relationship, this making a link to the table with the ability to add a record through this relationship. Once the key match is set up, simply use the Set Field command to update the foreign key from the current table. Very easy, very straightforward.
I made a short video showing how to set up this relationship:
If anyone knows how that key relationship field is being set, please let me know. I like this trick.
FileMaker DevCon 2018
I am off tomorrow morning to FileMaker DevCon 2018! I will be tweeting at various times and will try to post something while I am there. Message me if you will be there and we can try to meet up at one the meals or the events.
See you there!
August 5, 2018 @ 8:07 pm
Cool trick. It’s simple to understand with the analogy of a portal. When you set a field in the last (empty) portal row, it automatically populates the foreign key with the parent field value.
In your file, the “foreign key”, key_new, is a temporary key. Since it starts empty there is no related record, just like the last row of a portal. When you set a related field, you’re essentially setting a field in the last portal row and creating a new record. When you clear key_new, it breaks the relationship, and in essence places you back on the new empty last row of a portal, ready to create another record.
The greatest benefit to this method is that, as you said, you don’t switch layouts. This preserves layout state (open popover, current tab, portal scroll position etc…), prevents layout nav triggers from firing, and MOST IMPORTANTLY, does not automatically commit the record. You can create 10 related records in a row this way and commit them all at once. If something goes wrong while creating one, you can do Revert Record and it will roll back all the changes. Todd Geist has a good video about transactions somewhere that describes this concept.
This type of record creation is also central in the Selector-Connector graph design, although the actual mechanics are slightly different. In SC, the parent table has the temporary key. You set that to “” to break the relationship instead of the child foreign key. I’d argue that the SC approach is more refined because it allows you set the parent key with a child’s foreign key and pull up that record to edit later if needed.
I hope more developers start using this approach for creating, and more importantly, editing records.
J Willing
August 6, 2018 @ 2:04 pm
Thanks, Joshua! I like the fact that it does not commit the records. That will be very helpful with invoicing multiple line items.
Don
August 5, 2018 @ 8:36 pm
Here’s an augmented example that uses the same idea but allows you to create and edit records through the relationship: https://1drv.ms/u/s!AjlO8vJLqR_igQPstydjp8tC75P0
August 6, 2018 @ 2:06 pm
Again, thanks!
August 6, 2018 @ 9:44 am
NightWing’s (Ray Cologon) Duplicate Hierarchy example has a similar thing – using SetFieldByName and an Evaluate function. There is no explicit NewRecord called, though I’m still scratching my head.
I run it with the debugger on and see the records created, but it’s still magic.
August 6, 2018 @ 2:05 pm
Thanks,Tom! Check out Joshua and John’s answers for the secret to the magic..
August 6, 2018 @ 10:46 am
Don,
this technique goes back to FileMaker 3.0 an 4.0.
It’s a nuance how records are created through relationships. A single Set Field step can create a new record in a related file. The only prerequisite is the check box allowing record creation in the Edit Relationship dialog box.
The good thing is that new related records will only be created if there are NO current related records. This technique can generate whole sets of records using a single Set Field script step inside a loop.
Not new, just “old” relationships technique.
But I agree, not many FM users know about this little fellow.
I use it since FM 3.0/4.0.
Respectfully.
JW
August 6, 2018 @ 2:01 pm
Thank you, John! I have been using FM since 1989 and had never heard of this trick. I will be using is again!
Don
August 6, 2018 @ 2:39 pm
This is generally the technique referred to as “magic key.”
August 7, 2018 @ 5:56 am
Indeed.