Calculation/Lookup from Previous Record

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 and Players 2-4 each lose 33.

The NEXT time they play THAT game, I would like their base scores to
reflect the changes from the last game (so, new scores would be 5100,
4967, 4967, 4967).

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 a sample file I can email. In that sample file, in Record 1,
"Bob" played game id #1, started with a rating of 5000, and added 100
points. Chris also played and lost 33 points, so his rating is 4967)

Bob shows up again in Record 5, but playing a different game (game id
#2) - so, again, he starts with a rating of 5000 and loses 33 points.
Chris also played and lost 33 points (so he's at 4967 for game id #2,
as well)

Bob's final time is in Record 9, playing game id #1 again.

What I NEED is for the "Base Rating" in Record 9 to reflect the "New
Rating" from the last Bob played game id #1 (which was in Record
1)...so his starting rating would be 5100, and Chris' base rating in
Record 10 (for game id #1) would be 4967.

I have created self-joins and lookups, but nothing is allowing me to
pull the information from that record forward (again, I have a sample
file, about 30k, that I'm using to test everything).

I'm using FM 5.5

Thank you for any help you can provide! This is driving me a little
batty ;)

Chris

Re: Calculation/Lookup from Previous Record

In article <1189025002.520004.291860@w3g2000hsg.googlegroups.com>,
Lemur wrote:

> 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 and Players 2-4 each lose 33.
>
> The NEXT time they play THAT game, I would like their base scores to
> reflect the changes from the last game (so, new scores would be 5100,
> 4967, 4967, 4967).
>
> 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 a sample file I can email. In that sample file, in Record 1,
> "Bob" played game id #1, started with a rating of 5000, and added 100
> points. Chris also played and lost 33 points, so his rating is 4967)
>
> Bob shows up again in Record 5, but playing a different game (game id
> #2) - so, again, he starts with a rating of 5000 and loses 33 points.
> Chris also played and lost 33 points (so he's at 4967 for game id #2,
> as well)
>
> Bob's final time is in Record 9, playing game id #1 again.
>
> What I NEED is for the "Base Rating" in Record 9 to reflect the "New
> Rating" from the last Bob played game id #1 (which was in Record
> 1)...so his starting rating would be 5100, and Chris' base rating in
> Record 10 (for game id #1) would be 4967.
>
> I have created self-joins and lookups, but nothing is allowing me to
> pull the information from that record forward (again, I have a sample
> file, about 30k, that I'm using to test everything).
>
> I'm using FM 5.5
>
> Thank you for any help you can provide! This is driving me a little
> batty ;)
>
> Chris

Here is a suggested structure:

Three tables:
Player
Game
Result

Result is a join table that connects player to game, many-to-many:

Player::kpPlayerID = Result::kfPlayerID
Game::kpGameID - Result::kfGameID

This allows many games per player and many players per game.

Result stores the result obtained in that game by that player.

Player stores the identity of the player, the initial score, and the
current score.

The current score of the player is initial score plus the sum of all the
related results for that player.

A portal in a layout of Player displays all the results of that player.
A portal in a layout of Game displays all the results for that game.

Variants are possible using scripts to automatically assign players to
games, to store intermediate values in appropriately defined fields,
etc.

--
For email, change to
Bill Collins

Re: Calculation/Lookup from Previous Record

Hi Bill,

Thanks for responding. What you described is exactly what I have,
believe it or not (albeit, I have many more tables - probably 10 in
total). I'm trying to do this calculation in the join table.

I'm going to email the sample file to you - I'm hoping you don't mind.
It's just much easier to show people what I'm talking about, than to
describe it in the abstract....

Thanks!

Chris


On Sep 5, 5:00 pm, Bill wrote:
> In article <1189025002.520004.291...@w3g2000hsg.googlegroups.com>,
>
>
>
> Lemur wrote:
> > 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 and Players 2-4 each lose 33.
>
> > The NEXT time they play THAT game, I would like their base scores to
> > reflect the changes from the last game (so, new scores would be 5100,
> > 4967, 4967, 4967).
>
> > 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 a sample file I can email. In that sample file, in Record 1,
> > "Bob" played game id #1, started with a rating of 5000, and added 100
> > points. Chris also played and lost 33 points, so his rating is 4967)
>
> > Bob shows up again in Record 5, but playing a different game (game id
> > #2) - so, again, he starts with a rating of 5000 and loses 33 points.
> > Chris also played and lost 33 points (so he's at 4967 for game id #2,
> > as well)
>
> > Bob's final time is in Record 9, playing game id #1 again.
>
> > What I NEED is for the "Base Rating" in Record 9 to reflect the "New
> > Rating" from the last Bob played game id #1 (which was in Record
> > 1)...so his starting rating would be 5100, and Chris' base rating in
> > Record 10 (for game id #1) would be 4967.
>
> > I have created self-joins and lookups, but nothing is allowing me to
> > pull the information from that record forward (again, I have a sample
> > file, about 30k, that I'm using to test everything).
>
> > I'm using FM 5.5
>
> > Thank you for any help you can provide! This is driving me a little
> > batty ;)
>
> > Chris
>
> Here is a suggested structure:
>
> Three tables:
> Player
> Game
> Result
>
> Result is a join table that connects player to game, many-to-many:
>
> Player::kpPlayerID = Result::kfPlayerID
> Game::kpGameID - Result::kfGameID
>
> This allows many games per player and many players per game.
>
> Result stores the result obtained in that game by that player.
>
> Player stores the identity of the player, the initial score, and the
> current score.
>
> The current score of the player is initial score plus the sum of all the
> related results for that player.
>
> A portal in a layout of Player displays all the results of that player.
> A portal in a layout of Game displays all the results for that game.
>
> Variants are possible using scripts to automatically assign players to
> games, to store intermediate values in appropriately defined fields,
> etc.
>
> --
> For email, change to
> Bill Collins

Re: Calculation/Lookup from Previous Record

Lemur wrote:
> 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 and Players 2-4 each lose 33.
>
> The NEXT time they play THAT game, I would like their base scores to
> reflect the changes from the last game (so, new scores would be 5100,
> 4967, 4967, 4967).
>
> 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 a sample file I can email. In that sample file, in Record 1,
> "Bob" played game id #1, started with a rating of 5000, and added 100
> points. Chris also played and lost 33 points, so his rating is 4967)
>
> Bob shows up again in Record 5, but playing a different game (game id
> #2) - so, again, he starts with a rating of 5000 and loses 33 points.
> Chris also played and lost 33 points (so he's at 4967 for game id #2,
> as well)
>
> Bob's final time is in Record 9, playing game id #1 again.
>
> What I NEED is for the "Base Rating" in Record 9 to reflect the "New
> Rating" from the last Bob played game id #1 (which was in Record
> 1)...so his starting rating would be 5100, and Chris' base rating in
> Record 10 (for game id #1) would be 4967.
>
> I have created self-joins and lookups, but nothing is allowing me to
> pull the information from that record forward (again, I have a sample
> file, about 30k, that I'm using to test everything).
>
> I'm using FM 5.5
>
> Thank you for any help you can provide! This is driving me a little
> batty ;)
>
> Chris
>


GameID/PlayerID
key_Last = GameID -1

multiparameter rel: key_Last::GameID | PlayerID::PlayerID


c_Last Score = rel::Score


Chris

Re: Calculation/Lookup from Previous Record

In article , Chris Brown
wrote:

> Lemur wrote:
> > 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 and Players 2-4 each lose 33.
> >
> > The NEXT time they play THAT game, I would like their base scores to
> > reflect the changes from the last game (so, new scores would be 5100,
> > 4967, 4967, 4967).
> >
> > 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 a sample file I can email. In that sample file, in Record 1,
> > "Bob" played game id #1, started with a rating of 5000, and added 100
> > points. Chris also played and lost 33 points, so his rating is 4967)
> >
> > Bob shows up again in Record 5, but playing a different game (game id
> > #2) - so, again, he starts with a rating of 5000 and loses 33 points.
> > Chris also played and lost 33 points (so he's at 4967 for game id #2,
> > as well)
> >
> > Bob's final time is in Record 9, playing game id #1 again.
> >
> > What I NEED is for the "Base Rating" in Record 9 to reflect the "New
> > Rating" from the last Bob played game id #1 (which was in Record
> > 1)...so his starting rating would be 5100, and Chris' base rating in
> > Record 10 (for game id #1) would be 4967.
> >
> > I have created self-joins and lookups, but nothing is allowing me to
> > pull the information from that record forward (again, I have a sample
> > file, about 30k, that I'm using to test everything).
> >
> > I'm using FM 5.5
> >
> > Thank you for any help you can provide! This is driving me a little
> > batty ;)
>
> GameID/PlayerID
> key_Last = GameID -1
>
> multiparameter rel: key_Last::GameID | PlayerID::PlayerID
>
>
> c_Last Score = rel::Score

If all you want is the very last score, then there's a slightly easier
way. Instead of bothering with the "key_Last", simply create the
Relationship to sort in reverse order (presumably by Date) and then
Rel::Score will be the most recent one wanyway. :o)


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Calculation/Lookup from Previous Record

Helpful Harry wrote:
> In article , Chris Brown
> wrote:
>
>> Lemur wrote:
>>> 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 and Players 2-4 each lose 33.
>>>
>>> The NEXT time they play THAT game, I would like their base scores to
>>> reflect the changes from the last game (so, new scores would be 5100,
>>> 4967, 4967, 4967).
>>>
>>> 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 a sample file I can email. In that sample file, in Record 1,
>>> "Bob" played game id #1, started with a rating of 5000, and added 100
>>> points. Chris also played and lost 33 points, so his rating is 4967)
>>>
>>> Bob shows up again in Record 5, but playing a different game (game id
>>> #2) - so, again, he starts with a rating of 5000 and loses 33 points.
>>> Chris also played and lost 33 points (so he's at 4967 for game id #2,
>>> as well)
>>>
>>> Bob's final time is in Record 9, playing game id #1 again.
>>>
>>> What I NEED is for the "Base Rating" in Record 9 to reflect the "New
>>> Rating" from the last Bob played game id #1 (which was in Record
>>> 1)...so his starting rating would be 5100, and Chris' base rating in
>>> Record 10 (for game id #1) would be 4967.
>>>
>>> I have created self-joins and lookups, but nothing is allowing me to
>>> pull the information from that record forward (again, I have a sample
>>> file, about 30k, that I'm using to test everything).
>>>
>>> I'm using FM 5.5
>>>
>>> Thank you for any help you can provide! This is driving me a little
>>> batty ;)
>> GameID/PlayerID
>> key_Last = GameID -1
>>
>> multiparameter rel: key_Last::GameID | PlayerID::PlayerID
>>
>>
>> c_Last Score = rel::Score
>
> If all you want is the very last score, then there's a slightly easier
> way. Instead of bothering with the "key_Last", simply create the
> Relationship to sort in reverse order (presumably by Date) and then
> Rel::Score will be the most recent one wanyway. :o)
>
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)


quite so Harry! But I just noticed Lemur is using FM5.5. It's been so
long, did 5.5 permit mutiparamter rels? I seem to vaguely remember
having to create dedicated calc keys...

regards

Chris

cortical dropout exemplar: awoke this am thinking it be Thursday; is in
fact Saturday; where does the time go?

Re: Calculation/Lookup from Previous Record

In article , Chris Brown
wrote:

> Helpful Harry wrote:
> > In article , Chris Brown
> > wrote:
> >
> >> Lemur wrote:
> >>> 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 and Players 2-4 each lose 33.
> >>>
> >>> The NEXT time they play THAT game, I would like their base scores to
> >>> reflect the changes from the last game (so, new scores would be 5100,
> >>> 4967, 4967, 4967).
> >>>
> >>> 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 a sample file I can email. In that sample file, in Record 1,
> >>> "Bob" played game id #1, started with a rating of 5000, and added 100
> >>> points. Chris also played and lost 33 points, so his rating is 4967)
> >>>
> >>> Bob shows up again in Record 5, but playing a different game (game id
> >>> #2) - so, again, he starts with a rating of 5000 and loses 33 points.
> >>> Chris also played and lost 33 points (so he's at 4967 for game id #2,
> >>> as well)
> >>>
> >>> Bob's final time is in Record 9, playing game id #1 again.
> >>>
> >>> What I NEED is for the "Base Rating" in Record 9 to reflect the "New
> >>> Rating" from the last Bob played game id #1 (which was in Record
> >>> 1)...so his starting rating would be 5100, and Chris' base rating in
> >>> Record 10 (for game id #1) would be 4967.
> >>>
> >>> I have created self-joins and lookups, but nothing is allowing me to
> >>> pull the information from that record forward (again, I have a sample
> >>> file, about 30k, that I'm using to test everything).
> >>>
> >>> I'm using FM 5.5
> >>>
> >>> Thank you for any help you can provide! This is driving me a little
> >>> batty ;)
> >> GameID/PlayerID
> >> key_Last = GameID -1
> >>
> >> multiparameter rel: key_Last::GameID | PlayerID::PlayerID
> >>
> >> c_Last Score = rel::Score
> >
> > If all you want is the very last score, then there's a slightly easier
> > way. Instead of bothering with the "key_Last", simply create the
> > Relationship to sort in reverse order (presumably by Date) and then
> > Rel::Score will be the most recent one wanyway. :o)
>
> quite so Harry! But I just noticed Lemur is using FM5.5. It's been so
> long, did 5.5 permit mutiparamter rels? I seem to vaguely remember
> having to create dedicated calc keys...

Nope. FileMaker 5.5 doesn't have multi-field relationships. You can
only use one field and it can't be a Calculation on the child side.
You can use a Calculation on the parent side to combine other field
values, but on the child side you have to use Auto-enter fields - which
adds a complication if you want to later change the fields' values, but
you shouldn't have that problem when using unique IDs rather than
something like a Player's Name.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Calculation/Lookup from Previous Record

Thanks guys,

That makes a lot more sense. I didn't know how to do what Chris was
suggesting.

I am looking into upgrading maybe to FM 8 or 9....that (apparently)
will make it much easier....

Chris



On Sep 8, 12:52 am, Helpful Harry
wrote:
> In article , Chris Brown
>
>
>
> wrote:
> > Helpful Harry wrote:
> > > In article , Chris Brown
> > > wrote:
>
> > >> Lemur wrote:
> > >>> 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 and Players 2-4 each lose 33.
>
> > >>> The NEXT time they play THAT game, I would like their base scores to
> > >>> reflect the changes from the last game (so, new scores would be 5100,
> > >>> 4967, 4967, 4967).
>
> > >>> 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 a sample file I can email. In that sample file, in Record 1,
> > >>> "Bob" played game id #1, started with a rating of 5000, and added 100
> > >>> points. Chris also played and lost 33 points, so his rating is 4967)
>
> > >>> Bob shows up again in Record 5, but playing a different game (game id
> > >>> #2) - so, again, he starts with a rating of 5000 and loses 33 points.
> > >>> Chris also played and lost 33 points (so he's at 4967 for game id #2,
> > >>> as well)
>
> > >>> Bob's final time is in Record 9, playing game id #1 again.
>
> > >>> What I NEED is for the "Base Rating" in Record 9 to reflect the "New
> > >>> Rating" from the last Bob played game id #1 (which was in Record
> > >>> 1)...so his starting rating would be 5100, and Chris' base rating in
> > >>> Record 10 (for game id #1) would be 4967.
>
> > >>> I have created self-joins and lookups, but nothing is allowing me to
> > >>> pull the information from that record forward (again, I have a sample
> > >>> file, about 30k, that I'm using to test everything).
>
> > >>> I'm using FM 5.5
>
> > >>> Thank you for any help you can provide! This is driving me a little
> > >>> batty ;)
> > >> GameID/PlayerID
> > >> key_Last = GameID -1
>
> > >> multiparameter rel: key_Last::GameID | PlayerID::PlayerID
>
> > >> c_Last Score = rel::Score
>
> > > If all you want is the very last score, then there's a slightly easier
> > > way. Instead of bothering with the "key_Last", simply create the
> > > Relationship to sort in reverse order (presumably by Date) and then
> > > Rel::Score will be the most recent one wanyway. :o)
>
> > quite so Harry! But I just noticed Lemur is using FM5.5. It's been so
> > long, did 5.5 permit mutiparamter rels? I seem to vaguely remember
> > having to create dedicated calc keys...
>
> Nope. FileMaker 5.5 doesn't have multi-field relationships. You can
> only use one field and it can't be a Calculation on the child side.
> You can use a Calculation on the parent side to combine other field
> values, but on the child side you have to use Auto-enter fields - which
> adds a complication if you want to later change the fields' values, but
> you shouldn't have that problem when using unique IDs rather than
> something like a Player's Name.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)