Henny,
I believe I can explain the behavior you're experiencing.
I created a query qryCalls (datasource Calls, under it datasource CallCategories (jointype innerjoin)).
When I run the query from a job I get a Cartesian product (all calls will be related to all callcategories).
Only when I define in the query the relation between calls and callcategories the output of the query is OK.
The solution to this issue is to on the DataSource object within your query, set the property 'Relations' to YES. After you save, inspect the 'Relations' node underneath the DataSource, and you will notice the relations have been copied into the 'Relations' node based on your table relations. I believe this will dynamically update based on your table relations.
Next I created a report Calls. Datasource qryCalls. AutoDesign. I clicked GenerateDesign and two Body were created (one for Calls, one for Categories)
I want to have all Calls fields and the CallCatDesc on one line, so I moved all fields to the Calls Body.
When I ran the report the wrong CallCatDesc was shown (or no CallCatDesc at all).
Only when I move the CallCatDesc to the Body for Categories the report will shown the correct output.
So what should I do to get the report I want (all fields on one line)?
The cause of your issue is that at the time the report prints the section, the tableBuffer for CallCatDesc is stale. On the Body report section, you may notice the 'Table' property. The 'Table' property specifies which tableBuffer, when sent (send() was called) will trigger the respective section. The reason why all your fields are correct when you move the report controls to a different section is because the 'Table' property on that section is set to a tableBuffer which is sent last, so every other tableBuffer has been updated to the latest record. Reference the fetch() report method on axaptapedia.com or another resource for more information on what I'm referring to here.
Moving the CallCatDesc to the Overview only works OK if I set the jointype to InnerJoin. So why was it set at delayed in the first place?
The reason why your fields work correctly when the 'JoinMode' property is set to 'Inner Join' is because 'Inner Join' will immediately fetch the joined records for every record in the parent table. 'Delayed' JoinMode will only fetch records which are related to the currently active parent table record ONLY. The reason why 'Delayed' is a default is because it performs better for most standard AX-style forms (with a header / line style). Basic idea here is to use 'Inner Join' for when you want to have fields from several tables on the same grid. Use 'Delayed' when you have two related grids.
I DID set relations on my Extended Data Types! According to the documentation -as I understand it- the relations set at the EDT's wille be valid as long as they are not overruled by relations defined at the table.
If I define relations at the table what's the use of relations defined at the EDT???
Your understanding is correct. If you specify the relations on the Extended Data Types there is no reason to add the same exact relations onto the Table. From my experience, if a relation to the same related table is on an Extended Data Type and a Table, AX will honor one, but not the other. I don't think it consistently chooses the Table relation (although I would agree that would make sense).
Hope this helps,
