
How to Create queries in D365 F&O
Table of Content:
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.

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 InnerJoin, OuterJoin, ExistsJoin, or NoExistsJoin | |
Group by | Groups the returned data | |
Having | Use to filter aggregate data | |
Order by | Use to order returned data |