This article is going to be mostly catch-up, for me to go back and explain some things in the example file, Phone.fp3. There were a couple of layouts and operations that got left out. They are a little more advanced.
Alphabet Layout with Calculated Key
Keys do not have to be plain text fields or number fields. You can also use calculated fields on either side of a relationship, as long as they can be indexed, and always on the left side.
In the Phone.fp3 file there is a layout named Alphabet. There is a global field, Alpha_g, on the left, formatted as radio buttons. It is also formatted to use the calculation field Alpha_c as its Value List. As soon as you have enough records, it will appear as the letters of the alphabet (you could type in the alphabet yourself, but why bother).
Alpha_c is defined as the simple formula, = Left (Last, 1)
Last is the last name field. This will give you the first letter of the last name.
There is a relationship between the two fields, Self-Alpha;
Alpha_g= ::Alpha_c
The portal is also based on the same relationship. So if you click one of the letters, a relationship will be established between that letter and first letter of the last name field (in the portal) instantly showing the related records. To go to that record, a transparent button over the names is attached to the script "Go Main Alpha_g":
Set Field ["Serial_ID_g","Self-Alpha ::Serial_ID"]
Exit Record/Request
Go To Related Record [Show,"Self-Serial_ID_g"]
Go To Layout ["Main View"]
The global Serial_ID_g temporarily holds the value of the Serial_ID field, which is an auto-entered text serial number; most files should have these two fields. The Self Serial g relationship between them will always be able to identify a record(s). You have to use these steps to get out of the portal, which is considered as if it's a separate file.
Clairvoyance Layout (Type Ahead)
The Clairvoyance layout takes this idea a step or two farther. It uses a simple multi-key calculated field to achieve a many-to-many relationship. We've seen a similar relationship before in the Copy All Records trick (which I've already written about twice). This time though the multi-key is built by a calculation field rather than by the copy/paste trick. It's still the same general principle, which I would state as:
For relationships FileMaker considers each line of an entry in a field to be a separate record.
So, the only thing that is needed is to put paragraph returns between them. In this case we'll just expand on the earlier calculation field (text result) to create a new one;
Match_c = Left(Last, 1) & "¶" & Left(Last, 2) & "¶" & Left(Last, 3)
The first line will hold the first letter, the next the first two letters, etc..
A global text field, Last_g, will hold user-entered letters to match up to this. Make a relationship between them, Match Last_g;
Last_g= ::Match_c
As you type letters into the Last_g field, the records that match it will narrow down to the correct name(s). There is one problem: the portal records don't refresh unless you either hit the Enter key or click somewhere outside the fields. But then you're not in the Last_g field anymore. To type another letter you have to click back into the field. Clunky.
There are two methods I know of to fix this. Both use "pause" scripts. The first begins a loop, pausing and resuming every second to refresh the portal. This works, but the screen refreshes are unnerving, and you still have to click to end the script. I came up with an alternative script, "Clairvoyancer," which is a little smoother, but still requires the Enter key.
Loop
Exit Record/Request
Go to Field ["Last_g"]
Pause/Resume Script []
End Loop
It requires a button, Begin, to start it. You enter a letter with your left hand then hit the Enter key with your right. This feels fairly natural. It triggers the Pause/Resume step, the portal refreshes and the cursor is in the Last_g field, ready for the second letter (in case just one wasn't enough). The loop continues.
Now we come to the fun part. Because of the paragraph returns, the two letters in Last_g will match the Left(Last, 2) of the Match_c field. If that's not enough, enter a third letter, and hit the Enter key again. Last_g will now match the Left(Last,3) line of Match_c.
There is also a transparent button over the names in the portal, this time based on the Match Last_g relationship, to set the Serial_ID and go to that record.
The one caveat is that the loop is still continuing. It can be stopped by choosing the Exit Script option in the little drop-down menu that appears when you attach the following script to the transparent button:
Set Field ["Serial_ID_g", "Match Last_g::Serial_ID"]
Exit Record/Request
Go to Related Record [Show, "Self-Serial_g"]
Go to Layout ["Main View"]
Just to be safe, in case there is no match and the user leaves the layout, every navigation button on the layout should also have this option chosen. You'd want to stop them using the Window Menu also; but that's a whole new kettle of fish.
Limiting Menus
On that topic, let me just say, without going into detail, that you should provide buttons for most functions for three reasons:
1. You can add script steps and parameters, such as above, that are unavailable to the standard FileMaker menus and command keys.
2. The user can get used to finding what they need on the layout, so they'll use the buttons rather than the standard menus.
3. Eventually you may have to lockout certain menu commands or even the entire Menu Bar to keep control of things, so you may as well begin adding buttons now.
Multiple Checkbox Choices in a Global Field
In another situation, with a non-alphabetical field, the global field could be formatted as a checkbox to take advantage of the multi-line matching ability as well, without needing a calculated field. Relate it to a regular text field. Multiple choices in a checkbox field (or radio buttons if you use the Shift key) are also considered as separate entries, so all records relating to any of the choices would show up in a portal based on the same relationship.
Remember that the fields in the portal have to be defined as belonging to that relationship as well or they won't show up.
Send comments, questions, toys to Fenton Jones