WALKTHROUGH - Creating Fact Boxes in AX 2012 - PART II

Hi,

In my last post (http://axwonders.blogspot.com/2011/12/walkthrough-creating-fact-boxes-in-ax.html) I mentioned a few things about the new AX 2012 fact boxes, and that I wanted to demonstrate this through a simple Auto Rental project.

So, let’s get to work right away. Let’s create the 3 EDTs and 4 tables. Just note that the naming convention for the entire project’s objects will be as follows:

Eduardo_ObjectName (It is going to look weird, but just change it to whatever you want)

Create the Extended Data Types (EDT):

Steps:

1-      Open a new development workspace.

2-      In the AOT, expand the Data Dictionary node.

3-      Right-click the Extended Data Types node.

4-      Select New > String. A new EDT called Type1 is created.

5-      Right-click Type1 and select Properties.

6-      Modify the property Name to Eduardo_VehicleMakeID.

7-      Modify the property Extends to SysGroup.

8-      Modify the property Label to Vehicle Make ID.

9-      Click Save in the AOT to save your changes.

10-   On the Database Synchronization dialog, click Yes. This will take a few minutes to complete.

Next, create the Eduardo_VehicleTypeID and Eduardo_VehicleModelID EDTs by repeating steps 3 to 10. Just remember to change the Label to Vehicle Type ID and Model Type ID.

You AOT should look something like this:

The next step will be to create the tables, fields, indexes and relationships. Let’s create the Eduardo_VehicleTypeTable.

Based on our simple DB diagram, the Eduardo_VehicleTypeTable should have two fields; (1) Vehicle Type Id, and (2) Name.

Steps:

1.       In the AOT, expand the Data Dictionary node.

2.       Right-click the Tables node and select New Table. A new Table called Table1 is created.

3.       Right-click Table1 and select Properties.

4.       Modify the property Name to Eduardo_VehicleTypeTable.

5.       Modify the property Label to Vehicle Types.

6.       Press Ctrl-D to open another AOT.

7.       Expand the Data Dictionary node.

8.       Expand the Extended Data Types node.

9.       Locate the Eduardo_VehicleTypeID EDT.

10.   Drag the Eduardo_VehicleTypeID EDT to the Fields node of the Eduardo_VehicleTypeTable table. A new field called Eduardo_VehicleTypeID is created.

11.   Right-click the Eduardo_VehicleTypeID field and click Properties.

12.   Note that the Extended Data type for this field is set to Eduardo_VehicleTypeID.

13.   In the Name property, set the name of the field to VehicleTypeID.

14.   Locate the Name EDT in the second AOT.

15.   Drag the Name EDT to the Fields node of the Eduardo_VehicleTypeTable. A new field called Name is created.

16.   Save your changes to the table. When changes to a table are saved, Microsoft Dynamics AX automatically synchronizes the changes made within its Data Dictionary with SQL: this ensures that the definitions for the tables remain consistent at all times.

Now, follow steps 2-5 for the Eduardo_VehicleMakeTable.

Before we continue with the Eduardo_VehicleModelTable, I would like to talk about creating relationships directly on an EDT instead of creating the relationship manually on a table. 

First, by creating a relationship on an EDT instead of a table, you can set a relationship with a table that can be reuse many times without having to create the same relationship over and over. Second, we can simplify the creating a new field, a new index, and a relationship steps into one simple drag-and-drop action.

NOTE: In Microsoft Dynamics AX 2012, relations can no longer be created under extended data type (EDT) nodes in the Application Object Tree (AOT). Relations that are defined under EDT nodes are still effective, but in a future release they will be obsolete and deleted.

The EDT relation migration tool helps you move relations from EDT nodes to table nodes. The EDT relation migration tool is found on the client menu, under Tools > Code upgrade > EDT relation migration tool.  (source: http://msdn.microsoft.com/en-us/library/gg989788.aspx)

The next step will be to create a relationship directly into both the Eduardo_VehicleMakeID and Eduardo_VehicleTypeID. Then, we will drag these two EDTs to a new table we’ll create shortly.

Steps:

1.       In the AOT, locate the Eduardo_VehicleTypeID EDT.

2.       Right-click the Eduardo_VehicleTypeID EDT and select Properties.

3.       In the ReferenceTable properties enter Eduardo_VehicleTypeTable.

4.       Close the properties sheet.

5.       Expand the Eduardo_VehicleTypeID node.

6.       Right-click the Table References node and select New > Table Reference.

7.       Right-click the Eduardo_VehicleTypeID Table Reference and select properties.

8.       In the related field property, enter VehicleTypeID.

9.       Save your changes to the EDT.

10.   If the database synchronization dialog appears, click Yes.

Now do steps 1 to 10 to create a relationship on the Eduardo_VehicleMakeID EDT.

This should look like this:

The next step is to create the Eduardo_VehicleModelsTable. This table will have 5 fields, which three will be the EDT we created earlier.

Steps:

1.       In the AOT, expand the Data Dictionary node.

2.       Right-click the Tables node and select New Table. A new Table called Table1 is created.

3.       Right-click Table1 and select Properties.

4.       Modify the property Name to Eduardo_VehicleModelsTable.

5.       Modify the property Label to Vehicle Models.

6.       Press Ctrl-D to open another AOT.

7.       Expand the Data Dictionary node.

8.       Expand the Extended Data Types node.

9.       Locate the Eduardo_VehicleModelID EDT.

10.   Drag the Eduardo_VehicleModelID EDT to the field node on the Eduardo_VehicleModelsTable.

11.   Change the name to VehicleModelID.

12.   Locate the Eduardo_VehicleTypeID EDT.

13.   Drag the VehicleTypeID EDT to the field node on the Eduardo_VehicleModelsTable.

14.   When prompted to add the Foreign Key relation from the EDT, click Yes. This will automatically create the relation between the Eduardo_VehicleTypeTable table and the Eduardo_VehicleModelsTable.

15.   Locate the Eduardo_VehicleMakeID EDT.

16.   Drag the Eduardo_VehicleMakeID EDT to the field node on the Eduardo_VehicleModelsTable.

17.   When prompted to add the Foreign Key relation from the EDT, click Yes. This will automatically create the relation between the Eduardo_VehicleMakeTable table and the Eduardo_VehicleModelsTable.

18.   Locate the NoYesID EDT in the second AOT.

19.   Drag the NoYesID EDT to the Fields node of the Eduardo_VehicleModelsTable. A new field called NoYesID is created.

20.   Change the name to Status.

21.   Locate the Description EDT in the second AOT.

22.   Drag the Description EDT to the Fields node of the Eduardo_VehicleModelsTable. A new field called Description is created.

NOTE: In addition to the relationships, two new indexes and two new fields were created as well. You may change the name of these fields to make them meaningful to you.

The Eduardo_VehicleModelsTable should look something like this:

Now that the Eduardo_VehicleModelsTable has been created along with the fields, indexes and relationships, the next step is to create the relationship on the Eduardo_VehicleModelID EDT.

Ah! You might ask yourself, why didn’t we create the relationship for this EDT earlier? The reason we didn’t do this earlier was because an extra relationship would have been created if the Eduardo_VehicleModelID   EDT had a relationship before creating the Eduardo_VehicleModelsTable.VehicleModelID.

So, let’s create the relationship on the Eduardo_VehicleModelID

Steps:

1.       In the AOT, locate the Eduardo_VehicleModelID EDT.

2.       Right-click the Eduardo_VehicleModelID EDT and select Properties.

3.       In the ReferenceTable properties enter Eduardo_VehicleModelsTable.

4.       Close the properties sheet.

5.       Expand the Eduardo_VehicleModelID node.

6.       Right-click the Table References node and select New > Table Reference.

7.       Right-click the Eduardo_VehicleModelID Table Reference and select properties.

8.       In the related field property, enter VehicleModelID.

9.       Save your changes to the EDT.

10.   If the database synchronization dialog appears, click Yes.

So far so good, I hope you are still with me. Now we need to create a table that will hold customer and car information data. Then this table will be our main data source on a custom form we will create later in this post.

Steps:

1.       In the AOT, expand the Data Dictionary node.

2.       Right-click the Tables node and select New Table. A new Table called Table1 is created.

3.       Right-click Table1 and select Properties.

4.       Modify the property Name to Eduardo_CustCarTable.

5.       Modify the property Label to Customer Cars.

6.       Drag the Integer EDT to the field node on the Eduardo_CustCarTable.

7.       Change the name to RentalID.

8.       Press Ctrl-D to open another AOT.

9.       Expand the Data Dictionary node.

10.   Expand the Extended Data Types node.

11.   Locate the Eduardo_VehicleModelID EDT.

12.   Drag the Eduardo_VehicleModelID EDT to the field node on the Eduardo_CustCarTable.

13.   When prompted to add the Foreign Key relation from the EDT, click Yes. This will automatically create the relation between the Eduardo_VehicleMakeTable table and the Eduardo_VehicleModelsTable.

14.   Change the name to VehicleModelID.

15.   Locate the CustAccount EDT.

16.   Drag the CustAccount EDT to the field node on the Eduardo_CustCarTable table.

17.   Drag the TransDate EDT to the field node on the Eduardo_CustCarTable table.

18.   Change the name to RentalStartDate.

19.   Drag the TransDate EDT to the field node on the Eduardo_CustCarTable table.

20.   Change the name to RentalEndDate.

The Eduardo_CustCarTable should look like this:

At this point you should fill both the Eduardo_VehicleTypeTable and the Eduardo_VehicleMakeTable [tables] with some data. Once you do that, open the Eduardo_VehicleModelsTable and add some data about the vehicles. You should be able to select a Vehicle Make from the VehicleMakeID field and a Vehicle Type from the VehicleTypeID field.


Vehicle Make

Vehicle Type

NOTE: If for some reason one of your relationships is not working (you'll know because you won't be able to select either a vehicle make or vehicle type from the fields), you will have to delete the relationship that is not working and create it manually. Also make sure that your indexes names are correct.

In my next post we will create a primary index for a table and dive deeper into Form Parts.

Take Care!