While this is possible to set up, it would seem simpler to use a relationship based on the player ID to add the field from the related table to your layout such that when you select the Player in the value list formatted field, the multi-line info automatically appears in a second field on your layout.
To get what you want, however, Open Manage | database, find this multi-line field. Double click it and use storage options to turn off indexing. Then set up your value list with Player ID and field 1 and this field as Field 2. But you cannot hide field 1 nor sort your values on field 2 when the field 2 value comes from an unindexed field.
And when you click OK to close the dialog where you set up this value list, some versions will display a warning message that the field is unindexed and cannot be used. Ignore this spurious message.
Thanks for your response Phil. I did think of this as an option, however I have 20 players to place on a single layout (it basically places players in a starting lineup - there is a field for each position on the playing field) and to have two fields per player will make the layout quite cluttered.
Hope this makes sense
But you already need a field for the player's name, so if you modify the data shown in this field, field 1 can show the player name and field two can show the rest of the multi-line data.
Or you can hide the drop down list behind the multi-line field if you use behavior settings to deny browse mode access to the field in front. When you click on the multi-line field, the drop down list in the field hidden behind it can pop to the front and deploy, disappearing back behind the other field after you have selected a player.
And you might want to use a set of records for selecting your line up instead of a set of fields all in one record. This can look just like you have it now when entering the info, but may offer some better flexibility when it comes to setting up reports based on your line up.
I was thinking of setting up as you suggested with a set of position records tied to a game. Am i right in thinking that I would need to use portal filters to then place the positions on the playing field? the more i think about this, the more appealing it seems
Portals would make sense, though they are not the only option. And they might very well be filtered portals--but that may not be absolutely necessary, it can depend on how you set up your tables and relationships.
However you decide to approach this, you have a many to many relationship between players and games. Each player participates in many games, each game roster will list many players. The most flexible way to manage this is with a third, "join" table that links a player to the game where you have listed them on the roster. The position that they play can be listed in a field in the join table.