App Resorting Dropdown Values

Ms_Silver · 09-26-2022 03:10 PM

On my Google Sheet, I have a dropdown column that my app references. There's a set order, from top to bottom, that I want the values in. For example, some of the values are in this order: Happy, Angry, Surprised, Confused. My App sometimes puts the values are in the order I set. Other times, I'll open it and they are all mixed up.

I don't want them in alphabetical order so didn't use SORT(). My sheet does have an ID column. Or maybe I need to sort by _RowNumber? Can I make SORT() sort by those columns?

I'm using this expression in Suggested_Values of my DROPDOWN column:

SELECT(Lists[DROPDOWN],ISNOTBLANK([DROPDOWN]))

dbaum

I don't know why your list of values might sort differently on different occasions.

Nonetheless, you can use the ORDERBY function to sort a column's values based on another column from the same table.

Also, your SELECT expression could potentially be simplified to just Lists[DROPDOWN] if the column doesn't include any blank values. Maybe that's a factor in the inconsistent sorting?

Ms_Silver

Yeah it's very frustrating. Perhaps it is SELECT as you said. I did try simplifying it to Lists[DROPDOWN] but the app is still resorting it AND now includes a blank value in the list even though the only blank values on my sheet are after my last entry.

What would the ORDERBY() be with ISNOTBLANK? I tried this but it's invalid. (ID is my numeric column).

ORDERBY(Lists[DROPDOWN], "ID", false, ISNOTBLANK(Lists[DROPDOWN]))

dbaum

@Ms_Silver wrote:
the app still included blank values in the list even though the only blank values on my sheet are after my last entry

If that column's blank values are in otherwise non-blank rows, then the blank values should indeed be included in the results of a function like SELECT or a list of an entire column, such as Lists[DROPDOWNS]. Nonetheless, you can remove blank values via list subtraction: Lists[DROPDOWNS] - LIST("").

Confirm ORDERBY, which returns a table's keys and is apt for Ref type columns would provide the data you need and then follow the syntax defined in the help article. If you use ORDERBY, the first argument can be an expression (e.g., using SELECT or list subtraction) that removes the blank values.

Ms_Silver

Thanks!!!! I'll give Lists[DROPDOWN] - LIST("") a try for a bit and see if it resorts my values or not.

On the other hand, this is my first time using ORDERBY. Maybe I'm not understanding but it's returning my ID (key) numbers instead of my DROPDOWN column.

dbaum

@dbaum wrote:
ORDERBY, which returns a table's keys and is apt for Ref type columns

Indeed. It's most applicable when you have a Ref type column and the referenced table's label column is what you need users to see.

Ms_Silver

I made DROPDOWN ENTRY a Ref and made it reference my table (LISTS) where my DROPDOWN column is. In valid IF I put this:

ORDERBY(Lists[ID], [DROPDOWN ENTRY], false)

But my ID numbers are what is showing up in the dropdown list on my app. How do I get my DROPDOWN values to return instead?

Ms_Silver

Okay using this expression it seems on my spreadsheet the ID and the DROPDOWN column with my list have to be side by side. I had columns between them. Those columns were being added into the dropdown list on my app instead of the DROPDOWN column.

EDIT: I fixed it. I needed to change the Label in the Data Columns to DROPDOWN

Steve

Drop-down from worksheet data validation rules - AppSheet Help

Ms_Silver

I used this expression in Valid If:

ORDERBY(Lists[ID], [RECORD #])

It returned my list in the proper order but at the bottom I'm getting the rest of the ID numbers in the list. Not sure what's going on. Is there anything I can try to eliminate those numbers in the list?

Here's a fake example of my spreadsheet for reference.

dbaum

ORDERBY(SELECT(Lists[ID], ISNOTBLANK([Emotion]), [RECORD #])

Ms_Silver

Oh I see now. It was missing a parenthesis, but once added it worked perfectly. Thank you much!

ORDERBY(SELECT(Lists[ID], ISNOTBLANK([Emotion])), [RECORD #]