If at all possible, I'd use a summary field for this. A summary field set up as a "running total" is one option that often serves.
There are also ways to access data from the records in the current found set using the "getnthrecord" function that may serve though I personally avoid that option unless I have no alternative as it depends on a proper foundset correctly sorted into the correct order in most cases.
Spelling out in detail what you are attempting to compute here might help us suggest a good approach here.
Sure Phil, I'm happy to fill you in on what I'm trying to accomplish.
Basically. I work for a production company that produces, (among many things), a televised poker show. What I am trying to do is come up with a "running chip count" for the players at our final table. This will accomplish many things needed for the post production tasks.
Right now it is set up so that each record is one hand of poker. I have a row of fields set up for each player, (to enter their bet amounts), then at the bottom I have the grand total of all of their chip counts, (6 in all).
As data, (the betting amount), is entered into each field, that amount is subtracted from the player's chip count total.
The math seems to be working, but sometimes it gets off...I'm wondering if past records are having an impact on the current totals.
Is it better to designate the chip count totals as simple numbers fields, or are making them calculation fields the way to go. With a simple numbers field I'm able to use the "value from last record visited" option. But something tells me I need to have these as calculation fields. Is there a way to copy the value of a calculation field into the same field on a different record and still have the calculations work out?
Summary fields don't seem to be the answer.
I hope I explained myself well enough for you to understand.
Thanks for your help!
I knew your post looked familiar ;-)
Here's a method you may be able to use:
Treat your chipcounts and players as bank balances in a ledger.
The chips they start with are the deposit and each time they put chips in the pot is a withdrawel from their account. Presumably, winning the pot puts that total back into their "account" as a new deposit--but that may be overkill here.
If you can define your table this way:
ChipsIn, ChipsOut (number fields)
cChipBal (calculation field: ChipsIn - ChipsOut )
Make this field a summary field that computes a running total of cChipBal, restarting the totals with each PlayerID, then you can show the current chipcounts for each player if you sort the table to group all the records by playerID.
Another options is to put the betting action in a separate related table so that Sum ( Betting::cChipBal ) (or you can use Sum( Betting::ChipsIn ) - Sum ( Betting::ChipsOut ) ) will then give you the chipcount for each player in the parent record. Either way, modifying a value in a field to correct the amount placed in the Pot or even deleting a record will automatically re-compute the correct chip count for each player.