value from different field in previous record?

[option8]

i'm trying to calculate the value of a field in a new record from the value of a *different* field in the previous record.

essentially, i'm trying to track something from day to day, and my calculations of one field involve the change from previous day's value of another field. and i'm trying to do it automagically when the new record is created.

so Field1 = yesterday's Field2 - today's Field2

i can't get my head around the relationship/portal thing in this case, or figure out a script that would do it in an auto-enter...

any ideas?

[LiveOak]

Create fields:

RecordNum (number, autoenter, serial number, indexed)

PrevRecNum (calculation, number, indexed) = RecordNum - 1

Create a self relationship called PrevRec with PrevRecNum <--> RecordNum

To create a new record (script)

New Record/Request

Set Field(Field1, PrevRec::Field2 - Field2)

If you are worried about deleted records breaking this approach, don't auto enter RecordNum, do the following:

Define a field:

Constant (calculation, number, indexed) = 1

Create a self relationship (call it "SelfRel") with Contant <--> Constant

Create a slightly different new record script:

New Record/Request/Page

Set Field(RecordNum, Max(SelfRel::RecordNum) + 1)

Set Field(Field1, PrevRec::Field2 - Field2)

-bd