Grid: sorting of a column using lookup value

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?

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