Subject Re: [IBO] IB_LookupCombo
Author Helen Borrie
At 10:58 PM 4/02/2003 +0000, you wrote:
>Looking for Ideas and a solution.
>I have a table with four columns. Lets call them ColA, ColB, ColC and
>ColR.
>
>(The table represents finding a tyre code from a manufacturer).
>Suppose A has three different values in it 13, 15, and 17.
>For each of these values B has several values such as 145, 155, 165
>etc perhaps up to ten different values for each value in col A.
>Col C has several values for each value of B. Col R is the 'result'
>that we are trying to find.
>So a few records from the table could look like:
>
>13,145,45,P
>13,145,50,Q
>13,145,65,R
>15,155,50,Q
>15,155,60,R
>15,155,75,T etc
>
>The user needs to enter a lgitimate value for A then one for B then
>one for C and hence get the result from R.
>
>So I have the table called 'tyres' and i put a query on col A
> select distinct A from tyres,
>
>Then another query on col B
> select distinct B from tyres where A = :A
>
>Then another query on col C
> select C,R from tyres where A = :A and B=:B
>
>Passing the values from one query to the next. Fine it works.
>But why not Master-Detail. OK I set that up, that works nicely.
>
>But these are actually lookup combos and I want to link them into a
>table called Cars.
>
>As soon as I try to make a query operate in Master-detail and link
>its keylink to the Cars key it all siezes up solid.
>
>For example query B says 'select distinct colB from tyres'
> master source is a source from queryA
> master links say colA=tyres.colA
> keysource is source from cars query
> keylinks say colB (of course cars has a colB).
>
>I have struggled for two days. But I get a message on the lines
>of 'multiple lines from a singleton query'. As well as assorted key
>violations.
>
>Perhaps I am approaching the problem from the wrong end.

David,

'Multiple lines from a singleton query' always indicates some form of
ambiguity that the logic of your application can't resolve. There
are...errmm....several ambiguities in this setup.

A master-detail needs a set of unique master rows, each of which can
connect to zero or more detail rows. The detail set's masterlinking needs
to point back to one and only one master at each respective level. But,
by using SELECT DISTINCT, you have set up ambiguous masterlinking at both
levels of the drill-down structure.

Now, going to the other end of your model, what you are actually looking
for is a single keylinks value of colR to hook up with the polling column
in Cars, right? Now, ColR is not a unique pointer to any of the other
columns in Tyres. For example, Colr='Q' can apply (theoretically) to any
combination of ColA + ColB + ColC. Here you have another ambiguity. The
polling code in a keylinked relationship (the pointing key in the
Keysource) must point to one and only one row in the lookup table. Your
structure doesn't permit this.

To make this work, back in DDL-land, you need to alter the Tyres table to
make a surrogate key (call it ColPK) for the complete combination of all of
the other columns that form the unique group - in this case, presumably
ColA, ColB and ColC. Place a unique constraint across these three
columns. If you must implement keysource linking between the two
structures, then the unique key of the Tyres table (PointToColPK) is what
you must store in Cars, not a pointer to ColR (PointToColR).

Now, let's say you DO want this live-linking, rather than just to ensure
that a valid ColR code gets stored there.

In this case, the Keysource set needs to include both the PointToColPK
column (with Visible=False in your output) plus a subquery on the Tyres
table to deliver the value of ColR as a read-only value in the output set.

Keysource table:

SELECT Cars.P, Cars.Q, Cars.R,
Cars....,
Cars.PointToColPK,
(SELECT Tyres.ColR from Tyres
where Tyres.ColPK=Cars.PointToColPK) as TyreCode
from Cars

Remember to set the Computed attribute of TyreCode to True.

In the Lookup table, you need both the primary key and ColR. You would set
KeySource (obviously) plus KeyLinks (Tyres.ColPK=Cars.PointToColPK) and
KeyDescLinks (Tyres.ColR=TyreCode).

You still have to resolve the ambiguities in your drill-down, which has to
arrive at a single Tyres record (ColPK) that is a unique combination of
ColA, ColB and ColC. If you don't constrain this group for uniqueness,
then you still have ambiguity, since it will be possible to have the same
group pointing to more than one ColR value. If this is a requirement, then
ColR needs to be included in the unique constraint.

Nested master-detail linking looks attractive as a way to achieve the
drill-down but you have bumped into two problems:

1) unless you use DISTINCT, your sets will contain duplicate values.
2) your output sets currently don't contain enough keys to avoid ambiguity.

It will be OK to use DISTINCT on the topmost set (ColA). But you *want*
the duplicate occurrences in the lower sets. You would need to revisit the
structure and the UI. Don't use DISTINCT for these. You would include the
keys so far in the hierarchy but set all of the columns invisible except
the one you wanted to display for selection. The lowest level set (the one
that is hooked by the keysource set) would need all of the columns.

But..but..but...although all of this resolves the ambiguities, all of this
linking is still going to cause synch problems at the times when the
keysource is polling out to the lookup set, i.e. whenever you scroll the
parent set. Your AfterScroll event will need to use some jiggery-pokery
with the keys on the bottom-level dataset to reach out and "fix on" the
right records in the other two sets. Without it, your lockup problem won't
go away.

It could be done; but it might make more sense to decide between
masterlinking the selection structure and keylinking the lookup. You can
retain the keylinking and use a non-linked mechanism to drill down to the
bottom set; or you can decide that you want to stick with the masterlinked
structure for your selection UI and not use keylinking for editing the Cars
set's tyre pointer code...

Or...if the number of rows in Tyres is quite small and not likely to grow
very quickly, you could turn the whole thing upside down, so that your
current bottom level set (after the structure changes, of course) displays
ColA, ColB and ColC for selection as a horizontal set in a
hierarchically-ordered output set. This way, if the embedded lookup is
important, you can do it without complications. The user selects the
combination she wants for the tyre and the Keysource takes the (invisible)
primary key from the selected combination.

Helen