Populating Dropdowns - Best Practices?

[mykrobinson]

When populating a dropdown box with values using a reference, is it best that each dropdown list be its own page in a "variables" spreadsheet, or is it okay to create a single page for variables with dropdown options in its own column?

[Joseph_Seddik]

Could you please explain your question further? maybe with examples? Thanks.

[mykrobinson]

Would this be okay, or should Dropdown 1, Dropdown 2, and Dropdown 3 be separate pages in the spreadsheet since the information in each row is not related to each other in any way?

[Steve · Accepted solution]

Neither of those approaches is "best". Instead, create a table called Options with three columns: a Key column, a Context column, and an Option column. The Context column identifies the context for which the row's Option column is valid. The Option column contains a valid option for the row's given Context value.

Key	Context	Option
1	names	Bob
2	names	Sara
3	names	Mateo
4	directions	North
5	directions	East
6	directions	South
7	directions	West

To use the names options, for instance as Suggested values:

SORT(
  SELECT(
    Options[Option],
    ("names" = [Context]),
    TRUE
  )
)

To use directions options, perhaps in a Valid If:

SELECT(
  Options[Option],
  ("directions" = [Context]),
  TRUE
)

[mykrobinson]

I have much to learn 🤔

Thank you

[Joseph_Seddik]

Hello Steve, 

A question please. Wouldn't creating separate tables allow us to avoid the use of SELECT? This is what I've been doing. If this approach is suboptimal I'll go back and change it. What do you think please? Thank you!

[Steve]

Sure, separate tables would eliminate the need to use SELECT(), which makes for simpler expressions and allows for dependent dropdowns. I don't like having all those extra worksheets, though. You could also use my single worksheet approach then use slices to separate the options within the app. I use this approach when the Options table gets large to improve efficiency.

Your approach is perfectly fine; it's a matter of preference.

[mykrobinson]

If i may deviate for a moment, in your example, let's assume you have a long list of options for various things. How do you go about generating your key?

I like the idea of UNIQUEID() in appsheet, but i have also learned that if I manually populate a spreadsheet with information, AppSheet doesn't see that data if the column with the key is not populated in advance.

If I understand this correctly and am using the right words, AppSheet cannot retroactively populate a key with UNIQUEID(), the key field must be populated in advance in order for AppSheet to "see" the data. Is this correct? Also, using a formula is in a key field is not supported either, right?

I saw an article about using a spreadsheet formula to create values similar to how UNIQUEID() works, but since it doesn't like spreadsheet formulas for keys, I feel i am missing something due to inexperience here 🤔

[Steve]

If you're populating a spreadsheet manually, I'd just drag the previous key's cell down to generate an ascending series. It's really entirely up to you. Just ensure the Key column has a value, else AppSheet won't see that row.

[Joseph_Seddik]

Single sheets with slices, I haven’t thought of that and that’s definitely better 🙂

Thanks a million @Steve 🌷