Running calculation fields

[Lemur]

Whew...let me see if I can explain this properly, and then, see if it's even possible.

I'm looking to set up a running calculation for my boardgaming group. One formula basically compares the scores of the various players, and determines how much each player will win or lose. That total affects the previous "base" total, and a new base total is generated.

So, for example, let's assume all 4 players start the game, tied at 5000. They're playing Monopoly. At the end of the game, the formula determines that the 3 losing players should lose 33 points each, and the winner should gain those 99 points. For the NEXT game, their scores would be 5099 and 3 players at 4967. This is only a running total, assuming they play the same game. If the next game is, for example, Sorry, they would all start over again at 5000. But, if two of them played Monopoly a year from now, they would start with their new base numbers (and, the new players would start at 5000).

I don't even know if this is possible. The hardest thing I'm trying to wrap my head around is how the program will now to pull the 'new base' from the NEWEST related record (all the records are related even within the same database).

I'm sure I'm not explaining this 100% accurately -- but, does the basic premise of this sound like something I can accomplish? Can anyone point me to a link? I'm using version 5.5.

Thanks!

Chris

[VICH]

from that explaination... i see... each game being its own record. with a point value feild and a win/lose feild for each player. then with scripting have the appropriate values subtracted from everyone considered a loser and added to the winner.

do you think something like that could work for you?

[Fitch]

My advice would be at the very least to separate the players and their games into separate tables (files in FM5).

Either way, you can create sorted relationships to pull in the newest values (you can relate a file to itself), or you could use scripts, there's no one right way to do it.

[Lemur]

Hi Vich & Fitch,

I already have them in separate tables - in fact, the whole 'database' is 9 tables, with 3 join files.

I know I can get it to work - the hard part is telling it to pull the MOST recent base value to work from. How would you do that?

i.e., assume the following example:

Game 1: Base value 5000, gain 33, new base value 5033

Game 2: Base value 5033, gain 50, new base value 5085

Game 3: Base value 5085, lose 25, new base value 5060

How do I ensure that the Game 4 base value is 5060? I can do a relationship in the table, but how can I direct it to pull the base value from the most recent entry, and not from Game 1 or Game 2, for example?

Thanks!

[AudioFreak]

How do I ensure that the Game 4 base value is 5060? I can do a relationship in the table, but how can I direct it to pull the base value from the most recent entry, and not from Game 1 or Game 2, for example?

Sort the relationship so the last record is at the top of the list.

Michael

[Lemur]

And, in other words, it will always pull from the most recent? always?

I can definitely set up a script to sort by date upon closing the table -- that will ensure the newest is the most recent....

Chris

[AudioFreak]

No need for a script. Sort the relationship. If you have the sort set up yes it will always pull the data from the last related record.