How to: Create Queries by using the AOT

  • Article

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

The Application Object Tree (AOT) provides a graphical interface for creating queries. These are known as static queries. When a query is created using the AOT, the SysQuery and SysQueryRun classes are used behind the scenes.

Create a Query for a Single Data Source

  1. In the AOT, right-click Queries, and then click New Query.

  2. Right-click Data Dictionary, and then click Open New Window.

  3. Drag a table, map, or view from the second window to the Data Sources node under the new query in the first window. All the fields in the data source are included in the query by default.

    For more information, see How to: Create Tables, How to: Create Maps, and How to: Create a View Based on a Query.

  4. To remove fields and add fields, do the following:

    1. Click Data Sources, click the data source that you added in step 3, right-click Fields, and then click Properties.

    2. Set the Dynamic property to No.

    Note

    By default, the Dynamic property is set to Yes and returns all fields from the table, map, or view. This makes maintenance easier because you don't need to change the query if a field is changed in the underlying data source. However, restricting the fields returned by the query is better for performance because unused data is not returned to the client each time the query is run.

    1. Expand the Fields node, locate the field that you want to delete, right-click the field, and then click Delete.

    2. Right-click the Fields node, and then click New > Field.

    3. Right-click the new field, click Properties, and select the field that you want to add in the Field property list.

  5. To override a QueryRun Class method, right-click the Methods node, click Override Method, and then click a method.

  6. Right-click the new query, click Properties, and then modify the properties as needed.

  7. Save the new query.

Define a Group By

This section describes how to add a field to the Group By specification of your query. You can add several fields to the Group By by repeating the following steps:

  1. Navigate to AOT > Queries > YourQuery > Data Sources > YourDataSource.

  2. Expand the Fields node.

  3. Drag any field onto the Group By node.

Define an Order By

This section describes how to add a field to the Order By specification of your query. You can add several fields to the Order By by repeating the following steps:

  1. Navigate to AOT > Queries > YourQuery > Data Sources > YourDataSource.

  2. Expand the Fields node.

  3. Drag any field onto the Order By node.

  4. In the properties window for the Direction property, you have the option of changing the default from Ascending to Descending.

Define a Range

  1. In the AOT, click Queries, and locate the query that you want to define a range for.

  2. Expand the query, click Data Sources, and then expand a data source.

  3. Right-click Ranges, and then click New Range.

  4. Right-click the new range, click Properties, and then select a field in the Field property list.

  5. Type an expression in the Value property to specify which records are retrieved.

Note

Complex range expressions can impact performance. The more filter expressions Microsoft Dynamics AX must apply to the data the longer the query will run.

Range expressions on different fields are ANDed. Range expressions on the same field are ORed. The following table lists the operators and symbols that can be used in range expressions.

Symbol

Meaning

Example

Description

=

Equal to

=Smith

Finds Smith.

!

Not equal to

!Smith

Finds all values except Smith.

..

Between two values

A..C

Finds all records from A to C including records with A and C.

>

Greater than

>1000

Finds all values greater than 1,000.

<

Less than

<1000

Finds all values less than 1,000 including negative values.

*

Match values containing the characters before or after the asterisk.

S*

or

*east*

Finds all values that begin with the letter S.

Finds all values that include the word east.

The NOT symbol, !, can be used in front of these wildcard * values. For example, if you set the range’s Value property to the eight characters of ! *east*, all values that contain the four characters east are excluded from the query results.

?

Match a single unknown character

Sm?th

Finds all values that include only the letters s, m, t, and h in the order shown and any character in the ? placeholder.

,

Separates expression components

A..D, !C

Finds A, B, and D.

  1. Set the Status property to specify specific behavior for the range.

    1. Open - The user can modify the query range.

    2. Locked - The user can see the range but cannot modify it.

    3. Hidden - The user cannot see the range but can add new ranges to the query.

See also

How to: Create Queries by Using X++

Using Expressions in Query Ranges

Query Properties

How to: Add Multiple Data Sources to a Query

Query Framework in the AOT

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.