Hai all,
I am new to Microsoft Dynamics AX and i am working on AX 2012
Can any one help me on what is table relations in AX 2012 and how they are used and when we have to use the table relations with example
your suggestions are appreciable
Regards,
Kiran
hi kiran,
Table level mainly 3 types of relations there…
1)normal 2)fieldfixed 3)related fieldfixed
Normal:::To specify the relation fields without any conditions…
Fieldfixed:::To specify the relation fields to restrict the records in the" primary table"…[Means first table]
Related Fieldfixed:::To specify the relation fields to restrict the records in the “related table”…[Means Related table]
ForExample: take two tables…Custtable having Accno,Name,Gender[male,female---- male means0 female means 1] [primary table]
CustTranse having Accno,Transdate,Amount,Paid [ yes or no-----yes means 0 no means1] this is related table
Normal-----Custtable.Accno==CustTranse .Accno
Fieldfixed---- Custtable.Gender == 0
Related Fieldfixed---- 1 == CustTranse .paid
thanks,
Suresh.
Hai Suresh,
thanks for your reply
but iam asking relations in AX 2012 there are so many new things are added like surrogation key ,foregin key etc… explain me about that plz
Thanks,
Kiran
Hi Kiran!
Maybe this is useful for you: http://msdn.microsoft.com/en-us/library/hh812105.aspx
Regards,
OPi
hi kiran,
Primary Key
The primary key is usually the type of key that other tables, called child tables, refer to when a foreign key field in those other tables need a relational identifier.
When we create a new table in AX2012 the Primary key was enforced by an index that has one field, this field should be unique, by default the primary key is based on Recid field. This is represented as surrogateKey in the table properties primary index.
By default the CreateRecIdIndex property was true and ClusterIndex as surrogateKey.
The ClusterIndex value is given to the underlying Microsoft SQL Server database system as a performance tuning choice. This choice generally controls the physical sequence in which the records are stored in the underlying database.
ReplacementKey
This key is used to define the replacement fields to display in your design when you referred to some relation using the Recid.
ex:-
Table B is referring to Table A where the TableB have the Refrecid from Table A, but when you open the form for TableB We cant show the Recid of the TableA in the Presentation,So whatever the field that you want to display, you will define one index on TableA with the fields that you want to show and and index alternateKey property to Yes. define the Replacement key property for the table A as this index.
If a ReplacementKey is chosen, its fields can appear on forms to helpfully identify each record.The ReplacementKey should be a set of fields that represent the natural key.
In AX2012 Relation represents a foreign key.
**Foriegn Key-**In Microsoft Dynamics AX, an AOT node under MyTable > Relations represents a foreign key.
natural key-A key whose value has meaning to people. Most replacement keys are natural keys.(ReplacementKey).
**unique key-**this applies to primary keys and to alternate keys. It does not apply to foreign keys. This term emphasizes that all values for a given key must be unique within one table. All fields in a unique key must be not-nullable.
Thanks,
SureSh.
Hi,
This might be a useful link for you to understand table relations
http://msdn.microsoft.com/en-us/library/bb190076.aspx
http://msdn.microsoft.com/en-us/library/hh803130.aspx
Thanks,