How to Create queries in D365 F&O

Rumman Ansari   Software Engineer   2025-02-03 11:12:05   192  Share
Subject Syllabus DetailsSubject Details
☰ TContent
☰Fullscreen

In finance and operations, you can use query objects in the development platform to build SQL statements.

You can predefine, build, or modify queries with code at run time. Use queries to render data into forms, reports, views, and more. Additionally, you can write SQL statements by using select queries.

We recommend that, rather than using tables directly, you use query objects in forms so that you can select the fields that you want to display in the form. This approach helps reduce the size of the SQL statement that's running behind the form, and it helps improve performance. The same notion also applies to reports and views. Additionally, you can use Microsoft Visual Studio to create predefined queries.

Create queries

To add predefined queries to a project, right-click the project or activate the context menu in the Solution Explorer window. Then, select Add > New Item and Data Model > Query.

How to Create queries in D365 F&O
Figure: How to Create queries in D365 F&O

You can use the Designer and Properties windows to change a predefined query. The Designer window contains a top-level node with the view name.

The following table lists the top-level properties for queries (options in bold are default values).

Property Options Description
Description Label reference Describes the query
Title Label reference The query title
Interactive No or Yes Specifies whether the query might open a dialog for query or not
Literals Default, ForceLiterals, ForcePlaceholder Specifies how literals are represented in SQL statements
Allow Check No or Yes Allows the checking of table rights
Allow Cross Company No or Yes Enables a cross-company query
Form SysQueryForm Provides a form for user interaction
Name   The query’s system name
Query Type Join or Union Indicates the query type
Searchable No or Yes Specifies whether query results are searchable
User Update No or Yes Indicates whether a user can update the query or not

The following table lists the nodes for a query in the designer.

Node Description
Methods Indicates the methods for the query
Events The node exists but isn’t used
Data Sources Indicates data sources in the query To add new data sources, right-click or activate the context menu and then select New Data Source.

The following table lists important properties for data sources.

Property Options Description
Enabled No or Yes Indicates whether the data source is active or not
Update No or Yes Indicates data that’s selected with the forupdate keyword
Dynamic Fields Clear, No or Yes Specifies how the field list is built for the data source The Clear value results in a build error.
Name   Indicates the data source name This property isn’t the table name; rather, it’s the name for the data source.
Table   Indicates the table or view name for the data source

The following table lists the various nodes that a data source has and which you can access in the designer.

Node Options Description
Fields   Indicates a dynamic field list if the Dynamic Fields property is set to Yes The system automatically adds new fields by using the Dynamic Fields property. To create fields, you can also right-click the Field node or activate the Context menu and then select New. You could also use aggregate functions.
Ranges   Specifies the field range that the query uses
Data sources   Indicates that a data source can join to another data source with InnerJoinOuterJoinExistsJoin, or NoExistsJoin
Group by   Groups the returned data
Having   Use to filter aggregate data
Order by   Use to order returned data


No Questions Data Available.
No Program Data.