Calculation Fields referring to previous record

[Lemur]

Hello,

I'm trying to determine the best way to set up a running calculation that uses previous records as the base.

Imagine, for example, 4 players play a game for the first time. The base score all players start with is 5000.

During that game, Player 1 gains 100, Player 2 gains 50, Player 3 loses 50 and Player 4 loses 100.

The NEXT time they play the game, I would like their base scores to reflect the changes from the last game (so, new scores would be 5100, 5050, 4950, 4900), and so on.

I've been able to set up relationships within the file to allow the 5000 to be placed, and for all the computations. The problem is, I'm unable to set up a calculation for the second playing, without creating a circular reference.

I have attached a sample file, to illustrate what I'm trying to do.

I'm using FM 5.5

Thanks for any help or ideas you can provide!

Chris

Sample.zip

[Søren Dyhr]

This seems to be exactly what this template illustates:

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

--sd

[Lemur]

Hi,

Yes, this is very nearly what I want. There's only one difference -- I'm not looking to create a running tally of ALL the records...just the ones that are 'related.'

Take a look at the sample file (which I've updated a bit from the first post). You can see in Record 1, that "Bob" playing game #1, started with a rating of 5000, and added 100 points.

Bob shows up again in Record 5, but playing a different game (game id #2) - again, he starts with a rating of 5000 and loses 33 points.

His final time is in Record 9, playing game #1 again.

What I NEED is for the "Base Rating" in Record 9 to reflect the "New Rating" from Record 1...so his starting rating would be 5100.

You can see my attempts at relationships and calculations. Obviously, this is not correct; but I think I'm on the right track. The "Lookup" from the LULAST file you showed me cleared things up somewhat...

Thanks!

Chris

Sample.zip

Edited September 5, 2007 by Guest

[Søren Dyhr]

Since your version of filemaker lacks the GetNth( function, should you exploit this templates mechanincs:

http://www.nightwing.com.au/FileMaker/demos/CalculateDown.zip

--sd

[Lemur]

Hi,

Yes, I saw that one too...it's pretty close. The problem is it relies on serial number (which is fine). And, then, each record has a 'previous serial number) which is the current serial number -1. In my case, there may be hundreds of serial numbers before the same 'game id' comes back.

Chris

[Søren Dyhr]

I could only hope you can find the money for an upgrade, I havn't touched fm5 ever since fm7 came out some 4 years ago. Todays version is FM9...

--sd