Devart Forums

Hi,

I guess I found another bug with the Master / Detail function :

Create 3 Tables

MasterTable :

MasterId : Integer (autoinc)
Caption : Varchar 10

DetailTable:

DetailId : integer (autoinc)
MasterId : integer
Caption : varchar 10

JoinTable:

JoinTableId : integer (autoinc)
DetailId : integer
Prize : double (15,2)
MasterId : Integer (autoinc)
Caption : varchar 10

On a datamodule add 2 queries : one named QrMaster, the other one named QrDetails.

On the QrMaster SQL :

Select * From MasterTable

On the QrDetail SQL :

Select DetailTable.*, JoinTable.* From DetailTable Left OuterJoin JoinTable on (DetailTable.DetailId = JoinTable.DetailId ) where DetailTable.prize 0 Order by DetailTable.Caption

Set the mastersource property + details / Master fields (in this case to MasterId).

Now try to open the Query : you will get an Error #23000 Column 'MasterId' in where clause is ambigious...

If I look with the DBMonitor , In can see that :

Select DetailTable.*, JoinTable.* From DetailTable Left OuterJoin JoinTable on (DetailTable.DetailId = JoinTable.DetailId ) where ( DetailTable.prize 0 ) And MasterId = : MasterID Order by DetailTable.Caption

This should be "DetailTable.MasterId = :MasterId"...
I've also tried to modify my where clause (by adding the "DetailTable.MasterId = :MasterId". This didn't worked since the component already added a new clause : "And MasterId = :MasterId"

When joinning the key, isn't it possible to select from witch table the Detail key will be taken ? ( having a drop down list from the selected table like you did in the "Generate insert/Delete/update/refrehs SQL Statement wizard" ) or even use the "UpdatingTable" property ?

I've found a workarround (but this is not valuable) :
I've removed the DetailKey property. So, only the MasterSource and MasterKey are filled.
I've edited my Detail Query and added this to my Where Clause :

AND DetailTable.MasterID = :MasterID

Now I can open / close my query without any issues... But as said this is a temporary solution as it is not really clean.

Our SQL generator can't work with such complicated queries. To achive correct Master/Detail relationship in this situation you should manually set correct query statement. The solution you have found is correct.

Hi Antaeus,

It will be nice to add further enhancements to the master/Detail property, what about this one :

When joinning the keys, isn't it possible to select from witch table the Detail key will be taken ? ( having a drop down list from the selected table like you did in the "Generate insert/Delete/update/refrehs SQL Statement wizard" ) ?

We don't think the enhancements you offer will be very useful.
However, we have added this feature to users wish-list, so it may be implemented in future versions if there will be more demand for the feature.

Thank you antaeus