Self-Relationships with ID Fields
Example file: Eats
This month I’m going to go on a bit more about self-relationships. They are one of the most useful tools for identifying records within a file. You can use these self-relationships for many things that would normally require a Find operation (which can be slow, especially with many records), do many things that would require a summary report (also slow, and requiring a change to Preview mode), and easily do things that would be very difficult otherwise.
If you haven’t done anything much with relationships, these are a good way to start. The “self” part of the name just means that both sides of the relationship are fields within the same file.
Self-ID Relationship
The most basic is a self-relationship relating a field to itself. Just click the same one on each side in the dialog box. I like to name them “Self” and then the name of the field (duh). Usually it’s an ID field, so one for a Job ID would be “Self-Job ID,” and look like: Job ID =::Job ID. That keeps it separate from relationships to other files that use the Job ID; they would have the other file name first, as in “Materials Job ID.”
There are several uses for self-relationships. There are two basic possibilities for an ID field in a file. The first is that it is unique for each record in the file. The other is that it isn’t unique, that there are of several instances of it in the file.
In the first case, you may not even use a self-relationship on the ID itself. There’s not much point, since there’s only one; it would usually be an auto-entered serial number and can be validated by the built-in “unique” checkbox.
Duplicates (again)
It would be useful in that case to have a self-relationship between another field that should be unique, but which wouldn’t be if there was a duplicate record. This is usually a concatenated field, put together from pieces of several others. An example:
Left (FirstName, 3) & Left (LastName, 3) & Left (Address, 3)
A self-relationship on that field could find duplicates. There are a few ways to set that up; I’ve written about in my article on Duplicates, so I won’t go into it now.
What I haven’t written about before (at least not very clearly) is how to use self-relationships in files where there are several instances of an ID field.
Pseudo Design Theory
In this case, some other ID would be the “primary key” defining each record. The ID used in the self-relationship would often be a “foreign key,” also related to another file where it was the primary key.
If you look at it structurally, you’ll often see that the self-relationship on the ID in the file where there are several mirrors the structure of the file where the ID is unique and only in one record.
Go To All Instances
There are many uses for a self-relationship on the ID. Since there are several records containing it, scattered all over the file, it would be nice to be able to quickly bring all instances of it together in a list view. A single script step using the self-relationship could do that:
Go To Related Record [Show, “Self-ID relationship”]
Calculations
The self-relationship can also be used in calculations. It is available for use with the Aggregate functions, such as Count (Self-relationship::field). Sum(Self-relationship::number field) will give you a total for all records with that ID. These values will be reliable and independent of the Found records, so they can safely be referenced from other related files (which Summary fields, dependent on the found set, cannot).
The only disadvantage is that they are unstored, and any further calculations based on them will become unstored upon closing the Define Fields dialog. Not only that but they will remain so forever, unless you either remove the related field from the calculation or change it so that it can be indexed; and then also go into the Storage Options of each calculation and manually uncheck the “Do not store results” box!
Not being stored can be a feature in some situations. A field with the count of the relationship can show you how many records there are of that ID without even having to perform a find, and it will update if you add or delete records.
Global Self-relationships
A similar kind of relationship that is extremely useful is what I’ll call a global self-relationship. In this case the two sides of the relationship are not quite the same field, but they are pretty close.
Create a global field, of the same type as your ID. Name it the same, but with a “g” as a prefix or suffix. Choose that as the left side of the relationship, and the regular ID field as the right side.
What this relationship does is to free you from having to use the ID field itself in operations. This becomes essential when you are not actually on the ID’s record. Two common cases of this are when you change the found set within scripts, and when you choose records in a portal.
In both cases, all you have to do is to get the real ID value into the global ID field while it is the current record. Then at any later time use these two steps:
Exit Record/Request
Go To Related Record [Show, “Self-Global ID relationship”]
[The Exit Record/Request isn’t always needed, if you’ve exited the record after setting the global. But you’ll get unreliable results if you haven’t.]
Global Self-relationships and Portals
Portals are kind of an illusion, in that the records can appear in it, but the fields are related fields, based on whatever relationship the portal is based on.
So you see what looks like your regular ID field (or a text name associated with it) in there, but it’s difficult to go to the those records with a script using just the “Go To Related Record” step. The reason is that the ID field in the portal is not quite the ID field; it’s a related field, based on the same relationship the portal is based on.
For example, if the portal is based on a relationship between a Constant=1 field and a First Entry Mark field (both being number 1, in an earlier article), showing one entry for each ID, the ID in the portal would actually be “Constant::ID.” Because you don’t have a relationship between this field and your ID field you can’t go there directly.
But you can use the global ID field as a go-between. Just set the Constant::ID field into the global first.
Set Field [“IDg”,”Constant::ID”
]
Exit Record/Request
Go To Related Record [“Self-IDg relationship”]
Go To Layout [“List View”]
Warning
If you set the ID field rather than the global ID field, so you can just use the Self-ID relationship, you will be changing the current record’s ID field to your choice. Since you may not even see the current record on the portal layout, this could be disastrous. (Of course, if you are using it to set the ID for a new record, that may be exactly what you want to do.)
Logical Calculations on the Self-Relationship
You can use calculations on the self-relationship itself to check certain conditions. The most useful is to see if it is valid; using (guess), IsValid (self-relationship::ID). The field in the last part can be any field that you are sure has data. I often use my trusty Constant=1 field.
You can use the same check with Self-Global relationship.
This is great to use before the Go To Related Record [Show, “Self-Global relationship”] step. One of the great problems with Go To Related Record [“”] is that it will go even if there are no matching records, leaving the user with no records, often on a different layout (or even file), with no error message or clue as to what went wrong. It’s even worse than the built-in Find when there’s no records.
But you can use the following simple steps:
Set Field [“IDg”,”Whatever relationship::ID”]
Exit Record/Request <–Don’t forget this baby
If [not IsValid, “Self-IDg::ID”]
Exit Script <–Use Halt Script if there are Subscripts later
End If
Go To Related Record [Show, “Self-IDg relationship”]
Go To Layout [“List View”]
If the script is attached to a button, when the user clicks it and there are no matches nothing will happen. You can put a message if you want, but I like the simple zilch.
It is very useful if they are looking at a portal that is based on another file, since there may or may not be records for that ID in this file.
The If [IsValid, “Self-ID::ID”] or, alternatively, If [IsValid, “Self-IDg::ID”] check can be used within scripts, whenever you need to check to see if there are records. It is completely independent of the Found set; but it does depend on being on the record with the correct ID or getting the ID into the global ID field (and using the Exit Record/Request step).
Counting a Global Self-Relationship
You can count the global self-relationship just like you can count a self-relationship, using the aggregate function;
Count (Self-IDg::ID)
This can be used during scripts if you need to make a decision based on how many records there are for any ID (which would first be set into the IDg field).
Self-Relationships vs. Finds
In some cases you could perform the same operations, isolate the same records, by doing a Find operation. However, my experience is that Finds become slower when the number of records become very large, whereas operations using relationships seem to increase much less slowly. It is always good to consider just how big the database might become over time. It may be worth going to a little extra trouble to create a self-relationship for an operation that will performed all the time on a large database.
In many cases, especially during complex scripts, self-relationships are invaluable, because of the extra control they give you.
So, next time you get in a tight spot trying to bring things together (or keep them apart), consider whether one of these small relationships could help get a handle on that pesky data.
My Mac Turns 50
Happy Anniversary to us all!
Back in the early days of my Internet adventure (about three years ago), it wasn’t so easy to find much material on the web. It was even more unstable than it is now (if you can believe that), and at 2400 baud it was like watching an old movie in slow motion.
So I was very happy to find a little magazine devoted to Macs. I could read about the little shareware programs that were (and still are) so essential to make your Mac easier and more fun to use. I spent a lot of time trying them out and learning quite a bit about the computer in the process.
I remember sending an email to Tim, probably pointing out a little program that I thought he should review. I was quite surprised at his response, which was basically, “You write pretty well, why don’t you write a review?”
I began with URL Manager, which I still use daily. I then followed that for several months with all the little programs I loved, BBEdit Lite, Eudora Light, Snap-To.
That was over two years ago, and I haven’t stopped.
A little while later I fell for FileMaker Pro, and after a review and an introductory column, I was off on my tutorial series, FileMaker 101.
It has been a labor of love (though it isn’t always an easy application to love). I write about what I’ve learned by hard effort (trial and lots of error), as well as the helpful tips I’ve picked up in many places on the web, especially the FileMaker mailing lists.
I write not so much to create a textbook or manual but to share the little tricks and techniques that allow you to go beyond step one. I try to stay focused and be clear, but I also allow myself to write about what interests me, and in my own style, rather than formal “technical writing.” It is an informal column, though its contents are pretty geeky.
I want to thank Tim, Russ, Adam, and all the other writers who spend so much time making this all happen. Their dedication and openness makes it possible. We all do this simply because we want to do it, and we want to share it with you, our readers.
I know that if I write sincerely I will be supported. And that if I manage to pull together the various bits of knowledge into a coherent article it will help someone, and save them some time and frustration, and maybe occasionally make them jump up and say, “Ah ha! That’s how you do it.” (I do that anyway.) It also helps me; by explaining it to others I’m forced to get it clear for myself. I even remember most of it : )
The last thing I want to say is that you who are reading this are part of the My Mac team. Anything you have to say on the subject is important. So write to the authors with your praise, criticism, or questions. In the future we hope to be able to include you even more directly. We are a community, we Mac users.* We’ve seen some low times and some high times. But we’ve always helped each other.
*FileMaker users are another community, reaching across platform lines; I’m sure many of my readers use PCs. I look forward to the day when all documents and applications are fully interchangeable. Computers have become too important to the world to be limited by incompatibilities.
See you next month with more on relationships and some fun with portals.
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
You must be logged in to post a comment.