Changing fields for multiple records with shared field data

[qlat]

I have a table of "teams" and there are two members to a team, each with their own record. If I change the status of one member on a team, I want that status change to occur in the other team member's record as well. Each record contains a team name field so I assume I am correct in making that the relational field since both team members have the same team name.

I've duplicated the teams table and created a relationship between the team names in both tables. My script changes status via SetField but it only changes the status of the current team member, not the other.

Edited January 6, 2008 by Guest

[mz123]

If I understand correctly, you'd want to do the following:

Have a table called Members and a table called Teams, each having a field called Team Name. Members and Teams would be related by Team Name (assuming you won't have duplicate team names).

The team's status field would be in the table Teams. On the Members layout, the Team Status field would be a related field. That way, when you change the related field on the Members layout, it's really changing the field in Teams, which reflects on all records in Members with the same Team name.

Hope that makes sense! Let me know if you need a sample file or something. Thanks :)

[qlat]

That makes sense. What happens if I have multiple teams with the same name? They will at least have another field in their record to differentiate them (aside from having different team members, of course).