Walkthrough: Leveraging Foreign Keys in Query Relations

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

You can take advantage of the foreign key relationship that exists between two tables when you create a query that joins the two tables.

This walkthrough illustrates the following tasks:

Creating the parent and child tables
Creating the query
Creating a form to display the query results

Prerequisites

The following topics can help you understand this topic:

How to: Add a Relation to a Table – explains how to add a foreign key relation.
How to: Create Queries by using the AOT – explains how to create simple queries.

Creating the Parent and Child Tables

In this section, you create two tables. The child table has a foreign key field that contains the same value that the primary key field contains in the parent table.

In the AOT, create a table. Name the table TabParent. The RecId field is the default primary key.
Add a string field named ParentComment, and set its Mandatory property to Yes.
Create a table named TabChild. Add a string field named ChildComment, and make it mandatory.
On TabChild, add a foreign key relation that references TabParent. Set the RelatedTableRole property to masterParent. For more information, see How to: Add a Relation to a Table.
The TabChild table now has a field named TabParent. Rename the field to TabParentFky.
Right-click the TabParent node, and then select Open. The Table Browser form is displayed. Use the form to add a row of data to TabParent.
Add a row of data to TabChild.

Creating the Query

In this section, you create a query that joins the parent and child tables. The system uses the foreign key information to help you specify the join.

In the AOT, right-click the high level Queries node, and then select New Query.
Right-click the new query node, and then select Properties. The Properties window is displayed. Set the Name property to QryPF.
Under the QryPF node, right-click Data Sources, and then select New Data Source.
For the new data source node, set the Table property to TabParent. This creates a new node under Data Sources, and the system might name it TabParent_1.
Another Data Sources node is under the TabParent_1 node. Right-click this Data Sources node, and add TabChild as a data source. This creates a TabChild_1 node.
Expand the TabChild_1 node, right-click the Relations node, and then select New Relation. A new relation node is displayed.
For the new relation node, set the JoinDataSource property to TabParent.
Set the RelatedField property the empty value. Make sure the property value contains no text and no icons. Set the Field property to empty also.
Click the drop-down list for the JoinRelation property, and then choose masterParent. This completes the action of adding the New Relation to the query

Alternative Way to Add the New Relation

There is an alternative to using the New Relation menu that is described in the previous steps. You can set the Relations property to Yes on the AOT > Queries > QryPF > Data Sources > TabParent_1 > Data Sources > TabChild_1 node.

This technique automatically adds a relation node under AOT > Queries > QryPF > Data Sources > TabParent_1 > Data Sources > TabChild_1 > Relations node. If there are multiple relationships between the parent and child tables, you must verify that the system selected the relationship that you intended.

Create a Form to Display the Query Results

You can create a simple form to display the results of your query. You can verify that the table join is working as expected by examining the data that the form displays.

In the AOT, right-click the Forms node, and then select New Form.
Right-click the new form node, and then select Properties. The Properties window is displayed. Set the Name property to FormPF.
Drag the QryPF node onto the FormPF > Data Sources node.
Click FormPF > Designs > Design > New Control > Grid.
From the FormPF > Data Sources > TabParent > Fields node, drag the fields that are named RecId and ParentComment to the Grid node.

Note

You cannot drag these fields from the FormPF > Data Sources > QryPF > Data Sources > TabParent > Fields node.
From the FormPF > Data Sources > TabChild > Fields node, drag the fields that are named RecId, ChildComment, and TabParentFky to the Grid node.
Right-click the FormPF node, and then select Open. The form displays the data.

Next Steps

For more information, see the FormReferenceControl.