Marking with lookups, duplicates & imports
Example file: Import-Transit

In last month’s article I mentioned John Osborne’s excellent Serialize by Category file (at http://www.best.com/~jmo) and the ability to mark first entry records automatically without having to run a script. It involves a simple technique, but with an unusual twist. Basically it relies on not being able to lookup a match based on a self-relationship.

The second part of his solution uses this fact and goes on to lookup a count of the self-relationship in order to serialize sub-records (people working for a company, etc.). I’ll have to get to that next month. It’s pretty tricky. Let’s just look at marking first entries for now, using my file Phone.all (different from last month’s Phone.fp3).

First you’ll need a self-relationship based on a unique identifying field. It could be a concatenated calculation field, type text, using the name fields (First & Last), or it could be any indentifying ID field, if you have one assigned. We don’t, so I’m using the Concat field, with the self-relationship, Concat= ::Concat.

Create a field First Lookup Not, so named because it looks up nothing for the first entry. Click the Options choice to see the Auto-enter dialog, then click the box for Lookup. In the next dialog choose the Self Concat relationship in the drop-down box. In the box for what to enter if there’s no match click “use,” but don’t enter anything.
For the field to lookup if there is a match I chose the simple Constant=1 field.

If you enter a new unique name, it will attempt to lookup a match. It won’t find one, so it will enter nothing in the First Lookup Not field. If there is a match it will lookup 1.

First Lookup Not will be empty for every first entry, and have 1 in it for any duplicate entries. For our purposes, it would be better to have the opposite, a 1 in a field and nothing in duplicate entries.
So make a field, First Lookup, type calculation=
If (IsEmpty, First Lookup Not, 1, “”)

Having the 1 will be useful. Since it is indexed, a simple relationship from the Constant=1 field to the First Lookup field will allow you to quickly Go To Related Records, showing only the first entries on list views. Portals using the relationship will also show only the first entries. It is by far the fastest most reliable way to control visibility.

There are a few cons however. Lookups are dynamic in a funny way; they change when the key (the field that the relationship is based on) is changed, but not when the related field changes (like regular related fields do).

If you delete that first entry, the next one won’t change to take its place, so there will no longer be a marked First Lookup for that name; therefore it will disappear from your filtered lists.

If you duplicate a first record the 1 will duplicate, so you’ll have two first entries for the same name.

If you cut one of the names out then paste it back in, or even edit it, the First Lookup mark will be deleted (only if there is a duplicate record), as the name is part of the key. It causes a Relookup on the relationship, which is now valid, since there’s more than 1 record with the same name.

The very worst, however is if you do a Relookup on all the records, using the Concat field (which you might be tempted to do in order to update some other field that also uses the relationship in a lookup–but don’t do it). Since there is now a valid relationship in all the records that have the same names, all of those First Lookup marks will be wiped out.

Even if you do, all is not lost. Going back to last month’s article, we can use the old scripted method of marking first entries to fix it. On the First Lookup Not field, use the self-relationship in a Replace operation, calculated result,
= If (Serial ID = Self Concat::Serial ID, “”, 1)
You can’t use it on the First Lookup field because it’s a calculation, but it updates. I’ve included this Replace First Lookup in the Script Menu.

Warning: Make sure the field First Lookup Not is on the layout in this case. It can be hidden, but it must be there for the Replace to work.

By the way, I know this whole business of marking the first entry in this way is unnecessary in a simple phone index-type file, where you only have one record for each name. It is very useful in many other files, even phone files if they have multiple entries for each company, such as in Serialize by Category. The Phone.all file had already been built for the second part of the article; I grafted this little operation into it so you could see an example in a simple file. Eventually we’ll get into a Job Service file where there are always multiple entries and marking is essential.

Part 2
Importing and Duplicates

There are times in this less than ideal world when you have to bring data in from outside, either from another FileMaker file or from a spreadsheet. One of the most tedious tasks on earth is manually going through the records afterwards, searching for duplicates, trying to figure out which one to keep, etc.

Here are two different techniques. The first is used in the instance when you definitely don’t want any information from duplicate records to be imported. It finds the duplicates in the new file before importing and deletes them–saving time–as imports are what I call “butt-slow” (blame my teenage daughter for that one).
It requires that both files have either a unique ID or a unique concatenated calculation field (such as First & Last). You should also have a Serial ID field, type text, auto-entered serial number, incremented by 1 (it’s still text).

I know that First & Last is a pretty flimsy unique field. I’m using it in this case because if you add the phone number or address to it then you won’t be able to update them with a lookup.

[OK, I couldn't stand it, so I added a warning message and a few steps to let you review and delete the duplicate names. I can't explain it now. But for some real fun, tile the 2 database file windows horizontally and watch what happens.]

If you’re importing from a spreadsheet or tab-separated text file, you need a separate FileMaker file to temporarily house the records for importing; Transit. It has the same fields as those in the main Phone.all database that you’d want to get data for, as well as a similar Concat field of First & Last, to use as the relationship key.

Copy All Records Trick
The first operation you’ll use is the “Copy All Records” trick, which I wrote about a couple of months ago; but I’ll give it again quickly. It allows FileMaker to create a Many-to-Many relationship between two files.

In this case you’ll first need a global field, Concat g, type text, to mirror the Concat field. Then create a blank layout. Put only the Concat field on it. Put the global Concat g field on another layout in the body part (doesn’t have to the only field on the layout).
Create a relationship between this Concat g field and the Concat field in the Transit file:
(Phone.all) Concat g= ::Concat (Transit)

Create the following script:
Find All
Go To Layout ["Concat Only"]
Copy All Records
Go To Layout ["Concat g"]
Paste [Select, No style, "Concat g"]
Exit Record/Request
Go To Related Record [Show, "Concat g to Concat"]
Perform Script [External: "Transit"]

This external script in Transit (which you have to create first, then call by choosing “External” at the bottom of the drop-down script list, then finding Transit, then the script) has two steps:
Delete All Records
Find All

The first part of the script copies the Concat field from all records, then pastes them all into one global field, Concat g, separated by returns. Make sure you check the “Select” box, or you’ll quickly reach the text field limits. You could also put a Clear ["Concat g"] step before the paste, but Select seems to work.

The last 3 steps will go to only the records in Transit that are duplicates of ones in Phone.all, using the many-to-many relationship, then delete them all. The last step will find the remaining records, which could be imported into Phone.all (but see below for more).

Eliminating Duplicate and Updating Records

OK, that was simple enough (groans). But what about the more useful operation of using the data in the new file to update the duplicate records in the Phone.all file, then import non-duplicates as new records. No problemo.

In this case we’ll use the Concat relationship to establish a link with records in the text or database file that represent people we already have records for in our main file.

An Alternative Method Using Serial ID
You could also do this entire operation with the Serial ID field, instead of the Concat field. You’d use the Serial ID method if you were synchronizing two databases (or a spreadsheet), such as from two different locations of a business or from a template file to an active file, where the Serial ID field would be what would be used to identify items. In that case you wouldn’t need the Concat field at all, and could do the Copy All Records trick, layouts, lookups, etc., with the Serial ID field and a Serial ID g global field.

I don’t want to confuse you with two alternatives. But there are different possible solutions depending on the problem. One person may want to import basic contact data from simple non-categorized text lists, whereas someone else might be importing itemized inventory data from another FileMaker database. Use the Concat method for the former, and Serial ID method for the latter. We’ll stick with Concat for now.

Define Fields as Lookups
In Phone.all create a relationship to Transit, Concat= ::Concat (Transit). Redefine all the fields you want data for to be auto-enter lookups, based on this relationship. It’s pretty easy, as the field names are the same in both files.

You can’t lookup their name; it’s being used in Concat (that would be another case of needing to use the Serial ID method). You can’t lookup a field that’s being used to establish the relationship (FM will tell you so if you try).

Make sure to put that Concat field somewhere on the layout you’ll be on. It can be hidden by making it really small, the same color as the background, removed from the tab order and non-enterable. But lookups only work if the referenced key field is on the layout.

Transit Relationships and External Scripts
In Transit create a similar relationship between Concat in Transit and Concat in Phone.all.
Create a calculation field, type number, in Transit to count occurrences of this relationship, using the aggregate function. Count Concat =
Count (“Concat Relationship::Last”)
This will enter a 1 in any record that has a match in the Phone.all file, so we can find them later. “Last” could be any field that always has data.

There are two scripts which must be created in Transit, to be called as external scripts from Phone.all. The first will find and delete the matching records after the lookup has brought over the new values into Phone.all, then show the remaining ones. The second will delete the remaining records after the import; then the entire operation will be finished.

The first script, “Find Count Concat,”
Go To Layout ["List"]
Enter Find Mode
Set Field ["Count Concat", "1"]
Perform Find []
Delete All Records [No dialog]
Find All

The 2nd script, “Delete All,”
Delete All Records [No dialog]

One final detail. You should import some records from Transit into your Phone.all file first, getting all the fields lined up properly (not too hard, as they’re named the same) Put a check in the “Perform auto-enter options” box. Create a script in Phone.all, “Import Transit,” to save the order so that it’s automated for all future imports (I’ve done it for you in the example).

Relookup Script
Here’s the whole shebang, as one script, “Relookup,” in the Phone.all file (also available as an External script from Transit):

Enter Browse Mode []
Go to Layout ["List"]
Relookup [No dialog, "Concat Relationship"]
Perform Script [Sub-scripts, External: "Transit"]
Comment ["Find Count Concat\Delete"]
Perform Script [Sub-scripts, "Import Transit"]
Perform Script [Sub-scripts, External: "Transit"]
Comment ["Delete All"]
Find All
Sort [Restore, No dialog]

The Comments name the External scripts in Transit (which are annoyingly hidden within the script otherwise, reachable only through dialog boxes). It’s a good idea to use Comments to name these in your scripts, as well as weird Sort orders, Finds, etc..

I would like to thank Ilyse Kazar and Anne Verrinder, two very smart and helpful women (together now as Datatude), for the tips that inspired these two techniques. (You didn’t think I came up with this all by myself, did you?)

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

Marking entries with a self-relationship
Example file: Phone.fp3

One of the greatest strengths of a database, as opposed to a spreadsheet, is the ability to control the visibility (or found set) of records. You can, of course, do this with Finds, but you also need ways that work automatically and are unaffected by the found set.
The following techniques allow you to have multiple entries for someone or something, but only show one entry for each person (or any defined category) in lists or portals. This makes it both easier to navigate and to enter data.

Review
In last month’s article I wrote about how to mark the first or last entry of duplicate entries for any person or category. This month we will be using and expanding the same technique of marking the first entry with a self-relationship, so first re-read that part if needed. We will use that first-entry mark field to make only those records visible.
You would also need a self-relationship on the person’s ID field. It is different from the Serial ID field (which is just an auto-enter serial number, unique for each record, a permanent record ID, as opposed to the built-in record number, which is only temporary). The person’s ID would be the same in each record for that one person. I’ll go into methods of ensuring that next month.

Person Self-relationship: ID=::ID
The MarkFirst field would be (type calculation, number result):
If (Serial ID = Person Self-relationship::Serial ID, 1, “”)

The first entry mark is created by using a self-relationship based on an identifying ID field. It is unstored, hence updates itself automatically when entering new records. The problem with this is that no fields based on relationships can be indexed, and fields that can’t be indexed can’t be used to build further relationships. It’s kind of a Catch-22. There is a work-around, but it requires another field and a few more steps.

Mark First Entry Permanently
Create another field to mirror the First Entry Mark field, but just make it a regular number field; I would name it FirstMark_Set (so I know which one it is). Now, the trick is to get the unindexed FirstMark into the new field. There are two ways to do that, depending on when you want to run the operation. They are very similar, using the same calculation, but use different script steps.

Replace Method
This first method’s basic script, which can be run at any time is:
Replace ["FirstMark_Set", "FirstMark"]

If you have a lot of records, you might want to find just the records for that person or category first. The Replace function can take a little while.
An important warning: the FirstMark_Set field must be on the current layout (always required for the Replace function, think of it as a power Paste). You can make it one pixel high or hide it off to the right, but it has to be there. It’s best to include a Go To Layout step before the Replace to make sure it is, or put it on all layouts where you’re likely to need it.

You would have to run this script every time you created an entry that had a new main ID. For example, if your main category is people, several entries for each person would share the same person ID code. The system would work fine until you entered a new person with a new main ID. Their FirstMark field would get marked with a 1, but the FirstMarkSet field would remain empty until you ran the operation again.

You would notice because that new person would not show up in lists on layouts where you’d filtered for first entries only, which I’ll discuss after looking at the next method. One great use for the Replace method is in an existing database where you want to include this navigation method, setting all existing records at once.

Set Field Method
This alternative method fixes the above problem, but has disadvantages of its own (that’s why I’m giving both methods). Rather than running a replace occasionally on sets of records, this method just sets the field right after creation. It’s simple.
Set Field ["FirstMark_Set", "FirstMark"]

It would have to run after filling in the record (especially the ID field). It would add a speed hit, though only a small one, to creation of new records. The problem is making sure that it runs before leaving the record. FileMaker doesn’t yet have to ability to run scripts based on filling in a field, so it’s up to you to ensure it, which isn’t all that easy, as someone could enter the data and then immediately leave the record.

Warning Field
You could use a text calculation warning field, similar to the duplicate warning in last month’s article. In this case it would look something like:
If (FirstMark ‚ FirstMark_Set), “Click Here Before Leaving!”, “”)
["‚" is does not equal]
Put that on an unused part of the layout in red letters, formatted (Format Menu) as a button, attached to the Set Field script. It would only appear on new records, and would disappear after being clicked or if the field had already been set for that person.

It doesn’t matter if someone inadvertently clicks the area and thereby runs the script, since the two fields are mirrors of each other, and should be equal.
However, if users (or you) ignore the warning, you could attach the Set Field script to every button that leaves the layout, and restrict access to both the Menu and Status bars (overkill).

Perhaps the best way to solve this problem is to script the creation of new records, taking care of essentials such as ID fields and settings.
I’ll go into it in detail later, including creation between files; maybe next month.

First Entry Only in List View
Now, with the first entry permanently marked (for any existing entries anyway) we can use it to create a relationship. The FirstMark_Set field will have a 1 for each first entry. We should already have a Constant1 field (auto-enter or calculation =1). We’ll create a relationship between them:
First Entry Relationship: Constant1=::FirstMark_Set

Now all we have to do is use this relationship to filter records appearing in list views.
Go To Layout ["List View"]
Go To Related Record [Show, "First Entry Relationship"]
Sort [Restore, No Dialog]
Only one entry for each person (ID) should appear.

Add a transparent button over the name, with the script:
Go To Related Record [Show, "Person ID Self-Relationship"]
You will then have a basic navigation system for your file.

Stop the Press! Lookup First Entry

I’ve just discovered a method by John Mark Osborne which can be used as a basis for marking a first entry without having to run a script. It uses a trick with lookups. I’ll have to write about in detail next month. If you want it now, go to http://www.best.com/~jmo and look for the “Serialize by Category” file in the Tips and Tricks section. While you’re at his site, get the “Clairvoyance” and “Visibility” files as well. I’ll be writing about them soon.

I feel a bit like an idiot, as even the first basic part of his technique can mark a first entry into an indexed field upon entry (but only at entry, not later). The basic idea is to define the marking field as auto-entry, with a lookup, using the self-relationship of the ID field that defines the entry. It won’t find a match for the first entry. Have it enter “”
(nothing; just click in the box, but don’t type anything) if there isn’t a match. If there is a match, have it enter your Constant =1 field (mostly for size considerations).

So now all new “first entries” will have nothing in the field (let’s call it Marknot; you’ll see why), and all subsequent entries will get a 1. That’s the opposite of what we want. So make another field for the Mark;
calculation, = If (notMark = 1, “”, 1).
These “1 or nothing” fields add very little overhead to a database, so the extra field is OK.

Now you’ve got an indexed field with a 1 for first entries and empty for everything else; no need for scripts to fill it in. But there is one drawback: if you ever delete that first entry, the next one will not fill itself in with a 1, and a Relookup won’t do it, either. So this method is for databases where you are not going to be deleting records. But that’s generally where you’ll want to mark first entries of a group, anyway.

This is all a bit rushed, as I’m writing this on deadline eve. I’ll try to clarify the “ifs, ands and buts” next month.

The previous scripted techniques are not a waste of time. You’ve learned (hopefully) how to use Set Field or Replace to get an unindexed value into an indexed field in one or many records, and how to display a conditional warning (or any kind of) statement. You’ll still need the unindexed technique for marking the latest entry, as that one can’t be stored (otherwise it wouldn’t be the latest).

This “indexing” technique can be used for other things as well, such as for Value Lists (only indexed fields can be used) and to speed up otherwise very slow calculations which were using unidexed fields (later).
Let’s move on to fun with globals and portals.

Global Choice Field above a Portal

We’ve talked before about using a global field to mirror a regular field. In this method the field that the global mirrors is placed in a portal below it. The portal allows several records seen at once, with several fields visible. A change in the global instantly changes the found records in the portal.

Global Field with Value List
You could mirror any field, but the most useful method is to mirror some overall category field that is important for that file, but that doesn’t have more than ten or twenty possibilities.

In Layout mode, you can format the global to be one of the pop-up list choices. Set its Value List to use the values from the field itself. If you use an ID field, check the “Also display values from” and select a name field, so the ID field is recognizable. If both are indexed fields (as they should be), then the list will sort by the second name field.

You get a different look depending on which kind of list you choose. Checkboxes or radio buttons are going to need some room on the layout; radio buttons are the best way to make a quick choice from just a few possibilities. You can even make multiple choices using the Shift key. The other lists take up less room, but aren’t as fast to use.

Global Field to Real Field, Relationship for Portal
Create a relationship between the global field that you’re using to the real field that it mirrors. If it was an ID, it would look like ID Relationship: ID g= ID.
Draw a portal on the layout below the global field, based on the above relationship. (It’s the square tool with spaces in it.) Drag fields into its row with the Field tool.

Put the “real field” in it, but based on the relationship. The most common problem with portals is forgetting to make all the fields in it based on the same relationship as the portal itself. Choose the relationship name in the little drop-down menu above the field names (where it usually says “Current File”), then choose the ::field name. It would look like “ID Relationship::ID”.
As far as FileMaker is concerned that field is not the same as the original anymore. It’s considered to be the same as a field in another file. That’s the meaning of “relationship” and “portal.” Think of it as a window showing the fields of another file, even if it’s only based on a self-relationship within one file.

You can put any other field in it, as long as it’s also based on the same relationship.
Be sure to put in the Serial ID field that tags each record, as well as any other ID fields that identify the subcategory (in the case where there may be several records for each subcategory).

Now, since all the fields in the portal are based on the global field, making a new choice will instantly change all the records showing in the portal. It’s the fastest find technique you can imagine. And since the global is based on the field itself, it will update itself whenever you add new records. You have to try this to see the speed.

Using Portals for Navigation
So now you can change the overall categories quickly and find the subcategory or name that you’re looking for. The power of portals is that you can now either go immediately to the record you click on, or set a field to one of the values in the record you click on, either in that file (when it’s based on a self-relationship) or in any other related file.

To do either of these things you need a button in each portal row. You only have to make the button once in the portal, in layout mode; it will then appear on every row. The size and type of the button depends on whether you want to be able to edit the records that are visible in the portal or create new records by just typing into one of the fields.
If you’re using the portal for navigation or to set a field with a script, as we’re doing now, then you don’t need to enter anything; in fact, you can make a big button over the whole portal row.

Make the fill transparent. I like to use a subtle gray line color so I know it’s a button, but it doesn’t distract from reading the text beneath it. Create a script that will be attached to this button. First let’s make one just for navigation to that particular record, switching to a Main View screen to see all its details.

You’re also going to need a global field mirroring the Serial ID field, Serial ID g, and a relationship pointing back to the real Serial ID field.
Serial ID Relationship: Serial ID g=::Serial ID

Set Field ["Serial ID g", "Portal Relationship::Serial ID"]
Exit Record/Request*
Go To Related Record ["Serial ID Relationship"]
Go To Layout ["Main View"]

* Always put Exit Record/Request after setting a field and then using it in a script step involving the relationship. If you forget (and we all do) it will rarely work.
Notice also that when use Set Field you specify the portal relationship (in the drop-down box) then the field.

If you want to isolate that record as the only record in the found set, check the “Show” box. The step will then be:
Go To Related Record [Show, "Serial ID Relationship"]

If you have more than one portal on the layout, put this additional step as the first.
Go To Field [Desired Portal Relationship::Serial ID]
It helps FileMaker identify which portal to go to; otherwise it may go to the wrong one, even when you click in the right one.

Subcategory Navigation
You don’t have to go to just one record. If you have another global field defined to mirror a category ID field, then you could go to all the records for that category. An example would be if, in a Jobs file, listing many jobs by title, you would also have an ID field for the people the jobs were being done for. You might do several jobs for the same person. You would then put their name and ID (hidden) in the portal.

You would also need a relationship between the two fields.
Client ID Relationship: Client ID global=::Client ID
The script would now read:

Go To Field [Portal Relationship::Serial ID]
Set Field ["Client ID g", "Portal Relationship::Client ID"]
Exit Record/Request <–I just forgot it; it’s real easy to do
Go To Related Record [Show, "Client ID Relationship"]
Go To Layout ["List View"]

There would be two transparent buttons in this case. One would go over the client’s name, and have this script attached. The second would go over the job name and run the “go to serial ID script, as in the first example. You’d click the one over the client’s name if you wanted to see all their records, then hit the second when you saw the job you wanted to see in detail.

Constant =1 Filtered Portal
Now I’d like to pull two of the previous techniques together to create a simple automatically filtered portal. We’ve already seen how to mark a record with a 1, either the first entry or the latest, and how to set that mark into an indexed field.

Now all we need to filter the portal is a constant 1 field. You should always have this field in your files. It’s a simple calculated field, number result, Constant =1, or a number field, auto-enter 1. You can use it for many things. In this case we’re going to relate it to our MarkSet field. The relationship would look like:
First Mark Relationship: Constant =::FirstMarkSet.

If you base a portal on that, and the fields within the portal, then it will always contain only the first entry records.

Active Records with checkbox
Another use of this filtering technique is to create a manually marked set of records for visibility, ones that you select as being the ones you want to see. Let’s call them Active.
You’ll need another field, Active, type number. Format it as a checkbox. Create a Checkbox value list for it. In the custom values, enter 1, nothing else. You can use this list (if one thing can be called a list) for all checkboxes in your file.

Back on the layout, also format the Active field as Boolean. Enter 1 for the “yes” box and clear the “no” box (from the Format Menu, Number).
So Active is a checkbox, 1 or nothing. Make it just big enough to show the box, but not the 1; it looks like checkbox (duh).

Now all you have to do is use Active instead of FirstMarkSet as the field for Constant to relate to in the previous example.
Active Relationship: Constant =::Active
Base the portal and the fields in it on this, and only your chosen records will appear in the portal.

To quickly “mark” a whole slew (technical term meaning “a bunch”) of records at once, create a script that uses the Replace step. Choose the option, by calculation, number result. Just enter 1 in the dialog box. It will mark all the found records pretty fast (as long as the Active field is on the layout). Create a similar one with “” (two quote marks, no space) as the option and you’ve got an “unmark” records script.

Universal Portal
One last thing I’d like to mention. So far we’ve been talking about using a portal in one file. But since you’d have a constant =1 field in all your files, it’s a simple matter to make a relationship back to the original file from anywhere, Constant =::Original file Active field. You can then create a portal and fields based on this to see the same Active records in the new file.
In other words, you’ve created a universal portal, to use as a user-configurable multi-file value list for navigation or even data entry. Awesome, dude.

That should keep you busy for a month. Use the same sample file as last month, if you have it. Otherwise go get Phone.fp3.

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

An introduction to basic fields

This month we’ll go into some of the fields, concentrating on the ones you’ll need later on to add functionality to your database.

One of the most important fields to put in any database is one for serial numbers. They uniquely identify each record with a tag that stays the same. The default Record Number given by FileMaker will change whenever an earlier record is deleted, so it’s useless for most operations.

First go to Define Fields (Command-D), and create a field, either number or text. Then hit the Options button (or double-click the field name, or click Enter or Return), to bring up the Auto Enter choices. There is a checkbox for “Serial number.” Put 1 in the number box if it’s not already, and increment by 1.
Now every new record will get its own serial number. If you import records, be sure to check the “Perform auto-enter” box, so they get serialized as well.

If you’ve already got records in your DB, don’t worry. It’s pretty easy to redo them. First you might want to sort all the records in the order you normally view them; but it doesn’t really matter.

Now type a “1″ in the serial number field of the first record. Then choose Replace from the Mode Menu (Command-=, equal sign). Then hit the radio button for “Replace with serial numbers.” Type 1 in the “Initial value,” if it’s not there already. Check the “Update entry” box, so new records will begin after your last serial number. Then hit “Replace.” You will notice that “Cancel” is the default choice. This is a precaution, because Replace is not undoable. Once done it can’t be reversed, so take your time with those choices.

Serial numbers can be either text or numbers. I’ve read that Text is usually better, because of the way relationship keys work (later for that). It has to do with the size limit of number fields, 255 characters, whereas text fields can have 64,000. For now, though, either will do.

Text has one disadvantage though, in that a Find on a text field will find all matches that begin with the characters, and ignoring zeros after numbers. So, it will find both 20 and 2. This is easily fixed by beginning the text serial numbers with a few zeros. The first would then be “0001″, depending on how many records of that type you expect to have (3 zeros would allow 9999 records with no problems). You can even put a letter to identify which field it is, for example Contact ID’s could be C0001. This helps later if you have several ID fields in one area (such as a portal row), and can’t easily tell which is which.

Another kind of field is a Calculated field. These are amazingly powerful. One of the simplest and most useful is a concatenated (added together) field combining two other fields. This provides a quick way to get unique identifications for unserialized records.

The most obvious is to define a field combining a First Name field with a Last Name field. Click the Calculation radio button. In the Options (Specify Calculation) dialog that pops up, type (or choose from the field list); First & Last *(see below) with a text result (in the box down at the bottom).
If you want to use this to identify duplicates, you might want to add something else, in case two people had the same name. You could also add in a few numbers of their address. This might look like; Left (First, 4) & Left (Last, 4) & Left (Address, 4) The Left function was used to get just the first four characters of each field; it keeps things reasonably short.

*I’m not going to put the usual double-quote marks or punctuation around calculations I’ve written here, for two reasons. Field names and operators (such as &) are entered in calculations without double quotes. Quotes are reserved to identify actual text. Calculations are written here the same as they would be in FileMaker; they would generate an error if I put them in quotes and you copy/pasted them into the dialog. The asterisk ain’t part of it either; it would be considered a multiplication operator. Confusing? Yes. Logical? Maybe.

On the plus side, you can have spaces between terms in calculations, so it’s not all bunched together. Don’t worry too much, FileMaker will not only tell you if the thing won’t work, but can usually point out not only what’s wrong, but where the error most likely is. Strangely, if you want actual text double quotes, you have to enclose them with other quotes, such as “”"” (that’s four double-quotes to get one). Carry on.

You can now use this field to check for duplicate records. It’s much safer than using just names, as there could easily be two people with the same name, but they’re not likely to be living at the same address.

Another use of a concatenated calculation field is for simple text layout. It’s a more foolproof way to do such things as addressing envelopes, without having to rely on the “sliding” options in the Printing/Sliding dialog. It’s not that they don’t work, but they don’t always slide exactly how you want. It’s easier to add spaces and punctuation in a calculated text field.

Here’s an example (field names are written as they are, all other text must be enclosed in quotation marks, even spaces; the & symbol joins things together):
First & ” ” & Last & “¶” & Address & “¶” & City & “, ” & State & ” ” & Zip
(¶ is a paragraph symbol, in case it doesn’t show properly here, and it needs the quotes. Notice that the “, ” is a text comma and space; the other commas are term separation punctuations, which are part of the calculation structure itself.)

You can get fancier, using logical “If” statements to check for other additional fields:
If (not IsEmpty (Prefix), Prefix & ” “, “”) & First & ” ” & If (not IsEmpty (Middle), Middle & ” “, “”) & Last & “¶” & Address & “¶” & City & “, ” & State & ” ” & Zip

This checks for an entry in a Prefix field (such as Mr., Ms., Sir, etc.); also in a Middle name field. If there is one, it puts it, followed by a space. If not, it leaves it out. Double quote marks, with nothing between them, mean “nothing” or “don’t do anything,” and are a common way of ending an “If” statement.
The “not IsEmpty” is a backwards ways of saying “has something in it,” which there’s no direct way to say in a calculation. The “not” operator changes anything into its opposite. (Hopefully these all work; experiment with your own.)

Other useful fields are creation date and modification date. These can be set up to “auto-enter” the above dates when you create or modify a record. First make sure you’ve chosen Date for the field type, then hit Options. The first screen is the Auto-enter choices (which are numerous). Check the first box, and choose from the drop-down menu. The Modification Date choice is unique, in that it calculates whenever you change any field in that record. Usually the auto-entry choices only calculate when you create a new record, so this one is special.

The date fields are useful for sorting records. In my Job-tracking DB, I use a reverse sort, so that the jobs I’ve either created or modified most recently end up at the top of the list. You can sort by up to three fields at a time.

Next month I’ll go into some more calculation fields for showing record numbers, and a few calculation techniques for text formatting. Maybe we’ll make it as far as “global” fields.

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

Marking and finding duplicates
Example file: Phone.fp3

Oops. Another article (last month’s), another error. Fortunately it probably didn’t cause too much confusion, unless you tried to save IDs for more than about 16,000 records. I said that the global text field for saving them could hold 64,000K. That should be 64K or 65,536 characters (give or take). If you try to do more it will tell you that you’ve exceeded the text field limit. That can also happen if you don’t put a step in your script to either select the whole field or set it to “” (nothing) before pasting; after a while those numbers add up.

This month I’ll tackle the dreaded DUPLICATES. There’s little point in storing records if you have two for the same person with different addresses in them, one from a old address they moved out of a year ago. FileMaker has a good built-in duplicate search facility; you just type “!” in a field in Find mode. But it has some limits.

For one thing, you have to decide just what is a duplicate. It’s quite possible that several people could have the same name; it’s also possible that people with different names could be living at the same address, related or unrelated. Once you’ve decided, then you should create a concatenated (put together) field with the criteria. You can also use some of the text functions to keep the size down.

Duplicate Field Calculation
Here’s an example Dupe field (calculation, text) for a contact-type file:
Dupe = Left (First, 3) & Left (Last, 3) & Right (Main Phone, 4) Search with a “!” in that field and you should only get one person. Remember, no more than 20 characters without a space in an ID field, and less than 60 total (indexing limits).

One problem with the built-in Find is that it finds all the records that are the same, including the original. Then you have to look at them to decide what to do with them. You can’t write a good script that would safely delete them. If you write a looping script that finds duplicates then deletes every other record, it will screw up if it ever hits a triplicate (and if there are two, there could be three). Even if there aren’t, looping scripts can be very slow.

Another limit of Finds is that you have to run the search. It would be better if you could be notified automatically while trying to create a new record. To do that you’ll have to use a self-relationship. First, you must have an auto-entered serial ID for each record (always); I use a text field, with a letter followed by zeros in front (e.g., A0001).

Self-Relationship
Create the relationship (I’ll call it Self\Dupe) in the same file with the Dupe field on both sides; Dupe= ::Dupe (same Dupe field from earlier).
Create a new calculated field, type number, Dupe Mark=
If (SerialID = (Self\Dupe::SerialID), “”, 1)
The serial ID will only equal itself for the first record of the relationship. If you enter a new record and the same data is in the Dupe field, it will get marked with the 1.

Duplicate Warning
Now create your warning. It would be a calculated text field.
If (Dupe Mark = 1, “DUPLICATE”, “”)
Put it on the data entry layout where there’s some space. Set it to not allow entry and take it out of the tab order. Make the text red, but with transparent background and no line. It normally won’t show, but enter a duplicate and it will jump out. Since it’s based on a relationship it’s unstored, so it doesn’t even require exiting a field or record to trigger it. (Which is important. It doesn’t do much good to have a warning that only shows up after the user leaves the layout. You can’t count on them clicking outside of any fields before exiting.)

When You Want Duplicate Records
Using the same Duplicate relationship you can do a number of things. Often times you want duplicate records for a person or item in a database, and you need to track them. You may have several invoices for a person, or whatever; the principle of self-relation is still the same. Use a field that is always the same in the group of records and create a self-relationship on that field. Usually it’s a text ID field.

If you want to find out how many records, just count it with a calculated number field:
Count Dupe = Count (Self\Dupe::Serial ID)
It will instantly update. You can use similar self-relationships with an identifying key to total any number fields in its set of records. For example, let’s say you had a number field, Charges. Create a calculated field, Person’s Charges=
Sum (Self\Dupe::Charges)

You could get the same total with summary functions, but this one is more reliable, as it doesn’t depend on sort order. You can even use it in other calculations, such as, Balance Due=
Sum (Self\Dupe::Charges) – Sum (Self\Dupe::Payments)
They are unstored calculations, therefore slow, but so is sorting and going into Preview mode (another way to get this total).

Marking First or Last Entries
If you want to mark which is the first entry in the group, use this:
If (SerialID = Self\Dupe::SerialID, 1, “”)
It’s just the opposite of the duplicate calculation.

To find the latest entry you can use the Maximum function, but you have to do a little compensation for the fact that the Serial ID is a text field.
If (TextToNum(SerialID) = Max(Self\Dupe::SerialID), 1, “”)
This will also find all non-duplicates.

To find all duplicates that are not the latest entry, exchange the 1 and “”;
If(TextToNum(SerialID) = Max(Self\Dupe::SerialID),”", 1)
or use “‚” (does not equal);
If(TextToNum(SerialID) ‚ Max(Self\Dupe::SerialID), 1, “”)

If you want to use other criteria for the first entry other than when it was entered, then you can do that also, by using the minimum or maximum aggregate functions.
For example, the record with the biggest payment for a person:
If (Payment = Max(Self\Dupe::Payment, 1, “”)

Deleting Duplicates
You can now reliably find a set of only the duplicates of records, any number of them, excluding either the first or last entries. You can then add the single script step:
Delete All Records
That should do it.

Once again, I must disclaim. Make these marking fields visible on a list view layout. Experiment on test records until you are sure that they are doing what you want before committing to an automated Delete All script. Don’t worry, once you’ve got them down they will work well, with probably less error than if you did it by hand (well, than if I did it, anyway).

I’ve included a sample file, Phone.fp3, to illustrate how it works. You can modify it and add your own records. Have fun. If you have problem or specific question, please write me and I’ll try to answer.

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

Finds using globals in scripts

Hello again. I think this month we’re going to flatten out the learning curve just a bit, for both our sakes. There isn’t enough time, space (or knowledge) for me to be writing a manual here, and I’d rather write about tips to actually do things. Not to worry, I’ve got lots to keep us busy.

Here are some tricks with Finds, especially how to use relationships and globals to do “find-like” operations.

One simple scripting technique with Finds is to isolate the current record, in the case where many are showing. There are two ways to that. The first uses the Find and Omit steps in a simple script:

Find All (shows all records)
Omit (omits the record you’re looking at, just like clicking the Omit box manually)
Find Omitted (finds only that record)

It’s useful in Preview -> then Printing scripts, as Preview Mode always takes you to the first record of the found set. These lines in a print script allow you to print just the one record you’re looking at in Browse mode, or have found with a Find script.

The second method, using Relationships, is a bit more complex, but can be customized to do more than one record. It requires the creation of a global field.
Record ID g (Global, type text)

You should also have a Record ID field, serial number (text), auto-entered for each new record. If not, define one. (You can use the Replace, by Serial Number command to serialize an existing database file; look in the earlier articles.) Also, create a self-relationship (same file) with the Record ID global field on the left and the Record ID field on the right. Name it something like Self\Record ID g.* Now all you need are simple steps in any script to save that Record’s serial number and return to it:

Set Field ["Record ID g," "Record ID"]
Exit Record, Request
Comment ["You can go do other things here; the next step returns."]
Go To Related Record [Show, "Self\Record ID g"]

So far so good. But what if you want to be able to return to more than one record? An elaboration of this method will do this. You need a new layout. Put only the Record ID field on it. That’s all. Create another layout. Put the Record ID g field on it, in the body. It could have other fields. Neither of these needs to show on the Layout Menu (though I usually leave all layouts visible while I’m developing, at least till I’m sure they work).

The script works by using an undocumented but real ability of FileMaker to treat multiple entries separated by paragraph returns as if they were separate entries. It vastly extends the power of relationships, as you’ll see.

The script is fairly simple.
Freeze window
Comment ["Looks better and saves time."]
Go to Layout ["Record ID"]
Copy All Records
Comment ["This will copy all the IDs in the found set of records."]
Go to Layout ["Record ID g"]
Set Field ["Record ID g," """"]
Comment ["This initializes (clears) the global. Good idea."]
Paste [Select, No style,"Record ID g"]
Comment ["This pastes ALL the found ID's into the global field, separated by paragraph returns."]

At this point you can go off and do other things, many of which would normally lose that found set. There’s no limit to what you can do, as long as you don’t reset the Record ID g field. The same single step at the end of the script or attached to a button will bring back the former found set of records.** Go to Related Record [Show, "Self\Record ID g"] You would also want a step somewhere to go to the right layout (otherwise you’d still be on the Record ID g layout).

You can remove the Comment steps. There is a limit to how many record IDs you can paste into a text field, but it’s pretty high, 64,000K, including paragraph characters.

The idea of using a global to temporarily hold the value of a regular field is one you’ll use many, many times. In fact, the steps:

Set Field ["Fieldx g,""""]
Comment ["Makes sure it's empty"]
Set Field ["Fieldx g," "Fieldx"]
Comment ["Do anything else here, including Enter Find Mode"]
Set Field ["Fieldx," Fieldx g"]

Should be used in most cases instead of Copy/Paste. It’s faster, it doesn’t use the clipboard, and it’s safer, as Copy/Paste requires that the fields be on the present layout. You can have several Set Fields filling different fields in one script operation, whereas the clipboard is limited to one thing at a time; ergo only one Paste from the clipboard after entering Find Mode. Some exceptions are Date and Time fields, which are finicky using Set Field; the Paste Result or even Paste Literal must sometimes be used.

A Global ID field mirroring an ID field is also an easy way to present choices from a few values. You can allow a choice in a global field in Browse mode without changing any data in the records, such as using the field itself would do.

Create a global field mimicking the original (i.e, if Fieldx ID, then Fieldx ID g). Then, in layout mode, format the global field to be radio buttons, using the “values from a field” choice. Specify the original Fieldx ID field; show also the field’s associated name field so the choices are recognizable (the ID alone isn’t).
Stretch out a box on the layout for as many ID’s as you have (they can form columns). It’s not real pretty, as the ID numbers show (unless you do something to hide them), but it’s highly functional.

Choose one, then hit a Go to Related Record scripted button (Fieldx ID g::Fieldx ID), and you’ll be instantly transported to those records. You could also use a pop-up menu if you have a lot of names. You can even use checkboxes (or the shift key w/radio buttons) to choose multiple values, using the previously mentioned ability (multiple choices being automatically separated by paragraph returns).

Another amazing use is to put such a global field above a portal whose relationship is based on it, e.g., Job ID g::Job ID. This could be a self-relationship in the same file or in a different file with that same ID field. Then, as you click on different buttons all the records in the portal will change immediately to reflect the new ID choice. More on Portals later. Please note that due to the non-indexable problem you can begin a relationship with a global, but not end it with one (example: global field on the left side, OK, but global field on the right side, No Way).

Well, as usual I went off on a tangent (albeit an interesting one), ignoring the increasing incline of the learning curve. Hopefully I’ll be more fun next month.

*BTW, you may find my naming techniques strange; but one thing I can tell you for certain, you will need to come up with a scheme that makes sense to you. It is important that you can recognize fields and scripts by their names.

I like to name similar fields with similar beginnings, so that they sort alphabetically together (scripts and relationships must be sorted manually, so it doesn’t matter). Scripts that make a call to (or are called from) another file are also labelled with arrows (–> or <–) and the abbreviated file name, so I know where they are coming from or going to. It also makes them easier to separate from in-file scripts.

Oh yeah, I use the “\” sometimes to separate names. You can’t use a “/” in field names, but the “\” is fine. If I want words to be visually apart, such as “JobID g,” then I use an option-space before the “g.” It really sets it off, but still allows you to double-click it as one word to put into calculations. [Note: It's just a regular space here because DOCMaker, which My Mac is written in, can't deal with option-spaces.]

One of the greatest strengths of FileMaker, as opposed to others, is that it allows you to change the names of fields, layouts, scripts and relationships, readjusting them for you throughout all the related files. One thing it does not do is readjust changed file names. This means that if you change them it will break the relationships (which must then be manually reestablished).

**Globals have different characteristics on networked databases. It should still work on each local machine’s file, at least until you quit. I know little about networking.

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

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