At 07:50 PM 21/04/2004 +0000, you wrote:

>Thanks Helen.
>I don't know if what I'm trying to do is so stupid or too much of
>against good DB design. There are things that have fixed values like
>gender=masc/fem and others.
>
>For this fixed small stuff I have a table I call TYPES like this
>INFOTYPE - INFOCODE - INFOVALUE
>gender - 1 - Masc
>gender - 2 - Fem
>place - 1 - Country
>place - 2 - State
>place - 3 - City
>Obviously, key fields = INFOTYPE and INFOCODE
>
>To get a customer's gender it's very easy to look it up:
>
>SELECT CUSTNO, CUSTNAME, (SELECT TYPES.INFOVALUE FROM TYPES WHERE
>TYPES.INFOCODE = CUSTOMERS.GENDER AND TYPES.INFOTYPE='gender') AS
>GENDER FROM CUSTOMERS
>
>This seemed smart at first because it saves me a lot of work and I
>didn't want to create a table for each little piece of info that
>never changes.
>
>This is why I'm having trouble with TI_LookupCombo:
>
>TIB_LookupCombo.Datasource.Dataset.SQL = 'SELECT TYPES.INFOVALUE FROM
>TYPES WHERE INFOTYPE = 'gender')
>TIB_LookupCombo.Datasource.Dataset.KeyLinks
>= 'TYPES.INFOCODE=CUSTOMERS.CUSTGENDER'
>
>When the lookupcombo does his internal stuff here's what it does:
>SELECT TYPES.INFOVALUE FROM TYPES WHERE INFOCODE = ?
>
>Thus the multiple rows for the singleton select.
>
>All I wanted was to have AND INFOTYPE = 'gender' added to it.
>
>OK I take critic, please if there's no way I can do it, you can call
>me crazy and tell me to look for treatment (ie a DB design course :-)

The mistake here is trying to link these two columns as a relationship in
the database: which is the purpose of TIB_LookupCombo. To make this work
relationally, you would need to one of two things, either:

1. Include both the infotype and the infocode as columns in the table that
uses the lookup and include both columns in the lookup dataset. You would
then have two KeyLinks fields in the Keysource-Lookup relationship in the
user interface.
or
2. Add a surrogate unique key to the lookup table. Then the seeking table
would store that key, rather than 'M/F' or whatever.

Neither of these is satisfactory. IMO, you would do better to abandon the
lookupcombo and use a query on your existing lookup table (once per session
only) to have a "non-live" connection between the lookup table and the
seeking table. Use tib_cursors to fetch these data during the FormCreate
of the datamodule - one query per tib_cursor - and use them to populate
comboboxes. Once the boxes are populated, you can just close down the
tib_query, since it's a one-off and you won't need it any more.

Unlike tib_lookupcombo, the tib_combobox is "single data-aware". You link
a single field (which can be invisible) to the field in the seeking
dataset; but the control itself is not dependent on a dataset to provide
"live" values. It has a nice little property ItemValues. During the
static loading, you can feed the value that you want to *store* into this
array, invisibly to the user.

I find these a lot more useful for these "little lookups" than the
double-data-aware control. If you surface your Types table in this manner,
then your design decision to stuff these various small lookup sets into a
single table makes good sense.

Helen