Tuesday, November 1, 2011

Creating a table in AX

Right-click on Tables under Data Dictionary node and select New / Table.
The new table object will be created with the name Table1. Right-click on the newly created table object Table1 and select the option Open or you can press Ctrl + O.
You should get the error message Cannot select a record in Table1 (Table1). The SQL database has issued an error.
The reason for the error is because the object has been created in AX but has not been created in the SQL Server database. Let’s use the SQL Server Management Studio (SSMS) to look into the list of tables in the AX database. I have shown here only the tables between alphabets S and T. You can notice that there is not object named Table1.
Let’s save the table in the AOT. To save the the object in AX, right-click on the object Table1 and select the option Save or press Ctrl + S.
Now, if we go back to SSMS you can see that the object Table1 has now been created in the AX database.
If we expand the newly created table Table1 in the SSMS, we can see the following nodes namely Columns, Keys, Constraints, Triggers, Indexes and Statistics.
  • AX creates new tables with three fields namely DataAreaId, RecVersion and RecId.
  • DataAreaId column will store the company code to which a record belongs to.
  • RecVersion column contains the version of the record in the table.
  •  RecId column contains the unique identification number of the record.
  • The constraint that begins with CK is the check constraint to make sure that the RecId doesn’t contain the value zero.
  • First default constraint is created to store the value dat in the field DataAreaId, which is then actually updated with the appropriate company account id value in which the record is created.
  • Second default constraint is created to store the value 1 in the field RecVersion. The value 1 is always stored in the field RecVersion whenever a new record is created in the tables. If the record in the table is updated, the value in the field RecVersion is replaced with a version number. We will get to learn about that as we proceed with the Flight Stats application development.
  • In addition to the constraints, a unique non-clustered index containing the fields DataAreaId and RecId is created to keep the values in these fields unique.
Let’s go back to the AX application and open the newly created table Table1 again. You should see the Table browser as shown below with the three automatically created columns that we just discussed earlier. Of course, the table is empty!
We need to rename this table because it should be called Airline and not Table1. To rename the table, right-click on it and select Rename option.
Rename the table to Airline.
Once the table is renamed and saved, the table in the SQL Server database will be synchronized.

No comments:

Post a Comment