Today we’re going to look at a technique called Magic Key, which allows you to flexibly create and populate related records. I believe that the name originates with European developer Ugo Di Luca, and we’ll get to the details in a moment, but first let’s look at a scenario where it might come in handy. Have you ever built or inherited a system with data stored in text fields formatted as check box sets?
There are pros and cons to this approach, and one of the cons is that data stored in this manner can be a pain to report on. But I have good news: Magic Key can help alleviate that pain, as you will see if you download and explore today’s demo file, magic-key-check-box-reporting. (Of course it can be argued that one simply should not, under any circumstances, store data in text fields formatted as check box sets. This is not an argument I will be getting into today.)
Magic Key is certainly not the only way to accomplish this, but it is probably the most streamlined method. And at this point I had better mention that the technique is undocumented. I have relied on it since 2004 and it has never let me down, but the standard disclaimer applies: use at your own risk.
Okay, we’ve got return-delimited values sitting in various text fields in the Volunteers table. We want to parse those values into a Tasks table…
…and use that as the basis for a standard subsummary report.
Now we’re almost ready to examine the Magic Key technique in detail, but before we do, I think it’s a good idea to review how a standard “allow creation” relationship works. In a nutshell, you create an equi-join relationship between the primary key field in the parent table, and a corresponding foreign key field in a child table…
…and make sure to check the “allow creation” box on the child side of the relationship.
From the context of the Volunteers table, there are two ways you can use this relationship to create/update related child records.
Method A: If you only need to create or update a single record, you can use the Set Field command to insert some data into the child table, e.g., Set Field [tasks::category ; “Special Events”]. If the related record does not exist, it will be created and id_volunteer will automatically be populated with the appropriate value to make the relationship valid:
If you do a second Set Field across the relationship, e.g., Set Field [tasks::task ; “Sales for Survivors”], FileMaker sees the existing related record, and updates it, rather than creating a new one.
The salient feature of method A is that it only works with a single related record, and if you already have multiple related records, it can only interact with the first one. If you want to create/update multiple related records using a standard “allow creation” relationship, you will want to use…
Method B: Place a Tasks portal on a Volunteers layout, like so:
Now you (or your script) can create as many related records as you want… just go to the first empty portal row and start typing (or do a Set Field). In either case, the act of pushing data into one of the related fields will automatically create a new related record, and as per method A, the foreign key will be automatically populated.
So, having reviewed standard “allow creation” behavior for relationships, we are now ready to dive in to Magic Key. However, I should warn you that this technique seemingly violates the laws of FileMaker physics, and the first time you see a Magic Key relationship on a Relationships Graph, your reaction is likely to be, “What was that developer smoking???”
Preparing to use Magic Key
1. Define a global text field, g_magic_key, in your parent table.
2. Place a new table occurrence, tasks_create, on your Relationships Graph.
3. Link volunteers and tasks_create, and check the “allow creation” box on the tasks_create side.
That’s right, your eyes are not deceiving you: g_magic_key is linked to the primary key in Tasks, and we’ve got “allow creation” turned on. And yes, Tasks::id does have an auto-enter serial number.
Magic Key Explanation
So what on earth happens when you issue a Set Field command across this freak of a relationship? Well, assuming the g_magic_key field is empty…
- a related record will be created in Tasks
- whatever field you choose to “set” will be populated accordingly
- the primary key (id) in Tasks will be populated via its auto-enter serial number
- the value in Tasks::id will “magically” flow backward into g_magic_key
At this point g_magic_key is no longer empty, and the relationship between volunteers and tasks_create is now valid. This means that any subsequent Set Field steps you perform will update the record you just created, and you can do as many of those as you wish.
When you’re done populating the current record, simply clear g_magic_key and begin the process anew until you’re done creating related records.
Magic Key in Action
Let’s return to our original challenge. How do we parse the following into a Tasks table so we can report on it?
Our routine uses three nested loops as follows:
- Inner loop: Parses all the values for a given field
- Middle loop: Traverses all desired fields for a given record
- Outer loop: Traverses all the records for the current found set
If you’ve read this far, you’ve almost certainly downloaded today’s demo file, and can see the scripts in action, so I’m just going to concentrate on step 1, the inner loop, which is where Magic Key is used… here is an annotated version:
Well, that’s about it for now. This technique has many other applications, and we’ll examine at least one more at some point in the near future. My next posting will be much shorter, and will a) show a simple improvement whereby the above script can be reduced to a single Set Field step with no loss of functionality, and b) implement a safety feature so that two or more users can run the report at the same time without stepping on each other’s toes.