PROBLEM SOLVED.
But ...I still need a bit of
other help.
Firstly
Thanks a million to Steve Van Els for his un-ending patience and interest in this thread - and behind the scenes.
[b}The problem[/b]
To have a Master/Detail relationship between 4 tables called
PARENT (where P_NO is the father number.)
CHILD (where P_NO is the father number and C_NO is the child number.)
GCHILD (where P_NO is " " C_NO is " " and GC_NO is the grand child number.)
GGCHILD (where P_NO is " " C_NO is " " GC_NO is "" and GGC_NO is the grt gchld number.)
When pointing to (say) father 2:
We do NOT want
1.
To see ALL the children in the database when pointing at (say) father 2 in PARENT.
2.
Child 1 of (say) Father 2 to be Child 3 - because Father 1 has 2 children - which WILL
happen if one indexes on C_NO, GC_NO and GGC_NO. (Thus making them unique. So that any
attempt at entering "1" for Child 1 of Father 2 will result in a key-violation.)
We DO want:
To see the all the Children (GChildren, GGChildren) of that father ONLY.
Child 1 for EACH father - MUST obviousely be "Child 1" in each case.
The following codes will NOT do the trick.
Select * from CHILD
where P_NO = P_NO
NOR WILL
Select *
from GGCHILD t4, GCHILD t3, CHILD t2, PARENT t1
WHERE (t4.GGCHILDGC_NO = t3.GCHILDGC_NO) AND (t3.GCHILDC_NO = t2.CHILDC_NO) AND (t2.CHILDP_NO = t1.P_NO)
...as these merely result in ALL the Children in CHILD who have a father in PARENT becoming available in the first instance - as will any GGCHILD who has relatives in
the other tables in the second instance.
I have solved the Master/Detail problem as follows:
A.
Table CHILD has a field CAKV. (For Child anti-key-violation if you like.
) So that
we DO have a unique field in the table. Whilst GCHILD has GAKV and GGCHILD has
GGAKV for the same reason.
B.
I have another table (called TRNSFR) in which I have four fields
being P_NO, C_NO,G_NO and GG_NO - to correspond with similar fields in tables
PARENT (where P-NO is the number of each father.)
CHILD (where C_NO is the child number of each father.)
GCHILD (where GC-NO is the grand-child number for each child.)
GGCHILD (where GGC-NO is the grand-child number for each child.)
This table must NEVER have more than ONE record in it - to obviate the problem
described above.
C.
In the event-handler of AfterScroll property in IBDataSet (which I am using) of PARENT I post the value of P_NO into TRNSFRP_NO. In the same manner I post
the values of C_NO, GC_NO into TRNSFRC_NO and TRNSFRGC_NO.
The following code then produces a perfect Master/Detail result for the Father to GreatGrandChild relationhsip.
Select *
from GGCHILD t1, TRNSFR t2
WHERE (t1.GGCHILDGC_NO = t2.TRNSFRGC_NO) AND (t1.GCHILDC_NO = t2.TRNSFRC_NO)
AND (t1.CHILDP_NO = t2.TRNSFRP_NO)
BUT ...
AA
I need to cause CAKV, GAKV and GGCHILD to AUTO-INCREMENT by 1 upon each new record being created. Something I still don't how to get to work.
(In the model I did this with manual entries.)
BB
I need the code required at AfterScroll to post the Value of P_NO (etc.) into TRNSFR - bearing in mind that it is a table in an Interbase DATABASE called PCHild.gdb.
Whilst I am familiar the code required to post to a field in a table (using Paradox Tables) I don't know the code to use to do it when using an Interbase DATABASE with a table in it.
Anyone?
