Print PDF

There are a few ways to create new related records within FileMaker Pro. Each of them has its own pros and cons.  The technique that I’m discussing today is an undocumented one (its main con).  I’ve been using it for a few versions of FileMaker Pro now, it’s extremely fast, and has never before let me down.  Discussing techniques that don’t have a name can prove difficult though, so I’ve been using the goofy name of “Complicated Relationship With The GF”.  Let’s look into how it got its name, and how to use this novel technique.

If you’re already familiar with our naming conventions, you’ll know that we’re very strict about how we name our fields here.  Any kind of field that is to be used solely for developer purposes should start with the letter “z”.  This is mainly so that when the list of fields is being displayed (and sorted alphabetically), these fields will be displayed near the bottom of the list.  Furthermore, any match fields (AKA keys –> primary/foreign/alternate) will have a prefix of “zk”.  If it’s a primary key, then its suffix will be “p”, if it’s a foreign key, it will have a suffix of “f”… and so on.

It’s also mentioned in our naming conventions that global fields have their own suffix.  You guessed it, they’ll have a “g” within their suffix.  Having fields named like this is extremely helpful for many reasons.  Enough of the technical details about that though, you can always read our naming conventions to find out more if you’re curious.  Let’s get into how to utilize this technique now.  Once you see how the pieces come together, you’ll start to understand how this technique got its name.

Relationship Graph

To create new related records using this technique, you’re going to need to set up a relationship specifically for this purpose.  Although the name indicates that it’s a complicated relationship, it’s not setting up the relationship itself that’s complicated, but rather understanding how it works that is.  Here’s how to set up this kind of relationship.

For the purpose of this explanation, let’s assume that we’re dealing with a simple CRM system.  We’re currently looking at a company record, and we want to create a new contact who works for this company.  This means we’re going to need to create a table occurrence for the new “Contact”, and relate it to the company appropriately.

Edit Relationship

This relationship has been defined such that a global field (zk__newRecordID__gf) in the company table must be equal to the primary key of the Contact table (zk__PPLID__p).  You should also note, that there is a checkmark in the “Allow creation of records” box on the Contact side of the relationship.

Once this relationship has been defined, you’ll find it very simple to create new records through it. All you’ll have to do is:

1. Set the global field’s value to NULL (A01__CMP__CMP__Company::zk__newRecordID__gf = “”)
2. Set the foreign key in the far table (A02__CMP__PPL__NewContact::zk__companyID__f) so that it will match the current company’s primary key
3. commit the record

Voila!

You now have a new contact associated to this company.

Since we used a global field as one of our keys, it only follows that it would have a suffix of GF… hence the comical name for this technique.  If you’re playing around with this technique, you’ll notice that immediately after performing these steps, that the global field on the near side will now be set to the ID of the newly created record.  Very handy indeed.  This technique will also circumvent the need to have your scripts jump around from layout to layout, and will also make sure you don’t have to have one of those portals with the empty last row on them (they seem to be a source of confusion for many).

One further note:
This technique takes advantage of undocumented functionality of FileMaker Pro, keep in mind that YMMV if you use it.

Other Sites