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

Scriptcase 7 Bugs
#1

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.

#2

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

#3

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.

#4

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

and a DESC or ASC after the name ?

#5

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

#6

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

#7

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?

#8

I’m pretty new to SC, so this may not be correct, but in raw PHP and other systems, the trick to solving this is to do a join with the lookup table so you display the name and not the ID as part of the grid. Then you can sort that column with no problem because the actual name is in the grid, not the ID. I don’t think it’s reasonable to expect any system with a grid and a lookup to sort on the lookup value. Just my opinion, of course.

I came from a CodeCharge Studio background, and when you did a join to solve this grid sort problem, it had to be a grid that you weren’t going to update, or else you had to write the insert and update code yourself. That was certainly do-able.

Your only issue here is if you need for that to be a lookup field that the user can change. In that case, this solution won’t work. Hope that helps, or at least gives you some ideas.

1 Like
#9

Agree - it would be nice if it sorted by the look up value (as it does in many other areas) but the work around is to INNER JOIN to the look up table in the initial SQL and then display that as the column.

Irritating but fairly easy to work round.

#10

But what about the same problem, but with editable grid application? We can use only ONE table name in main sql statement, no chance to extend it with “join” or “where f1.t1=f2.t2” clause…

#11

Yep, this one is a bit of a basket case. The “Sort” section of the (form) editable grid application has fields from the original source table and a complete list of fields including my derived ones. However, I cannot use any derived fields to sort by. While obviously not a bug, IMHO it’s definitely a feature that’s needed.