Relationships—you can live without them, but you'll be limited. FileMaker Pro 2 was a "flat-file" application, but version 3 and up are "relational." Since I began with 3, I learned to use them right away, but they can be a little confusing at first.
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. That way you know for certain they are going to be the same. Names are less certain; even a single typo or spelling error and there will be no match.
ID Fields as Keys
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 (00001, 00002). You can add a letter in front as well (C00001). The zeros let them be sorted properly, and appear nicely in columns (in value lists). Otherwise numbers work OK (though there are some considerations if you want to use "multi-key" values, but that's later on).
Related Fields
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 name will appear on every one of their Job records, without taking up any disk space in the file, if their ID number is filled in.
If you edit the name in the original file (or here, if you allow modification of the field), it will change (I was going to say, "in both files," but it really only exists in the Contacts file; so it changes there, but is visible in both files. The value only exists in one place.).
Reciprocal Relationships
In order for files to pass or reference 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.
New Related Record using a Portal
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.
Cascading Delete :-0
By the way, I'd stay away from the "allow deletion of related records" button until you completely understand it. It can allow the deletion of a whole lot of records from several related files very quickly, a "cascading delete."
Basically it's saying, if I delete the main record, such as a Person, then delete all the records tied to that person in all the other related files. In this case, with only one-to-many relationships, that's probably what you'd want. Only files belonging directly to that person would be deleted; all of those records would be "orphans" without their person anyway. Just be careful.
(If you have many-to-many relationships anywhere down the line, you could be deleting someone else's records too. But we haven't gotten that far yet.)
Related Fields in the Portal 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.
You don't really need the portal to see a related field, just the field from the other file on the layout. The portal is a convenient way to see fields from several records in the other file, such as several jobs for one contact (a one-to-many relationship). And it is needed to get the blank line to type into, for creating a new related record.
You can type into a related field on a layout, and that will change its value in its "real" file; but you can't automatically create a new related record that way.
New Related Record in its file
You can also just create a new record while in the related Jobs file and choose the Contact ID from a pop-up list of the ID's in the Contact file. This will "match" up the relationship.
You can create a Value List for the ID, but showing both fields (you need to have the names appear as well, or the numbers won't make much sense).
Go to Layout Mode. Select the ID field and choose Field Format (Com-Opt-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 Contact ID field.
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.
Self-Relationships
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.
Navigation between files using relationships
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 there are more then additional steps are needed, 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.
Relationships and Calculations
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.
Related Calculations Vs. Summary fields
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 contact's 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.
Related Calculations and Speed
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, as each one must be recalculated. It depends on the complexity of the calculation as to how slow.
If the calculation is complex, and references other calculation fields, it is always better to write the calculation from scratch in the field rather than referencing other "unstored" calculation fields.
Unindexed Again :-(
As I mentioned once before, if you change any field on which a calculation is based into one based on a relationship, FileMaker will un-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 response seems sluggish it's a good idea to check the Field Definitions to see if you've inadvertently changed up one of your regular calculated fields to "Unstored." You have to follow the trail back to the first unstored field that should be stored." 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 list view "developer" layout for experimentation. I put fields based on the relationship(s) across it 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.
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 properly. 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. This is especially true of setting global fields, then using them in a relationship.
Send comments, questions, toys to Fenton Jones