Friday, August 26, 2011

Inheritance in AX Tables

Yes, we can now inherit the tables as well which means less code writing and extending the capabilities of base table to be used in derived table.
Important: [Do not use term “Parent” when discussing inheritance. – As per MSDN]
[Do not use “Child” when discussing inheritance. – As per MSDN]
Two main properties to enable the table inheritance are:
·         SupportInheritance
·         Extends
Need of Table Inheritance:
·         When there is 1:n or n:n relationships between two tables
·         When base table and derived table both carry different information about same item/object.
Scenario: The organization has 50 data-cards and allocates it periodically to the employees travelling to customer place. The system need to capture the information of data-cards with their unique number in one table, details of purchase and vendor in second table, data about data-cards issued to employees in third table and data about damage/loss of data-cards in fourth table. Below will be the table structure in case we are not using table inheritance:

DataCardTable
DataCardPurchaseDetails
DataCardIssueDetails
DataCardDamageDetails
ProdNumber
ProdNumber
ProdNumber
ProdNumber
Make
Make
Make
Make
DataUsagePlan
Date of purchase
Issue to employee
Reason [Lost/Damaged]

Purchase price
Date of issue
Date of damage

Warranty in years
Date of return
Remarks


Basis the table inheritance, we can restructure the tables as mentioned below:

DataCardTable
ProdNumber
Make
DataUsagePlan
InstanceRelationTypeId*
RecId**


* - The system field InstanceRelationTypeId has been added to the base table and its values refer to derived tables.
** - Each value in the DataCardTable.RecId column must match a RecId value in one of the tables that derives from DataCardTable.

Table structure for derived tables would be as below:

DataCardPurchaseDetails
DataCardIssueDetails
DataCardDamageDetails
Date of purchase
Issued to employee
Reason [Lost/Damaged]
Purchase price
Date of issue
Date of damage
Warranty in years
Date of return
Remarks


We will walk through with one base table and one derived table in this example as mentioned in the below steps.
Step 1: Create a new table ‘DataCardTable’.
Step 2: Set the property ‘SupportInheritnace’ to yes.





Note that if the fields are added first, you will not be able to set this property to yes. Remove the fields first to set this property.
Step 3: Create the derived table ‘DataCardIssueDetails’
Step 4: Set the property ‘SupportInheritnace’ to yes.
Step 5: Add the fields to the base table. Also, add the field ‘InstanceRelationTypeId’ of type Int64.







The InstanceRelationType property tells the system which field must store the table IDs. The field must be of type Int64.
Step 6: In the properties window for base table, set the InstanceRelationType property to InstanceRelationTypeId.



Step 7: In the properties window for derived table, set the “Extends” property to base table i.e. DataCardTable.





Step 8: Add the fields to the derived table.




Insertion effects:
1.       If you enter the data in the base table, the records entered in the base table would look like this:

ProdNumber
Make
DataUsagePlan
InstanceRelationType
12345
Reliance
Talk299
100388 (TableId of Base table itself)
12346
Tata
Talk399
100388 (TableId of Base table itself)


2.       If you enter the data in the derived table, the record in base table would also be created and the new record would look like this:


ProdNumber
Make
DataUsagePlan
InstanceRelationType
12347
Samsung
Talk499
100389 (TableId of Derived table)


And the same record in the derived table would look like this:


ProdNumber
Make
DataUsagePlan
IssuedToEmployee
DateOfIssue
DateOfReturn
12347
Samsung
Talk499
Vishal
25-Aug2011



Deletion effects:
Deleting record from base table: Deleting the record from base table which has corresponding record in derived table would result in deletion of record from the derived table as well automatically.
Deleting record from derived table: Deleting the record from derived table which has corresponding record in base table would result in deletion of record from the base table as well automatically.

Modification effects:
Modification of record from base table: Modification of the record in base table which has corresponding record in derived table would result in same modification of record in the derived table automatically.
Modification of record from derived table: Modification of the record in derived table which has corresponding record in base table would result in same modification of record in the base table automatically.




5 comments: