Subject Re: [IBO] Helen .. perhaps this is clearer
Author Helen Borrie
At 12:20 PM 20/11/2004 -0600, Woody wrote:

> Don't you also have to set the gridlinks property of the lookupcombo to
>the fields you want displayed when it's dropped down? I know that I had to
>define them when I use the component.

No, although you may if you wish. You use GridLinks in grid controls
(including the drop-down part of tib_lookupcombo) to specify a field
ordering different from the order specified by the SQL statement.

Most often, a lookup set is just a unique key and a description. If you
don't want both in the dropdown, you set the Visible display attribute to F
in the dataset editor (or include ThatField=F in the FieldsVisible
list). You only need to use Gridlinks if you have multiple columns in the
drop-down and you want them in a special order.

Adrian Wreyford wrote:
>> I see I can do this with Foreign Keys, I read the chapter in your book, but
will leave that for another day!

Don't get confused between database rules and application rules. There is
often a foreign key relationship between a lookup record and the rows in a
table that uses it. The database rule helps the application, insofar as we
want the user rows to select a lookup key that actually exists. On the
database side, we can even implement the FK to allow null and use that to
implement a "vacant" lookup. The user row can still exist, even if the
lookup value is null. Thus, in our application design, we are able to make
use of a FK to implement our rules about lookups, without making the
existence of the lookup user row dependent on the existence of any specific
lookup key.

In apps we implement "useful" foreign key relationships as master-detail,
taking advantage of the capability of the database rule to enforce the
existence of one and only one "master" record (the referenced side of a FK
relationship) in order to permit creation of zero or more "detail"
records. We can add triggers in the database to enforce a rule that at
least one "detail" record must exist in order for it to be legal for the
"master" record to exist. A nullable FK is not much use to use in a
master-detail relationship, since we depend on a non-null FK to implement
master-detail in our application.

From the application's point of view, a lookup relationship is upside-down
to master-detail, in the sense that there may be many of your main records
referring to a single lookup key, yet the main record's existence does not
depend on the lookup value. Indeed, we often want to use the same lookup
set for a number of different user-sets.

So, on the application side, the usefulness of a FK relationship, where a
lookup key in the main table refers to a single lookup value in the
referenced table, is its ability not just to provide discrete valid values
but to reject invalid ones.

This doesn't exclude the possibility of implementing the same *database*
relationship as master-detail in another context. For example, somewhere
else, you might want to use the "lookup" table as the master and the "main"
table as the detail. To use your animals/locations example, the Location
table could be the master, used to select (as details) all of the Animal
records having the matching location. So there, you have two distinctly
different application implementations that are able to make use of a single
database constraint.