How can I change the pull-down order in another column?

[Emily]

Thank you for your continued support.

It is a consultation about the order of pull-downs when creating pull-downs.

The app has two tables, “department” and “user”.
The department table has a sort_num column for pull-down sorting.

I entered the following formula in Suggested values in the department column of the user table, but the id is displayed in the pull-down.

ORDER BY (department [department_id], [sort_num])

How can I sort by the sort_num column of the department table and display department_name in the pull-down?

[TeeSee1]

Do you have your Department Name set as the label of the department table?

[Emily]

@TeeSee1

Thank you for your reply.

No, department_name is a column name, not a label.
The department table is as follows.

[TeeSee1]

In your column definition, you set your department_name as the label.

See this

https://help.appsheet.com/en/articles/961466-row-labels

[takuya_miyai]

Hi @Emily

If the department column is an Enum type, it may be displayed with a Label name if the base type is set to Ref.

FYI
@TeeSee1

[Emily]

@TeeSee1

Thank you for your advice.
However, I set the department_name column to the label, but the order displayed in the pull-down was not the order of sort_num.
Also, setting sort_num to the label did not change.

I want the pull-down to show Sales, Marketing, Admin in that order.

[Emily]

@takuya_miyai

Thank you for your help.

I changed the department_name to Enum, but the pull-down order displayed in the user form did not change.

[takuya_miyai]

@Emily

Is Basetype in Ref?
Please refer to the following.

If this doesn’t help, a screenshot of the column settings and the Key and Label settings for the reference may help.

[takuya_miyai]

@Emily

Oh, I’m sorry.
I see that you are able to display the Label. (I overlooked it because this community has become hard to see)

Can you show me a screenshot of the column setting where you are setting the Orderby?

[TeeSee1]

In the “Auto Compute>>Suggested Values” of the Dept Name field of the USER table, could you try to enter..

ORDERBY(department[department id],[sort_num])

[Emily]

I set the expression given by @ TeeSee1 to the suggested value of department_name.
However, the pull-down order does not change …

[takuya_miyai]

@Emily

Perhaps you have the following list in your Valid if?

department[department_id]

That may have priority over the others.

[TeeSee1]

Can you check this demo

https://www.appsheet.com/Template/mobilepreview?appId=ccba9780-49c4-4ead-ab44-e7b3a1626b3f

EDIT

See here to take a look inside

https://www.appsheet.com/portfolio/3401559

[Emily]

Thank you very much for creating the demo!
The department names sorted by sort_num are displayed in the pull-down menu! !!
I was impressed that there was such a way.

It would be perfect if this could be done, but is it possible to prevent the selection in the pull-down from being changed if the master is changed?
Also, when adding a choice, can it be added temporarily instead of being added to the master?(Like setting “Allow other values” in Enum type column)

[TeeSee1]

Curious

“prevent the selection in the pull-down from being changed”

“can it be added temporarily instead of being added to the master”

=> Why do you even need a master list if you do not want to see the changes in it? And your user’s department will have no system relationship to your dept table.

Well, but if you really want to do what you are saying, the below setting may provide a solution

I have updated the demo. See if it is what you are looking for.

[Kirk_Masden]

Coincidentally, I was just doing something similar. In my case, I put the items I wanted in my pulldown menu for an Enum column in Data Validity → “Valid if” and that determined my order. You are working with a different column type but I wonder if putting your expression in “Valid if” might not fix your problem.

[takuya_miyai]

Hi @Kirk_Masden

Perhaps @Emily would like to achieve this with Suggested Value.

Therefore, I believe that the minimal needs can be met with NULL for Valid if and ORDERBY for Suggested Value.

[Emily]

I am developing an application management system on AppSheet for another project, but there was a request that the user did not want to update the information of the project applied in the past.

Thank you for your suggestion. It means that you need to specify it directly with Suggested value.
Thank you for your long-term support!