How do I add a search field to a dataset?
I have a dataset for which I need a search box. The problem is that this dataset structure is determined by the query result. I cannot add the field as a TFieldDef before setting .Active = true;
because it is overwritten and I cannot add it after running the query because you cannot change the structure of the open dataset.
There must be some way to do this. Does anyone know how?
EDIT: There seems to be some confusion about what I am looking for. I am not looking for a search during the query. I'm looking for a search field , a TField object where FieldKind = fkLookup, so that it can be used with a search field with a list of data designed to edit the data after the query has returned a result.This has nothing to do with SQL and everything related to Delphi data model and data-centric controls.
source to share
You need to create the fields yourself before opening the dataset.
First get all your field definitions from the database
DataSet.FieldDefs.Update;
Then loop through each fielddef and create a normal field as well as create a search field where needed. A simplified version of code like this;
for I := 0 to DataSet.FieldDefs.Count - 1 do
begin
// Allocate a normal field
AField := DataSet.FieldDefs[I].CreateField(DataSet);
// Add lookup field if we have another table to look it up from
if (??? this is the key field of a lookup table) then
begin
AField := TStringField.Create(DataSet.Owner);
AField.FieldName := ???;
AField.DataSet := DataSet;
AField.FieldKind := fkLookup;
AField.KeyFields := ???;
AField.LookupKeyFields := ???;
AField.LookupDataSet := ???;
AField.LookupResultField := ???;
end;
end;
Then you can open the dataset.
source to share
You have two datasets in your form (say tblOrder , tblCustomer )
One field in the order is a foreign key to the customer table customerId
Key field of the customer
table Id , Name = Name
Add all fields (right click on datasets, in the field editor, add all fields.
Then right click on the table of orders and select the field editor, then right click on the new field .
Name = myLookup, Type - string, size - xx, Field Type = Search .
Key Field = customerid, dataset = tblCustomer, lookup Key = Id, Result Field = Name .
Your search field is now defined.
To make it work in an editor (say in TDBLookupCombo)
Add data sources to the dsOrder form .
Connect it to tblOrder.
Now set datasource = dsOrder, Field = myLookup
You don't need to set search source ...
source to share
Suppose you have a table Orders
with a field (among other things) CustomerId
(of type Integer
) and a table Customers
with fields of CustomerId
(type Integer
) and CustomerName
(type String
). Then in the Delphi IDE the table_main
datasets form table_main
and table_lookup
(for tables Orders
and Customers
accordingly). Open the field editor for table_main
, add (among other things) a field CustomerId
, then create a new field with field properties:
name: CustomerName (for example)
type: integer
field type: search
and with search properties:
dataset: table_lookup
Key fields: CustomerId
Search Keys: CustomerId
Result fields: CustomerName
Hope this is still relevant now.
source to share