Microsoft DynamicsFree generatorPDF, Excel, Word exportMicrosoft Dynamics 365 Finance and Supply Chain

X++ Select Statement and Query Generator for Dynamics 365

This generator produces four common X++ data access patterns from one set of inputs: a while select loop, a firstOnly lookup, a Query API version and a set-based update_recordset. Replace the defaults with your own table and field names and copy the result into Visual Studio.

Fill in

Table name

Application table or your own table.

Buffer variable

Filter field

Filter value (X++ expression)

Use a literal such as "10" or an enum such as SalesStatus::Invoiced.

Order by field

Field to update

New value

Company scope keyword

crosscompany reads all companies you have access to.

Generated code

Download:

1. Row-by-row while select

CustTable custTable;

while select [Company scope keyword] * from custTable
    order by custTable.AccountNum
    where custTable.CustGroup == "10"
{
    info(strFmt("%1", custTable.AccountNum));
}

2. Single record with firstOnly

CustTable custTable;

select firstOnly custTable
    where custTable.CustGroup == "10";

if (custTable.RecId)
{
    info(strFmt("%1", custTable.AccountNum));
}

3. Query API version

Query query = new Query();
QueryBuildDataSource qbds = query.addDataSource(tableNum(CustTable));
qbds.addRange(fieldNum(CustTable, CustGroup)).value(queryValue("10"));
qbds.addSortField(fieldNum(CustTable, AccountNum));

QueryRun queryRun = new QueryRun(query);
while (queryRun.next())
{
    CustTable rec = queryRun.get(tableNum(CustTable));
    info(strFmt("%1", rec.AccountNum));
}

4. Set-based update

CustTable custTable;

ttsbegin;
update_recordset custTable
    setting CreditMax = 50000
    where custTable.CustGroup == "10";
ttscommit;

Check field names and types in your own model, because queryValue needs a value compatible with the field. update_recordset falls back to row-by-row processing if the table has overridden update logic, event handlers or database logging, so test in a non-production environment. Avoid reading large tables without a where clause.

Skip the query writing

See how read-only natural language access over your ERP database can replace one-off queries and reports.

Optional. The tool and every export work without this. No spam.

How to use it

Enter the table, a buffer variable name, the filter field and a value expression. The value is inserted as written, so use quotes for strings and the enum symbol for enum fields. Pick crosscompany only if you intend to read across companies. Copy each block into a class or runnable job in a development environment. The Query API block is the one to prefer when the filters are decided at runtime or when you will reuse the query in a form or batch.

Fill table and field names exactly as in the AOT.
Use enum symbols for enum fields instead of numbers.
Run in a development or test environment first.

Choosing between the patterns

A while select is simple and readable for small sets and fixed filters. firstOnly is right when you need a single record because it tells the database to fetch one row. The Query API builds the query as objects, so it supports dynamic ranges, joins and reuse. update_recordset runs as one database operation and is much faster than a loop for bulk changes, but only when the table does not have logic that forces row-by-row execution.

Performance and correctness

Always filter on indexed fields where possible and select only the fields you need with a field list when the table is wide. Never put database calls inside a loop of another select unless you must. Remember that X++ select places order by before where. In batch jobs, wrap updates in ttsbegin and ttscommit scopes of reasonable size to avoid long locks.

Common mistakes

Developers frequently forget that crosscompany returns rows from every legal entity, which can leak data or produce duplicates in reports. Others compare strings without considering case or trailing spaces.

Forgetting the container or company context in batch jobs.
Using firstOnly without checking RecId afterwards.
Calling doUpdate in a loop and skipping business logic unintentionally.
Hardcoding enum integers instead of enum symbols.

Frequently Asked Questions

What is the difference between select and while select in X++?

A plain select reads the first matching record into the buffer, while while select loops through every matching record. Use select firstOnly when you only need one row, and while select when you need to process each row in the result.

When does update_recordset fall back to row-by-row?

It falls back when the table has overridden update or related methods, database logging or alerts configured for it, or certain event handlers. In those cases the platform must run logic per record. You can skip some of this behavior only through documented skip methods, with care.

How do I read data across companies in X++?

Add the crosscompany keyword to the select statement or set allowCrossCompany on the query, and optionally restrict to a container of company ids. Be careful to filter on dataAreaId where needed and to respect security requirements.

Should I use the Query API or a select statement?

Use select statements for fixed, simple queries in code. Use the Query API when ranges, sorting or joins are decided at runtime, or when the query is shared with forms, reports or batch jobs. Both end up as database queries.

If you want business users to ask data questions without writing X++ or reports, Netray can help set up read-only natural language access over your D365 database.

Talk to an Expert Explore More Free Tools

Related Tools

Microsoft Dynamics

More Microsoft Dynamics tools

See all

Back to All Tools