Getting data from a related table via a calculation field

[DWestphal]

I have two tables "COACHES" and "GAMES"

In "COACHES" I have the following fields

Name, Year_Start, Year_End

In "GAMES" I have numerous fields including

Game, Coach, Year_Season

When I create a new record in "GAMES" I want the coach field to be calculated based on the season. In other words, lookup the name based on comparing the Year_Season with the Year_Start and Year_Finish fields. Hopefully I'll be able to pull a summary of games won and lost back into the "COACHES" table.

Thanks in advance!

[Søren Dyhr]

Just give me one reason for not referencing instead of looking up or calculated data ... you are missing a join table, from where the reporting should be done!

--sd

[comment]

IIUC, there is one coach to many games - so I see no need for a join table. However, I believe a coach may be replaced in mid-season, so the 'real' relationship should be based on CoachID (not name!). The relationship by season can be used to lookup the CoachID as a suggestion only.

I think we need more information about the "Year_Season" field. If all three fields (Year_Start, Year_End and Year_Season) are plain year numbers, the relationship can be by range:

COACHES::Year_Start ≤ GAMES::Year_Season

AND

COACHES::Year_End ≥ GAMES::Year_Season

[DWestphal]

Thanks for your response. The season is simply a year (number). e.g. The games started in 1998 and ended in 1999. Therefore the season is 1998.

I can't figure out how to make my "Games" table pull in the coach's name from my "Coaches" table based on the season. For example, if I enter information about a specific game that occured in "Season" field = 1972, I want the "Coach" field to be automatically filled in. The tricky part is some coaches left and came back. e.g. coached from 1982-1987 and again from 1992-1995. I think this is why you are suggesting using coachID as the relationship link. I just can't figure out how to make a conditional relationship link.

[comment]

See if the attached makes sense. Note that it's a simplification - a smarter implementation would have separate tables for Coaches and for their 'terms in office'.

The reason to use CoachID instead of name is that you only want to enter a name once. You also want to be able to correct the name without breaking existing relationship. And it seems that most coaches are named "Coach" anyway.

Coaches.fp7.zip

[DWestphal]

FANTASTIC!!!

That is exactly what I needed. It took some trial and error on my part to figure out the details of what was going on but I finally got it. I would only warn others that using a lookup does not update automatically when a change is made to the original reference table. I had to create a simple script and then made a button to execute the script.

The script is:

Show All Records

Relookup Field Contents [No dialog; Current Layout::Source Field]