Globals & Filtered Portals
Example file: Eats
Filtered Portals
The basic problem with Portals is how to get the records you want to show in the portal. The difficulty is that we tend to think of the ID, or key, of the records we want to see rather than the “encompassing” ID of the portal, which will be quite different. In other words, we tend to think of what identifies each record rather than what identifies the group of records we want to see. The key for the portal will have to be something that is the same for every record we want in the portal, but not shared by any records not in the portal.
We may even have to define a calculation field and/or set a field, often a global, to get a custom key for the portal, in either the current file, the portal’s records file, or both.* This is a “filtered” portal.
* The global would only be set in the current file; you usually can’t have a global, or any unstored field, as the target of a relationship. Regular fields can be set in either file; though modifying a key in the records of the second file would require a Replace operation, and is beyond the scope of this article.
Remember that portals are based on relationships; and that relationships are pretty much independent of the Found set of records in any file. They are only dependent on what the key is for the relationship. This key can either be a regular field in the current record or a global field, which is, by definition, the same for, and available to, all records in its file. The portal contains the records that match the key on the other side of the relationship, either in another file, or, in the case of a self-relationship, within the same file, no matter what records are currently found.
The rest of the article will attempt to illustrate filtered portals using examples in last month’s sample database, Eats, beginning with the main file Food, on the layout Food Choice.
Groups Portal
The first portal shows a list of Food Groups. They are the records from another hidden file, Groups, which has one record for each group.
The portal is not based on the relationship Group ID::Group ID; it is based on the Constant relationship (basically 1=1 for every record). This relationship relates any record in the Food file to all of the records in the Group file. Hence it is the “encompassing” relationship, allowing all the food groups to show. It would be an “unfiltered” portal (if there was such a name).
Foods Portal
Clicking on a group name in the portal sets a global field in the Food file (using the portal Constant relationship), the Group ID g field.
This global field has a relationship to a Group ID field that has been manually entered in every Food item just after record creation.*
So setting the global, then exiting the record, refreshes the list in the second portal, which shows the food items that are in that food group.
In this case the Group ID is the encompassing ID, and the food items are the records showing within it.
I’ve also created a relationship between the global Group ID g field and the Group ID field in Groups. This allows me to show the Group name in a field by itself just above the Food portal, so you get immediate feedback on what choice you made in the first portal. The thing to remember here is that by using globals to make the choices, you are able to change the view of the records, but as yet you haven’t either made any actual changes to any of the records, or even moved away from the current record. That is what makes globals and portals a good match.
Food Self-relationship
*There is a wrinkle. Foods is actually a “join file.” I’ve allowed multiple entries of each food, one entry for each occurance in a “meal,” e.g., butter is entered 3 times, Breakfast, Lunch, and Dinner. I’ve had to create a self-relationship and use it to “lookup” a mark (1) in the field FoodIDMark for the first entry of each food (see earlier articles).
Filtered Group ID
A calculation, If (FoodIDMark, Group ID, “”), puts the Group ID in only those first ones. The portal uses that field for the Group ID.
It is needed so there aren’t duplicate food items showing in the portal. Otherwise you would just use the Group ID.
You could create another file to hold the multiple instances of each food. But I found that all the fields I wanted for a food would work with this self-relationship. The number fields, Amount (on hand), Threshold (below which it was time to buy), and the calculation field Buy; If (Amount<=Threshold, 1, “”), are the same for all instances of each food. If I had 1 cube of butter, I’d have 1 no matter whether it was breakfast or dinner. You can modify the number field Amount, but you can’t modify Buy; it is a calculation.
One of the advantages of portals, versus other value list choice methods, is that not only can you show several fields at once (vs. 2 at most for defined Value Lists), but you can combine navigation (clicking on the name), data input and visual feedback all in the same portal. The disadvantage is that they take up a lot of space on the layout.
Constant=::Buy Portal
The last portal is an example of a Constant=::Mark relationship. The Constant=1 field is related to a Mark or Flag field (any field which contains 1 only for certain records, entered either manually, or by calculation or script). In this case it’s the Buy field. It shows, independently of the other portals, food items that are below their threshold–a shopping list.
Refresh Problem
You’ll notice a “Refresh” button below the portal. There is often a problem with calculations and portals. A recalculation doesn’t always show until somehow the portal is forced to refresh. There are clever ways to do this when defining the calculation, but in this case none of them worked.
If you change the Amount of a Food in the Food portal so that it falls below the threshold, the Buy checkbox will be checked. But it won’t show up in the Buy portal until it refreshes.
A simple way to force this is a script with two steps:
Go to Preview Mode [] <–no pause
Go to Browse Mode
It just takes a split second and always refreshes the portal, so the food shows up, or goes away (if the Amount is over). But it does require a button if you’re not already running a script.
Concatenated Keys with Portals
So far we’ve been using a single ID or value field as the key for the portal relationship. It is possible to filter further, using a concatenated key of 2 ID fields put together, to show only those records where both ID fields are present.
Meals File
This is what I’ve done in the Meals file. The first portal is just another Constant relationship, showing all the meals. A choice of meal sets the global Meal ID g. This is related to the Meal ID in Foods, and the fields in the portal are the Group ID and Group name in Foods. The Group name is actually a calculation, =::Group name in Groups, since the name isn’t in the Foods file.
The 3rd portal uses a concatenated global field, Meal-Group IDg
,
=Meal IDg & ” ” & Group IDg
It is related to a field in Foods, Meal\If\MealGrpMark
, which has a few steps in its creation (as you might guess from its name):
The first step is simple, but has a twist: Meal-Group ID
= If(IsEmpty(Meal ID) or IsEmpty(Group ID), “”, Meal ID & ” ” & Group ID)
The reason for the “IsEmpty” is to correctly “lookup” a first entry mark (below). Otherwise the lookup occurs as soon as you exit the first field, Meal ID in this case; it doesn’t wait for you to enter the Group ID.
The second step is to mark the first entry of every unique value in Meal-Group ID. Meal\GroupID\Mark, number, is a lookup. Using a self-relationship on Meal-Group ID it looks up a 1 for a unique entry. Here are some examples of unique entries; breakfast dairy, breakfast grain, lunch dairy, etc.
The last step puts the Meal ID in a field for those unique entries. Meal\If\MealGrpMark= If (MealGroupIDMark, Meal ID, “”) The reason for all this is simple actually. The Meal is the encompassing ID. You choose the Meal in the Meal portal. But for the correct groups to show in the next portal, you need to have the Meal ID in only those records in Foods that are the first unique entry for that Meal ID and Group ID. If the Group does not have any entries for that Meal, you don’t want it in the portal. If the Group has more than one entry for that Meal, you only want one of them.
There is probably a way to do this by using another “join” file for Meals & Groups. But I tend to keep things in the fewest number of files. In this case I started with the list view in Foods, keeping all the data entry there, even for the Meal and Group files. You can experiment with it.
Global Concatenated Key
When you choose from the first portal, you set the Meal g field. That filters the choices in the Groups portal. When you choose a Group, it sets the Group g field. This completes the Mealg-Groupg field. It matches the encompassing field for the Foods portal, which in the Foods file is just the Meal-Group ID.
[Correction: In the example file it’s Meal\Grp\If\FoodMark, =
If(FoodIDMark, Meal\Group ID, “”)
This works, but is overkill. A particular food is never entered twice for the same meal. Duh! I forgot. You can edit the relationship to point to Meal-Group ID in Foods instead, then delete the above field. It works just the same and saves disk space.]
Clear the Portal, Matey
The script for the first Meals portal has a step at the end to clear the Group IDg field; Set Field [“Group IDg”,””]
This empties the Food portal on the right (since nothing matches just the Meal ID g).
Use this technique for the first portal whenever you have three or more linked choice portals, otherwise you’ll have values still showing in the third one from the last time it was used; but they won’t be correct, and it won’t be obvious that they aren’t.
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.