How can I reference fields in different records for calculations?

[bena]

I have set up a database to keep track of my workouts. I have a calculation field (( reps*weight)+(rep1*weight1) etc..) that determines the total weight lifted and is part of that record.

The goal I want to acheieve is to be able to reference this field from a prior record to comapre it to the current record and calculate a percent change.

The problem is I don't know how to write a calculation that references that calculation field for a specific record.

Any suggestions are apprechated.

Thanks

Ben

[CobaltSky]

There are various ways to do that, Ben.

If your records are in the current found set and you simply want to reference the previous record in the current sort order, use:

GetNthRecord(YourField; Get(RecordNumber) - 1)

Otherwise (ie if you have some other criterion for determining which is the "prior" record), an alternative option would be to set up a self-join relationship based on a calculation of some sort (eg SerialNo - 1 etc). :wink2:

[bruceR]

Fields like "Weight1, Weight2" etc are a big warning flag. Almost always a better solution would be a relational design; and in this case it would address your summation issue as well. For useful examples, look at almost any invoicing solution for tracking totals of items ordered.

[bena]

I was able to use GetNthRecord(YourField; Get(RecordNumber) - 1)

Im curious why I don't see the GetNthRecord in the List of Get commands?

One last thing, I noticed that if I use the above as a Calculation field, if the data changes on the previous record, it does not auto update. Is there a way to force a rerun of the calculation.

Thx Much

[Søren Dyhr]

Im curious why I don't see the GetNthRecord in the List of Get commands?

The Get( commands takes no variables as argument, where the GetSomething( which takes arguments, are splayed across various sections. There somewhat 10-12 of those you can arrive to by instead under "all functions by name" and then hit the g-char.

The Get( commands deals internal globals such as time recordnumber or the found sets size, and not values found in fields ... if this categorisation is found counter intuitive or directly illogical, are most likely to follow the spectators prerequisites ... I could just say that it makes sense to me!

Conditional plucking among the genuine Get( functions needs to be stuffed inside either a Case(/Choose( statement or a Evaluate(

--sd