Dropdown from a different table

[victsei]

I have the following setup:

• A table called "Teams" with info about the team and all the players who are part of the team (i.e, team_id, team_name, player_id, player_name, player_email)
• A slice of the table above called "My team" which filters only the rows which have team_id the same as the user's team_id (i.e, checking by email). Apparently this slice works as intended
• A table called "Selection" which is intended to capture the players that will be selected for each round of the tournament, with fields player_id_1 and player_id_2

I want to have a form for selecting the team (i.e, populating table "Selection") and I want the fields player_id_1 and player_id_2 to be a dropdown list of the player names that show up in my slice "My team". I've tried using Enum, Enum List, Ref and I seem to be getting nowhere.

Any help is appreciated!

[victsei · Accepted solution]

I actually found something that works, which is similar to what you described. I set the fields "player_1_id" and "player_id_2" as Text and also set the Valid If formula as MyTeam[player_id]. This works.

The only problem I'm facing now is that I wanted to add validation rules to these fields - for example, I wanted to have a rule on player_id_2 that, if the same as player_id_1, it would show a message saying this is not possible. However, given Valid If formula is filled already for this field, I can't seem to find a way to add another rule. Any clues?

[AleksiAlkio]

 It sounds like you are loooking for something like this with the Enum or EnumList..
MyTeam[player_id] and add this into "Suggested Values" option.

[victsei · Accepted solution]

I actually found something that works, which is similar to what you described. I set the fields "player_1_id" and "player_id_2" as Text and also set the Valid If formula as MyTeam[player_id]. This works.

The only problem I'm facing now is that I wanted to add validation rules to these fields - for example, I wanted to have a rule on player_id_2 that, if the same as player_id_1, it would show a message saying this is not possible. However, given Valid If formula is filled already for this field, I can't seem to find a way to add another rule. Any clues?

[AleksiAlkio · Accepted solution]

Move that formula into Suggested values because then you can use Valid_If for this other purpose.

[victsei]

That does work, thanks a lot! Final thing I'm struggling with: I cannot find the way to sort this list. The caveat is that I'm actually using player_name instead of player_id as I had mentioned before. I've tried ORDERBY(MyTeam[player_name], [player_name]), but it won't let me do this apparently because in this table Selection I don't have player_name.

I'm pretty sure it has something to do with brackets, quotes, etc, which I haven't yet figured out. Any clues?

[lynchk21]

try  SORT(MyTeam[player_name])   

[victsei]

Perfect! It does work!

[JPAlpano]

There are many ways to create dropdowns.  Search something like "ways to create dropdown lists in Appsheet" in youtube.

Just select the one method you are most comfortable to work with.