Autofill new field existing records

[TMaher1]

I have two tables that are joined together. One is called Players, the other Rosters. Rosters is the portal table. The field that joins them together is PlayerID.

I have a field in Players called PlayerName. I want to add to Rosters a field also called PlayerName. I want to fill in the new PlayerName field in Rosters with the data from the Players table using the PlayerID field to validate the PlayerName data in the new Rosters field. In want to do it not only on new records I enter, but on all the existing records. How do I do this??

[Brent Durland]

It sounds like you're trying to create lists of players, or rosters, by choosing from your players. To do this in a portal, you need three tables: One for players, one for rosters, and one for roster assignments.

This roster assignment table is called a join table. This table has two key fields: one for player and one for roster. There is one record for each assignment.

How many rosters are there? Create a record in the roster table for each one. Then, for each roster, create a record in the join table for each player assignment.

This is how you tie the tables together:

On a layout based on the roster table, you would create a portal based on the "Roster_Assignment" relationship, but you would show the name field from the Player relationship. Then, to add students to a roster in the portal, enable "Allow create of related records" for the Roster_Assignment relationship and add the Player ID field to the portal. Then, enter a Player ID using a drop-down based on a value list made of Player IDs and showing player names.

If you haven't done this before it will take some effort to put all the pieces together. Let me know if you need some more assistance.

Edited August 12, 2009 by Guest