Calculation Field vs Calculated Value vs Lookup

[Thomas An]

Hi all,

Although I have been using Filemaker for almost a year and have some instinctive understanding of the various field types... it seems that on second thought I do not really understand some things in regards to fields on the deeper level.

For example:

1. What is the difference between a calculation field being unstored versus a calculation field being stored ?

2. What is the difference between a stored calculation field versus a number-field having a calculated value with the "do not replace existing data" unchecked ?

3. What is the difference between a number-field having a calculated value (and the calculation is simply a reference to another field) ... versus ... a lookup value ?

Are there any references you could point me to, that have these things juxtaposed and explained ?

Many Thanks

Thomas

[Genx]

1. What is the difference between a calculation field being unstored versus a calculation field being stored ?

... Well exactly that. Unstored calculations don't actually store the result of the calculation but calculate only when they are needed e.g. when they are on a report, or on a current layout -- Where as stored calculations store the result and recalculate whenever a field that's referenced within them changes -- These actually require space within the database because they store the info. Further unstored calcs can't be indexed and so can't be used on the child side of relationships and for the same reason take longer to search over.

2. What is the difference between a stored calculation field versus a number-field having a calculated value with the "do not replace existing data" unchecked ?

Not much i suppose...

3. What is the difference between a number-field having a calculated value (and the calculation is simply a reference to another field) ... versus ... a lookup value ?

You can trigger a "re-lookup" using a lookup field but that's about it.

Anyone can feel free to correct me but i think that's most of them.

[comment]

2.

An auto-entered calculation is STORED - even if referencing a related field (or a global field, etc.). Of course, it will not re-evaluate when a related field is modified. But something like:

Localfield + Relation::Relatedfield

will recalculate every time Localfield is modified, and the result can be indexed and used as a match for another relationship.

In addition, an auto-entered calculation can be overwritten by the user - provided it doesn't reference itself.

Edited January 16, 2007 by Guest

[Genx]

In addition, an auto-entered calculation can be overwritten by the user - provided it doesn't reference itself.

I was going to mention that except i re-read the statement the op mentioned -- "do not replace existing data" unchecked ?

Re: the Localfield + Relation::Relatedfield being storable but not necessarily updatable -- The use never occured to me -- This will get me out of a couple of squeezy spots... Heres a question for you comment -- What if it references an unstored calc? Will it update when the unstored calc ... refreshes -- I suppose it's not likely but..

[comment]

An auto-entered calculation can be overwritten by the user, even if it's set to replace existing data - provided it doesn't reference itself. Of course, if a referenced field is modified again, the user's input will be lost.

If the field references an unstored calculation, it will update the CURRENT RECORD when the unstored calculation refreshes - same as with a global field, I think.

[Genx]

Cool, thanks comment :

[Thomas An]

1. What is the difference between a calculation field being unstored versus a calculation field being stored ?

... Well exactly that. Unstored calculations don't actually store the result of the calculation but calculate only when they are needed e.g. when they are on a report, or on a current layout -- Where as stored calculations store the result and recalculate whenever a field that's referenced within them changes

Well, a "need" occures when something changes somewhere. Doesn't this mean that stored calculated fields still recalculate as needed ? (at which point, I am still not sure what the practical difference is, other than internal storage, maybe ? ... so the behavior is identical other than saving space, but being slower versus not saving space but being faster ?)

Further unstored calcs can't be indexed and so can't be used on the child side of relationships and for the same reason take longer to search over.

ahh, thanks for this. Yes, I have encountered the child indexing issue.

So to summarize (and please correct me)

Calculation field properties:

1. User cannot edit at run time (their value is driven entirely by their calculation)

2. Stored or unstored behave virtually identical except when it comes to being the child in a relationship

3. Stored takes physical space but sorts faster (?) whereas unstored is computed on the fly (memory) but slows performance on large files

2. What is the difference between a stored calculation field versus a number-field having a calculated value with the "do not replace existing data" unchecked ?

Not much i suppose...

In addition, an auto-entered calculation can be overwritten by the user - provided it doesn't reference itself.

aha !

So, an auto-enter calculation is identical to a stored calculation field with the exception that the user can (temporarily) edit the contents ... until something triggers a reculculation; at which point the user input is lost. Right ?

3. What is the difference between a number-field having a calculated value (and the calculation is simply a reference to another field) ... versus ... a lookup value ?

You can trigger a "re-lookup" using a lookup field but that's about it.

gotcha. The only benefit is the trigger menu entry to "Re-lookup field contents".

So, they could have a menu entry saying "Re-calculate auto-enter calculations which have their DoNotReplaceExistingValue option checked" ... at which point we could remove the "lookup" option from filemaker without any loss of functionality. Right ?

[Fitch]

Lookups, either via field options or the Lookup functions added in FileMaker 7, also have the options to lookup the next higher or lower value, or a default value when the specified match fails. That can be useful (e.g., price breaks based on quantity).

FWIW, these days I try to avoid auto-entry options if possible, except for primary key fields. I like the control of setting values in new records using scripts.

[Thomas An]

Fitch, GenX, Comment,

Thank you all for your insight / responses / comments. It helped quite a bit... and it is much appreciated !

[comment]

Stored or unstored behave virtually identical except when it comes to being the child in a relationship

I wouldn't say so. The difference is more fundamental. An unstored calculation is evaluated every time it's needed, be it for display, or when referenced in another calculation. This includes relationships that use an unstored calculation as the parent key.

unstored is computed on the fly (memory) but slows performance on large files

Not necessarily - it depends. Eliminating the need for indexing could speed things up in some circumstances.

an auto-enter calculation is identical to a stored calculation field with the exception that the user can (temporarily) edit the contents

Technically yes, but the implications are wider than just that. For example, it allows you to modify user's entry in the same field, or make two fields depend on each other in a circular fashion.

they could have a menu entry saying "Re-calculate auto-enter calculations which have their DoNotReplaceExistingValue option checked" ... at which point we could remove the "lookup" option

Unless someone wanted the possibility to relookup their lookup fields, WITHOUT re-calculating their auto-enters....

I think you could benefit by reading this very long thread.

[Thomas An]

I wouldn't say so. The difference is more fundamental. An unstored calculation is evaluated every time it's needed, be it for display, or when referenced in another calculation. This includes relationships that use an unstored calculation as the parent key.

In that case I am still not clear on this (sorry for being somewhat thick)

I can't picture a situation where the result from a stored would show different from a result of an unstored calculation.

In my mind, when an unstored calculation "needs" to change, then so does a stored one.

Unless someone wanted the possibility to relookup their lookup fields, WITHOUT re-calculating their auto-enters....

[comment]

I can't picture a situation where the result from a stored would show different from a result of an unstored calculation.

A calculation using Get (CurrentDate) would be an obvious example.

[Thomas An]

A calculation using Get (CurrentDate) would be an obvious example.

hmm, you are right ... but the result baffles me.

In the above example, the "time" is an unstored calculated field. The two fields bellow it are simply referencing the time field. In this case both the stored and the unstored are updating simulataneously (because the field they are referencing is changing)

Now, the two bottom fields contain the get(currentTime) on their own ... but now the stored calculation does not update even though the result of its own calculation keeps changing values.

This is getting too subtle (and I need to be careful in the future). So, the stored calc updates only if it contains a reference to another field ... and the contents of that other field change.

[comment]

the stored calc updates only if it contains a reference to another field ... and the contents of that other field change.

I think that is basically correct - at least no obvious exception to the rule comes to mind.