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 is to mirror some overall category field that is important for that file, and 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 and 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 just for navigation or to set a field with a script, as we're doing now, and you don't need to enter anything, then you can make a big button over the whole portal row.
Make the fill transparent. Make the line either a subtle gray or none, so 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 global field then using it in a script step involving the relationship.
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"]
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
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.
Filtering Lists
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 easier to navigate and even to enter data.
Review
In the last article I wrote about how to mark the first or last entry of duplicate entries for any person or category. This time 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). The person's ID would be the same in each record for that one person. I'll go into methods of ensuring that next article.
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 will cause all calculations using them to become unstored, hence useless for anything requiring speed. There is a work-around, but it requires another field and a few more steps.
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.
Web sites:
John Mark Osborne
Send comments, questions, toys to Fenton Jones