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
Problem with Multiple Foreign Key
Moderators: gto, cipto_kh, EgonHugeist
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
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
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
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
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:
And the SQL of QQualityGrain looks like that:
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
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;
Code: Select all
select * from qualitygrain where grainid = :graininfid and farmid = :farmidI hope that is what you looked for.
Regards,
Rüdiger
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:
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
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
| | ...By the way, be welcome Olga! There are almost no Women here, or at least, they don't post heheh
