>>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
> (PROV, CVEART).
>

The INVE01 table is actually a Products table and not an inventory table.
The products table is in no way a detail of suppliers.

The way I look at it is
Master: PROV01 Suppliers
Detail: PROVART products related to the suppliers.
Lookup: INVE01 Products.

The Key of PROVART includes the Product key (CVEART) primarily to avoid
having the same product registered more than once for the same supplier.

> 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.
>

The database I am using is from an existing database where I need to make an
new application to add functionality to the an already existing old
application (there is no source code to that application). Therefore, I
cannot modify the existing database schema to ensure I don't affect the
existing program. As you can see, it is an old database in Dialect 1. It is
not my choice, it is an imposed necesity.

The actual validation of the rules will be programed in the application and
I really don't see how this should affect the lookups.

> 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
> "Productos".
>
> 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.

A "product category" is a common use of a lookup mentioned in this mailing
list. This kind of master-detail relationship is like saying that "Products"
is a detail of "product category" which really is technically true. However,
it is generally accepted that the "Product Category" is a lookup of
"products". In the same way, for my purpose, "Products" (INVE01) is a lookup
of PROVART.

>
> 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.
>

For testing purposes, I added a new field SEC (sequence) to the PROVART
table, filled in some values, and changed the primary to (PROV,SEC) instead
of (PROV, CVEART) to eliminate CVEART from the primary key and make CVEART
independently replaceable.

IT STILL DOES NOT WORK.

> 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.
>
> Helen
>

I think this subject has completely changed into database design discussion
(and I am not going to challenge your expertise on the subject) which has
nothing to do with the original subject of how to get a simple lookup combo
component to work correctly.

With all other Lookup combo components the functionality is basically
DATA.LOOKUPKEY --> Seek into LOOKUPTABLE.PRIMARYKEY ---> and return
LOOKUPTABLE.DESCRIPTION

The only real condition is that LOOKUPTABLE.PRIMARYKEY does not have
duplicate values.

The components may have diferent ways to specify this condition, but
basically it should be that simple. There should be no restriction as to how
the database is designed. I don't expect a component to figure out all the
details of the design of any specific database.

I would be very pleased to see an actual real sample (like the one I
mentioned before) that demonstrates the correct usage of the lookup combo
component. From the mailing list, it seems to be quite clear that I am not
the only one having problems setting up this component. It would be wise if
a CLEAR EXAMPLE was available which would also save a lot of your time (an
Jason's) by avoiding a lot of questions from ignorants like myself. I am
sure that in the time you have already dedicated to me with this problem,
you probably could have made the sample that would explain it better. This
of course is assuming that the component actually does work, because as far
as I am concerned, there seems to be a serious flaw with it.

Regards
Jay