[SOLVED]Column Sort on an Automatic Lookup field doesnt sort on the Display value

I have a grid application on a table that has ID fields that I use the grid lookup automatic lookup method to display the text value associated with the ID. That all works fine, except when I make the column sortable and click the column. I expect the column to sort by the text values, not by the integer ID values. Example:

Lookup table:
ID Name
1 randy
2 frank
3 larry

In my Grid application, the source table has ID field that I use the Grid Lookup Automatic method to display the Name value from the Lookup table. When I click on the column to sort it I would expect the sort to be:
frank
larry
randy

Instead I get:
randy
frank
larry

Which is the order of the underlying ID field, not the order of the Display value of the Name.

Re: Grid Column Sort on an Automatic Lookup field does not sort on the Display value

you have to expand your SQL in the Lookup with something similar to ORDER BY Name DESC

Re: Grid Column Sort on an Automatic Lookup field does not sort on the Display value

I checked all the lookup SQL and everyone of them has the ORDER BY clause with the field that is to be displayed.

Re: Grid Column Sort on an Automatic Lookup field does not sort on the Display value

and a DESC or ASC after the name ?

Re: Grid Column Sort on an Automatic Lookup field does not sort on the Display value

Hi,
it can’t work this way because you can only sort by field value not what’s displayed. The ORDER BY statement in the Lookup option has nothing to do with the sort function of the grid.
It is just for displaying an associated value from another table. If you want your grid to be sorted by the name than you have to include it in your SELECT statement of the grid. Same applies to SEARCH function.

jsb

Can you give me the sample code. I am trying to do the same with a grid for contacts table. under contact_type it draws from listboxe_contacttype. I have tried a million combinations of select and order by and it always results in an error

I don’t understand how this thread is marked [SOLVED]… the original issue hasn’t been solved at all. I’m facing the same situation with v9 and don’t know what to do.

Anyone figured this out yet?