AX / Dynamics 365 For Finance and Operations blog

Working with Dynamics 365 Finance & Operations form queries is usually straightforward—until you need to join multiple data sources using multiple fields coming from different tables.

What is trivial in SQL can quickly become confusing when expressed through QueryBuildDataSource (QBDS), especially in forms that already contain a complex query tree.

This article describes a real-world scenario, why the naive approach does not work in D365FO, and the pattern that ultimately solved the problem in a clean and stable way.

The Problem

The form already contained a complex query with several data sources, including:

• SalesLine
• InventTable (joined to SalesLine via ItemId)
• InventDim
• Multiple WHS-related data sources
• A custom AOT view:
  OTS_WMSAvailabilityFromStockAggrNew

The functional requirement was to:

1. Join SalesLine → OTS_WMSAvailabilityFromStockAggrNew
   using three custom dimensional fields:
   • ots010_length
   • ots010_thickness
   • ots010_width
2. Join SalesLine → InventTable
   using ItemId
3. Add an additional constraint between
   InventTable → OTS_WMSAvailabilityFromStockAggrNew
   using AltItemId
4. Add another constraint between
   InventDim → OTS_WMSAvailabilityFromStockAggrNew
   using ConfigId

From a SQL perspective, this is a single join with multiple predicates across several tables.
In a D365FO form query, however, this cannot be implemented directly.

Why the Straightforward Approach Fails

D365FO form queries are tree-based, not graph-based.

This implies several important rules:

• A data source can have only one parent (JoinSource)
• addLink() creates joins only toward the parent
• A data source cannot be joined to multiple parents
• You cannot dynamically change the parent of a datasource in X++

Because of this, attempting to join the same datasource to SalesLine, InventTable, and InventDim using only addLink() is structurally impossible.

A Common Hidden Issue: AutoQuery

While debugging, I encountered the following error:

System.NullReferenceException: Object reference not set to an instance of an object

The root cause was not code-related at all.

The datasource based on the AOT view had:

AutoQuery = No

When AutoQuery is disabled, the form does not build a QueryBuildDataSource for that datasource.
As a result:

queryBuildDataSource() returns null.
Setting: AutoQuery = Yes

ensures that the datasource participates in the query and makes the QBDS available for manipulation.

The Correct Pattern: One Parent Join + Cross-DataSource Conditions

The working solution combines two complementary mechanisms:

1. addLink()
   Used only for joins toward the datasource’s actual parent (SalesLine)
2. Cross-datasource QueryBuildRange expressions
   Used to add additional constraints involving other datasources (InventTable, InventDim)

This approach respects the query tree structure while still expressing complex multi-field logic.

Final Working X++ Code

/// OTS_WMSAvailabilityFromStockAggrNew
QueryBuildDataSource id1  = InventDim_ds.queryBuildDataSource();
QueryBuildDataSource it1  = InventTable_ds.queryBuildDataSource();
QueryBuildDataSource ots1 = OTS_WMSAvailabilityFromStockAggrNew_ds.queryBuildDataSource();

// Join OTS to SalesLine (parent join)
ots1.addLink(
    fieldNum(SalesLine, ots010_length),
    fieldNum(OTS_WMSAvailabilityFromStockAggrNew, ots010_length)
);
ots1.addLink(
    fieldNum(SalesLine, ots010_thickness),
    fieldNum(OTS_WMSAvailabilityFromStockAggrNew, ots010_thickness)
);
ots1.addLink(
    fieldNum(SalesLine, ots010_width),
    fieldNum(OTS_WMSAvailabilityFromStockAggrNew, ots010_width)
);

// Cross-datasource condition: InventTable.AltItemId = OTS.AltItemIdNew
QueryBuildRange otsQbr1 =
    ots1.addRange(fieldNum(OTS_WMSAvailabilityFromStockAggrNew, AltItemIdNew));

otsQbr1.value(strFmt(
    "((%1.altitemid == %2.altitemidnew))",
    it1.name(),
    ots1.name()
));

// Cross-datasource condition: InventDim.ConfigId = OTS.ConfigId
QueryBuildRange otsQbr2 =
    ots1.addRange(fieldNum(OTS_WMSAvailabilityFromStockAggrNew, ConfigId));

otsQbr2.value(strFmt(
    "((%1.configid == %2.configid))",
    id1.name(),
    ots1.name()
));

What This Looks Like in SQL

Conceptually, the form query generated by the kernel is equivalent to the following SQL logic:

SELECT
    sl.*,
    it.*,
    id.*,
    ots.*
FROM SalesLine sl
INNER JOIN InventTable it
    ON it.ItemId = sl.ItemId
INNER JOIN InventDim id
    ON id.InventDimId = sl.InventDimId
LEFT JOIN OTS_WMSAvailabilityFromStockAggrNew ots
    ON  ots.ots010_length    = sl.ots010_length
    AND ots.ots010_thickness = sl.ots010_thickness
    AND ots.ots010_width     = sl.ots010_width
    AND ots.AltItemIdNew     = it.AltItemId
    AND ots.ConfigId         = id.ConfigId;

Notes:

• The LEFT JOIN reflects JoinMode::OuterJoin used in X++
• The additional constraints implemented via QueryBuildRange appear as extra predicates in the join condition
• Alias names and exact join order may differ in the SQL generated by D365FO, but the logical result is the same

Why This Solution Works

• OTS_WMSAvailabilityFromStockAggrNew has one parent only
• All addLink() calls are valid and deterministic
• Additional constraints do not require extra parents
• No datasource duplication
• The generated SQL is correct and performant

Key Takeaways

• D365FO form queries are tree-based
• addLink() works only toward the parent datasource
• Multi-field, multi-datasource joins require cross-datasource ranges
• Always verify AutoQuery when queryBuildDataSource() returns null
• Think in terms of query structure, not raw SQL