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
Leave a Reply
You must be logged in to post a comment.