Storing a calc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rans - 26 Feb 2004 15:05 GMT

Hello,

To make my question simple, i'm looking for a way to store a
calculation field that can't be stored directly by Filemaker. I want
to store the result in another field that can be indexed to make a
relationship. A constraint: it has to be dynamic, just like a calc.

A solution i tried is to store the calc in a lookup field by forcing
the relookup with the calc is changed. Nice theory, but it doesn't
work. It works fine with indexed fields, by doing a self-join using a
calc as the related field. In this calc, i do something like :

Case(Name = Name, 1, 1) & Id

I'm referring to the field "Name" in the calc, so the calc is
refreshed when i modify the field "Name". Since the self join is made
on this calc, and that i made a lookup that is using the self-join,
the relookup is forced and it works fine.

However it doesn't work if a replace the field "Name", which is
indexed, by a calculation that is not stored. So that i have :

Case(SomeCalcField = SomeCalcField, 1, 1) & Id

There is no forced relookup when SomeCalcField is changed,
unfortunately. Any work around for this? Or any other way to
dynamically store a calc in another indexed field when the calc is
changed? Thanks for any help.

Matt

Reply to this Message

Lynn allen - 26 Feb 2004 16:38 GMT

> Hello,
>
[quoted text clipped - 24 lines]
> dynamically store a calc in another indexed field when the calc is
> changed? Thanks for any help.

Sorry, Matt, you've run into a basic property of the software. No result
or reference to an unstored calc can be stored.

What we do where we need stored data in such a situation is to manage
setting the stored field through a script. Since all our navigation is
through scripts as well, we integrate our data management into the
navigation. When a user navigates to or from a screen where the stored
data is different from the unstored, the stored data is updated.
Sometimes we give the users a warning onscreen and a Refresh button so
they can trigger the update themselves.

In situations like reports where large numbers of records MIGHT need
updating, we also run scripts to mass-update.

You might also look into plugins like Activator or Events which perform
a script when a field value (including unstored calcs) changes. We
choose not to, since our plugin-overhead is pretty high already, but
many people employ them to great effect for needs just like yours.

Lynn Allen
www.semiotics.com

Reply to this Message

Rans - 27 Feb 2004 14:28 GMT

Hi Lynn,

Thanks for the reply, very useful. Since i'm not using scripts for
navigation through records, i will have to update the stored value of
the calculation as often as possible. Almost every button/sctipt in
the interface calls the "refreshing" script. It's not very elegant but
at least i can make a direct relationship with thunder fast finds.

Your idea to show a visible message when the stored value is not the
same as the calc is an excellent one, i will use it.

This is a good solution for now because i only have 1 table in which i
have to store the value of this calc. But in the future i will have
many tables, in different levels. (I'm restructuring a big project of
about 100 tables) Refreshing the stored value of the calc in many
tables/levels will be slow and not reliable at all.

Filemaker has many good advantages, but this time it's a big thumb
down for not allowing to store the value of a calc. Thanks again.

Matt

Reply to this Message