Page 1 of 1
Problem with Multiple Foreign Key
Posted: 27.07.2007, 03:36
by Olga
Hi, friends.
I have a little problem.
Please, tell me "how can I create master-detail relation beetwen 2 tables with multiple foreign key".
______________________________
|table Farm:
|idFarm
|...
|______________________________
|table GrainInf:
|idGrainInf (PK)
|farm_idFarm (FK)
|...
|______________________________
|table QualityGrain
|GrainInf_idGrainInf (FK)
|GrainInf_farm_idFarm (FK)
|...
|______________________________
Properties MasterFieldS and linkedFieldS have only One Value
What can i do?
Best regards, Olya
Posted: 27.07.2007, 09:47
by pol
Hi Olga,
Why do you need the GrainInf_farm_idFarm in QualityGrain? GrainInf is already linked to Farm, so when QualityGrain is linked to GrainInf (by GrainInf_idGrainInf) it is automatically linked to Farm.
It would be another thing if GrainInf would be "Farm-independent" (which in my opinion would make sense). Then QualityGrain would in fact have to be linked to two different tables. Interesting thing. I have no idea how to do that with Zeos, but I will give it a try.
Regards,
Rüdiger
Posted: 27.07.2007, 10:17
by Olga
Hi, Rüdiger.
Relations in this database are:
table Farm -> one to many table GrainInf
table GrainInf -> one to one table QualityGrain.
all keys from GrainInf pass to table QualityGrain automatically.
And I need create relation master-detail between:
idGrainInf
farm_idFarm
and
GrainInf_idGrainInf
GrainInf_farm_idFarm.
Regards,
Olga
Posted: 27.07.2007, 10:36
by pol
Hi Olga,
Now I tried it. It can't be done automatically, because the "MasterSource" property of a TZQuery only accepts one. But it is not so bad. In the "OnChange" method of both components where you display the Farms and Grains I did that:
Code: Select all
QQualityGrain.Close;
QQualityGrain.ParamByName('FARMID').AsInteger:=TFarmFARMID.AsInteger;
QQualityGrain.ParamByName('GRAININFID').AsInteger:=TGrainInfID.AsInteger;
QQualityGrain.Open;
And the SQL of QQualityGrain looks like that:
Code: Select all
select * from qualitygrain where grainid = :graininfid and farmid = :farmid
and the DBText linked to QualityGrain.Quality shows the right value for the choosen combination of Farm/Grain (or nothing if there is no such combination).
I hope that is what you looked for.
Regards,
Rüdiger
Posted: 27.07.2007, 12:11
by gto
There's no need to use OnChange. You can use the database property, like this:
http://zeos.firmos.at/viewtopic.php?p=2199#2199
If the relation between Grain and Quality is dependant from the farm then, to make this type of relationship better, even for selecting data in future reports, try to do this:
Code: Select all
Farm | Grain | Quality
----------------------------------------
FarmCode | FarmCode | FarmCode
... | GrainCode | GrainCode
| ... | QualityCode
| | ...
If the relation between Grain and Quality is
not dependant from the farm, then there's no problem at all, as they'll be all single relations.
By the way, be welcome
Olga! There are almost no Women here, or at least, they don't post heheh
Posted: 30.07.2007, 04:05
by Olga
Thanks, friends. I'll try to use yours advice.
I'm pleased to be the first woman in this forum
Hi-Hi-Hi
