Table Relations in Ax 2012

Hi Friends,

I would like to explain about table relations with a very basic example for understanding  about the relations what is the exact usage and working of relations.

Why  Tables?

Tables are used to store data in the database.It has associated with columns and rows.In Ax tables are acting like objects.We can use tables wherever we want in Ax.

A table relation associates with two tables that contain related data. Usually the primary key field of one table appears as a foreign key field of the related table. The table with the primary key is called the parent table. The table with the foreign key is called the child table.

Elements of Microsoft Dynamics Ax Table:

Relation in Tables:

1. Normal
2. Field fixed
3. Related Field Fixed
4. Foreign Key–SingleAlternativeKey based
5. Foreign Key–Primary key based

1. Creating a table:

1. Let consider a table to store Person information is to be created.

1. Create a new table,on Tables node in the AOT, right-click and choose New Table.
2. A new table will be created. Open the property sheet and rename the table to “Person”.
3.  Open another instance of the AOT and go to Data Dictionary–>Extended Data Types.
4. Locate the extended data type AccN0 and drag the extended data type AccNo to the Fields node of “Person”.
5. Add Fields to the person tables Name,Location and Salary
6. Save the table by pressing Ctrl+s . The table is now synchronized to the database and you have created your first table.

7.Now open the table .

 

How do we create  Normal Relation in Tables:

1.Create another table PersoncarDetails. Here we have AccNo that should be a Foreign Key for the Person Table.

2.Now Right Click on Relations node  and add Relation set the properties like below:

3.Right click on the Relation node and add normal relation

4.Now See the Result it will show the person table details

Related Field Fixed Relation in Tables:

• It  filters the data based on our own defined condition in a related table.
• Related Field fixed  will works on Integer Data types.

Lets see how it will be worked:-

1.Right Click on Relation add related field fixed and set the properties:

2.Here the result is displayed based on the condition.

 

Field Fixed Relation in Tables:

1.Create another table PersonParent

2.Add Field Fixed Relation in the Relation node:

3.Now see the Result.Whenever we are selecting the select table field based on the table it is showing the related table data.