Autopopulate dropdown list in sequence based on previous list.

[ltjbsa]

I could use suggestions / help thinking through the logic of having two successive form fields autopopulate based on actions taken in the previous one. 

• My setup: I am building an app for my Little League, to perform player evaluations. I have a table named PLAYER and a table named EVALUATIONS.
• Player Table holds player information, including a unique ID to each player, and a Group #. A group contains multiple unique player IDS.
• Coaches input their evaluation into the form, which pulls information about the player being evaluated into the EVAL table.  

I'm working on an ease of use project. Right now, my form looks like this: 

The Player ID dropdown is populated suggested value "players [player_key]" (pull the ID list from the players table). When the user selects the ID from the drop down, the Player Name field auto-populates using AutoCompute / Initial Value of "LOOKUP([Player ID], "players", "player_key", "_PlayerName").

The list of Player IDs has grown (its over 500) and I'd like to present them to the user by Group. So I'd like to add a new field that says Group, that when the user selects a value, autopopulates the Player ID dropdown with only the IDs related to that group. And then, when the user selects that value, the Player Name field autopopulates as it did. Something like this: 

(sorry for crude drawing).

Any suggestions? I am getting stuck because the ref between players and evals is the unique ID, and not the Group, so I am unsure how to link the Group and Player ID field together with logic that allows Group ID to direct the Player ID values, and then trigger the autofill of the name. I have searched this forum but have not found a use case like this that works. 

Would appreciate any help someone can provide. Thank you in advance!