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!

[dbaum]

See Dependent drop-downs - AppSheet Help. 

---

@ltjbsa wrote:

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").

---

Various modifications to consider:

• If the [Player ID] column is a Ref type that references the Players table and the Players table's label column is the player's name, then you may not need this separate column at all--the [Player ID] column would simply show the player names.
• Again, if [Player ID] is a Ref type column, then even if you need this [Player Name] column you shouldn't need to use any function like LOOKUP. Rather, just use a dereference expression, e.g., [Player ID].[_PlayerName].
• Your expression perhaps should be in the column's App formula property rather than its Initial value property.

[ltjbsa]

Thank you. I should have explained the useage a bit more:

Coaches are evaluating players on a field. The players are wearing nametags that are their Player ID number. There are 30+ players on a field at a time, and 8 groups throughout the day. 

So the coach will view at the Player ID Badge worn by player, find that ID number in the dropdown form in the app (or search for it) and then begin the evaluation portion of the form. The Player Name autofil is a benefit to the coach, so that after they evaluate the player they can call him by his name. That is why the Players Table label is Player ID and not the Player Name - players are referenced on the field by their unique ID. 

Given there are multiple groups on the field over the course of the day, I wanted to shorten the list of numbers in the Player ID field by reducing it based on the Group currently on the field. So instead of having 200 Player IDs to scroll to, or having to type "263" and wait for the search, the contents of the Player ID box would be reduced based on the Group selected. 

This is why I need the sequential connection Group --> Player ID --> Lookup Player name.  The Group limits the universe of players IDs to those on the field at that time, and then the coach uses the ID name badge to understand the players name. 

Thank you for showing me a dereference expression. I am new to this and have edited that. I don't understand from the link you sent why I might want to put the expression in App Formula vs Initial Value -- the article you linked to says to do both ("Add an App formula and Initial value.")

[dbaum]

---

@ltjbsa wrote:

That is why the Players Table label is Player ID and not the Player Name - players are referenced on the field by their unique ID.

---

Sure--makes sense.

---

@ltjbsa wrote:

This is why I need the sequential connection Group --> Player ID

---

ICYMI: See Dependent drop-downs - AppSheet Help.

---

@ltjbsa wrote:

App Formula vs Initial Value

---

In slightly oversimplified terms: Initial value is calculated when a row is created; App formula is calculated every time a row is updated. So, if a row's Player ID value ever changed after initial row creation, the Player Name value would no longer match. Based on your description, perhaps that never happens, in which case putting the expression in either property is functionally equivalent.

[ltjbsa]

Thank you. I am getting closer.  I went through the Dependent Drop downs video and was able to create the link between the dropdowns, but am having this strange behavior:

Essentially, I want the GROUP to be a dropdown/selection, not to appear after I have selected the Player ID. Any advice on what I am missing?

Here is how I have those fields set up:

I am slowly learning this platform and appreciate your advice to nudge me along. Thank you in advance. 

[ltjbsa]

One more detail: When I change the Group type from TEXT to REF, the field becomes a dropdown but it does not display until the user takes action on the Player ID function (reverse of what I want...Ideally I want it to work exactly like it did in the video).

[dbaum]

I don't know what the issue is. Here are some troubleshooting techniques.

• Ensure the [Group] column's Show if property is enabled outright--i.e., doesn't have an expression that shows the column only if [Player ID] is  not blank.
• The help article references the sequence of columns (i.e., "...because it follows the Lead Region column..."). This likely refers to the sequence within the data source (e.g., not the sequence in the view). Ensure the [Group] column precedes the [Player ID] column in the data source.
• The help article links to a sample app. Review that app's configuration.
• The help article provides the explicit behind-the-scenes expression for its example. Try replicating that.

[ltjbsa]

Thank you. I figured it out - to close the loop.  The "Group" field in the table was inadvertantly read-only. This is now working, sort of:  

How can I alphabetize/order the lists in the drop-down? I have tried (in GROUP valid-if)

SORT(
SELECT(
players [player_group],
TRUE
)
)

Which alphabetizes the list but breaks the linkage between Player ID (and I don't understand why). 

I have also tried 

ORDERBY(players[player_key], [Player ID], TRUE)

in the Player ID field but that also breaks the linkage with Group. 

See the animation below - I'd like Group to be in order, the Player ID dropdown to be always present, and the numbers in the Player ID dropdown to show as sequential.

Second, Is there any way to make the form for Player ID a dropdown with a search box? I have tried to put the expression "players [player_key]" in the Suggested values box (screenshot) but this breaks the link to the Group field above (meaning, no filter based on Group)

Thank you.

[dbaum · Accepted solution]

---

@ltjbsa wrote:

How can I alphabetize/order the lists in the drop-down?

---

As explained in the other thread that you referenced in a post on this question, the only approach may be to use the technique of a more explicit expression that is described in the AppSheet Help article. I don't know why the behavior you describe seeing in the sample app seems not to depend on this technique.

---

@ltjbsa wrote:

Is there any way to make the form for Player ID a dropdown with a search box?

---

I don't know what governs the availability of the search box. Potentially it just appears if the list of values exceeds the number that fit the screen without scrolling. If you're not able to get it to appear reliably, I suggest post a new question.

---

@ltjbsa wrote:

players [player_key]

---

Normal syntax would be to not use a space between the table name and the column name.