Multi-keys with Portals
Example file: Storess (replaces last month’s file of the same name)*
[“How to use the different lines in a multi-key to filter a portal in a variety of ways.”]
Portal vs. List
In the last month’s article, with the example file, Storess Ÿ, I showed how to create the small window list. Its purpose was to allow you to choose from a “filtered” list of stores (in this case), showing only the stores that a person had already shopped at.
Portal
This month we’ll use an upgraded version of the same example files, but use a portal instead. It will demonstrate another way to extend the flexibility of multi
key fields in relationships.
One advantage of using a portal instead of a list is that the portal, by definition, is already a related group of records. This means that we don’t have to issue a “Go to Related Records” command to get the records to change after changing the key (which we did with the window list). All we have to do is change the key, then exit the record, and the records will change to match the new key.
In order to be able to change the key to anything we choose, without affecting the records, it should be a Global field (Portal g). It should be defined as related to an indexed key in the file we want to see in the portal (in the same file or another file).
Exit Record/Request Step (again)
The Exit Record/Request step should always be used whenever setting a global field, before using it in a relationship. It allows FileMaker to register the new value.
In fact, it is often needed when you change a global. So use it liberally in your scripts. About the only time it can cause a problem is if you use it before doing something that requires FileMaker to know the cursor focus, such as before setting a portal row value into a global field. Just put it after the step.
If it is needed in a script, you can often tell because the script may work sometimes, but very inconsistently, often going to records related to the last value that the global was set at instead. It’s about the only time that happens.
Portal Refresh
It also is needed to exit the fields, so they don’t remain outlined with dotted lines, and to refresh the values in the portal. This latter used to be more of a problem, but it’s mostly fixed in the later releases.
If portal values, especially calculations, don’t refresh, try these two steps:
Enter Preview Mode
Enter Browse Mode
Another trick is to set the portal key to itself, then exit the record.
Multi-line Target Field for Relationship
I have not said yet what you are going to put in the global key field to change the records, or what the target field on the other side of the relationship is going to be. That is because I’m going to allow more than one kind of value. Usually you just have one kind, such as an ID field, matching another ID field. But you are not limited to this.
Since a particular portal can only have one relationship, and it can only match one field to one field, something extra must be done to allow more than one kind of value.
In this case I will create a multi-line field in the target file.
Each line in the field will function like a key by itself. The values entered into the global field on the other side will only match some values on one of the lines. You’ll see what I mean.
In this case the field, in the Storess file, will be a calculation field, result Text (important). Portal ID c =
People IDs & “¶” &
Left (Store, 1) & “¶” &
“1”
What?! The first line is kind of what you’d expect, except that People IDs is already a multi-line field of its own. But that doesn’t really matter, you can have lots of lines. It holds all the people that have shopped at that store.
That is the power of multi-key fields. You can match any line.
The second line calculates the first letter of the store’s name, Left (Store, 1)
The last line is just a “1,” a constant value for all records.
As you can see, there is no real “overlap” between the lines. A text value is not going to match any of the ID’s in People IDs or 1.
1 will match all of the records.
3 Ways to Change the Global Key
Everything happens back in the Events file, where the portal is.
Click the little portal graphic on the Entry view screen to see it.
As soon as you click it, the Person’s ID is entered into the Portal g key, an you are taken to the Portal layout.
The Person’s ID will match any stores that have that ID in any of the lines of the Portal ID c field, and these will show in the portal. This is the default view.
If you want to enter a store that wasn’t visible, that they had not shopped at before, then there is an easy way to look for it.
Click the box with the little letter in it, the Last g field. This triggers a script that enters the field, pauses for your input, then sets your choice into the Portal g field, then exits.
Portal g will now contain a single letter, which will match the Left (Store, 1) line of Portal ID c. All stores beginning with that letter will show in the portal.
The “All” button sets a “1” into the field. This will match the “1” in Portal ID c, which is the same in all records, so all the stores will show.
Value List Voodoo
Lastly, I wanted to cover a little problem with Value Lists, which I have experienced myself. If it’s happened to you, you’ll know what I mean. If it hasn’t yet, it’s best to be warned, as you can enter the wrong data without knowing.
It only happens when you show two fields in the value list, when you are using the “Also show values from a field,” showing a 2nd field on the right. It also only happens when the 2nd field is indexed. It usually is, as it allows that field to determine the sorting order.
If there are two entries which are not unique in the 2nd field shown, only one of them (the first) will show in the list.
If the values of the 1st field are ID numbers, which show no meaning by themselves, you can choose the ID for the wrong record by mistake.
For example, you have two people with the same name. Each has its own record, with a unique ID, 001 – John Smith and 002 – John Smith. That’s as it should be. You have further concatenated them in order to get a sorted field (LastFirst) for a value list, = Last & “, ” & 1st.
This gives you Smith, John for both, still not a problem, since the ID will keep them separate if you use the field on forms and lists, and the ID would be used for relationships. But when you format that ID field as a pop-up value list, using itself as the 1st field and the LastFirst field as the 2nd, you only get one choice:
001 Smith, John
The 002 John Smith is simply left out, since value lists always drop any duplicates in the indexed field that is used for sorting. And, since the ID is not much of a clue, you could easily end up with the wrong data.
There are a few cures. The first is to create a mirror of the 2nd field, an unstored calculation field, = Last & “, ” & First. But then it couldn’t be used for sorting, which defeats half the purpose of using it.
A better fix is to tag a unique value onto the end of the 2nd field. You could add the ID field again, = Last & “, ” & First & ” ” & ID.
This wouldn’t look so hot, but now you’d get both records and it would still sort.
001 Smith, John 001
002 Smith, John 002
This is one reason I seldom use the built-in value list. The above problem, as well as those of filtering and layout make it easier to use my list and portal methods outlined above.
An new feature of FileMaker 5, basing value lists on a relationship, will make them more flexible in these situations. I have read that it allows you to choose which field to use for sorting. You could then probably use any unindexed field for sorting, or, if necessary, use a self-relationship based on a Constant =1 field to create a related unindexed “mirror” field. It might fix this problem; but it might not. I’ll let you know when I get it (I’m still waiting for the Developer Edition, due soon).
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.