The Blank Portal Row
Part 1 of 2
By John Mark Osborne (jmo@databasepros.com)
BONUS FILE: PORTAL1.FP3 and RELATED.FP3
PLATFORM: Macintosh & Windows
Portals are a great feature, but they weren't designed very well. They don't slide properly when you print, they don't display horizontally, they loose their position and default to the first portal row, don't offer horizontal scroll bars and to make them sort you have to sort the entire relationship. Well, I can't solve all your portal dilemmas but here I'll solve a couple of them in this two part article.
The Problem
When you check the option to "allow creation of related records", in a relationship, a blank portal row will be added to any portal based on that relationship. Great idea for power users! Bad idea for novices. Novices don't understand that the blank portal row allows them to add new related records. Even if your users are a bit more savvy, there may be enough occupied portal rows so that the blank row is hidden by the scroll bar. Any confusion, no matter how slight, is bad.
The Solution
Your first thought might be to add a button titled New Portal Row. The attached script would be very simple:
Go to Portal Row [Last]
This will go to the last portal row, and since that portal row is the empty row, the user will feel he/she is creating a new entry. A simple solution and you have solved the problem. The only issue remaining is users don't understand why the blank portal row is there - especially if they haven't clicked the New Portal Row button. It may seem like a minor issue, but every call a user makes for technical support is one which could have been prevented. Even if they call just to ask why the blank row is there, you've hindered your interface design.
The Real Solution
A better idea would be to never show the blank portal row until it is needed. There are many ways to accomplish this task. You could create a script that goes over to the related file, creates a new record, places the correct value in the match field, returns to the main file and goes to the last row where the new empty portal row will show. What isn't as advantageous about this solution is it requires two scripts. This doesn't really slow down the script, it just makes it harder to track down next time you review it. If possible, I try to avoid the Perform Script [External] script step. Let me show you a different approach to solving this problem. By the way, if you'd like to see the dual script approach, see the Bonus file and look at the script titled "Create New Portal Row [alternative]".
A Different Approach
The first thing you need to do is create a new global text field. In the Bonus file titled "PORTAL1.FP3" I have called this field "_Match". Let's take time out to understand why this field has an underscore in front of it. Instead of using the standard lowercase "g" at the beginning of all global fields, I have opted to use a naming convention Matt Petrowsky showed me. I like it for two reasons. First, the underscore sorts the global fields to the bottom of any field dialogs (e.g. Define Fields) instead of grouping them in the middle of all the regular fields. Secondly, the underscore differentiates global fields from regular fields better than the lowercase "g". Take a look at a long list of fields and you'll see that the global fields stand out far better with an underscore at the beginning.
Okay, that tangent took me a little longer than I thought. What you should have in your database is one new global text field named "_Match". This global field will be used in a new relationship that parallels the existing relationship attached to your portal. The new relationship should use the "_Match" global field on the left side and the match field in your related file. This relationship should have the ability to create new related records. Which reminds me! Go to Define Relationships and uncheck the "allow creation of related records" option from the relationship which is displaying your portal records. If you are at all confused about the new relationship you need inspect the relationship in the Bonus file titled "Add Portal Row".
Now all that's needed is a script. Let's show you the entire script before we describe it:
Set Field ["Add Portal Row::Match", "_Match"]
Set Field ["Add Portal Row::Match", "Match"]
Set Field ["Match", "Match"]
Go to Portal Row [Last]
The first Set Field script step creates a new related record by setting the "Match" field in the related file to the contents of the "_Match" global field.
Set Field ["Add Portal Row::Match", "_Match"]
Remember, the "_Match" global field is used in a relationship that allows the creation of related records. If the "_Match" global field contains a value that doesn't exist in the related file, a new related record will be created. The value in the "_Match" field is "zzz". You can put anything other than a number in this field since all the normal match values will be serial numbers. Therefore, "zzz" will never be found in the related database and will always cause the creation of a related record.
The next Set Field script step removes the "zzz" from the match field in the related file by setting it to the match value of the current master record.
Set Field ["Add Portal Row::Match", "Match"]
This step takes some time to wrap your brain around. The "Add Portal Row" relationship connects the global "_Match" value to whatever related record(s) have a "zzz" in the "Match" field. Even though the Set Field script step is changing the match value in the related database, it can still use the existing connection to locate the record that matches "zzz". Once the second Set Field script step is finished, you have a new record in the related file that links to the current master record.
Now all you have to do is refresh the relationship. That's what the third Set Field script step does.
Set Field ["Match", "Match"]
The easiest way to refresh a relationship is to set the Match field to itself. This forces a reevaluation in FileMaker.
The last script step simply selects the new blank portal row so data entry can occur.
Review
Let's review the steps quickly. There aren't a lot of them but they can be confusing:
Set Field ["Add Portal Row::Match", "_Match"]
Creates a new related record using the global relationship with "zzz" as the match value.
Set Field ["Add Portal Row::Match", "Match"]
Changes the match value of "zzz" on the new related record to match the current master record match value.
Set Field ["Match", "Match"]
Refreshes the relationship.
Go to Portal Row [Last]
Selects the last portal row, which is now a new portal row, to facilitate data entry.
Is that It?!?
At this point, you may be wondering why I come up with wacky solutions like this one. Why not just go with the two-script system and save a lot of time. The answer is that this solution helps you think outside the box. Don't always select the most obvious solution. Try to create several approaches to solving a problem and then pick the one that meets your needs best. I happen to like this solution because it puts all the functionality in a single script. Maybe you'll use the standard solution. Whatever you do, consider the advantages and disadvantages of both approaches before you choose.
Happy FileMaking!
## END ##