calculated field that pulls data previous record

[cgjoseph]

How can I define a field B that pulls the value of a different field (field A) in the previous record? I want a calculated field - not a script.

This is probably elementary, it is so simple to autoenter a value from a previous record - but I want it to calculate.

Version: v6.x

Platform: Mac OS X Panther

[RalphL]

If your records have serial number, create a field that = serial number - 1. Make relationship from this field to the serial number. Use calculation = Relationship::Field. A lookup would be better in that it could go to the next lowest record if the preceding record was deleted. The lookup could be indexed.

[CyborgSam]

If records are deleted the serial #s may have gaps. If deletion is not allowed, Ralph's method is fine. If deletion is an issue:

If you have a script to set up a list/portal: create a number field called sort order. Once you have a found set and it is sorted, have the script do a Replace Contents [sortOrder with serial #s.] Script usage is over now. Make the relationship using Ralph's technique, except use SortOrder - 1.

Will this work?

[CyborgSam]

My bad: my technique isn't the best way to do this, I forgot something I just learned the other day...

Status(CurrentRecordNumber) will work if, and only if, the calculation containing it is set to unstored so it will recalculate using the portal's sort order. In the calc's options, check "Do not store calculation results -- calculate only when needed" in the Storage Options dialog.

You can do this without a script:

Create a field called myRecordNumber with "Status(CurrentRecordNumber)". In the calc's options, check "Do not store calculation results -- calculate only when needed" in the Storage Options dialog.

Create a second field called previousRecordNumber with "Status(CurrentRecordNumber) - 1". In the calc's options, check "Do not store calculation results -- calculate only when needed" in the Storage Options dialog.

Define a relationship between previousRecordNumber and myRecordNumber.

Whatever your calculation is, be sure to check if myRecordNumber=1, since the relationship wont work for the first record, e.g.:

If( myRecordNumber=1,

0,

theRelationship::theValueYouWantFromPreviousRecord

)

[-Queue-]

Ralph's idea for a lookup should work fine whether or not deletion is allowed.

Note:

A lookup would be better in that it could go to the next lowest record if the preceding record was deleted.

Therein lies the magic of lookups. And it does not matter how many records may have been deleted in between; it will find the next lowest number, as long as one exists.

[CyborgSam]

You're right, Ralph's method is just what's needed, my method is unnecessary. I've got to learn how to read (posts), and I've got to remember more details (lookups)... Only 9 months until next years New Year's resolutions...

Sam

Version: v7.x

Platform: Mac OS X Panther