--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> At 11:11 PM 29/03/2003 +0000, you wrote:
> >--- In IBObjects@yahoogroups.com, "Marco Menardi" <mmenaz@l...> wrote:
> >I've two tables, each that has records that must be distinguished by
> >fiscal_year, and that are cloned every fiscal year, so the unique ID
> >is in the form:
> >FISCAL_YEAR + CODE
> >i.e.
> >2002 ABC
> >2002 ADS
> >2003 ABC
> >2003 ADS
> >
> >well, in table A I have a field that has a lookup in table B, the
> >lookup query.
> >I've set a master-detail relation on the fiscal year, so in the B
> >(lookup table) I've only the records of the same fiscal year of table
> >A.
> >The problem is that the B unique key is FISCAL_YEAR + CODE, so in the
> >B table (lookup) I've to set KeyLinks in the form of:
> >B.FISCAL_YEAR=A.FISCAL_YEAR
> >B.CODE=A.LKP_CODE
> >it works good but when I cancel a relation pressing CANC in the
> >TIB_LookupCombo, of course A.FISCAL_YEAR and A.LKP_CODE are blanked,
> >but A.FISCAL_YEAR is part of the primary key of A table, so I can't
> >post!
> >so I need:
> >a) specify B.FISCAL_YEAR and B.CODE in keylinks of the lookup table
> >b) don't specify B.CODE=A.LKP_CODE in the lookup table
> >
> >but this keylink:
> >B.FISCAL_YEAR
> >B.CODE=A.LKP_CODE
> >does not work :( (I've a lookup list, but the lookup field remains
> >always empty)
> >
> >how can I do?
> >thanks
> >Marco Menardi
> >--- End forwarded message ---
>
> Marco,
> Your problem stems from the fact that you are trying to implement a 1:1
> master-detail relationship as a keysource-lookup and trying to make

it work

> both ways. K/L is exactly the reverse of M/D so you are looking at a
> circular relationship here.
>
> For K/L you must have:
> -- a non-unique polling key in the parent (keysource) set
> -- a unique responding key (keylinks) in the detail set
> You use the K/L relationship to *write* a value from the lookup

dataset to

> the polling key
>
> For M/D you must have:
> -- a unique primary key in the parent (master) set
> -- a unique or non-unique responding key (masterlinks) in the detail set
> You use the M/D relationship to *select* a value for the foreign key

of the

> detail dataset.
>
> You need to choose which you need (I think it is M/D) and implement it
> accordingly. In short, K/L can't implement a master/detail

relationship.

>
> Helen

I know, but the problem is exactly that I can'n choose, I need both!
My porblem is: is there an easy way (that I can't see) to get it, or
should Keylinks property be extended to have both?
I can't belive I'm the only one programming a sort of accounting
program that needs to have this kind of situation.
If there were just a fiscal year, "CODE" will provide the right K/L
relation, but having to "duplicate" relations and have them divided
for fiscal year, I go into this "double" necessity:
a) a compound primary key
b) only part of that primary key is necessary for K/L
I can break the M/D relation and provide the fiscal year as a
parameter, but would be much more secure having it taken from the A table.
But would be nice that:
B.FISCAL_YEAR
B.CODE=A.LKP_CODE
will use B.FISCAL_YEAR and B.CODE for Keylink, and B.CODE=A.LKP_CODE
for Lookup data link.
Never happened to you to have similar needs?
many thanks
Marco Menardi