A handy way from Kevin Frank to normalize data stored in check box sets.
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.
More…Magic Key, part 1 — Filemakerhacks.