Hello I am having trouble trying to get fields to sort alphabetically in the list view, when the field uses a lookup wizard with a different link field and display field. What happens is the fields sort by the link field (which is a meaningless ID) rather than the display field.
So for example, say we have to following table of employee names
Employee Database Table
id ....... employee
1 ........ Steve
2 ........ Alex
3 ........ Darren
And then we use the employee IDs in the holidays table below so that we can see when different people are on holiday.
Holidays DataBase Table
id ....... employeeId ........... dateOnHoliday
1 ........ 1 .................... 17/3/08
2 ........ 3 .................... 22/7/08
3 ........ 2 .................... 09/2/08
4 ........ 1 .................... 04/2/08
We use a lookup wizard for the employeeId on the holidays table so that when adding entries we can select an employee name from the drop down box. In the list view the holidays table will be displayed as following
Holidays List
id .... employeeId ....... dateOnHoliday
1 ..... Steve............. 17/3/08
2 ..... Darren............. 22/7/08
3 ..... Alex.............. 09/2/08
4 ..... Steve.............. 04/2/08
But if I then try and sort by employee what actually happens is the employees are sorted by their Id and not name, so the resulting table look like this
Sorted Holidays List By Employee
id .... employeeId ........... dateOnHoliday
1 ..... Steve ................ 17/3/08
4 ..... Steve ................ 04/2/08
3 ..... Alex ................. 09/2/08
2 ..... Darren ............... 22/7/08
Which is sorted in the wrong order and confuses people. Is it possible to add proper lookup sorting?
A workaround is to manually edit the holiday table sql and join it with the employee table so that it looks like this
id ....... employeeId ........... dateOnHoliday ..... employeeName
1 ........ 1 .................... 17/3/08 ........... Steve
2 ........ 3 .................... 22/7/08 ........... Darren
3 ........ 2 .................... 09/2/08 ........... Alex
4 ........ 1 .................... 04/2/08 ........... Steve
Then employeeId is shown on the edit and add pages and employeeName on the list page (so that sorting works properly). This is fiddly to setup though and my project spans many many tables that work in the same manner, is there any other solution?
