Which one is better depends on the situation.
In general, doing something unnecessary is always good to avoid.
Unstored calculation gets recalculated whenever FileMaker things its value might have changed.
So if you're going to use the relationship multiple times then global field may be more efficient.
Global field will also let you force the relationship to refresh.
With global field you have full control over when it gets updated.
On the other side, if you use an unstored calc, you can completely avoid scripting.
If you worry about the overhead of setting the relationship keys unnecessarily in the on-open script then I would suggest you use global fields but don't set their values until you really need them.
However, in that case you need to set a good and reliable rule how to find out when you need to set the key.
You may also want to consider using stored calculation fields placed in a session table. One benefit of this approach is that the stored calc can be indexed, so when you connect tables A and B to the same key field, you can access the table A from table B and table B from table A through this relationship.
I have created a simple test database to compare these three approaches. Using the word "test" as relationship key, looping through 100 local records, accessing 100 remote records and calculating Sum from them took 0.032 sec using global field, 0.1 sec using unstored calc, and 0.1 sec using stored calc when tested for the first time after opening the file. Then every following try took around 0.017 sec using either method. After changing the relationship key to a more complex calculation, using global field took only 0.03 sec, using unstored calc took 0.186 sec and using stored calc took 0.105 sec the first time after opening the file. Then every following try took around 0.016 sec using either method, probbaly due to caching.
So using global fields is the fastest approach in most cases and the overhead of setting the global field is almost equal to the overhead of the calculation you have to evaluate to get the value you actually want to store into the field.
The keys in my case are meant to be simple constants. E.g. _kf_ID = "ACT"
There are a couple tricks to set globals permanenetly without scripts, but I don't really want to rely on those unless the globals are certain to be significantly faster and if I don't want every client and PHP connection to have to set all those globals everytime.
I think there is only one way to set global fields permanently - doing so while having the file open directly in FMP or FMPA.
However, that may be sufficient reason for not using that, especially if you develop on a server.
As you can see from my test, using globals can be 3 times faster even when using a simple constant, at least until the relationship gets cached.
I have also tried globally stored calculation and it's as fast (slow) as the regular stored calc.
The important thing is that using the relationship via global field is not 3 times faster only when accessing it for the first time. My test shows that the whole loop going through 100 records is 3 times faster. So it seems that what matters is whether the relationship is accessed for the first time from that particular record. The two approaches become equally fast only when accessing the same relationship again from the same source record.
1 of 1 people found this helpful
Why not do a check in your opening script to see if the file is being accessed by CWP, and if not, skip the global setting?
When developing on Server, you can convert a global calculation to a text (or other) global field, and the last calculated value will stick.
You've got me! Wasn't aware of this one. Neat trick!