At 07:18 PM 23-04-01 +0000, you wrote:

>Hi,
> is it possible to restrict the records displayed in a
>TIB_LookupCombo based on a relationship defined in a third table?
>
>My 3 tables are:
>
>Table: ACCNTS_USED (for a job, KeySource table)
>PK
>JOBNO
>ACCNTHLDR
>ACCNT_ID
>~
>
>Table: ACCOUNTS (All existing accounts, Lookup table)
>ACCNT_ID (PK)
>ACCOUNT
>SPEEDTYPE
>CLASS
>
>Table: ACCNTHLDRS_ACCNTS (Link table)
>PK
>ACCNTHLDR
>ACCNT_ID
>
>I decided I needed the link table because each user can use several
>accounts, and each account can be used by several users.

>My lookupcombos are displayed in a grid (because costs for a job can
>be shared amongst several accounts).
>In operation, the user first selects an ACCNTHLDR from the first
>TIB_LookupCombo, and I then want to display only the accounts linked
>to that ACCNTHLDR in the next TIB_LookupCombo in the row.

Let me guess at these statements...

Keysource dataset:
SELECT
PK,
JOBNO,
ACCNTHLDR,
ACCNT_ID,
OTHERSTUFF
from ACCTS_USED
where JOBNO = :JOBNO

Accounts lookupTable:
Select ACCNT_ID,
ACCOUNT,
SPEEDTYPE,
"CLASS"
from ACCOUNTS

>KeyLinks -
>ACCOUNTS.ACCNT_ID=ACCNTS_USED.ACCNT_ID

You wrote:

>So far, so good! This displays all accounts details in the lookup-
>combo.
>If I try to restrict the returned rows with
>[CODE SNIPPET]
>void __fastcall TJobsForm::AccntLUQryPrepareSQL(TIB_Statement *Sender)
>{
> AccntLUQry->SQLWhereItems->Add
>("ACCNTSUSED.ACCNTHLDR=ACCNTHLDRS_ACCNTS.ACCNTHLDR");
>}
>
>I get an error of "Column unknown ACCNTSUSED.ACCNTHLDR".

Is there a typo somewhere here? a missing underscore in ACCTS_USED? Not that it matters, as a reference to another table is not valid as a WhereItem for this query....

When you use SQLWhereItems, you must remember that the entries must be able to be combined with the "stem" of the statement as a valid WHERE clause. With your example, you would need something along the lines of:
AccntLUQry->SQLWhereItems->Add ('ACCNTHLDR=' + QuotedStr(qryAccntsUsed.FieldByName('ACCNTHLDR').Value));
(or whatever you use in BCCB to arrive at a quoted string...)

Don't try to use SQLWhereItems to limit the dataset, since it would only be applied when the statement is prepared. You would have to unprepare and reprepare the dataset explicitly on every scroll in order to fire OnPrepareSQL, which isn't what you want. Use a parameter instead.

>How do I let IBO know about this other column of my Keysource table?
>I can't seem to find the right SQL statement that doesn't give
>me "Multiple rows for singleton select" type errors.

If the statement is capable of returning multiple candidates for the lookup reference formed by the Keylinks, then it's either not eligible to be a lookup dataset or the Keylinks are incomplete. Only one lookup link can be current, so here you need both ACCNT_ID and ACCNTHLDR to get the singleton candidates. ACCNT_ID on its own can't do this. Remember, you are hooking two lookups to the same keysource so you need THAT relationship to acquire the candidates.

Assumed statement for AccntLUQry:
Select PK,
ACCNTHLDR, ACCNT_ID
from ACCNTHLDRS_ACCNTS
where ACCNT_ID = :ACCNT_ID

Add these KeyLinks:
ACCNT_ID=ACCNTS_USED.ACCNT_ID
ACCNTHLDR=ACCNTS_USED.ACCNTHLDR

Limiting the dataset to only those link rows that apply to the ACCNT_ID selected by the first lookup is a separate issue: Write a handler on the dataset for the first lookup so that the ACCNT_ID selected there is applied as the parameter for the second lookup. (As far as the KeySource dataset is concerned it needs the ACCNT_ID to be in the second lookup dataset, for the link, even though all of the rows will contain the same ACCNT_ID.)

This "workflow" doesn't seem to represent the full range of your many:many relationship. There is potential here to conditionally alternate the parameters so that you could select ACCOUNTS by ACCNTHLDR also. If this were required, I would want to merge the two lookups into one with a join and parameterise both ACCNT_ID and ACCNTHLDR, killing approximately three birds with one stone.

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________