I've got a table consisting of records which detail values that change occasionally, each change represented by a new record.

As the value is extracted from this table to another by script, there isn't much need to have more than two records at any one time. [Record 1 – first value. Record 2 – new value]

The script calls either record, by selection, and as it runs, it pastes a date specific to the other table, back into a date field on this table. This date field then triggers an evaluation calc which forms an audit trail.

So far, so good – all working fine.

95% of the time the script will run either from a single record [1 of 1] or the most recent, second record [2 of 2], but every once in a while, when updated info is received late for instance, things get a little more complicated and I have to run the script on record 1 [1 of 2]

To get around this, I've created a global calc field, coupled with a calc which subtracts the original value from the new global value, giving me the difference and allowing me the opportunity to run the script from the first record and a subscript which enters the difference as a separate transaction/record.

                   Value          g_Value               diff

Record 1            100             150                  50

Record 2            150             150                   0 

I wanted to create a second audit log triggered on a second date field, which would show the subscript date and the 'difference', but it won't work because the calc can't be stored...

This is presumably because the difference is calculated on a global... But when I remove the difference from the second evaluate calc, it still tells me it can't be stored.

So what's up with the second evaluation calc – when it's all but the same as the first? And how do I abstract a global so that it can be used?

Any help would be appreciated.

Perhaps you should investigate this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000333

Because I by and large thinks you're approaching things pretty healthy, all things considered, instead of the use of either aggregate or summaryfunctions.

--sd

Thanks Søren,

I'm pleased to have your esteemed view.

Okay: I've got the download and in terms of a self-join, it was an abstraction route I was thinking of. But I'm not actually trying to get a value from the preceeding record; I'm in the previous record, and my secondary evaluation will stay in this record. So are you suggesting that I abstract the whole process, making lookup's of both the second date and value?

I'll need some extra time to reread you posts, as well as think further!

--sd