FileMaker 101 – Part 6

An introduction to relationships

First of all I must apologize for leaving out a step in the script in last month’s article. Without it the script will just execute without letting you enter anything — not real useful. It should read:

Go to Layout [“SearchandReplace”]
Set Field [“AdamgSearchFor”, “”””]
Set Field [“gReplace”, “”””]
Pause/Resume Script [ ]
Set Field [“Field Name,”Substitute(Field Name, FieldToSearch g, FieldToReplace g)”]

The step would also be needed, in the same relative location, if you use the Replace step (by calculation, Substitute function) instead of the Set Field (by calculation, Substitute).

The Replace function also requires that the field you are replacing be on the present layout. If you want to run the operation on several fields in the found set of records, then put each of them on the layout. You can use the same two global fields (FieldToSearch g, FieldToReplace g) for all of them. If you only want to run it on one field at a time, make a separate button to run a Replace script (specifying that field) next to each field. To do all the fields at once just put duplicate Replace steps, each specifying a different field on the layout, one after the other in only one script. Then you only need one button (obviously).

As you can see, Set Field and Replace are similar. The first operates on one record at a time. It can be scripted to act on several records with the Loop step (later), but it still only operates on one record at a time. Replace does all the found records at once and is very much faster.

The above script could also include the Go To Layout [original layout] as the last step. That way you can call the same script from different layouts and return; just like a word processor.

Disclaimer:
I knew mistakes, especially those of omission, could happen as soon as I began including scripts. As you may know they are a bit of a pain in the butt to copy out of FileMaker (requiring separate software to do so). Next time, though, I will take the trouble.

On the positive side, it shows one of the great strengths of the program, in that you are able to build the scripts from menus rather than having to type them in, as I’ve heard is common in other databases. You can imagine how much fun that would be, and see an example of the likely results in my mistake.

On to this month’s topic.

Relationships. You can live without them, but you’ll be limited. FileMaker 2 was a “flat-file” version, but v.3 and 4 are “relational.” Since I began with v.3, I learned to use them right away, though I must say I sometimes find them a little confusing.

Relationships allow one file to read from and write to another, either copying the data, or just referencing and displaying it. The files are linked together by two fields, one in each file, which contain matching data. The most common fields to use are ID numbers. This way you know for certain they are going to be the same; names are less certain because of mistyping and spelling errors.

The basic relational concept is that each file should only have to contain one basic type of information, then be linked to other files containing other types. For instance, people would be in a “Contacts” file. Jobs that you are doing for them would be in a “Jobs” file. In the Contacts file you would have a field “Contact ID,” an auto-entered serial number (text in my files*). The Job file would also have a Contact ID field defined, as a simple text field (this is in addition to its own job serial ID field, which would be an auto-entered serial number). Multiple jobs for the same person would have the same contents in the Contact ID field.

(* I use auto-entered text serial numbers, with added zeros in front (0001, 0002). You can add a letter in front as well (C0001). The zeros let them be sorted properly. The reason for text rather than numbers has to do with the 255 character limit of number fields. There are some occasions when you have many IDs in one “multi-key” field, separated by returns (for “many-to-many” relationships, later); a number field can’t handle returns, either.)

The names of the clients could then be displayed in each record of the Jobs file without having to retype them. To do this (in the Jobs file), enter Layout Mode and simply drag the Field Tool off the Status Bar and place a field on the layout. When prompted for the field name, you have to drag down to the bottom, to “Define Relationships.” A dialog box pops up, with a list of all previously defined ones (none in this case). Hit the New button. The standard file open dialog box will appear, allowing you to choose any file on your hard drive to relate to (including the present file). In this case you’d navigate to and choose the Contacts file.

Now you’ll get another dialog box with two lists of field names. One is from the file you’re in, the other is from the file you’ve related to (the file names are shown at the top). In this case you’d highlight Contact ID in each list, and say OK, then Done in the next dialog. Your original Define Fields dialog box will reappear, but now it will show the name of the new relationship on top, with the fields from the Contacts file.
Each one will have the :: (double colon) in front to show that it’s from a related file. Scroll down and choose LastName or Name (name of the contact; calculation, text, First&” “&Last). Now their names will appear on every one of their Job records, without taking up any disk space in the file, if their ID numbers are filled in.

In order for files to pass information both ways, each needs to have the relationship defined. So create a similar relationship (Contact ID::Contact ID) in the Jobs file, pointing back to the Contacts file. You can do this from the File Menu, Define Relationships… You’ll get the same dialog box as earlier.

Getting the right Contact ID number into the ID field is another operation. One fairly direct method is to have a field or fields from the other file appear on the main file’s (Contacts) layout in a Portal. Just use the Portal tool to draw a box. Choose the ID relationship to base it on, with one addition: check the “allow creation of related records” box at the bottom of the relationship editing dialog. You’ll have to drag down to “Define Relationships” to reopen the dialog again to see those boxes.
As you can see, this dialog is available from anywhere that you could be using a relationship, even in Value Lists (later); very handy.

By the way, I’d stay away from the “allow deletion of related records” button until you completely understand it (I don’t). It can allow the deletion of a whole lot of records from several related files very quickly, the dangerous “cascading delete.”

Put the Job Name field (from the Jobs file; based on the ID relationship) on the top row of the portal, within its borders, as well as the ID field (it can be hidden behind). Now all you have to do (in Browse mode) is start typing a new job name in that field on the blank bottom row of the portal. A new record will now be created in the Jobs file with the proper ID numbers.

In fact you don’t really need the portal, just the field from the other file on the layout. The portal is needed if you want to see fields from several records in the other file, such as several jobs for one contact (a one-to-many relationship).

Another way would be to create the new record while in the Jobs file and choose the ID from a pop-up list of the IDs in the Contact file. You’d need to have the names also appear, or the numbers wouldn’t make much sense.

You can create a Value list showing both fields. Go to Layout Mode. Select the ID field and choose Field Format (Command-Option-F). In the dialog box, click the radio button for Pop-up list or Menu. Drag the unknown value list box down to the bottom, to “Define Value Lists…”

Now you get another dialog. Type a name for the list, such as Contact ID, click Create, then click the button at the bottom, for Use Values from a Field. In the next dialog box, hit Specify File and find the Contacts file. Find the ID field. Then check the Also display values from:, and choose Last (name) from the field list. Save the darn thing. Back in Browse Mode, create a new record and click in the Contact ID field. The numbers will drop down followed by the last names, so you can see which to pick.

You can also use relationships within a single file, a “self-relationship.” This is a very powerful tool, allowing you to quickly link records. Even on a simple level it allows you to perform operations that normally would require a find. The concept is much like a relationship between two files, but in this case you choose the same file you’re already in. Not only that, but you often choose the same field in the two lists (one will have the double colon in front). You’re basically saying that “this” equals “this” rather than “this” equals “that”.

In the case above, if you created a self-relationship in the Jobs file, based on the Contact ID field (in the Jobs file on both lists), then you could quickly find all jobs for a contact with a single step, attached to a button.* The step is Go to Related Record, Specifying the Self Contact ID relationship, with Show only related records checked. One main advantage you’ll notice right away over a Find is that it’s much faster.

(* A transparent button over the name works well. Do it on a list view layout where you don’t need to edit that field. It will overlay each name and magically find their records when clicked.)

Of course, this navigation step also works between two related files. Establish the relationship between them from each file in order to quickly jump back and forth between related records in different files. Put a small button on the Portal row, next to the name field. It will appear on each row. Click it to go to the Job file. It will take you to all Job records for that person, initially showing you the one you clicked on.
You could also use a transparent button over the name, but then you couldn’t type in the portal; it’s best used on portals that don’t have the “create related records” checked.

This technique works well when you only have one portal on a layout. If you have more than one portal, then additional steps are necessary to tell FM which portal to go to. I’ll get to that later, as it requires a multi-step script and a global field.

You can return from the Job file by displaying the Contact name in the Jobs file (put the field on the layout via the relationship, as mentioned earlier). A transparent button over the name, with the step Go to Related Record, will bounce you back to the Contact file.

You can also use self-relationships in calculations of many types. A simple one is to find out how many records have any particular ID. First create a Calculation field, of type number. In the Options dialog, choose Count(field) from the Aggregate functions (in the drop-down list on the right).

Select the word “field,” then hold down on the box above the fields (on the upper left) and choose the Self Contact relationship (it will then show in the box instead of “Current file”). Scroll down to the Contact ID field and double-click it. The calculation should end up looking like Count(Contacts::Contact ID), assuming you named the relationship “Contacts.” The field will now display the number of jobs this person has, updated when you add one.

If there is a simple number field on a Job record, such as a cost amount, then a similar Calculation field, using the aggregate Sum function, with the cost field, rather than the Contact ID field, in the formula, will give you the total cost per contact.

Summary or summary calculation fields will also give the same answer, but with this major difference: they depend on the contacts being the only found records, or on sorts in Preview Mode, whereas the self-related field will always contain the correct value. It can be referenced and used in other calculations, even in other files.

One downside to this cross-file referencing, however, and of relation-based calculations in general, is that a calculation based on a relationship can’t be indexed. If you use several of these on a report or list, display will slow down considerably, as each one must be recalculated.

As I mentioned once before, if you change any field on which a calculation is based into one based on a relationship, FileMaker will de-index all fields which depend on it. Even if you change it back, they will stay unindexed. You have to manually go into the Storage choices and uncheck the “do not index” box on each one. If you, like me, mess around with relationships and calculated fields while trying to make something work, it’s a good idea to check the Field Definitions occasionally to see if you’ve changed up one of your regular calculated fields to “Unstored.” A dialog box will stop you if it’s not OK to change it.

While learning about relationships and calculations I’ve found it helpful to have one column layout for experimentation. I put fields based on the relationship(s) across it, change things around, and see what happens. It’s a good way to see what works and what doesn’t. It’s also a good way to cause the de-indexing problem above, hence not recommended in the working copy of your database. But you’ll never learn much unless you experiment. Don’t worry, it’s very difficult to crash FileMaker or cause unfixable problems. I should know.

Oh, I almost forgot. One last little kink. Sometimes in scripts if you create a related record or reset the key field, then immediately use the step “Go to related record,” it doesn’t seem to work. Nothing’s wrong. Just add the step “Exit Record” right after the creation step. It lets FileMaker process the new key, then everything works fine.


Fenton Jones

Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com

Leave a Reply