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). Often 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 need 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 validated by the built-in "unique" checkbox. You might however make a similar relationship of a global ID field to the ID field (more on that later).
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 Related Record
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 be used in calculations as well. 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, 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 isn't needed, if you've exited the record after setting the global. 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.
Check with a Self-Global relationship
You can use the same check with a 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.
Send comments, questions, toys to Fenton Jones