Autopopulate Fields based on a key

[skoothor]

I have two tables in a database related by a field called "TeamKey". The tables are as follows:

 

Team

   Team Name (Text)

   Team Number (Text)

   Member 1 (Text)

   Member 2 (Text)

   Member 3 (Text)

   Member 4 (Text)

   Member 5 (Text)

   TeamKey (calculated combo of Team Name & Team Number)

 

Performance (each record is a daily team check)

   Team Name (Text)

   Team Number (Text)

   Member 1 (Calculation Field -> "TeamKey::Member1")

   Member 2 (Calculation Field -> "TeamKey::Member2")

   Member 3 (Calculation Field -> "TeamKey::Member3")

   Member 4 (Calculation Field -> "TeamKey::Member4")

   Member 5 (Calculation Field -> "TeamKey::Member5")

   TeamKey (calculated combo of Team Name & Team Number)

 

My goal is to set up all of my teams in the Team table, and then, in the Performance table, I can create a record and choose the Team Name (a pop-up menu from Team::Team Name) and then have the Members auto-fill, based on the Team table. However, when I set that up, the Member fields in the Performance Table are empty, even though there is data in the Teams Table and I've verified that the TeamKey matches on the two tables. I tried creating a merge field to show the Member name, but it just displays "<Index Missing>" as the value.

 

Any ideas what I've done wrong and how to set this up better? I just want to have a table of teams and then in another table select a team name and have the members auto-populate, based on that first table.

[Steve Martino]

That's not an ideal set up.  Best to use standard primary key (Team Table, related to a foreign key (Performance Table) set up (check 'allow creation...' in Performance.  The primary key should be an auto-enter serial number, that's never seen on the user interface, or could be edited by any user.

For performance records, why not just a portal of Performance on the Team Table?  Then entering any data in the portal (i.e.performance date) automatically creates the related record.

If you don't like a portal for entry, then maybe a card window. A simple script would put the primary key into a variable, open the card window layout based on a performance TO, new record request, set foreign key to the variable (foreign key field doesn't have to be on the layout).  When the user is done enter a new record, a Save  button can perform a 'Save Records' and close window, or Cancel button can revert record (or delete) and close window.  

[comment]

It is difficult to advise without understanding what exactly is your solution supposed to track in real life, and for what purpose. One thing jumps out immediately, though: you have numbered fields - and that is an unmistakable sign of poor structure. Apparently, one team has many members - and this should be handled by two separate tables - Teams and Members, linked by TeamID (a meaningless value, typically generated as an auto-entered serial number in the Teams table). This is assuming a member belongs to one team only (or at least to only one team at a time, and  there is no need to keep record of members' team assignment history).

Next, we have performances. I am guessing from your description that this means team performances, not performances of individual members. This would also be a one-to-many relationship between the Teams and Performances tables - again linked by TeamID, and by TeamID only.

If it's important to know who the team members were at the time of recording a performance, then you would need a field in the Performances table to auto-enter a list of the current MemberID values, so that you can use it in a relationship to (another occurrence of) the Members table. Otherwise you can simply display the related details (e.g. member names) directly from the Members table on a layout of Performances.