Code Examples

Practical X++ code patterns for working with views — computed columns, layered view composition, and basic data access.

---

Selecting from a View

A view buffer behaves like a read-only table — use standard X++ select syntax:

SAMOCustSummaryView samoView;

// Simple select
select samoView
    where samoView.CustGroup == '10';

info(strFmt("Customer: %1", samoView.CustName));

// While-select loop
while select samoView
    where samoView.TransDate >= today() - 30
{
    info(strFmt("%1 — %2", samoView.AccountNum, samoView.Amount));
}

// Joining a view with a table
SalesTable salesTable;
SAMOCustSummaryView samoView;

select samoView
    join salesTable
        where salesTable.CustAccount == samoView.AccountNum;

danger

Do not call insert(), update(), or delete() on a view buffer. Views are read-only. The compiler will not prevent the call (inherited from Common), but the runtime will throw an error.

---

Computed Columns Using SysComputedColumn

Computed fields are defined by a static method that returns a SQL expression string. The method runs at sync time (not at query time) and the returned string is spliced into the CREATE VIEW SQL.

/// <summary>
/// Returns the full name by concatenating first and last name.
/// </summary>
private static server str samoFullName()
{
    DictView dictView  = new DictView(tableNum(SAMOCustSummaryView));
    str      dsName    = identifierStr(CustTable);
    str      firstName = dictView.computedColumnString(
                             dsName,
                             fieldStr(DirPartyTable, FirstName),
                             FieldNameGenerationMode::FieldList);
    str      lastName  = dictView.computedColumnString(
                             dsName,
                             fieldStr(DirPartyTable, LastName),
                             FieldNameGenerationMode::FieldList);

    return SysComputedColumn::concat(firstName, ' ', lastName);
}

This produces SQL like:

CREATE VIEW [dbo].[SAMOCUSTSUMMARYVIEW] AS
SELECT
    ...
    CONCAT(T1.FIRSTNAME, ' ', T1.LASTNAME) AS SAMOFULLNAME,
    ...
FROM CUSTTABLE T1

---

Replacing Display Methods with Layered Views

Display methods execute per-row in X++ and cannot be sorted or filtered. Layered views replace them with a real SQL column.

The Problem: Display Method

// Runs per row — not sortable, not filterable
display Amount openInvoiceTotal(CustTable _custTable)
{
    return (select sum(AmountMST) from custTransOpen
                where custTransOpen.AccountNum == _custTable.AccountNum
           ).AmountMST;
}

The Solution: Layered Views

1. View 1 — SAMOCustOpenTotalView: aggregates CustTransOpen by AccountNum, returns one row per customer with Sum(AmountMST).
2. View 2 — SAMOCustWithTotalView: outer-joins CustTable to SAMOCustOpenTotalView, exposing all customer fields plus the aggregated total.

The form data source becomes SAMOCustWithTotalView — the total column is a real SQL column that users can sort, filter, and export.

---

SysComputedColumn Helper Methods

Method	SQL Equivalent	Purpose
if	CASE WHEN ... THEN ... ELSE ... END	Conditional logic.
concat	CONCAT(...)	String concatenation.
cast	CAST(... AS ...)	Type casting.
isNullValue	ISNULL(...)	Null replacement.
equalExpression	=	Equality comparison for CASE expressions.
notEqualExpression	<>	Inequality comparison.
and	AND	Logical AND for combining conditions.
or	OR	Logical OR for combining conditions.
sum, count, min, max, avg	Aggregate functions	Aggregation expressions.

warning

Always use SysComputedColumn helpers and DictView.computedColumnString() to generate column references — never hard-code physical SQL column names, as these can differ from the AOT field names.