Is it possible to sort a TDBGrid on a lookup field?

Question

I have a DBGrid with a column based on a lookup field.

How can I set it up so that when a user clicks on the column title, it will sort by that field.

My problem here is that I can't figure out a way to create an index on a lookup field.

I'm using Absolute Database for this, but most things that work with the BDE or TClientDataSet will work with Absolute.

Thanks!

Answer 1

I don't think it is possible to create an index on a lookup field. It is possible to create an index on an internally calculated field of a ClientDataSet though. In the OnCalcFields event handler set its value to the value of the lookup field. And set the visible property of the lookup field to false. Now you can sort on the internally calculated field.

Answer 2

You cannot sort by a lookup field. But you can 'fake' this. Let's suppose that you have the following tables: (PK means Primary Key)

Contacts

• ID - Integer (PK)
• NAME - Varchar(40)
• COUNTRYID - Integer

Countries

• ID - Integer (PK)
• NAME - Varchar(40)

Then you can have the following query in the dataset which is linked to the TDBGrid:

SELECT C.ID, C.NAME, C.COUNTRYID, CO.NAME 
FROM CONTACTS C
JOIN COUNTRIES CO ON C.COUNTRYID=CO.ID

(Not tested but I think that you got the idea)

Also you can put this in a view.

Then you'll display in your TDBGrid (as columns) only the ID, NAME and the desired lookup field which you already have (let's call it COUNTRYLOOK).

When one clicks on the Title Header you can change the query by adding in the 4th line an ORDER BY . For the specific column of the lookup field (COUNTRYLOOK), instead of using the 1:1 mapping you can put in the 4th line of your query ORDER BY CO.NAME. Reopen the query and that's it. In practice is much more simpler than my description here.

Answer 3

What you could do (especially if the data is readonly, and does not have zillions of rows) is use a ClientDataSet to display data in your grid.

Roughly the steps would be like this:

1. Load the data from your regular into the ClientDataSet,
2. add a calculated field to the ClientDataSet that contains the value obtained from the lookup,
3. then add an index to that calculated field.

--jeroen

Answer 4

I'm trying to do the sorting on a lookup fields the way like you mentioned (make an InternalCalc field). But something always goes wrong.

I have a LookUp field (called NameField) and a CalcField (NameCalcField). In the ClientDataSet's OnCalcFields event I do the following:

DataSet.FieldByName ('NameCalcField').AsString:=DataSet.FieldByName ('NameField').AsString;

Then I add an index to that calculated field and set the index for the ClientDataSet, but I always got the original order back, when the index was created. For example:

Before the index creation:

FieldA   NameField   NameCalcField
3        B           B
1        C           C
2        A           A

Now I create an Index on NameCalcField. After that I sort it by "FieldA": ClientDataSet.IndexFieldNames:='FieldA'; It works.

FieldA   NameField   NameCalcField
1        C           C
2        A           A
3        B           B

Now I tell the CDS to use the Index that I've created:

ClientDataSet.IndexFieldNames:=''; ClientDataSet.IndexName:='Index1';

The result is:

FieldA   NameField   NameCalcField
3        B           B
1        C           C
2        A           A

The original one. The order should be "ABC".

What am I doing wrong?

Thanks!