Refreshing Related Fields
By John Mark Osborne (editor@isoproductions.com)
http://www.filemakerpros.com/
BONUS FILE: INVOICE.FP3, PRODUCTS.FP3, LINEITEM.FP3
PLATFORM: Macintosh & Windows
If you've been programming FileMaker long enough, you've probably seen quite a few portals, related fields and calculations (calculations that reference related fields) that don't refresh properly. There are many techniques for refreshing relationships, but all of them require a script. I've seen lots of techniques for refreshing portals, but none as good as the one I am about to describe. This solution automatically refreshes troublesome relationships without the need for a script.
Go to Field
The most basic script approach to refreshing a portal is to mimic what you would do manually. All you need to do is click into a field and it will be refreshed. So, a common technique is to use the Go to Field to enter the field that is not refreshing and then exit it with the Exit Record/Request script step. This works pretty well, but is not very elegant since it flashes the screen when the cursor bounces around from field to field.
Set Field
A more sophisticated technique is to refresh a relationship by setting the match or key field to itself. For instance, if you need to refresh a relationship that uses the Match field as a key field, your script would look like this:
Set Field ["Match", "Match"]
This script is significantly more sophisticated than the previous. First of all, the cursor doesn't jump around on the screen. In fact, the cursor will stay exactly where it is. This enables the user to refresh the portal, related field or calculation without interrupting their work flow. An even better reason for using Set Field is it's ability to modify fields that aren't even on the current layout. This enables you to omit the match or key field from the layout and use the screen real estate for more important things.
Auto Refresh
Okay, that's enough of the old methods for refreshing relationships. Let's get on with the real reason for this article. But, before starting, let's make sure you understand that this solution is not a cure for every relationship refresh problem. You may need to use a script from time to time. Just use the best solution for the job.
I first saw this technique back when I was the technical lead for FileMaker at Claris (3 or 4 years ago). Dave Heiber was attempting to get some calculated fields that were based on a relationship to refresh. The problem was that FileMaker didn't want to update a change to a related field more than on level deep. Since the calculated fields referenced related fields that were based on the result of another relationship, he needed to click into the field to update the values whenever a change was made. Dave's solutions was to place a portal around the calculated fields in order to force an update.
About a month ago, a FileMaker programmer by the name of Flaviano Crespi crespifl@tin.it sent me a file called Wake Up which is posted on my web site in the Bonus Files area. He applied the same technique but to update fields in a portal. Flaviano placed two portals on top of each other. This rekindled my interest in this area of FileMaker and I began experimenting with portals and I understand a little more about the original solution that Dave Heiber created. While it is related to the solution by Flaviano Crespi, I am going to leave the explanation of his technique to the file on my web site. Besides, what is important in this article is the concept and not the specific implementation. Learn the concept and you will be able to apply it to your unique scenario.
By now, you probably have a pretty good recollection of a situation where you couldn't get a related field to refresh and just want to know the solution. Well, the solution is really no more difficult that placing a portal on a layout.
To make the solution easier to understand, take a look at the Bonus files that accompanies this article. There are three red filled calculation fields and three green ones. Try changing the Quantity field in the portal for an existing line item. Notice that the green calculation updates the appropriate type or category total, but the red calculation doesn't unless you click into the field. The reason the field doesn't update in the red calculations is because of a two level deep relationship. When you change the Quantity field in the Portal, FileMaker updates any fields within the same relationship. Since the total fields are based on a second relationship, they are never triggered when FileMaker updates the relationship, even though they use the Quantity field in their calculation.
In order to update the green calculations, an invisible portal is placed around the fields that aren't updating. The portal is set to have a fill the same color as the background so as to appear invisible. The relationship that is attached to this portal is the same one that is used for the portal where the update to the Quantity field is being made. Whenever a change is made to a field in the portal, it refreshes anything within that portal. Since a portal based on the same relationship is surrounding the green calculations, they get updated along with the other values in the portal.
Now for the part that I figured out. When I was working on this solution, I couldn't get modifications to the Quantity field to update the green calculations. I knew I had everything programmed correctly, but it just wouldn't work. I must have played around with the solution for a few hours trying to decipher where I went wrong. Then it finally hit me when I made a change to the Type field and I saw the portal around the green calculations flash. The Type field was updating the portal because it was used in a calculation in the related field. So, all I did was make a simple change to an existing calculation in the related file. A calculation called Match by Type in the LINEITEM.FP3 file used to look like this:
Invoice # & Type
I changed it to the following:
Case(IsEmpty(Quantity), Invoice # & Type, Invoice # & Type)
The additions to this calculation makes absolutely no sense. It always results in the same value whether the Quantity field is empty or not. However, it forces the portal to update when this field is modified so that any calculations based on it can also update. You are tricking FileMaker into updating when you want.
Again, make sure you understand the concept and not the specific implementation. The idea is to understand FileMaker so you can better implement solutions that meet your needs. You may use this technique in an entirely different way than I did. In fact, I encourage you to download the Wake Up solution from my web site so you can get more exposure to the technique. Happy FileMaking!
# # END # #