Relation in Ax 2012 (relation data base)

Add a Relation to a Table in the AOT

The initial steps for adding a relation are the same regardless of the relation type that you are adding. The later steps diverge based on the relation type.

Remember to save your changes in the AOT.

1. In the AOT, move to Data Dictionary > Tables, and then expand the table that the relation will be added to.
2. Right-click the Relations node, and then select New Relation.
3. Right-click the newly added relation, and then select Properties.
4. Set the name of the new relationship by modifying the Name property.
5. In the Table property, select the related table.
6. Use the Validate property to determine whether the relation should be used to validate data when information is entered into forms.
7. Right-click the new relation, select New, and then click one of the following:

• Normal to specify relation fields without conditions.
• Field fixed to specify relation fields to restrict the records in the primary table.
• Related field fixed to specify relation fields that restrict the records in the related table.
• ForeignKey to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

Related Fixed Field Relation in AX 2012

1. Create four tables

S.No	Name
1.	ClothesTable
2.	MenClothesOrder
3.	WomenClothesOrder
4.	ChildrenClothesOrder

2. Create BaseEnum "CollectionTypeId" with the following elements:

S.No	Name	EnumValue
1.	Men	0
2.	Women	1
3.	Children	2

3. Create ExtendedDataType "CollectionTypeEDT" and set EnumType property as BaseEnum "CollectionTypeId".

4. Add following fields and set properties for " ClothesTable".

S.No	Name	Data Type	ExtendedDataType
1.	ClotheId	String
2.	Name	String
3.	CollectionTypeId	--	CollectionTypeEDT

5. Add following fields in "ChildernClothesOrder", "MenClothesOrder" and "WomenClothesOrder" :-

1. ClotheId 2. OrderId

3. Qty

6. On "MenClothesOrder" create a new relation with "ClothesTable" and specify the follwing two relations:

1. Normal Relation between MenClothesOrder.ClotheId and ClothesTable.ClotheId
2. Related Fixed Field Relation and set following properties.

Field=CollecTionTypeId

Value= 0

SourceEDT = CollectionTypeEDT

7. On "WomenClothesOrder" create a new relation to "ClothesTable" and specify the follwing two relations:

1. Normal Relation between WomenClothesOrder.ClotheId and ClothesTable.ClotheId

2. Fixed Field Relation and set following porperties:-

Field=CollecTionTypeId

Value= 1

SourceEDT = CollectionTypeEDT


Case 3:

7. On "ChildernClothesOrder" create a new relation to "ClothesTable" and specify the follwing two relations:

1. Normal Relation between ChildernClothesOrder.ClotheId and ClothesTable.ClotheId

2. Fixed Field Relation and set following porperties:-

Field=CollecTionTypeId

Value= 1

SourceEDT = CollectionTypeEDT

********************************************

Relations

         Normal to specify relation fields without conditions.

         Field fixed to specify relation fields to restrict the records in the primary table.

         Related field fixed to specify relation fields that restrict the records in the related table.

         New ForeignKey to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

Ex:-SalesTable->salesLine.

Normal Relation

1.       In the Field property, select the field in the primary table that relates to a field in the present table.

2.       In the RelatedField property, select the field in the related table.

Ex:-Salesline.salesid==SalesTable.salesid

Field fixed Relation

1.       In the Field property, select the field in the primary table to use to restrict the records.

n the Value property, enter the value of the selected field as the filter. This relates only records in the primary table that match that field value. Only numeric values can be entered in the Value property. Field fixed relations can be created only on numeric fields. Each of the related fields are AND ed in the table relation.

Ex:-Salesline.Salesid==1234

Related field fixed Relation

1.       In the Value property, enter the filter value of the selected field. This causes only records in the related table that match that field value to be related. Only numeric values can be entered in the Value property. Related field fixed relations can be created only on numeric fields.

2.       In the Field property, select the field in the related table to restrict the records. Each of the related fields are ANDed in the table relation.

Ex:- 1234==SalesTable.SalesId.

New ForeignKey Relation-2012

We often use the term child to refer to a table that has a foreign key column. And we use the term parent to refer to the other table that supplies the value for the foreign key column. But we never use the terms parent and child to describe the base and derived tables in an inheritance relationship.

1.       Set the Table property to the name of the parent table, the table that contains the primary key field.

2.       Set the RelatedTableRole property to a word or phrase that describes the purpose of the parent in the relationship.

Note

This value is added automatically as a method name on the child table. This method is displayed by IntelliSense in the X++ editor, but you cannot see the method in the AOT. For more information about how to use this method, see How to: Use the UnitOfWork Class to Manage Database Transactions.

3.       Set the Name property. A helpful value is a combination of the Table property and RelatedTableRole property values.

4.       Right-click the node for your relation, click New, and then click New ForeignKey. Next click either PrimaryKey based or Single field AlternateKey based. A new field is instantly added to the child table. This field stores the foreign key values.

5.       Under the Fields node, click the new field, and then change its Name property value.

6.       For performance benefits, you might decide to add an index on the new foreign key field.