Configuring TIB_LookupCombo

[Article by Lars George lgeorge@gmx.net ]

TIB_LookupCombos provide the same functionality which can be found in the VCL's TDBLookupCombo plus a lot more. They can have custom popup controls like TIB_Grids or provide incremental search facilities. The major difference to the VCL's control is how it is set up and bound to datasources. The following will explain how this is done.

Setup the link
Although the TIB_LookupCombo works without being linked to another table where it can be used as a TIB_ComboBox, the main use will be providing a list of values which are gathered from another table. Both tables the main and the lookup table must have a relation definied so that the lookuptable has one field which is used in the main table to reference a record of the lookuptable. Let's work with an example here: Suppose we have the following tables in our database

Table Produce:
Name A(20)   Table ProduceType:   
Type A(4) ß------------------------------------à   Type A(4)
Description A(40)   Description A(30)
Price N(15, 4)
Qty I

The link is set between the two Type fields where table Produce has a foreign key constraint on its field Type related to the table and field ProduceType(Type). Hence the Type field is the primary key of its table ProduceType. The names of the used IBO components so far are

	QryProduce: TIB_Query   The query for the Produce table
	QryProduceType: TIB_Query   The query for the ProduceType lookup table
	DsrProduce: TIB_DataSource   The datasource for the main table
	DsrProduceType: TIB_DataSource   The datasource for the lookup table

What we want to do is set up an entry form where a standalone lookup combobox is placed nearby casual TIB_Edits. Also we want to have a grid, where all records of Produce are listed with the column Type not showing the internal short value but the full Description, which is located in the lookup table.

First we need two TIB_Query's to feed the entry form with data. We now have to enter the following details to each table:

Table Produce:
Property SQL

·	select
·	NAME,
·	(select DESCRIPTION from PRODUCETYPE T
·	where T.TYPE = P.TYPE) as TYPE_DESC,
·	DESCRIPTION,
·	PRICE,
·	QTY
·	from PRODUCE P

This achieves that we have a new column in our result dataset which is also shown in the grid named TYPE_DESC. The shown inline select is also called singleton select, because it returns just one value at any time. The foreign key definition of the main table and the primary key constraint of the lookuptable assure that this is always true as we can not have two records with the same value in its primary key. So all values in the field Type of the lookuptable must be unique.

Property ColumnAttributes

·	TYPE_DESC=COMPUTED

Here we tell IBO how to treat the generated field so it is handled and displayed correctly within all controls of IBO (e.g. TIB_Grid).

Set all other required properties like KeyLinks to the standard values, which are explained within this help file or in the IBO context-sensitive help.

Table ProduceType:
Property SQL

·	select * from PRODUCETYPE

As you can see there is nothing special here.

Property KeyLinks

·	PRODUCETYPE.TYPE=PRODUCE.TYPE

This is the the first step of the link between the main and lookup table. It tells IBO which fields are the one belonging to the actual link.

Property KeySource

·	DsrProduce

This is the second step of the link. Here we assign the first query, which is the main table, to the second query, which is the lookup table. Do NOT use any of the MasterXXXX Properties to set up the link as this is not a master/detail relationship rather than a real lookup relationship.

Property KeyDescLinks

·	PRODUCETYPE.DESCRIPTION=TYPE_DESC

To allow IBO a proper handling of visual updates within the grid you have to declare, which field of the lookup correspond to the computed field in the main table.

Property OrderingItems

·	Type=TYPE;TYPE DESC
·	Description=DESCRIPTION;DESCRIPTION DESC

This is the first step of telling IBO how the records in the result dataset could be sorted. Refer to the context-sensitive help of IBO to get more details about this.

Property OrderingLinks

·	TYPE=1
·	DESCRIPTION=2

Another setting for the ordering functionality of IBO. See above.

Property OrderingItemNo

·	1

With setting this property IBO automatically switches the sorting to the first OrderingLink. Hence all record are sorted ascending by Type.

Finally assign both queries to the appropriate TIB_DataSource, which are named above.

Assembler
Now how we have configured the non visual components, what do we have to do to get this working: Place a TIB_Grid, a few TIB_Edits and a TIB_LookupCombo onto the form. Than place another TIB_LookupCombo onto the TIB_Grid which you inserted first.

Attention: First click on the grid to select it within the Delphi designer, then click on the TIB_LookupCombo icon in the tool palette. Finally click on the grid again and the selected TIB_LookupCombo will show up within the grid. This is the magic trick to make a not standalone lookup rather than one which is automagically used by the grid!

Here are the names of the new components used:

	CbxLkpProduceType: TIB_LookupCombo   The standalone lookup combobox
	CbxLkpGrid: TIB_LookupCombo   The lookup combobox for the grid
	GridProduce: TIB_Grid   The grid for the maintable
	EdtXXXX: TIB_Edit   The edit controls for the remaining fields

Now assign the datasource of the main table DsrProduce to the datasource property of the grid and the edit controls. Edit the DataField property of any TIB_Edit to make them data-aware.

Assign the datasource of the lookup table DsrProduceType to the datasource property of both TIB_LookupCombo's as they are responsible to show exactly these record.

I do not go into greater detail here about how to open the queries at startup and so on. This should just point out the important steps in respect to the use of TIB_LookupCombo controls.

Custom Grids
A very neat feature is the possibility to assign a custom popup to any TIB_LookupCombo control. What you can do is just drop a TIB_LookupList onto the form and hook it into the desired TIB_ LookupCombo's CustomPopup property. The TIB_LookupList is simply derived from a normal TIB_Grid which sets a few more default options while being created (e.g. ListBoxStyle = True). After dropping the TIB_LookupList assign the lookup table's datasource to the matching property of the newly created control.
The greatest advantage of using custom popup is the sorting feature of it. Because we defined the OrderingLinks above you can easily sort the custom popup by clicking on any column header. This is simply the same functionality provided by any TIB_Grid you use.
Also by changing the ordering column the TIB_LookupCombo automatically changes the displayed text to the one which is displayed in this column. By changing the ordering column you can let the user decide if he want the short version or the long description displayed in his lookup comboboxes.

NB: You can override this feature by assigning a value to the DisplayField property of the TIB_LookupCombo. The different sorting and searching is still fully functional, only the displayed value will always be the one you would like to be.

Conclusions
The drawback of the IBO way to achieve lookup functionality is that you need a query for every lookup you want to have. And even worse, if you use custom popups you will need the same number of them. Assume you have a table with eleven fields where ten of them have to be fed by the same lookup table, then you end up having ten queries and ten lookuplist components on your mainform rather than just one of them.

But hang on … Jason is investigating on this (minor) one