Walkthrough: Creating a Form Based on a Composite Query

Article
05/18/2015

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

A composite query is one that uses another query as its data source. In this scenario you create a form that uses a composite query as a data source. The scenario is a realistic example that has form UI grids coordinated to match the parent-to-child (or foreign key) relationships of the underlying tables.

A composite query automatically starts with all the code from your original query. You benefit from more code reuse when you build forms and reports from a composite query, instead of creating a new query from scratch.

This walkthrough illustrates the following tasks:

Creating a query
Joining tables in a data source
Creating a form that uses a base query
- Creating a form with a data source
- Building a form UI of three grids
- Linking the child grids to their parent grids
Testing the form
Adding ranges to the query
Creating a composite query with more ranges
Creating a form based on the composite query
Inheriting X++ code from methods overridden in the base query

Prerequisites

To complete this walkthrough, you need:

Microsoft Dynamics AX
A license file that has access to the client (MorphX)
Data in SalesLine table for a company you can access

The Plan for this Scenario

The scenario is based on the three tables CustTable, SalesTable, and SalesLine. The parent-to-child relationships among these tables are as follows:

Parent: CustTable, child: SalesTable
Parent: SalesTable, child: SalesLine

You build a query that uses these tables for its data sources. You build a form that uses the query as its data source. You create a second query that extends the first query. We call the second query a composite query. You build a second form on the second query.

You can demonstrate the behavior of ranges added to the query and composite query is illustrated when you click different rows in the grids on the form. You can see the inheritance relationship between the query and the composite query when you override methods.

Creating a Query

In this procedure you create a query that uses three tables for its data sources.

To create a query

In the AOT, right-click the Queries node, and then click New Query.
Right-click the node for the new query, and then click Properties.
In the Properties window, change the Name to be QryCogBase1.
In the AOT, expand the QryCogBase1 node, and then expand the Data Sources node to see that it is empty and does not expand.
Right-click the Data Sources node, and then click New Data Source.
In the Properties window, click the drop-down list for the Table property and select CustTable. Then click the Name property to have the name automatically updated to match the selected table name.
Expand the CustTable_1(CustTable) node that is under the Data Sources node of our query.
Under the CustTable_1(CustTable) node, right-click Data Sources, and then click New Data Source.
In the Properties window, click the drop-down list for the Table property and select SalesTable.
Under the SalesTable_1 node, right-click Data Sources, and then click New Data Source.
In the Properties window, click the drop-down list for the Table property and select SalesLine.
In the AOT, right-click the QryCogBase1 node and click Save.

Joining Tables in a Data Source

In this procedure you join the tables listed under the Data Sources node of QryCogBase1.

To join tables in a data source

In the AOT under the Queries node, expand QryCogBase1, expand Data Sources, expand CustTable_1(CustTable), expand Data Sources, and then expand SalesTable_1(SalesTable).
Right-click the Relations node, and then click New Relation. A new relation is automatically added, and it is often created as we want it.
Ensure the automatically added relation says CustTable_1.AccountNum == SalesTable_1.CustAccount. If necessary, change the value by using the drop-down lists in the Properties window.
Under the SalesTable_1(SalesTable) node, expand Data Sources, and then expand SalesLine_1(SalesLine).
Right-click the Relations node, and then click New Relation.
Ensure the automatically added relation says SalesTable_1.SalesId == SalesLine_1.SalesId. Change the value if you have to.
In the AOT, expand Queries, QryCogBase1, Data Sources, CustTable_1(CustTable), Data Sources, and then click SalesTable_1(SalesTable).
In the Properties window, verify the JoinMode value is InnerJoin.
Verify the FetchMode is 1:n.
In the AOT, right-click the QryCogBase1 node and click Save.

Creating a Form That Uses a Base Query

This procedure is divided into smaller procedures.

Creating a Form With a Data Source

In this procedure you create a form that uses QryCogBase1 as its data source.

To create a form with a data source

In a second AOT window, right-click the Forms node, and then click New Form.
In the Properties window, rename the form to FormCogBase2.
Expand the FormCogBase2 node to see its Data Sources node.
In a new AOT window, expand the Queries node, and click QryCogBase1.
Drop QryCogBase1 onto Data Sources under FormCogBase2 in the other AOT window. Under the Data Sources node there are now nodes for three tables.

Building a Form UI of Three Grids

In this procedure you design a user interface (UI) on FormCogBase2. You add one grid for each table in the data source of the query.

The grids are created on the UI. In the next procedure the behavior of the grids is coordinated to reflect the parent-to-child relations between the tables. The goal is to have each grid display only the data that pertains to the row that is currently highlighted in its parent grid.

To build a form UI of three grids

In the AOT under the FormCogBase2 node, expand Data Sources. This shows the tables that are used by QryCogBase1.
Expand each table node to see the Fields node of each. Any Fields node can be expanded to show the available fields.
In the AOT under the FormCogBase2 node, right-click the Data Sources node, and then click Open New Window.
In the AOT under the FormCogBase2 node, expand Designs.
Right-click the Design node, click New Control, and then click Grid.
Rename the new grid node to Grid1CT.
Create two more grids and name them Grid2ST and Grid3SL.
In the Properties window, set the DataSource property for each grid as follows:
- Grid1CT to CustTable_1
- Grid2ST to SalesTable_1
- Grid3SL to SalesLine_1
Onto the Grid1CT node, drop the CustTable_1(CustTable) fields named AccountNum, Name, and PaymTermId.
Onto Grid2ST drop the SalesTable_1(SalesTable) fields named SalesId, SalesName, CustAccount, and DocumentStatus.
Onto Grid3SL drop the SalesLine_1(SalesLine) fields named SalesId, LineNum, Name, and SalesStatus.

Linking the Child Grids to Their Parent Grids

In this procedure you make the behavior of grids match the parent-to-child relations among their corresponding tables.

To link the child grids to their parent grids

In the AOT, expand Forms, FormCogBase2, Data Sources. A node for each table should be visible (together with a node for QryCogBase1).
Expand the node for the first child table, in this case SalesTable_1. Expand the Links node. A node for each of the other two tables is visible underneath.
Underneath, click the node for the table that is the parent of the current table. In this case click the CustTable_1 node. (Ignore the node for child table SalesLine_1.)
In the Properties window, set the LinkType to Delayed.
Expand Forms, FormCogBase2, Data Sources, SalesLine_1, Links. Click on SalesTable_1 (the node for the parent table).
In the Properties window, set the LinkType to Delayed.

Testing the Form

In this procedure you test FormCogBase2 to make sure that it is working.

To test the form

In the AOT, expand Forms, right-click FormCogBase2, and then select Open. This runs the form and populates its UI with data.
In Grid1CT (the top grid), click the first row.
Verify in Grid2ST (the middle grid) that all the rows have the same Customer account number as the one highlighted row in Grid1CT.
Click different rows in Grid1CT and verify that the Customer account values change appropriately in Grid2ST.
Click a row in Grid2ST.
Verify in Grid3SL (the bottom grid) that all the rows have the same Sales order value as the one highlighted row in Grid2ST.

Adding Ranges to the Query

In this procedure you add ranges to a query to reduce the amount of data that is returned.

To continue the scenario you must note three Customer account (AccountNum) values that are visible in Grid1CT on the form's UI. Those three values can vary between installations. Here they are referred to as 4000, 4009, and 4010. But you must use values obtained from your installation.

To add ranges to the query

In the AOT, expand Queries, QryCogBase1, Data Sources, CustTable_1(CustTable). The Ranges node becomes visible.
Right-click the Ranges node, and then click New Range. A default range node is generated.
In the Properties window, click the Field property drop-down list and select AccountNum.
Set the Value property to 4000.
Right-click the Ranges node again, and add another range for this same field AccountNum. Set its value to 4009.
Add a third range for AccountNum, with a value of 4010.

Note

These three ranges are treated with the Boolean OR operator, because they all have the same Field property setting, and because they are all part of the same query. In a later procedure it is explained that ranges on a composite query are always treated with Boolean AND operations in conjunction with the ranges of its base query.
Test the form. In the AOT, right-click FormCogBase2, and then click Open. Note that Grid1CT shows only rows with the Customer account values that are specified in the ranges.
Add a range on the Field called Name, with a Value of the five characters *xxx*.
Save QryCogBase1, and reopen FormCogBase2. The absence of all data shows the range on Name is conjoined with the AccountNum ranges by a Boolean AND operation. The AND is used to conjoin ranges that have different Field property values.
Right-click the range on Name, and then click Delete. Save QryCogBase1.

For more information about ranges, see Query Elements in the AOT.

Creating a Composite Query With More Ranges

In this procedure you create a composite query. You add a range to the composite query. The new range causes less data to be returned than would be returned by the base query.

To create a composite query with more ranges

In the AOT, create a second query named QryCogCompos5.
Expand QryCogCompos5. The Composite Query node is visible.
Drop QryCogBase1 onto the Composite Query node.

Note

The new QryCogBase1 node has a Composite Query node underneath. Nevertheless there is no meaningful scenario where you would drop a query onto that Composite Query node.
Expand QryCogCompos5, Composite Query, QryCogBase1, Data Sources, CustTable_1(CustTable), Ranges.
Right-click Ranges, and then click New Range.

Warning

In the AOT the new range node for the composite query appears as one more range on the base query. However, the new range belongs to the composite query only. The new range is treated with a Boolean AND operator whereas the original ranges are treated with Boolean OR. The AND is used even when the new range refers to the same field that the original ranges refer to.
In the Properties window for the new range, set the Field to AccountNum and the Value to 4022.
Save QryCogCompos5.

Creating a Form Based on the Composite Query

In this procedure you create a form to display the output of QryCogCompos5. You also test the effects of adding new ranges to QryCogCompos5.

FormCogBase2 cannot be reused for the composite query without erasing all the work done to design FormCogBase2 in the AOT.

To create a form that is based on the composite query

Create a form named FormCogCompos6 with QryCogCompos5 as its data source.
Design a UI of three grids for FormCogCompos6, just as you did for FormCogBase2.
Test FormCogCompos6. Notice that no data is displayed!

Note

No data is displayed because the range added to the composite query is treated with a Boolean AND in conjunction with the inherited ranges for the same Field. No one CustTable .AccountNum value can be 4022 and 4010.

You can force empty data to be displayed by setting the property InsertIfEmpty to Yes. You set this property on the node AOT > Forms > FormCogCompos6 > Data Sources > QryCogCompos5.
Click the node for the new range. Change its Field property to Name, and its value property to *Light*,*Lamp*.
Your data might require different strings to filter in only a subset of the CustTable rows that would be returned without this range.
Test FormCogCompos6 again. This time some data should appear.

Inheriting X++ Code From Methods Overridden in the Base Query

In this procedure you override a method on the base query. Then you open a form that is based on the composite query, and you see that the method override is run.

To inherit X++ code from methods overridden in the base query

In the AOT, expand Queries, QryCogBase1, Methods.
Right-click Methods, click Override Method, and then click init.
In the X++ code Editor window that pops up, add this one line of code:
info("QryCogBase1 .init override.");
Save QryCogBase1. Open FormCogCompos6. Verify that an Infolog message window appears and says QryCogBase1 .init override.

Next Steps

When you create a query, you should consider whether a composite query is an option. When an existing query resembles the new query you need, consider whether range modifications or method overrides would be sufficient to describe the new query. When you create a form or a report, you should consider creating a query first, for the data source of the form or report.