I’m trying to setup a master/detail relationship using an SQL join query as the detail datatable. The master table is a standard “stAutoSQL” DataTable. It has an ID field that needs linking to the Parent_ID field of the detail table.
However, the detail datatable is defined using this type of SQL:
select Details.*, DetailXRef.Parent_ID
from Details join DetailXRef on (Detail.ID = DetailXRef.ID)
where {WHERE};
As you can see the detail field (parent_ID) is coming from the DetailXRef table (which is a simple cross-reference table to implement a many-to-many relationship).
I run the master/detail wizard and open the master table in design time, link using ID = Parent_ID and all is well.
I hit problems thought when I specify Target Table = Details for the detail query in the Schema Modeller, (so that I can update this table).
When I open the master table on the client, I get “Invalid column name Parent_ID” (from SQL Server). When I remove the Target Table property in the schema on the server, the master/detail relationship works again.
How do I make the Query updatable and part of a master/detail relationship?
can you attach your schema and project, pls?
usually such fields “DetailXRef.Parent_ID” should cause problems at updating tables but not at opening,
for removing field from update, setting LogChanges to False on field is enough.
I’ve attached a sample project, including the SQL.
In the Schema I have a QUERY_ datatable which has the Target Table set so that it can be updateable.
Se the tbl_Master.Active = true in design time and you will see the Invalid Column error appear.
Remove the Target Table property from the schema datatable, recompile the server and try setting tbl_Master.Active = True again, and it works. But then try and edit the detail table - it complains about missing Target Table setting.
thx for tectase. I reproduced this problem. the problem is - dynamic where logic uses TargetTable for generating where clause.
I can suggest to you next workarounds:
specify static parameter like:
select Detail.ID, Detail.PageName, Detail.PageText, DetailXRef.Master_ID
from Detail join DetailXRef on (Detail.id = DetailXRef.detail_id)
where (DetailXRef.Master_ID = :Master_id) and {WHERE}
