Tuesday, November 1, 2011

Creating a table in AX

Right-click on Tables under Data Dictionary node and select New / Table.
20110805_P01_01_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.
20110805_P01_02_Open_Table
You should get the error message Cannot select a record in Table1 (Table1). The SQL database has issued an error.
20110805_P01_03_Open_Table_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.
20110805_P01_04_Table1_Does_Not_Exist
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.
20110805_P01_05_Save_Table1
Now, if we go back to SSMS you can see that the object Table1 has now been created in the AX database.
20110805_P01_06_Table1_Now_Exists
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.
20110805_P01_07_New_Table_Defintion
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!
20110805_P01_08_Table1_Using_Table_Browser
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.
20110805_P01_09_Table1_Rename
Rename the table to Airline.
20110805_P01_10_New_Table_Airline
Once the table is renamed and saved, the table in the SQL Server database will be synchronized.
20110805_P01_11_New_Table_Airline_In_Database

No comments:

Post a Comment