Query data source properties "JoinMode" and "Relations"

Hi There

When i edit the query for a report i found that there are two setting for a specific data source. they are “JoinMode” and “Relations”.

For “JoinMode”, i’m pretty sure that i can understand. but for the “Relations”, i’m a little bit confused.

I looked into some of the system queries, for some of the data source, if the setting for “JoinMode” is configured as “OutJoin”, normally i found that the “Relations” is configured as “No”.

Since the “OutJoin” is also one kind of relation, Why we have to configure the “Relations” as “No”. does it has special meaning ?

The Microsoft Official Specification for “Relations” as below:

Determines if the query system should use the relations that are defined for tables and extended data types.

If set to Yes, the query is automatically updated if a relation is changed.

If we configure the “Relations” as “No”, and the “JoinMode” as “OutJoin”, i think the system also use the relations that are defined for tables and extended data types, right ?

Please correct me.

many thanks.

The Point is Forms also have data source properties, but we only have to configure the property “LinkType”, i think it have the same functionality as the property “JoinMode” of query, but why we don’t have to configure the relations for the Form data source ?

The type of join defines how tables are joined, i.e. outer join means that records from the parent table are returned even if there are no corresponding records in the child table.

Relations define which fields (and possibly filters) are used when joining tables together.

You typically need a relation when using a join, but you can’t say that outer join is a relation. By the way, relations are used for many other things than just joins.

Relations property says whether a relation should be taken automatically from a table or EDT. If it’s set to No, you still can define relations directly in the query.

Hi Martin, thanks a lot for you reply, i met i tricky problem like below:

i have two tables “GeneralJournalEntry” and “LedgerEntryJournal”. i have to create the relation between these two tables. the join mode is “OutJoin”.

Senario 1:

if i set the Join mode as “OutJoin” and set the “relations” as “Yes”. there is a join create automatically under “relations” which is called “LedgerEntryJournal”, i have no idea where the system defines this one.

for this kind of config, the SSRS project in the VS can use this query and get all the fields.

Senario 2:

i set the relations as “No” and define the relations by my self (GeneraJournalEntry.LedgerEntryJournal == LedgerEntryJournal.RecId). the query can be compiled, but when i tried to use this query in the SSRS project. i got the error information as below:

0511.3.png

I met this kind of problem in the Dynamics AX 2012 R2 system, The tricky is this can work in the Dynamics AX 2012 System. The VS can invoke the query without any problem.

If you guys who also has the 2012 R2 system, can you please help me to verify this , many thanks.

You should have described the problem on the beginning; we wouldn’t have to waste time discussing unrelated things.

I agree that there is a problem - I managed to reproduce it in AX 2012 R3. But I don’t agree that it’s related to the type of join or the Relations property. The same happens if I use inner join. And it works if I keep Relations to No and create the right relation.

The difference is whether the relation is defined by or JoinRelation = LedgerEntryJournal (which works) or by Field = LedgerEntryJournal and RelatedField = RecId (which leads to Object reference not set to an instance of an object).

Because I don’t see anything wrong with the relation, I suggest sending a support request to Microsoft.

By the way, LedgerEntryJournal relation is defined on GeneralJournalEntry table.