Tables are the foundation objects in Microsoft Dynamics AX and store data used by the system. A table is made up of records (or rows) that contain information about a single entry in the table
Tables are located in AOT under the Data Dictionary\Tables node. Each table contains the following primary elements:
· Fields
· Field Groups
· Indexes
· Relations
· DeleteActions
· Methods
Fields:The Fields node contains all the fields in the table. By specifying a field's data type, you define the type of data that can be stored in it
· The best practice is to drag and drop either extended data types and base enums to create a new field.
· If existing EDTs and Enums does not fulfill your needs, you should start creating new ones EDT/Enum before creating your fields.
· One Important property to be defined is configuration key, as your fields will not be viewable for the application users if the related configuration key is disabled.
· All fields should have an extended data type or a base type set in the properties.
· A very powerful flexible feature of Axapta is by using EDTs/Enums, if you change a property value such as field length or alignment or Rename the EDT/Enum, it will locate all places in application where that EDT/Enum is used and will update them all accordingly.
· Best practice states that all fields should start with lower case letters.
· Few Common properties for fields are:
ü Mandatory (yes/no)
ü AllowEdit (yes/no)
ü AllowEditOnCreate(yes/no)
ü Visible (yes/no)
· Container Fields: Fields of the base type container are often use for storing binary files like bitmaps, which is large in comparison to other fields. So we should be cautious of adding such fields to existing tables.
For example, Table CompanyInfo is a widely used table and if we add a large bitmap for the company logo it may affect the performance of the application.
· System Fields: All tables have a set of system fields which are automatically added when creating a new Table.
The system fields are not listed in the AOT. You can get the list of system fields by checking the fields for the system table Common.
RecID: is a unique id for each and every record. And track of counter is done by Table SystemSequences.
The fields prefixed with Created* and modified* contain no value unless you set the properties with the corresponding name at the table property sheet.
System field
|
Table property
|
RecId
|
Always
|
RecVersion
|
Always
|
DataAreaId
|
SaveDataPerCompany = Yes
|
CreatedBy
|
CreatedBy = Yes
|
CreatedDate
|
CreatedDate = Yes
|
CreatedTime
|
CreatedTime = Yes
|
CreateTransactionId
|
CreateTransactionId = Yes
|
ModifiedBy
|
ModifiedBy = Yes
|
ModifiedDate
|
ModifiedDate = Yes
|
ModifiedTime
|
ModifiedTime = Yes
|
ModifiedTransactionId
|
ModifiedTransactionId = Yes
|
Fields Groups:Field groups are objects that group together fields that logically belong together.
· Field groups are widely used when creating forms and reports. Instead of adding fields one by one to a design, you should add field groups.
· If a field group is later changed, e,g, when adding a new field to a group, then the new field will automatically be available everywhere that specific field group is used.
· Fields visible for the application users must be part of at least one field group. However in Morphix we can use fields which are not part of any field group.
Example (1):In the previous example the table MyFirstTable was created and fields were added. Now let’s add field groups to the previously created MyFirstTable.
- Locate the field group node AutoReport. Mark CustName, CustGroupID & CurrencyCode and drag the fields to the AutoReport node.
- Go to the field group node AutoLookup. Drag the fields accountNum and custName to the AutoLookup node.
- Create a new field group called “Identification” by right-clicking the Field Groupsnode and choose New Group. The name of the field group is set by using the property sheet. Add the field accountNum to the field group.
- Create a new field group called “Overview” and add all 4 fields to the field group.
- Finally, create a new field group called “Details” and add the fields custName, custGroupId and currencyCode.
- Save the table.
Output:
You might wonder why so many field groups are needed for a few fields: AutoReport & AutoLookup are reserved field groups The other 3 field groups are used for grouping the fields on forms and reports.
AutoReport: fields in this are printed when selecting print icon from a form.
AutoLookup: fields in this are showed when pressing the lookup button.
Identification: should contain all fields which make up the Key fields of the table for unique identification.
Overview: fields for summarization of the table
Apart from Overview, all fields should be part of atleast one field group for logical grouping of all fields. For MyFirstTable, Identification & Details are the logical grouping of fields.
Indexes:An index is a table-specific database structure that speeds the retrieval of rows from the table. Indexes are used to improve the performance of data retrieval and sometimes to ensure the existence of unique records
· Any table must have atleast one index.
· Keeping in mind the performance issues because of improper use of indexes, the best practice is, Main tables and group tables like the customer table and customer group table should have unique indexes, Whereas, tables with a lot of records like transaction tables should not have a unique index.
· Each time data is changed in the table, database will update the indexes for that table.
· Tables with a unique index should have the properties PrimaryIndex and ClusterIndex set.
Creation of Indexes:Lets add indexes to MyFirstTable. We will create two indexes.
- Locate the node Indexes in MyFirstTable. Right-click and choose New Index to create an index. Use the property sheet to name the index “AccountIdx”.
Drag the field accounNum to the index.
- Go to the property sheet for AccountIdx and set the property AllowDuplicates to “No”.
- Create a new index named GroupIdx.
Add the fields custGroupId and accountNum in this order.
- Save the table.
Relations:Relations will connect your tables and let MorphX know how the data model looks like. To define the relationship between two tables that contain related data
· One good example to understand relations is, If you click the Transactions button in the customers form you will open the customer transaction form. . Try selecting another customer in the customer from. Notice that the customer transaction form will automatically be refreshed with the customer transactions for the selected customer.
· Table relations are most commonly used in form fields to enable the look-up of information in another table. So that Lookup button can be used.
· An Extended data type can also have a relation to a table. However, if a table has relations on the same field, the table relation will overrule an extended data type relation.
Example (2): Adding relations
Let’s create a relation in CustTable relating customer account to an account in MyFirstTable.
1. Find the table CustTable and go to the Fields node. Add a new field using the extended data type AccountNum.
Rename the field to ‘altCustAccountNum’.
Also update the label to ‘altCustAccountNum’
2. Go to the field group Delivery in CustTable and add the field altCustAccountNum.
3. Locate the node Relations in CustTable. Right-click the node Relations and choose New Relation.
Rename the new relation to ‘MyFirstTable’ using the property sheet.
Go to the property sheet and add ‘MyFirstTable’ for property Table.
4. Right-click the new relation MyFirstTable and choose Normal. Go to the property sheet and select altCustAccountNum as Field and an accountNum as RelatedField.
5. Save the table.
Output:
Here, we create a new field ‘altCustAccountNum’ and related to MyFirstTable. As the new fields is part of Group Delivery, if open CustTable > Setup tabpage > Delivery group, you will find a new field. Notice that the field also has a lookup button and shows the data from MyFirstTable because we related to it and can only select data from the same (throws error if we give someother data).
All of the fields part of a primary index must be added as normal relation fields. In our example, We only have one field in the primary index, only one field is needed.
Besides a normal relation, two other relation types exist, field fixed and related field fixed. These are used to narrow the choices of a relation and are often used for filtering data depending on the value of an enum, or even defining which table to relate. Good example for this are relations for LedgerTable, CustTable and VendTable in the table LedgerJournalTrans
For more details about Types of relations - Go here. DeleteActions:The DeleteAction element is used to maintain database consistency when a record is deleted. And the delete action values are None, Cascade, Restricted, and Cascade + Restricted.
· Delete actions use relations to figure out whether to delete or prevent deleting related data. So if there is no relation, delete action will have no effect.
· None: If you delete a record from a table, it will get deleted without any verifications.
· Cascade: If you have a parent child relation. We can have deleteActions to ensure that data record from child table is deleted whenever the related parent data record is deleted.
· Restricted: By using this relation we can restrict the deletion of existing records. Typically, transactions for a customer will prevent the customer to be deleted, whereas information only relevant for the customer like personal data will be deleted when the customer is deleted.
Samething applies when deleting records using X++, some rules must be followed to have the delete actions validated.
· Cascade + Restricted: This delete action mode will act as restricted if used from the table browser or from a form. Deleting a record using X++, this mode will perform a cascade delete from the related table without calling validateDelete()
Example:
We will add delete actions will be added to both CustTable and MyFirstTable to ensure data consistency.
- Go to the node DeleteActions in MyFirstTable. Right-click and chooseNewDeleteAction. Use the property sheet for the new delete action and set to the CustTable.
The Delete action mode should also be set to Restricted.
- Now locate CustTable and add a new delete action to MyFirstTable. Open the property sheet for the new delete action and choose MyFirstTable. Set the delete action mode to Cascade.
- Save the table
