SQL and configuration translated into X++

The statement-by-statement translation from T-SQL against the Unit4 Foundation database to X++ — select firstonly and while select replacing cursors, update_recordset and insert_recordset replacing set-based UPDATE/INSERT, ttsbegin/ttscommit replacing BEGIN TRAN, and DataAreaId/RecId replacing the client column — plus the honest limits of Attributes, Relations and FlexiFields once the model has to be compiled.

What you will be able to do

• Translate a T-SQL SELECT, cursor loop, UPDATE, INSERT and MERGE against the Foundation tables into its X++ equivalent
• Explain why direct SQL against a D365 production database is not supported and name what replaces it
• Describe ttsbegin/ttscommit reference counting and why there is no savepoint or partial rollback
• Account for the client column's replacement by DataAreaId and RecId when porting a query or join
• Judge which Attribute, Relation or FlexiField patterns become a financial dimension, a table extension field, or nothing at all
• Rewrite a Unit4 validation rule as a Chain of Command extension on validateWrite

Introduction

Ask a Unit4 practitioner what language they program in and most will hesitate. The honest answer is: mostly none, and mostly SQL. The day-to-day craft is T-SQL against the Foundation database — acrrelation, acrbatch, achead, acrtran — wrapped in configuration: Attributes, Relations, FlexiFields, Browsers, Report Writer and Analysis…

The honest DNA comparison

Unit4 work is SQL plus configuration; X++ is SQL-embedded and set-based by design. A SELECT, a WHILE cursor, a set-based UPDATE, a bulk INSERT — every one of these has a direct or close X++ counterpart, covered statement by statement below.

The select family: T-SQL, translated statement by statement

firstonly plays the role of TOP 1. There is no client predicate to write — the AOS injects the equivalent scoping automatically, which the next section covers in detail. Fields come back through custTable, a typed table buffer: the query target and the result holder are the same variable.

Transactions: ttsbegin, ttscommit, and the vanished savepoint

custTrans.Closed = NoYes::Yes; custTrans.SettleAmountMST = _settleAmount; custTrans.update();

Keys and company scoping: from client to DataAreaId and RecId

Almost every Foundation table carries a client column, and almost every query you have ever written filters on it explicitly. D365's equivalent, DataAreaId, works differently in a way that matters: it is not a field you add to your WHERE clause, it is a predicate the AOS injects automatically on every query against a company-scoped…

The model-fluidity problem: Attributes, Relations and FlexiFields meet a compiled schema

Unit4's signature strength is that a customer can reshape the model without code: a new Attribute Value, a new FlexiField on a Relation, a new coding combination on an Account Rule, all delivered by configuration this week.

Extension surfaces, mapped

Each Unit4 extension surface has a D365 landing point, but rarely a one-for-one object — most split according to who or what consumes them.

Worked translation: the same validation, twice

Requirement: a purchase order cannot advance until the supplier has a mandatory Contract Reference recorded. In Unit4, this is a stored procedure called from an IntellAgent rule or an ACT script, reading the FlexiField through acruserattr:

Knowledge check

Summary

X++ asks a SQL-and-configuration practitioner to learn an object model and a compiled metadata layer that genuinely did not exist in their toolkit before. It also hands back, unchanged, the one instinct that took years to build: think in sets, not in rows.