Calculation Fields and Storage Options

[Neville310]

I am working on an interface solution and have a question about calculated fields. My database has calculated fields for displaying tab graphics and the related labels. These calculated fields do not seem to update automatically. In fact, their behavior is not predictable; thus they are broken. The situation has me scratching my head as I have elementary knowledge about these intricate calculation fields. Upon reviewing another interface example, the calculated fields have both check boxes under “Storage Options.” Select a calculation field, then [Options]; then [storage Options].

 

Has A Checkmark Use global storage (one value for all records)

Has A Checkmark Do not store calculation results – recalculate when needed

 

The calculation script is correct since I copied it from the example file. The only difference between my calculated fields and the example is the “Storage Options.” Mine has “Use Global Storage” checked while the example has both. Apparently, Filemaker does not allow me to select both options; it deselect one when you select the second option. Does this behavior sound normal? How does one select Global storage and recalculate at the same time? I tried re-selecting both on the example file and Filemaker did allow me to re-select both options again. How do calculated fields act in Find Mode? Do my graphics disappear?

 

Let me know if you have any additional insight on my situation.

[comment]

[ QUOTE ]

Upon reviewing another interface example, the calculated fields have both check boxes under “Storage Options.”

 

[/ QUOTE ]

 

Where I can see this example file?

[CobaltSky]

Hi Neville,

the option to define a field as both unstored *and* global is reputed to have been an unintended quirk of earlier released of FIleMaker 7, and has been removed from the current rev.

 

Notwithstanding the 'example' you refer to, it would generally not be a great idea to define calcs for your interface using unstored calculations, because as you have correctly guessed, they will not show in find mode (calculations do not operate in find mode, instead calc fields are thrown open to form part of the find criteria).

 

If you want to use global calculation fields, I suggest that you explicitly force them to update during the start-up script./ For instance, include a reference to a 'trigger' field in their formulae, eg:

 

If(gYourTriggerField; gTheFieldYouWantToReference)

 

then have your start-up script write a positive numeric value into the gYourTriggerField field. That will cause all the global calcs which are constructed in this way, to refresh.

[comment]

Ray,

 

could you explain what "global storage" for a calculation field means, and what might it be used for?

[CobaltSky]

Hi Comment,

Global storage - for any field, including a calc field - means first and foremost that the field has one value for all records in the table (rather than a discrete value for each record). But it also means, among other things:

 

- that it can be accessed from any other table without requiring a relationship

- that it can't be indexed

- that it will hold its value in find mode

- that if used as the destination key for a relationship, the relationship will return a cartesian product (all records)

- that the value the field holds will be specific to each user in a hosted solution (and will be lost at the close of the session)

 

Because of the way global fields behave, they can be very useful. The fact that they can be accessed from anywhere on the relationships graph without a relationship is alone a significant reason for their use.

 

As to global *calculation* fields in particular, one of their most frequent and obvious purposes is to return a calculated result that is dependent on the values of other global fields - without forfeiting the 'global' properties and capabilities of the field.

 

It is also interesting to note that global calcs which reference standard data fields take their value from the most recently edited record on the current workstation. This too has its uses for interface, audit and control purposes.

 

But let me give you one example which may be on point for this thread. If you set up a series of different color swatches in successive repetitions of a global container field, then est up a global number field and a global calc with the formula:

 

gContainer[gNumber]

 

- users will be able to enter a number into the gNumber field to indicate their color preference, and the corresponding repetition from gContainer will be returned.

 

Since no relationships are required to access the glonal calc from anywhere, you might use it to provide the background of the layouts throughout a solution. When each user enters a number into gNumber, he/she will see backgrounds of the color of her/his choice for the duration of the current client session. This particular setup has a number of advantages over a similar arrangement which uses an unstored calc, because a) the calc is set only once, not recalculated every time the screen refreshes, and b) no relationships are required to retrieve gContainer.

 

But there are a legion of other examples...

[comment]

Thanks. If you don't mind a clarification:

 

Two global field, say gContainer and gNumber, two calc fields with the same calc, say gContainer[gNumber]. One of the calc fields is defined to be unstored, the other is defined to be global. What, if any, difference would there be between them?

 

Just to clarify, I got the point regarding global calc referencing non-global fields; I am asking about a global calc referencing global fields only - or none at all, e.g. Random.

[Neville310]

After turning off the Global option, the calculation field began performing as expected.

 

Now, the layout shows the tabs graphics and appropriate labels. The tab graphics and labels change when you switch layouts. But the layout does not gracefully go into Find Mode. The tab graphics and labels disappear in Find Mode. The interface example does not have the same Find Mode behavior; yet my solution and the example have similar construction by design. I can not help deduct that this scenario relates to the “Storage Options.”

 

The interface example has both “Use global storage” and “Do not store calculation results” checked. How come? How did the developer achieve both boxes checked.

 

The second situation occurs when a layouts show records from another table outside the table with the navigation calculations. The calculated fields now display “Unrelated table.” Again, my explanation may be ambiguous, yet people familiar with interface solutions should have an idea. Most interface solution face similar challenges like Find Mode and data separation.

 

Let me know if you could lead me in the right direction. Most comments have been helpful, yet have been general about global and calculation fields. Again, I am trying to recreate the example solution for educational reasons; not find a workaround. Another approach will come later when developing my own solution. Please PM me if you would kindly review both databases and comment on their differences. Thanks again!

[CobaltSky]

comment wrote:

Thanks. If you don't mind a clarification:

 

Two global field, say gContainer and gNumber, two calc fields with the same calc, say gContainer[gNumber]. One of the calc fields is defined to be unstored, the other is defined to be global. What, if any, difference would there be between them?

 

Just to clarify, I got the point regarding global calc referencing non-global fields; I am asking about a global calc referencing global fields only - or none at all, e.g. Random.

 

 

There are several differences.

 

One quite significant difference is that the global calc is stored, so it is calculated only once, whereas the unstored calculation must be re-evaluated each time it is to be displayed - which can contribute to a big performance hit if you have a lot of unstored calcs - or some which are fairly complex or are dealing with large amounts of data.

 

Another key difference is that a global calc can be accessed from other tables without requiring a relationship, whereas an unstored calc cannot - requiring a separate relationship for every other table occurence from which users (or scripts) may wish to access it.

 

A third difference, as mentioned in my previous post, is that unstored calculations do not show a result in FileMaker's 'find' mode - and in a number of situations this may prove problematic.

 

There are of course, situations when an unstored calculation is the more appropriate choice, just as there are a number of situations others where a global calc may be preferable.

[CobaltSky]

Neville310 wrote:

Let me know if you could lead me in the right direction. Most comments have been helpful, yet have been general about global and calculation fields. Again, I am trying to recreate the example solution for educational reasons; not find a workaround. Another approach will come later when developing my own solution. Please PM me if you would kindly review both databases and comment on their differences. Thanks again!

 

 

The ability to designate a calculation as both unstored and global was a by-product of a quirk in one of the early releases of FileMaker 7. It is not supported in the current release (7.0v3)

 

If you would like to see this quirk become a supported feature in a future version, I suggest that you pay a visit to the 'Product Feature Suggestion' page at the FileMaker Inc web site:

 

FMI Feature Request Page

[comment]

Many thanks. The point about a global calc being stored - in spite of referencing other globals - could be indeed very significant. Never gave much thought to using a global calc, but I will from now on. Thanks again.