Subject Re: [IBO] Lookup Doesn't work properly
Author Helen Borrie
Hello Jay,
At 02:24 PM 15/07/2005 -0500, you wrote:

>In all applications (mine or others) the original value in a lookup control
>or any other control is kept an it may or may not be changed. The normal
>behaviuor of all other lookup combos I have use is that the original value
>is kept ant when the lookup box is opened, you are positioned in the list
>exactly on the original value and from there you may navigate the list to
>make the change.
>The behaviour I am getting is as if there is no value and you have to start
>from scratch, not very user friendly at all.

OK, I'll try to explain to you why *this* is not a lookup relationship.

The main problem you have in trying to implement this interface is the
dependencies that you have in these three sets: lets call them "Parent",
"Intersection" and "Detail".

The way you have structured the sets, you have:

Parent: is derived from the table PROV01 (seems to be a Supplier
table). Its primary key is CPROV.
Detail: is derived from the table INVE01 (seems to be an Inventory
table). Its primary key is CLV_ART.
Intersection: is derived from the table PROVART, an intersection of the
Supplier table and the Inventory table. Its primary key is a compound of

There should be two foreign keys in PROVART: PROV as a FK to CPROV in
PROV01; and CVEART as a FK to CLV_ART in INVE01. In the sample DB you
provided, you have not declared these constraints. There are also no
triggers to enforce the rule that a CLV_ART must belong to a specific
supplier. Your intersection table, PROVART, is not prevented from
accepting anything except non-uniqueness in the primary key.

Nevertheless, the Intersection set is restricted in your application by a
master-detail relationship. The active records in the intersection set -
derived from the table PROVART - are thereby restricted to *only" those
PROVART records where PROVART.PROV matches CPROV in the Parent set, which
is derived from the table PROV01. The Intersection set would contain
records where the first field of the KeyLinks would be the same as the
current CPROV in the parent, while the second field of that KeyLinks would
be a unique value of CVEART for each record that you are displaying as

Now, if the Detail set - derived from table INVE01 - were also set up in a
Master-Detail relationship, linking the CVEART key of each PROVART record
with the (only) INVE01 record that applies to that PROVART record (through
MasterLinks), then the Detail set (the one derived from INVE01) would
contain just that single record, thus correctly representing the
relationships between the Supplier and Inventory tables and the
intersecting PROVART table.

However, that is not what you did here. You have tried to create a
*lookup* relationship between the intermediate "master" set (the
intersection of Suppliers and Products). The purpose of a lookup set is to
provide a collection of unique, interchangeable options, from which one key
value in the polling set (the set pointed to by the KeySource datasource)
can be replaced by another without destroying the existing dependencies.

Clearly, by implementing this detail-to-master relationship as a lookup
relationship, you potentially violate the existing dependency that is
implemented in the primary key of the polling set.

From the logical point of view of the lookup set, it can't satisfy the
rule that it supply an independently replaceable key to the polling
dataset. The reason why the lookup control displays the behaviour that you
don't like is that the lookup key is being asked to supply a key that is
NOT independently replaceable. Changing the polling key will change the
primary key, i.e. KeyLinks from the IBO point of view, of the underlying
intersection record.

I'm not telling you that you MUST implement this relationship cleanly. If
you are satisfied with it, who am I to tell you it's wrong? But, at the
same time, you do need to understand that using a mechanism that was
designed to implement one kind of relationship, to implement a conflicting
relationship, is likely to produce the kind of anomalies of behaviour you
are experiencing.