Multi-Key Value List Window
Example file: Storess

One of the under-documented features of FileMaker Pro is its ability to use separate entries in a field as if they were actually separate records. Combined with relationships, this unlocks the power of many-to-many linking, without even having to use a join file.

In fact, a few otherwise knowledgeable writers have criticized FileMaker for not having this ability, when it fact it does; it's just hidden.

The entries have to be separated by paragraph returns, which is only allowed in text type fields (incl. global text fields), and there is the usual text limit of 64,000 characters.

It is almost as if the multi-line field becomes its own little file. If a relationship is created, relating it to a field in another file, all records matching any of the lines within it will be matched. If it is related to a field within its own file, a "self-relationship," it will also match any record when the related field matches any of the lines.

I use "multi-line" or "multi-key" for it, rather than "multi-valued," as the latter means a field that has many values (too many) but they are on the same line, like "John Smith, 121 E. St., Maplethorpe, Kentucky."

The limitation of this multi-line feature is that it is not its own little file. So it cannot have auto-entered serial number IDs, and other necessities for serious data integrity. It's just a field, and it's up to you to populate it. But it has its uses.

The most common use for a multi-key field is to put the different IDs from several records into a global field, then use that to match records. An example of this would be the famous Copy All Records operation. I've written about it before, so I won't say much now. See my "Import" example file for a detailed look.
But quickly, you use the Script step, Copy All Records, on a layout having only an ID field, then Paste it into a global ID field.
This gives you a quick way to match all of those records in another file.

This time though, I'm not going to do that. I'm going to put the IDs from one file into a regular field in another file.
This is not unusual with related files; that's how you usually link files together, having a field with the same ID in each file. But this is different.

In this case I'm not going to have just one ID, to match one ID in the other file; I'm going to add several ID values, one at a time, with paragraph returns between them.

There need be no direct relationship between the file from where the IDs originated and the file with the multi-key. The whole purpose of the structure is to filter the records in one of the files in an indirect way, when it isn't possible to do so in a direct way.
(Don't worry, it's easier to see than explain. But I think it also helps to know the concepts behind what it does.)

Many-to-Many
The normal relationship of single ID item to a single ID would be many-to-one. Because the multi-key ID is a regular field, which can have different values in each record, not a global, which can only have one value per file, we would then have a many-to-many relationship.

In other words, there can be many different IDs from another file in the multi-key field; it can be different in each record; there can be the same ID in several different records, along with any combination of other IDs. Yikes. Useful, though.

What I am using it for is to keep a list of choices that would normally be unavailable to the file the multi-key is in. It is doing the work of an extra join file, in a file that is already a join file.
It is being used simply to create a filtered list for speeding data entry; nothing serious, nothing that needs its own file.

Example Files
I have 3 files. The first is Peoples,* the second is Storess, and the third, the join file, is shopping Events.
An event would always have one person and one store. Simple.

The problem is in data entry. I would create the new event from the People file, or duplicate a previous event in the Events file. In both cases, the event knows who the person is. But the store must be chosen.

It's easy enough to have a list of all the stores. But what if a person is shopping often at the same store, or a few stores, and there are a lot of stores.
And what if, rather than stores, it's something that is much harder to remember? My solution is to pop up a list of those stores that a person has already shopped at, with the option to see them all, or add a new one if needed.

Storess file
It is not really a pop-up list, it's the whole Storess file, but in a long narrow window along the left side of the screen. Because it's the whole file, it's pretty easy to implement sorting, finding all and adding new records.
Also, because it is not a value list per se, but a field(s) with a script attached, it can enter an ID code, but doesn't have to show it in the list.

But how does the Storess file know which stores the person has shopped at, since there is no connection between the People file and the Storess file?

There are two or three ways to do this. The first would be to use the Copy All Records routine in the Events file. Each entry in that file has both a Person's ID and a Store's ID in it.
If you used a self-relationship on the Person's ID, then copied all the Store ID's and pasted them into a global field, you would have a multi-key field of all their stores. This field could be related to the Store ID in the Storess file. A Go To Related Record script step would find those stores.
This would be foolproof; you can see this method in my Import example file.

Multi-Keyed People
But it would also be a little slow; the script would have to copy from all the person's records every time. It's really overkill for what we want, which is just a filtered data-entry list.

The method that I use in the example file is somewhat similar in principle. But rather than having to go to all the Person's records in the Events file and paste into a temporary global field, I store the Person's ID in a regular field in the Storess file.

Every time a store is chosen for the Events file, that Person's ID is brought over and added to the multi-key field in the Store's record. It could be the same person, it could be a different person.

I name the multi-key field, "People IDs". Even if the rest of the name were the same, the ending "s" makes it visually different from the Person ID field, which is in the other files. I do, however, use the "People ID g" global field interchangeably, as it's just for temporary use and can hold either one or many lines.

The Person ID value is passed to the Storess file in a global field via a Constant::Constant relationship. This needs no other relationship, since a global is the same for all records, and the Constant value is the same for all records.

Triggering the List of Stores
Every event already has the Person's ID. I don't allow creation of a Events record without the Person's ID. I don't actually stop it, but there are buttons in both files to do this and a visual warning if you don't use them.

There is a relationship between the Person's ID and the multi-key field with People's IDs in Storess.
Go To Related Record [Show, "Person ID"] opens the Storess window with the right records.
This step is attached directly to the Store field in Events, so that you will automatically trigger the script if you try to enter the field. (Unfortunately, it can't be part of the tab order.)

There is a "New" button at the top of the little list, so it's quite easy to add a new store, hopefully after checking that it doesn't already exist. You don't want people creating new stores, with new IDs, just by misspelling a store's name slightly.
(I have a more complex version of these files that can quickly find a store by its first letter. Maybe next month.)

Adding to a Multi-Key field
The basic technique for adding to a multi-key field is simple; set the field to itself, the new value and a paragraph return.
That way the value doesn't overwrite previous entries.
It's best to add the paragraph return after the value, rather than before. It insures that the value matches exactly, no matter how many records you have.

You can check to see that the ID is not already there before adding.
Use the PatternCount text function. This is the script step:
Set Field ["People IDs",
Case (not PatternCount(PeopleIDs, (PeopleIDg & "¶")),
PeopleIDs & (PeopleIDg & "¶"), <-- adds the new value to the end
People IDs)] <-- doesn't add it, it's already there

= This saves disk space if there are going to be a lot of duplicates, but functionally it doesn't make any difference in a key field; FileMaker ignores duplicates in any indexed value list, and still matches the same records.

You can also use PatternCount, then some other text functions (Middle, Position) to strip an ID out of the field, too. I'll have to leave that as a reader exercise for now.

Key fields
Click the "?" in either the Events file or the Storess file to see the key fields. Normally these should not be accessible to users.

Backwards, from Store to Events
The relationship from a particular store back to the Events file is from the multi-key People IDs field back to the Person ID field, which is in each event. So a Go To Related Record step will show every event that has any of those people in it, in other words, every time that anyone has shopped at that store.
Click the "Go" button in Storess, in the larger list view layout, for "Home Depot."

You could easily make a similar relationship back to the Peoples file, if you just wanted to go straight to the people and ignore all the events. This would show just one entry for a person, rather than possibly several in the Events file.
A portal in Storess based on this relationship would show the same people.

Events Portal in People
There is also a portal in the Peoples file showing the Events for that person. It is read-only, for the simple reason that it was too difficult, in the example, to implement the filtered choice pop-up window for stores during record creation.
But it can be done, using variations of the scripts already in place. The tricky operation is creating a new record and navigating to it in the portal rows, whose records are really in the Events file.
I took it out of the example, to make it easier on beginners (for a change).

Portal Disclaimer
My overall aim with the example file was to demonstrate the multi-key field and its "conditional value list" abilities. I sort of jumped ahead of explicitly explaining the simple many-to-many relationship between Peoples and Storess, which happens through the join file, Events. Hopefully you can see it behind the scenes.

In the example files, the "line item" records are being created directly in the join file, so there was little need to have record creation in portals. And, as I said, the filtered list complicated creating records in the portal.

I tend to write about what I find interesting and useful, which means I may get ahead of myself from time to time. It is difficult to cover a program like FileMaker in a monthly column without wandering a bit.

You can always send me an email and tell me where I lost you.

Portability of the Multi-Key field
Since the Multi-key field is not really tied to the file it is in, it is somewhat flexible. You could, for instance, get the same list using a multi-key field in Events. In that case, the keys would be the Store IDs. The relationship would be from that field to the Storess file. This is originally how I set it up.

There was a little more maintenance. Every time a new "event" was created for a person, the multi-key field from that person's previous event record had to be inserted into the new record.

For that matter, you could put a multi-key field in Peoples, so that you had one in Storess, with the People IDs, and another in Peoples, with the Store IDs.
This would allow you to get a filtered pop-up list of people in events file, after choosing a store. This would require some structural changes, however, as the person has to be chosen first the way it is now.
Since the multi-key is populated by a script, as long as you maintain scripted control of creating new records and making choices, you could put one in any of the files.

Letters
Another place where you might use this multi-key "storage" technique is in a letters, or correspondence file related to a contacts file. Normally, the relationship between a contact and a letter would be one-to-many. There is a case where this breaks down though, and that is "mass-mailing."

What if you wanted to send the same letter to several people--not everyone--but a selected group. In this case you might want to keep track of who got the letter. But it wouldn't really work to create a new record in the letter file for each recipient.

One solution is to store the IDs of those contacts in a multi-key field in the letter file. You would only do this for the "mass" mailings; with a normal letter the Contact ID would just go in a regular Contact ID field in Letters.

A relationship from this Contact IDs multi-key back to the contacts file could immediately isolate all those who got that particular mass mailing. You would only need one record in Letters for the letter itself.

The way that you would get the contact IDs would be to use the before mentioned Copy All Records. You'd Copy all those IDs from the found set in Contacts, then Paste them into the Mass-mailing Contact IDs field for that letter in Letters.
Remember that the clipboard (or a global field) will hold the IDs in between files, but that the Contact IDs field must be on the layout and enterable when you trigger the Paste step.

Passwords
There is a rudimentary password system in the example files. The master password is "Administer". Click the "?" button in Peoples for a detailed explanation. There just isn't room to put it here.
The other files will open the Peoples file if double-clicked, but with minimal access privileges. Peoples will open the others with full access.

This may confuse those who try the example files without reading this article :-)

Version 5 and Related Value Lists
FileMaker Pro v.5, which I haven't used but have read about, has the ability to base a value list on a relationship. In earlier versions you can base a value list on a field in another file, but not filter it with a relationship to a subset of records in that file.

I don't know all the uses of this, but one thing it would do is allow you to use the built-in value list mechanism to pop up a filtered list.

In other words, rather than have the filtered stores show in a little window, they could pop up (or down actually) directly from the field itself. This would make a cleaner interface; and it would also work within a portal.

By setting a filtering value into a global field first, then going to the field to drop down the list, you could dynamically filter a list in different ways, from any of your files, much like you can filter portals now. Neato.

Namess?
*"Peoples, Storess"? Because of FileMaker's useful, but somewhat dangerous practice of linking files by their names, it is not a good idea to have two FileMaker files with the same name on your hard drive, though it seems to be OK most of the time. Rather than add to the confusion, I'll try to name my examples with odd names from now on.

Fenton Jones
manavesh@mymac.com