Master / Detail auto filling of Foreign Keys not working.

sLesae · Post by **sLesae** » Wed 07 Mar 2012 13:33

Hi,

According to the Help files, the SDAC components should automatically fill in the Foreign Key fields in your Detail DataSet with the Primary Key Field value from the Master Dataset, even if you use a parameter in your Detail Query. Sadly ... it doesn't seem to work in my case (SDAC 6.1.5 in RAD Studio XE2 with Update 4).

I have 2 TMSQuery components on my Form and one DataSource. The DataSource points to the Master Query and the master query has a 'SELECT * FROM T_WAREHOUSE' SQL Statement. The Detail DataSet has a 'SELECT * FROM T_ZONE WHERE WarehouseId = :WarehouseId' SQL Statement and it's Mastersource points to the correct TDatasource.

When running my application the Master / Detail relation is working perfectly. But when I insert an new record into the Detail dataset the WarehouseId field doesn't get filled with the value from the Master DataSet automatically.

It works fine when I use the MasterFields / DetailFields combo ... but in that case it would add and additional ' AND WarehouseId = :WarehouseId' to the Detail Query's SQL Statement.

According to the 'Working with Master / Detail Relationships' topic in the help files, it should also work in my default setup, so I guess this is some form of a bug ?

Regards,

Stefaan

AndreyZ · Post by **AndreyZ** » Wed 07 Mar 2012 16:39

For the time being field values of the detailed dataset are automatically filled with field values of the master dataset only if master/detail relationship is established using the MasterFields and DetailFields properties. We will investigate this question.

sLesae · Post by **sLesae** » Wed 14 Mar 2012 09:09

Hi,

This is now actually causing a problem with a query containing the following SQL Statement :

SELECT [LocatieId]
,[T_LOCATIE].[ZoneId]
,[LocatieTypeId]
,[Straat]
,[Vak]
,[Niveau]
,[Subvak]
,[VastArtikelId]
,[T_MAGAZIJN].

Code: Select all

 AS MagazijnCode
      ,[T_ZONE].[Code] AS ZoneCode
  FROM [T_LOCATIE]
  INNER JOIN [T_ZONE] ON [T_LOCATIE].[ZoneId] = [T_ZONE].[ZoneId] 
  INNER JOIN [T_MAGAZIJN] ON [T_ZONE].[MagazijnId] = [T_MAGAZIJN].[MagazijnId]
ORDER BY [Straat]
        ,[Vak]
        ,[Niveau]
        ,[Subvak]


MasterField and DetailField are set to ZoneID so if the zone record changes, the corresponding Location records will get shown. Sadly ... ZoneID is ambiguous since it can be found in the Zone table and in the Location Table, so when opening the dataset, the system gives an Ambiguous Column error.

I seem to be able to fix my problem by seting DetailFields to T_LOCATIE.ZoneId ... is this the way I am supposed to do this ?

AndreyZ · Post by **AndreyZ** » Wed 14 Mar 2012 10:28

You found the correct solution. We know about this problem, we will fix it in one of the next SDAC builds.