How to update auto-calc field or stored calc

[LingoJango]

Hi,

 

I know I've read a thread about this in here - I may even have taken part in it - but I simply can't seem to find it.

 

I have an Auto-Enter, replace existing contents calc field which I used so that I could store and index information from a related table. How do I get it to update?

 

For some calcs which do not require the List function I've tried using the Lookup function in a calculation field instead of an auto-enter regular field, but I come up against the same problem. How do I get a stored calculation field to update? I've tried using Evaluate, but that only seems to work when it references a local field.

[AHunter3]

That's correct.

 

If you want it indexed, it can't be a live calculation; but if it's not a live calculation and is instead a locally stored field that acquires its info through some flavor of auto-enter calc option, there's no simple means of ensuring that it reflects totally current data from the other related tables. Evaluate will not readily fix that. You cannot (unfortunately), for instance, use a non-stored calc in the local table as the trigger field for Evaluate to occur.

 

If this were not the case, the entire phenomenon of related values not being indexable would sort of go away. (i.e., FileMaker would probably just have it happen, or would offer it as a field option, "index from vantage point of related tables").

 

Workarounds:

 

a) a calc field (unstored, unindexed) that reconciles to 1 if the local value does not match the external factors that it was designed to reflect; you run a script that finds them and does either a looping Set Field or a Replace Field Value on the field to update it just before needing it to be current for some report or process.

 

b) use a lookup field and use Relookup just before needing it to be current for some report or process

 

c) restrict the data entry process in the various related table::fields so that their operation is scripted, or so that various final record checks or signoffs or official "OKings" is accompanied by a refresh routine that cc's their data to the field in the original/local table that needs to stay current

 

d) use a plug-in such as ZippScript to fire up every time one of the source fields gets updated; it calls a script that updates the field in the original/local table each time one of the referenced fields gets changed

[LingoJango]

Thanks. I had attempted d) but the interface interaction was awful, so I guess I'll have to go for c or a.