Sorted Value List Needing Both Columns

[HandOfCode]

I want to display a value list that is sorted by year, then name, of the records.

The list should pull it's data for both fields from a table, one field being a PK, the other being the name.

Most solutions I've seen are quite restrictive as they require the use of one of the two fields for the purpose of sorting, but I need both fields.

The only trick I can think of is numbering the PK relative to where an item should appear in the value list, but I see this as more of a kludge than anything any would like to avoid it.

[bcooney]

Can you provide more info? You say that you want to store a primary key (good) and show a second field (name). I'm not sure where year fits in this scheme.

What fields do you have in the table from which you'll create the value list? Are you aware that you can use a concatenated field as the second field?

[HandOfCode]

Oh field info, sorry forgot. The table I'm pulling the value list from has a "year" that the event occured (it's an events table) and what I want to do is sort the entries by Year decending, then by name, to effectively "group" the events by year. (since it's quite unlikely that older events will be among the user's desired choices I want them near the bottom, and recent events near the top)

The reason I need to use both fields is the first field is set to the primary key, and the second field is set to the name, and the field that gets the value list is the PK field. (using some sizing tricks and "activate field" scripting I hide the actual ID field and display the dropdown when they click the name field)

I don't want my users to be able to see or edit the ID field directly, it should be out of sight and out of mind. They should only concern themselves with the name of the event they want.

[Ron Cates]

Why not use a filtered value list? Have the user select the year they want to select from and filter the value list showing only selections from the selected year.

[HandOfCode]

Adding buttons/dropdowns adds cognitive load, also the added step of having to choose a year every time wastes the users time. The more buttons you have, the more that can go wrong (user forgets to click, forgets to select, etc.)

[comment]

A value list is always sorted by one of the two fields used. This type of requirement is beyond the capabilities of a simple drop-down. Consider selecting from a list or a portal instead.

[HandOfCode]

Was afraid of that, ok I'll just do a modal pop-up window. Thank you for your help.

[David Jondreau]

This may help. Hot off the presses!

[HandOfCode]

What (if any) significance does "test" have in the primary key?

Edit: ugh took a while to see how it was working, this may work for what I need. Does anyone know some ascii (or is it unicode in FM?) values that are non-printing characters?