In a Lookup Table, sort by a third field, not Value or Display

[JohnM]

Is it possible, in the Lookup Table that populates a Dropdown, to sort not by the “Field for display” or the “Field for value” but by some other field in the “Table or view”?

 

 

In my case, I don’t want to reenter (and change when needed) the options in a Dropdown as custom values, in all the places I use this Dropdown. I want to store the options in a table and re-use as needed. But I need a sort order that is not alphabetical. I expected the “Sort by” to list fields, but it does not.

[futurist]

Hi @JohnM,

Can you specify how do you want your lookup table to be sorted by? Based on the screenshot you provided, how do you want them to be shown in the dropdown?

[JohnM]

Hi @futurist. I want the items to appear in the dropdown as they are shown in the screenshot. The Type_of_Item numbered 1 would be at the top of the dropdown. The one numbered 10 would be at the bottom.

[futurist]

Hi @JohnM,

so you want the options to be ordered by the Display_Order, right? I suggest you use the Display_Order on the "Field for value" then change the "Sort by" to "Value", but I think the reason why you chose "Type_of_Item" for both "Field for value" and "Field for display" is because you want to save the actual value instead of the display order number. With that being said, may I also suggest creating a relationship between Types_of_Archival_Items and the data source for the DataPage you are creating and link them together based on the field, and enable the "Use display view on DataPages" so you should still be able to see the value tied to the selected item (even if the Display_Order is the saved value):

I hope that made sense!

[JohnM]

 

Yes, in Type_of_Item I want to save the string “Letter” or “Email” or whichever,  not the Display_Order integers.  Would your approach let me do that?

 

[Tubby]

51 minutes ago, JohnM said:

 

Yes, in Type_of_Item I want to save the string “Letter” or “Email” or whichever,  not the Display_Order integers.  Would your approach let me do that?

 

@futurist's approach may not let you do that directly but you can use a Virtual field for the dropdown and do what @futurist did to be able to sort the options by sort order then for the actual field, make it's form element a "Calculated Value" and use a SELECT statement to save the string instead of the number.



Should look something like this when previewed.

Then you can just hide the actual field in the Advanced section, just check "Hide field". It will still save don't worry.

[JohnM]

I think I see. It’s roundabout but might work.

Is there a better way to handle a case where the same drop-down options are used in several places in the app? For maintainability,  it would be good to store the list (and its order) in one place. 

I really expected “Sort by” here to allow sorting on any field in the selected table or view. 

 

[kpcollier]

Another option... still a bit cumbersome. 

If you select 'Sort By: None', it will sort the values by the order in which they were inserted into the table. Which, by looking at your first screenshot, it seems you already have that in place.

Instead of changing order/adding new values to Custom Values in every dropdown, you would change the values in the table once. This can be done manually... or you can have an excel spreadsheet or something where you can edit your values there and then use the Import feature to set your Caspio table.

[JohnM]

@kpcollier, that isn’t too cumbersome at all. That is what I’ll do. I should have thought to try it! 

 

Thanks!

[Kurumi]

For sorting the values in the Dropdown, you can check this forum post: 

Here are some references:

Numerical

- https://stackoverflow.com/questions/71075997/jquery-how-to-sort-list-text-taking-into-account-numerical-values
- https://codepen.io/AdamCCFC/pen/ZWpZLE

Alphabetical

- https://stackoverflow.com/questions/61460640/custom-sort-of-dropdown-options-using-jquery
- https://stackoverflow.com/questions/54479381/jquery-order-select-box-with-numbers
- https://stackoverflow.com/questions/12073270/sorting-options-elements-alphabetically-using-jquery