Calculated keys & portals
Example files: Phone.fp3,Import-Transit
This month is going to be mostly catch-up, for me to go back and explain some things in the example files. There were a couple of layouts and operations that got left out. Maybe you’ve already figured them out.
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 Alphac 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 Serialg 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
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)
(“¶” is the paragraph return symbol in case html mangles it)
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 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 [“Lastg”]
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 [“SerialIDg”, “Match Last g::SerialID”]
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..
Reviewing Duplicate Records Before Importing.
I added a routine in the Import example file to allow you to review duplicate records in Transit before deleting them or using them to update the original entries in Import. The operations are fairly simple, but they hop back and forth a little..
Checking Related Records
I think I’ll put the script first, Check Duplicate Names:.
Perform Script [Sub-scripts, “Copy All Records”]
Go to Layout [original layout]
If [“IsValid(Concat g::Last)”]
Show Message [“There are new records in Transit with the same name.
Do you want to check them before overwriting the originals in Phone?”]
If [“Status(CurrentMessageChoice) = 1”]
Go to Related Record [Show, “Concat g”]
Perform Script [Sub-scripts, External: “Transit”]
Comment [“Halt Script <–Import”]
Else
Exit Script
End If
End If.
The new steps begin with If [“IsValid(Concat g::Last)”]. I love this step. In general terms, If [IsValid(“the relationship::any field with data)”]. Its beauty is that it checks to see if there are related records before having to go to them.
That is much better than using the Go to Related Record [Show, “Concat g”] by itself and then having to trap for Status(CurrentFoundCount)=0. You can use this step to check for related records first in either scripts or calculations..
The way it is used in this script if there are no related records the first If isn’t fulfilled and nothing happens.
If there are related records the message above is displayed. “Yes” is the default choice, which is trapped by If [Status(CurrentMessageChoice)=1].
A simple script with a single step, Halt Script, is then called as an external script in Transit to stop everything. It’s in Transit so that its window is brought to the front and you can see the duplicate records..
Halt Script vs. Exit Script
The reason that I used Halt here is because this whole script is called as a subscript by the main Relookup script. Halt stops all scripts, whereas Exit only stops the script it’s in. If it’s in a subscript like this one, then subsequent steps in the main script that called it will continue. It’s sometimes a big difference.
If the “No” button is clicked in the dialog then the Else step happens, the Exit Script stops this script but the Relookup script continues..
Review Button in Transit
To make it easy to review records a script was added to Transit, attached to a small button that shows up on each row in list view..
If [“IsValid(Import concat::Concat)”]
Go to Layout [“Form”]
Go to Related Record [Show, “Import concat”]
Perform Script [Sub-scripts, External: “Import”]
Comment [“Form View <–Transit”]
End If.
The first step checks if there is a related (duplicate) record. If so it displays it in form view in both files, so you can see all the information and make a decision. I sized the layouts and windows so they can be tiled horizontally and still see everything..
Finally, there is also a button to delete the record in Transit. There is no need to delete the one in Import, as it will be overwritten by the Relookup script..
You’ll have to run Relookup again manually after reviewing, since I halted it. Another way to do it might be to use a different combination of Exit script steps between the files, so the script would only be paused. But this way seemed good enough, and less prone to user (and creator) error..
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.