Grid: sorting of a column using lookup value

Applications Grid / Reports
#1

I have a grid with a field that uses an integer in my sql statement and I use lookup to display another value.
The lookup is done on another table, to give you an example:
Table A is the one used for the Grid, and it has the column “employeeID”
But I don’t want to show the employeeID, so I use a lookup, so it goes in my Table B (employee table), where I can get the Employee name based on the employeeID:
SELECT EmployeeName
FROM dbo.AllEmployees
WHERE EmployeeID= {employeeID}
ORDER BY EmployeeName
my issue is, that when I try to sort by this column in my Grid, it will use the “employeeID” to sort, and not the Lookup value (Display value) by ordering in alphabetical order based on Employees names!

How can I achieve this?

#2

For a report or Grid (you dont need do it with lookup) create a join.
ex:
select tableA.field1 as Field1, tableA.field2 as Field2, tableB.empl_name as Employee
from tableA, tableB
where tableA.id_emp = tableB.id_emp
order by tableB.empl_name

1 Like
#3

But what i can do, if my application is editable grid, and one field is a lookup from another table? In editable grid we cannot use more than one table name in main sql query. So no chance to create a join. And it will sort not by lookup value, but with id. Any ideas?

#4

So yes, in my particular case - for editable grid application we can add something like:

(select somefield from table2 where table1.id = table2.id)

inside ORDER BY in main sql, and it will sort like we need. Where “somefield” is the same thing from lookup value

#5

can you use a global var? eg.

ORDER BY [GLOB_VAR]