Previous

Next

Create, manage, and extend queries

Completed

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

You can modify and create queries from code by using the query class. The following example depicts a query being created with the name MyCustTable. It has only one data source custTable, with two fields: AccountNum and CustGroup. Then, you need to add the CustGroup to the range in code, which the following sample code illustrates.

The following example shows the result in code:

 internal final class MyCustTableQueryModifyInCode
 {
 
   /// <summary>
   /// Class entry point. The system will call this method when a designated menu 
   /// is selected or when execution starts and this class is set as the startup class.
   /// </summary>
   /// <param name = "_args">The specified arguments.</param>
   
   public static void main(Args _args)
   {
       Query                   query = new Query('MyCustTable'); // using the existing query named MyCustTable
       QueryBuildDataSource    qbds;
       QueryBuildRange         qbr;
 
       qbds = query.dataSourceTable(tableNum(CustTable)); // finding data source for table CustTable
       qbr = qbds.addRange(fieldNum(CustTable, CustGroup)); // adding range on CustGroup to the data source.
 
       QueryRun queryRun = new QueryRun(query);
       while (queryRun.next())
       {
           CustTable custTable = queryRun.get(tableNum(CustTable));
           info(strFmt('%1, %2', custTable.AccountNum, custTable.CustGroup));
       }
   }

}

Typically, you use the following classes to modify or create queries by code:

• Query
• QueryRun
• QueryBuildDataSource
• QueryBuildRange

Other classes are available for you to use, but they’re beyond the scope of this module. For more information, see Access data by using the SysDa classes.

Additionally, developers can use the SysDa framework to create extensible queries without a user interface, such as the select statement. See [Implement query objects and query builder](/tra ining/modules/build-reports-finance-operations/query-objects-query-builder /?azure-portal=true) for more information.

Manage and use queries

You can use predefined queries for reports, forms, views, and much more. They’re available in the Application Explorer or Solution Explorer, and you can drag them to your data source node to use them. Additionally, you can select them on the query property in the data source.

Before using a query or query extension, you need to build them.

Extend queries

To extend a query, locate it in the Application Explorer, right-click the view or activate the Context menu, and then select Create extension, as shown in the following screenshot.

After you’ve created an extension for a query, it appears in the project in Solution Explorer. The name of the original query is appended to the extension name with the model name, as shown in the following screenshot.

When extending a query, you can:

• Change labels.
• Add new data sources.
• Add new fields.

  Tip

  If you set the Dynamic Fields property to Yes on the data source, the system adds new fields automatically to the query field list. Therefore, an extension for the query isn’t needed for new fields.

• Add a new grouping, ordering, and having.

However, you can’t remove existing data sources, fields, grouping, ordering, or having.

Continue