Board index » delphi » Filtering on Lookup field display value

Filtering on Lookup field display value

I'm using all vanilla Delphi 6 (w/UP1) components
including a TDBGrid, TADOConnection, TDataSource,
and 2 TADOTables (one for the grid and one for a
foreign key lookup - to display the name of the referenced
item instead of the ID value.)

It all works great, but my client wants to be able to
filter and sort on the value appearing in the lookup
field.

Is this possible?

Attempting to filter using the lookup field display
value causes an EOleException
"Item cannot be found in the collection corresponding
to the requested name or ordinal"

At runtime the TADOTable.Fields.Count = 11
and TADOTable.RecordSet.Fields.Count = 10

So I suspect that under the covers the recordset
knows nothing of the lookup field and the filter
is being applied to the recordset.

Any guidance would be appreciated.

Thanks,
Mike

 

Re:Filtering on Lookup field display value


I'm using all vanilla Delphi 6 (w/UP1) components
including a TDBGrid, TADOConnection, TDataSource,
and 2 TADOTables (one for the grid and one for a
foreign key lookup - to display the name of the referenced
item instead of the ID value.)

It all works great, but my client wants to be able to
filter and sort on the value appearing in the lookup
field.

Is this possible?

Attempting to filter using the lookup field display
value causes an EOleException
"Item cannot be found in the collection corresponding
to the requested name or ordinal"

At runtime the TADOTable.Fields.Count = 11
and TADOTable.RecordSet.Fields.Count = 10

So I suspect that under the covers the recordset
knows nothing of the lookup field and the filter
is being applied to the recordset.

Any guidance would be appreciated.

Thanks,
Mike

Re:Filtering on Lookup field display value


Quote
"Mike Fidler" <mikefid...@aka-group.com> wrote in message

news:3c055502$1_2@dnews...

Quote
> I'm using all vanilla Delphi 6 (w/UP1) components
> including a TDBGrid, TADOConnection, TDataSource,
> and 2 TADOTables (one for the grid and one for a
> foreign key lookup - to display the name of the referenced
> item instead of the ID value.)

> It all works great, but my client wants to be able to
> filter and sort on the value appearing in the lookup
> field.

> Is this possible?

> Attempting to filter using the lookup field display
> value causes an EOleException
> "Item cannot be found in the collection corresponding
> to the requested name or ordinal"

> At runtime the TADOTable.Fields.Count = 11
> and TADOTable.RecordSet.Fields.Count = 10

> So I suspect that under the covers the recordset
> knows nothing of the lookup field and the filter
> is being applied to the recordset.

> Any guidance would be appreciated.

> Thanks,
> Mike

I believe that it is failing because you can use a lookup field in the
persistent TDataSet.Filter property.

I've done filtering by lookup field in the following manner...

1) Clear TADOTable.Filter property.
2) Set TADOTable.Filtered to True.
3) Create an event handler for OnFilterRecord.
4) In the OnFilterRecord handler, you will be able to perform your
comparison directly against the lookup field (because this is done at
run-time).  Simply set the Accept output parameter to either True or False,
depending on whether or not you want to include the record.

Warning: records will disappear from the DBGrid if you post edits that make
them unaccepted to your filter.

If you want to sort the records...

Is the lookup field recordset static or dynamic?  That is, will the use add
records to the lookup table, or is the content of the table defined by you
at design-time?  If it is defined by you at run-time, you could change the
primary key of the lookup table to be alphabetical in nature.  For
example...

Books (main table)
-------
BookID <-- Integer, primary key
CategoryID <-- Integer, indexed

Categories (lookup table)
-------------
CategoryID <-- Integer (not Autoinc), primary key
Category

As long as your CategoryID is defined by you, you can impose any ordering
you want.  That is, you could ensure that the CategoryID values in the
Categories table followed alphabetical order as far as the Category field is
concerned.

If the user can modify your lookup table content, then you have a nastier
problem...

You will (probably) have to abandon your TADOTable in favour of a TADOQuery.
Include the join to the lookup table in your SQL and name in the lookup
field in the SORT BY segment.

Warning: If you do abandon your TADOTable for a query, you will lose (or
complicate) your ability to edit the recordset in the DBGrid thereafter,
though.  Not a problem if you're only browsing results.  You might have to
resort to bringing up a modal form to edit individual records if you still
require that functionality (and then Resync to the current record after a
successful edit.)

Hope that helps.

Sean Dockery
s...@sean-dockery.com

Re:Filtering on Lookup field display value


Quote
> I believe that it is failing because you can use a lookup field in the
> persistent TDataSet.Filter property.

Replace can with can't in the above sentence.  :-)

Sean Dockery
s...@sean-dockery.com

Other Threads