6.2 Views, Queries & Maps

6.2 Views, Queries & Maps

Quick Answer: Queries in Dynamics 365 Finance and Operations are declarative AOT objects that structure complex data retrieval through hierarchical datasources, dynamic ranges, and four fundamental join modes: InnerJoin, OuterJoin, ExistsJoin (which filters parent records without loading child columns), and NotExistsJoin. Views encapsulate queries or physical tables into compiled SQL Server database views. Views support Computed Columns, which are implemented as static server X++ methods returning T-SQL expressions via SysComputedColumn helper methods (such as SysComputedColumn::if, SysComputedColumn::returnField, and SysComputedColumn::add). Computed columns execute entirely within SQL Server, eliminating row-by-row AOS processing. Maps define polymorphic schema abstractions across separate physical tables that share common business patterns (such as SalesLine and PurchLine mapping to SalesPurchLine), enabling developers to write centralized, reusable business logic against a shared interface.

---

1. AOT Query Architecture & Join Modes

AOT Queries provide reusable, object-oriented definitions for retrieving relational data. A query consists of one or more hierarchical Data Sources, relation links, ranges, sorting specifications, and grouping criteria.

Query Hierarchy and FetchMode

When child data sources are attached to a parent data source in an AOT Query, the FetchMode property dictates how the cursor traverses the result set:

• 1:1 (One2One): The parent and child tables are joined into a single unified record buffer cursor. Every row returned contains columns from both parent and child.
• 1:n (One2Many): The cursor returns a separate row for every matching child record, repeating the parent record buffer. This mode is required when iterating over line items associated with a document header.

The Four Core Join Modes

Understanding how join modes alter the SQL execution plan and the resulting cursor dataset is critical for performance and exam success.

Performance Optimization: Why ExistsJoin Matters

In enterprise ERP scenarios, developers frequently need to filter a parent entity based on child criteria without displaying child data. For example, finding all Customers who have placed at least one open sales order:

• If an InnerJoin is used, SQL Server must project and return child records across the TDS network connection to the AOS. If a customer has 10,000 sales orders, the query retrieves 10,000 duplicate customer rows or requires heavy DISTINCT processing.
• If an ExistsJoin is used, the SQL query compiles with a WHERE EXISTS (SELECT 1 FROM SalesTable ...) clause. SQL Server stops scanning child rows as soon as the first match is found (short-circuit evaluation), and zero child columns are transmitted over the network. This drastically reduces network bandwidth, memory consumption, and execution time.

---

2. Dynamic Query Ranges, Expressions & Grouping

While queries can define static ranges in metadata, enterprise X++ code dynamically manipulates queries at runtime using the QueryRun, Query, and QueryBuildDataSource classes.

Dynamic Query Range Construction

Query query = new Query();
QueryBuildDataSource qbdsCust = query.addDataSource(tableNum(CustTable));
QueryBuildDataSource qbdsTrans = qbdsCust.addDataSource(tableNum(CustTrans));

// Configure relation and join mode
qbdsTrans.relations(true);
qbdsTrans.joinMode(JoinMode::ExistsJoin);

// Add range on child data source
QueryBuildRange qbrTrans = SysQuery::findOrCreateRange(qbdsTrans, fieldNum(CustTrans, TransDate));
qbrTrans.value(SysQuery::range(str2Date("01/01/2026", 321), systemDateGet()));

QueryRun queryRun = new QueryRun(query);
while (queryRun.next())
{
    CustTable custTable = queryRun.get(tableNum(CustTable));
    // Processes customers with transactions in 2026
}

Advanced Query Range Expression Syntax

The QueryBuildRange.value() property supports powerful string expressions for pattern matching, logical evaluation, and open-ended ranges:

• Exact Match: SysQuery::value("US-001") generates WHERE AccountNum = 'US-001'.
• Multiple Discrete Values (OR): "US-001, US-003, US-005" generates WHERE AccountNum IN ('US-001', 'US-003', 'US-005').
• Inclusive Range: "1000..2000" generates WHERE Amount BETWEEN 1000 AND 2000.
• Open-Ended Greater-Than: "1000.." generates WHERE Amount >= 1000.
• Open-Ended Less-Than: "..500" generates WHERE Amount <= 500.
• Wildcards: "*Corp" (ends with Corp), "Contoso*" (starts with Contoso), "?001" (single character wildcard).
• Complex Relational Predicates (Extended Query Syntax): When a range must evaluate multiple fields with OR logic across fields on the same table buffer, developers use extended query syntax enclosed in double parentheses:

// Generating: ((Blocked == 1) || (CreditMax == 0))
qbr.value(strFmt('((%1 == %2) || (%3 == %4))',
    fieldStr(CustTable, Blocked),
    enum2int(CustVendorBlocked::All),
    fieldStr(CustTable, CreditMax),
    0));

Aggregations and Grouping

AOT Queries support aggregate operations (sum, count, avg, min, max) combined with addGroupByField() and addHavingFilter(). When aggregate functions are active, only grouped fields and aggregate values are accessible in the record buffer.

---

3. Views Architecture & Extensions

A View in Dynamics 365 Finance and Operations is an AOT metadata definition that the synchronization engine compiles directly into a physical SQL Server database view (CREATE VIEW ViewName AS ...).

View Foundations

A view can be constructed using two methods:

1. AOT Query Datasource: The view references an existing AOT Query. This is the recommended pattern because query metadata (joins, ranges, ordering) is centralized and reusable.
2. Direct Table Datasources: Tables are added directly under the view's Data Sources node, defining joins and field selections within the view itself.

View Extensions

Developers can extend standard views without over-layering:

• Add new fields from existing view data sources.
• Add new child data sources to the view.
• Add new computed columns to the view.
• Modify field groups defined on the view.

---

4. Computed Columns in Views via SysComputedColumn

A common architectural pitfall in F&O development is relying on X++ table display methods on high-volume forms and reports. Display methods execute row-by-row on the Application Object Server, preventing database-level sorting, filtering, and set-based optimization.

The Computed Column Architecture

Computed Columns solve this problem by generating a native T-SQL calculation that compiles directly into the SQL Server view definition. When a user queries, sorts, or filters by a computed column, the operation executes entirely within the database engine using SQL Server indexes and parallel execution plans.

Implementation Rules for Computed Columns

1. Static Server Method: The calculation must be written as a public static server str methodName() method on the View.
2. String Return Type: The method returns an X++ string containing the raw T-SQL expression fragment that SQL Server will execute.
3. SysComputedColumn Class: Developers must never hardcode raw SQL column names or table aliases directly. Instead, they must use the SysComputedColumn framework class to build platform-independent SQL expressions.
4. Binding Property: In the view designer, a new View Field (such as RealViewField or IntViewField) is created, and its ViewMethod property is set to the name of the static method.

Common SysComputedColumn API Methods

• SysComputedColumn::returnField(viewStr(MyView), identifierStr(MyDataSource), fieldStr(MyTable, MyField)): Qualifies a physical column name with the exact table alias generated by SQL Server.
• SysComputedColumn::if(comparisonExpression, trueExpression, falseExpression): Generates a SQL CASE WHEN ... THEN ... ELSE ... END conditional block.
• SysComputedColumn::add(expr1, expr2) / SysComputedColumn::multiply(expr1, expr2): Generates arithmetic operators (+, *).
• SysComputedColumn::equal(expr1, expr2) / SysComputedColumn::notEqual(expr1, expr2): Generates comparison predicates (=, <>).
• SysComputedColumn::cast(expression, targetSqlType): Generates a SQL CAST(expression AS targetType) conversion.
• SysComputedColumn::and2(expr1, expr2) / SysComputedColumn::or2(expr1, expr2): Combines logical predicates.

Code Walkthrough: Implementing a Computed Total Margin Percentage

public class CustInvoiceMarginView extends common
{
    /// <summary>
    /// Computes the net profit margin percentage at the database level:
    /// CASE WHEN Revenue > 0 THEN ((Revenue - Cost) / Revenue) * 100 ELSE 0 END
    /// </summary>
    public static server str computedMarginPercent()
    {
        str revenueField = SysComputedColumn::returnField(
            viewStr(CustInvoiceMarginView),
            identifierStr(CustInvoiceTrans),
            fieldStr(CustInvoiceTrans, LineAmountMST));

        str costField = SysComputedColumn::returnField(
            viewStr(CustInvoiceMarginView),
            identifierStr(CustInvoiceTrans),
            fieldStr(CustInvoiceTrans, CostAmount));

        str profit = SysComputedColumn::subtract(revenueField, costField);
        str marginFraction = SysComputedColumn::divide(profit, revenueField);
        str marginPercentage = SysComputedColumn::multiply(marginFraction, SysComputedColumn::returnLiteral(100));

        // Build conditional CASE statement to guard against division by zero
        str condition = SysComputedColumn::greaterThan(revenueField, SysComputedColumn::returnLiteral(0));

        return SysComputedColumn::if(
            condition,
            marginPercentage,
            SysComputedColumn::returnLiteral(0));
    }
}

When database synchronization executes, F&O generates the following T-SQL inside the database view definition:

CREATE VIEW [dbo].[CUSTINVOICEMARGINVIEW] AS
SELECT
    T1.LINEAMOUNTMST AS LINEAMOUNTMST,
    T1.COSTAMOUNT AS COSTAMOUNT,
    (CASE WHEN (T1.LINEAMOUNTMST > 0)
          THEN (((T1.LINEAMOUNTMST - T1.COSTAMOUNT) / T1.LINEAMOUNTMST) * 100)
          ELSE 0 END) AS MARGINPERCENT
FROM CUSTINVOICETRANS T1

---

5. Maps & Polymorphic Table Manipulation

A Map in Dynamics 365 Finance and Operations is a data dictionary object that defines a logical schema shared by multiple physical tables that exhibit similar business characteristics but reside in distinct normalized structures.

Real-World Architectural Examples

• SalesPurchLine Map: Implemented by both SalesLine (order sales lines) and PurchLine (purchase order lines). Both tables contain item numbers, quantities, prices, delivery dates, and line amounts, but belong to opposite financial modules.
• CustVendTable Map: Implemented by both CustTable (customers) and VendTable (vendors), abstracting party references, payment terms, and bank details.
• AddressMap: Implemented by various entity address tables throughout the supply chain.

How Maps Work: Structure & Polymorphism

1. Map Fields: The Map declares standardized fields (e.g., ItemId, LineQty, Price).
2. Mappings Node: Under the Map's Mappings node, each participating table is declared. For each table, developers map the Map's field names to the corresponding physical table field names.
3. Map Methods: Business logic methods can be written directly on the Map. When a method executes, it operates on whatever concrete table buffer was assigned to the Map.
4. Polymorphic Manipulation: A developer can write generic business algorithms that accept the Map type as a parameter, manipulating either a SalesLine or a PurchLine without writing duplicated code or complex switch statements.

// Generic method accepting the SalesPurchLine Map
public static void updateDeliverySchedule(SalesPurchLine _line, TransDate _newDate)
{
    // Operates polymorphically whether _line holds a SalesLine or a PurchLine
    _line.DeliveryDate = _newDate;
    _line.doUpdate();
}

// Calling the polymorphic method
SalesLine salesLine;
select firstonly salesLine where salesLine.SalesId == "SO-100";
SalesPurchLine lineMap = salesLine; // Implicit mapping
updateDeliverySchedule(lineMap, systemDateGet() + 7);

Maps vs. Table Inheritance vs. X++ Interfaces

• Table Inheritance: Requires a single unified inheritance hierarchy rooted in a common parent table (such as DirPartyTable), creating physical database relationships and shared RecId sequences across subtypes. Table inheritance is complex to maintain and has performance overhead.
• Maps: Do not alter the physical database schema. Participating tables remain completely independent in SQL Server. Maps provide a compile-time logical abstraction for shared data access.
• X++ Interfaces: Abstract class behaviors and methods, but do not provide data buffer mapping or automatic table field binding.

---

6. Scenario Walk-Through: High-Performance View for Customer Balances

Scenario Description

A financial controller requires a dashboard displaying active customers who currently owe overdue balances exceeding their credit limit. The grid must allow instant sorting by Overdue Balance and Credit Limit Utilization. Because the customer base exceeds 200,000 accounts and transaction volumes are high, using display methods on forms causes unbearable lag.

Solution Design

1. AOT Query (CustOverdueCreditQuery):
   • Root Data Source: CustTable.
   • Child Data Source: CustTransOpen.
   • Configure Relations = Yes.
   • Set JoinMode = ExistsJoin.
   • Add Range on CustTransOpen.DueDate: value set to ".." + date2StrXpp(systemDateGet() - 1) (due date earlier than today).
   • Add Range on CustTable.CreditMax: value set to "> 0".
2. View Metadata (CustOverdueCreditView):
   • Set Query = CustOverdueCreditQuery.
   • Add view fields: AccountNum, CreditMax, CustGroup.
3. Add Computed Column (computedOverdueUtilization):
   • Implement static server method using SysComputedColumn::divide() and SysComputedColumn::if() to calculate the ratio of overdue balance to credit limit.
   • Bind method to a new RealViewField on the view.
4. Form Integration:
   • Bind the new View as the datasource of the workspace grid. Users can sort and filter by overdue balance ratios instantly with zero AOS memory lag because the calculations and filters execute inside SQL Server.

---

7. Real-World Exam Traps: Views, Queries & Maps

[!WARNING] Exam Trap 1: Attempting to Read Child Table Fields After an ExistsJoin A classic MB-500 exam question shows an X++ code snippet where an ExistsJoin is executed between SalesTable and SalesLine, followed by an attempt to read salesLine.ItemId. The question asks what value is returned. The answer is blank/null/empty. An ExistsJoin never projects, selects, or transfers child table records into memory; it functions purely as an existence filter for parent rows.

[!WARNING] Exam Trap 2: Hardcoding SQL Table Names in View Computed Columns Options that construct computed column strings using hardcoded SQL aliases (e.g., return "T1.AMOUNTMST * 0.1";) are incorrect. SQL Server view aliases are dynamically generated during database synchronization and change across model builds. The exam strictly mandates using SysComputedColumn::returnField() to resolve physical column names safely.

[!WARNING] Exam Trap 3: Confusing FetchMode One2One with One2Many Questions asking how to iterate over sales order lines under an order header query expect FetchMode::One2Many. If FetchMode::One2One is used on a 1-to-many relationship, the query cursor only yields a single line per header, missing the remaining line items.

[!WARNING] Exam Trap 4: Assuming Maps Create Physical SQL Views or Tables Maps exist solely within the Application Object Tree (AOT) as an X++ compile-time and runtime abstraction. They do not generate physical tables, views, or triggers in the SQL database. Thinking a Map creates a database view or table inheritance hierarchy is a common misconception.