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.)
As you probably know, check box sets allow users to insert one or more values into a text field, and assuming multiple values are inserted, each will be separated by a hard return. At right you can see how the data is stored behind the scenes. While check box sets have their appeal, storing more than one value in a field violates First Normal Form (each “row-and-column intersection” should contain exactly one value), and if we want to report on this data, we will first need to clone it into a separate “tasks” table.
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.
Nice write up. I’ve been using this technique for quite some time.
I haven’t found it necessary to commit in between creating records. In fact, that’s the reason I use the technique. When creating records this way they are all “open” and issuing a
Commit RecordsorRevert Recordscomment all records will be committed or reverted simultaneously.The other thing I should mention is that unlike a regular field, setting or clearing this global field immediately updates the relationship. In this way, you can both create and check for existing related records with one caveat: because records created using this technique aren’t committed until you issue a
Commit Recordscommand, once you change the global fields value you can no longer access the uncommitted record that was created, even if you set the global to that record’s key.One other use I have for this global field is checking for existing records. Sometimes you want to get the primary key of a record based on its attributes. I will create a multi-predicate relationship from this one global field to each of fields in the related table. I then set the global field’s value to a return-separated list of the attributes. For example if I wanted to see if a particular task record already exists before creating it I would perform the following script steps:
Set Field [volunteers::g_magic_key; List ( volunteers::id; $category; $task )]
If [ IsEmpty (tasks_locate::id )]
Set Field [volunteers::g_magic_key; ""]
Set Field [tasks_create::id_volunteer; volunteers::id]
Set Field [tasks_create::category; $category]
Set Field [tasks_create::task; $task]
where the tasks_locate is a table occurrence related to volunteers based on the the following predicates:
volunteers::g_magic_key = tasks_locate::id_volunteervolunteers::g_magic_key = tasks_locate::category
volunteers::g_magic_key = tasks_locate::task
Use of this technique assumes that there is no overlap between the category and tasks values.
Thanks for taking the time to make such a thoughtful and thought-provoking comment, Corn. That’s a great existing record test.
I will try to find an example where I had to use a Commit Record step, because I agree it should not be necessary. But I know there have been times when, nevertheless, it was.
I believe a Commit Record step is only necessary in a data separation model solution. I encountered this a while ago working on someone else’s solution and went to implement this and it didn’t work. Through trial and error found that a Commit Record step was required for this to work and I presume it was because this was being done in the interface file – haven’t investigated further. As Corn notes this reduces the effectiveness of this technique in that you can’t revert records once the record is committed.
Hi Andrew,
Thanks for taking the time to comment, and for confirming the need to sometimes use the Commit Record step. I’ve been using Separation for about five years, and it’s entirely likely that when I encountered the problem it was in a separated solution. However, I can also state with certainty (because I recently tested) that normally you can use this technique in separated solutions without needing to commit.
I don’t know if you recall some of the problems in the pre-10 days with separated solutions, where FileMaker would confuse internal IDs between various files. E.g., if you started with a single file, and then later separated it, then FM wouldn’t have a problem, but if you employed a UI file and a data file that did not share a common source, then there were obscure problems (which I can’t recall at the moment I’m sorry to say).
But I’m thinking the latter scenario may have had something to do with the need to commit.
Regards,
Kevin
Hi Kevin,
I just made a quick test solution with an interface file and a data file and I wasn’t able to reproduce this – in all my tests I didn’t need a Commit Record script step for the record to be created.
I clearly recall this not being the case on another solution I worked on a few months ago but now I’m unable to reproduce this at present. I’ll keep testing and let you know if I can reproduce this.
following up on this I wanted to update based on something that I noticed recently.
Todd Geist once told me that the method I’ve outlined above (setting g_magic_key to null, and having the auto-entered value in the tasks_create::id field flow back into g_magic_key) wasn’t “supported” by FMI. As I had been using it for years I filed that along with all of the other “undocumented” behaviors in the product that appeared consistent between several versions.
Well, it turns out that Todd was right and it’s not completely reliable. Specifically, the step where I set g_magic_key to a null value doesn’t work 100% of the time; on some occasions (and I have yet to determine what conditions cause it to fail) no record is created. To account for this, and since I use UUIDs for my key values anyway, what I now do is set g_magic_key to a UUID value (either the built-in function in FMP 12 or a custom function in earlier versions of the product. This does work reliably in FMP 8 and on. (For the historians out there, this actually fails in FMP 5.x/6 on Windows where the two keys collide and result in a corrupted key value).
Thanks, Kevin, this is great! Some of us are 99% there – I currently use a system where I have a parent record and a portal. Instead of granting access to the portal the user clicks on the portal row she/he wants and the information is displayed in a section I call “Details”.
To make this happen I have a field in the parent table called Parent::details_g which is a global text field. The portal row is actually a button so when the user clicks on a portal row a script takes the primary key (Child::ID) from the portal record and populates Parent::details_g which, as I’m sure you know, is related to a separate child table occurrence where Parent::details_g = Child::ID (I call the T.O. “Child_details”).
However, I never turned on the “Allow creation of records…”. And I certainly didn’t know that doing so on my Parent -< Child_details would populate Parent::details_g. So, to create or delete a record, I would capture the Child::ID, open a new window off screen, navigate to that Child layout in table view, add a new record/find and delete the record, close the window, return to the original layout.
Magic Key now allows me to do this without using off screen windows and reduced my script length. Moreover, by using GetFieldName () function and Set Field By Name script step the script can be generic enough to work with any a portal.
The key, of course, is the Magic Key. I had absolutely no idea that global field would "magically" populate. Thanks for explaining this wonderful technique!
Hey Kevin
again, great article. THe only question i have would be would it be a HUGE issue to use this technique instead of going to another layout to create a “Valid” relationship using a script ; eg. [go to layout]:: child_record[ child_fk] ; copy parent:: ID to child_fk and going back to [original layout]
thanks,
-ian
Hi Ian,
I’m not sure I understand your question… but there’s no reason to use copy and paste, when you can use Set Variable and Set Field instead.
Would you mind elaborating?
Regards,
Kevin
What i mean to say is would it make a difference to use this technique not only for reporting like you did but to actually make the parent- > child relationship valid?
again, i hate to check the “ALLOW CREATE WITH THIS RELATIONSHIP” method as it makes empty rows within my portals. I have used this technique to establish a valid relationship on some test databases and was wondering if this could have “DIRE” consequences in the long run or are just a “BAD PRACTICE”.
The other method i would employ would be to copy the parent_ID in a $Variable and then go to the child layout and copy the $Variable into its new recordset and then go back to the original layout, which isnt hard either, just saying.
Does that make any sense?
-ian
Hi Ian,
I think that normal “allow creation” (AC) relationships have their place; it just depends on the requirements of the project. Nothing dire or bad about doing this (except the cosmetic blemish of the empty portal row).
E.g., I have a user who wants to create invoices as quickly as possible while touching the mouse as little as possible. With an AC relationship from Invoices to Line Items, she can tab through the portal creating new entries to her heart’s content (yes, I know this could be done with the help of script triggers, but with an AC relationship they aren’t necessary).
The point of Magic Key is that you don’t need a portal, and can create multiple children for a given parent record… whereas with a standard AC relationship (primary key in parent to foreign key in child), you can only create one child record via Set Field, unless you’re walking an AC portal while you do it.
As for your “other method”, I think you are using the word “copy” when you mean “Set Variable” and “Set Field”, and I agree it’s a great technique which I use all the time… and as you say, this way you can avoid an AC relationship and your portal won’t have the empty row at the bottom.
Hope this helps,
Kevin
thanks. Clears some stuff up for me.
i am fresh (new comer) i have just purchased FM11 pro
i am trying to a list of product from a certain vendor only then select any one or more from a list to sell is this possible if, i may twist this demo?
Hello Ilyas,
I don’t think that Magic Key has much to do with what you’re trying to accomplish. Perhaps my Popup Pickers demo would be more appropriate —
http://filemakerhacks.com/?p=1731
Hope this helps,
Kevin
Hi,
Just wondering if you can use this technique with ESS tables (e.g. creating a record in an ESS table from a FileMaker table)?
I confess I haven’t tried… perhaps you would be willing to test and post back?
I just did a quick test and I’m getting an error trying to set a field via the relationship: 510 Related value is empty or unavailable.
I’m not sure if this error is related to the particular SQL table’s definition or a more general error when working with ESS tables. Anyone else tried to use this with ESS?
I’ve compared this technique versus standard child creation (going to layout and using variables) creating 1000 records and I found out:
– Magic Key technique takes three times longer.
– Magic Key technique makes the script to show a question mark while executing.
Though for me the Magic key technique is very useful for invoicing purposes (eg. invoicing one or more delivery notes) I’d rather use the standard one.
Hi Kevin,
Just a follow up to my earlier comments. I just encountered a strange situation where I couldn’t get this technique to work. I was working on a multi file solution: File A, File B and File C.
In File A I have a layout based on File B table occurrence. On the graph in File A I created a magic key relationship from the File B TO to a File C TO. I was able to create the records manually on the layout, but when I tried to script the creation of records in a looping script it wouldn’t work. I was getting an error 509 on the first record – adding commit steps didn’t make a difference.
Hi Kevin,
Very late on this but someone lately pointed me to your blog, after implementing the Transaction Module where it is quoted.
First, when I first found out about this behavior and prior to implement it, I got in touch with several engineers at FileMaker who all confirmed this was totally supported, even if undocumented, as a logical consequence of the bi-directional way of relationships since FileMaker 7. I suspect this has not changed since then.
Second, with this technique, you can create as many child records but also create as many child records in this child records, and so on. I call it cascading creation.
Finally, if you set this global key as a repeating field, by keeping the first row empty, you can use it in a transactional way, il you use this key for some batch processing, keeping the history of all previous created records in the other rows. This can be very handy if your script have a lot of processing steps, moving one key to the first row enabling a relation to the former created records.
You may find a demo here ( althoug it’s french based and still old… ) of this second version involving a repeating field as the source key :
http://www.fmsource.com/index.php/topic/30986-clef-magique-version-2/
Cheers
Hi Kevin,
I’ve encountered a problem with this technique and the separation model in 16 (I haven’t tested previous versions yet) where I’m getting error 504 when committing the new record. I’m only creating a single record, setting a number of fields and then committing with no additional records being created.
I confirm the global field is empty, and set a field across the magic key relationship, creating the child record and populating the global field in the parent with the child record’s primary key. So far, so good.
I set another field across the magic key relationship while the global field is still populated with the new child record’s primary key and the new child record remains uncommitted. Instead of setting a field in the same record that has just been created, a new record is created in the child table with the same primary key as the first child record and the field I was attempting to set in the first child record is set in the second record.
The 504 error is caused when committing what I thought was just one record and is in fact two records with the same primary key, causing the unique validation to fail.
Committing the record immediately after setting the field that creates the record allows fields to be set in the new record without creating a second record. This reduces the benefits of the technique where the new record can no longer be reverted.
I’ve changed the technique to creating records from a single record Globals table in the UI file rather than a table in the Data file and the technique works fine. It seems that the technique doesn’t like creating records from a table that isn’t in the current file. I haven’t tried with a normal data table in the UI file to see if any issues occur but if it works from global fields in a Globals table, I expect it will work fine.
Hi Damon,
I wonder if this is connected to the need to sometimes include a “commit” step after the first Set Field, as per the final screen shot in the article, as well as previous comments above?
Regards,
Kevin
Hi Kevin,
That’s correct. Committing the record immediately after the first set field allows the technique to work. That’s not desirable though, especially when creating multiple records as a transaction.
I read through all the comments before posting but nobody reported a 504 error where unique validation fails. I discovered the cause by having a second window open on a layout based on the target table. I saw the record count increase by two and when I got the error on the Commit Records step I stopped the script and investigated the two new records.
For the technique to work transactionally in a separation model or multi-file system where you are creating records in an external file from where the script is running, you must use a source table/TO in the current file.
Regards,
Damon.
I’ve found that if you want to create several records two TO’s away using this technique (the first TO also having it’s records created simultainously in the same manner), you need to do a commit after each record is created in the table two TO’s away. Otherwise it just overwrites the first one with the following ones. Hope that made sense.