Creating the vehicle table

The vehicle table also follows a standard pattern; it is a main table, similar to the Customer or Product tables.

The structure of the table we will create is as follows:

Table

Label

Table group

 

ConFMSVehicleTable

Vehicle table

Main

 

Field name

Type

Length

EDT

VehicleId

String

20

ConFMSVehicleId

Name

String

60

Name

VehicleGroupId

String

10

ConFMSVehicleGroupId

VehicleType

Base enum

 

ConFMSVehicleType

Status

Base enum

 

ConFMSVehicleStatus

ServiceSchedule

String

10

ConFMSServiceSchedId

Creating the enumerated types for the vehicle table

Before we create the table, we need to create two base enums. The first base enum is ConFMSVehicleType, and should have the following elements:

Name

Label

EnumValue

Base

Base

0

Car

Car

1

Truck

Truck

2

Trailer

Trailer

3

The result should appear similar to what is shown in the following screenshot:

Creating the enumerated types for the vehicle table

We now need the Status enum—ConFMSVehicleStatus. Create this from the following table:

Name

Label

EnumValue

Created

Created

0

Acquired

Acquired

1

Active

Active

2

Unavailable

Unavailable

3

Disposed

Disposed

4

Once complete, this should look similar to what is shown in the following screenshot:

Creating the enumerated types for the vehicle table

Finally, we create EDTs as follows:

  1. Right-click on the Extended Data Types node. Navigate to New | Enum.
  2. Rename the EDT to ConFMSVehicleTypeId.
  3. Enter ConFMSVehicleType in the EnumType property.
  4. Create a second EDT for the ConFMSVehicleStatus enum by following the same procedure.

There is no need to set the label as this has been set on the base enum. Often, when creating generic enumerated types, we won't add a label to the base enum; we will only add it to the EDT.

Creating the table that supports inheritance

First, we need to create the vehicle ID EDT, which we will do as before. This EDT will be controlled by a number sequence, so it should extend the Num EDT.

Create the ConFMSVehicleId EDT as shown here:

Name

ConFMSVehicleId

Label

Vehicle Id

Extends

Num

Entering the Num EDT will force StringSize to 20 and be left aligned; this is critical if we are to create IDs using a number sequence. Also, create an EDT based on the Int64 type, called ConFMSVehicleRelationType, with the Vehicle type label. Reuse the same label that you created before.

We now have all the EDTs we need to create our table. Typically, the table structure of a Main table is the same as Group (Setup) table types. We just make the TableGroup property Main.

In this case, we are also going to enable this table to support inheritance:

  1. Create the ConFMSVehicleTable table as before (it is a convention to suffix Main tables with Table)
  2. Set the Label property to Vehicle details.
  3. Set the HelpText property to something appropriate.
  4. Set the TableGroup property to Main.
  5. Set the tracking fields CreatedBy, ModifiedBy, CreatedDateTime, and ModifiedDateTime.
  6. Set the SupportInheritance property to Yes.
  7. We will then get the following compilation error:
  8. Property InstanceRelationType must be set to an Int64 field for the root table.
  9. We need to create a reference field that the system will use to tie the inherited tables together. Right-click on the table's Fields node and go to New | Int64.
  10. Rename the field to InstanceRelationType. Set ExtendedDataType to ConFMSVehicleRelationType.
  11. Save the table.
  12. Set the table's InstanceRelationType property to InstanceRelationType.
  13. Compile the project by selecting the root node in the project window and clicking on the compile icon (or pressing F7), as shown here:
    Creating the table that supports inheritance

    Note

    See more on the InstanceRelationType property later in this section.

  14. Now we can add the main fields to our table. Do this by dragging the following EDTs (in the given order) onto the table's Fields node:
    • ConFMSVehicleId
    • Name
    • ConFMSVehicleGroupId

    Note

    The system will ask you Do you want to add the ForeignKey relation from the EDT to the current table?. If you want to create a relation, click on Yes.

    If this message does not appear, remove the field and verify the EDT's relation.

    • ConFMSVehicleTypeId
    • ConFMSVehicleStatusId
    • ConFMSServiceSchedId

    Note

    As before, remove the ConFMS prefix; this is our table. To maintain consistency, rename the ConFMSServiceSchedId field to VehicleScheduleId.

  15. Ensure that the following table properties are completed:
    • TitleField1: VehicleId
    • TitleField2: Name

Completing the table relations

Before we continue, we can now play with this table and you can learn a bit more about relations in AX.

To ensure that everything is synchronized, it is a good idea to force AX to synchronize its data dictionary with the physical database held in SQL Server. To do this, open the AOT (Ctrl + D), right-click on the Data Dictionary node, and select Synchronize. This may take 10-20 minutes, and may ask you to continue if there are any warnings. If it does, choose to continue.

Note

If this node is red, it means that AX has noticed that it is not in sync and needs to be synchronized.

Once this completes, go back to your project window and browse the ConFMSVehicleTable table (right-click on it and choose Open).

Press Ctrl + N to create a new record. Click through the fields, trying the drop-down options. The fields based on enums will show the appropriate elements and the system will automatically create dropdowns for the vehicle group and service tables—very powerful! This will get even better once we add the user interface.

The vehicle group and vehicle service fields are foreign keys to the respective tables, as defined in the relations on ConFMSVehicleTable. The following screenshot shows the definition of the ConFMSVehicleGroup relation:

Completing the table relations

The two relations are primary key relations, which has a special meaning with AX because it enables additional features over standard relations. One feature is that it can automatically create navigation methods. For this reason, we will always use primary key relations wherever possible.

The following screenshot shows the property sheet for the ConFMSVehicleGroup relation:

Completing the table relations

Occasionally, we may have multiple relations to the same table (perhaps filtered to a subset of records in the foreign table), so we should change the name to specify this role. Most relations are one-to-many and we will configure the cardinality like this:

Completing the table relations

Tip

There is a quick way to achieve this configuration. Once you are in the RelatedTableCardinality property, press in the following order: spacebar, Tab, spacebar, spacebar, spacebar, Tab, and spacebar. We can complete the property sheet in this way in a couple of seconds.

The default role names are normally fine. A problem occurs if we add a second relation to the same table—we will get an error stating that role names are not unique. This is because the default role names are based on the table name, not the relation name.

You can easily overcome this by changing UseDefaultRoleNames to No and setting the RelatedRoleName and RoleName fields manually. These are normally set as follows: RelationRoleName as the foreign table name and RoleName as the foreign table name suffixed with the relation's role. Two examples of this are VehicleGroup and VehicleGroup_VehicleTable. These values are validated beyond being unique across this table.

Note

All relations must have the correct cardinality set.

Completing the vehicle table

We still have a few tasks on the vehicle table to complete. These are as follows:

  • Creating a primary index
  • Completing the relation on the ConFMSVehicleId EDT
  • Creating appropriate field groups
  • Creating the find and exist methods

We have done this before and the process is the same as for other tables. Use the following checklist to complete the setup:

  1. The VehicleId will be the primary key, so create an index called VehicleIdIdx. Then add VehicleId to it. Ensure that AllowDuplicates is No and AlternateKey is Yes.
  2. Set the PrimaryIndex and ClusteredIndex properties to the new index.
  3. On the ConFMSVehicleId EDT, enter ConFMVehicleTable in the ReferenceTable property.
  4. Add the TableReference for the field VehicleId.
  5. Create up to three field groups, including Overview and Details, as you see fit. Add the fields as appropriate. Do not add the InstanceRelationType field; this will be added to the form later.

    Tip

    The labels for Overview and Details should be standard labels, as these are standard field groups. Overview is the first with no description, and the Details label states the Group details in the description.

  6. Create the find and exist methods, as follows:
    public static ConFMSVehicleTable find(ConFMSVehicleId _vehicleId, boolean _forUpdate = false)
{
    ConFMSVehicleTable vehicle;

    vehicle.selectForUpdate(_forUpdate);
    if(_vehicleId != "")
    {
        select vehicle 
            where vehicle.VehicleId == _vehicleId;
    }
    return vehicle;
}
public static boolean exist(ConFMSVehicleId _vehicleId)
{
    ConFMSVehicleTable vehicle;
    if(_vehicleId != "")
    {
        select RecId 
            from vehicle
            where vehicle.VehicleId == _vehicleId;
    }
    return (vehicle.RecId != 0);
}

Creating the inherited tables

Inherited tables are relatively quick to create. We just need to create the basic table definition as a main table and add the additional fields each type requires.

We will create tables for the following types:

  • Car
  • Truck
  • Trailer

The car will require these fields and therefore the EDTs:

  • CarType: The type of car, for example, Saloon, Sedan, Estate, and so on
  • TintedWindows: To define whether or not the car has tinted windows

The truck will require these fields:

  • Artic: To define whether or not it is an articulated truck

The trailer will require these fields:

  • TrailerId: This is the trailer's ID number

We therefore need to create the EDTs first. It is sensible to create one for each of these fields and extend an EDT as appropriate.

Use this table to create the EDTs, setting the Label and HelpText properties as appropriate:

Name

Type

Extends

Size

ConFMSCarType

String

 

10

ConFMSTintedWindows

Enum

NoYesId

n/a

ConFMSArtic

Enum

NoYesId

n/a

ConFMSTrailerId

String

Num

n/a

Once the EDTs are created, we can create the tables, use the following table to create the tables:

Name

Label

ConFMSVehicleTableCar

Car details

ConFMSVehicleTableTruck

Truck details

ConFMSVehicleTableTrailer

Trailer details

In all cases, set the following properties:

TableGroup

Main

SupportsInheritance

Yes

Extends

ConFMSVehicleTable

There is no need to define find or exist methods on this table or create a primary index. Finally, note that the system automatically creates a relation to the base table based on RecId. This is part of the mechanics behind the inheritance functionality.

More on the InstanceRelationType property

The implementation of table inheritance is unique to AX and it is worth pointing out what we have done and why.

We are about to actually create an AX table for each type, all extending this base table. This will create a physical table in SQL Server to support them. SQL Server does not support table inheritance, so this technology is handled purely by AX.

As a user interface option, when we create a new record, the system will want to know which type it is (in our case, car, truck, and so on). It does this by offering the user a choice based on the tables that inherit from this table. AX will automatically create a dialog with one field labelled after the InstanceRelationType field (which is why it is labeled Vehicle type) and the options will be the labels of all tables, from the base table to all child tables.

Data will be physically stored in only one SQL table. AX knows what type each record is by looking at InstanceRelationType. This field is actually a table ID and it stores the AX table ID of the child table that the record belongs to. The child table does not exist as a table in its own right in SQL. This is shown in the following diagram of ConFMSVehicleTable:

InstanceRelationType

VehicleId

Car.CarType

Truck.Artic

Trailer.TrailerId

103649

V001

n/a

n/a

n/a

103650

V002

Saloon

n/a

n/a

103650

V003

Estate

n/a

n/a

103651

V004

n/a

No

n/a

103652

V005

n/a

n/a

T001

To see this for yourself, using the table browser, right-click on the table and select Open. You can use Ctrl + N to create records in any of the tables. You will see that the inherited tables show all fields from the parent and their own specific fields. Also, the records created in the inherited tables appear in both the base table and the specific table they were created in.

Note

From AX 2012 R2 onwards, the way table inheritance was achieved in SQL server changed.

Before R2, a physical table was created for the parent table and its fields. Then another was created for each child table and those fields that it had and the parent did not. The InstanceRelationType field was used to determine which child table to look at in order to find the extra fields.

Selecting data from SQL with this structure involved a complex set of SQL joins and, worse than that, updating one child record in AX would require updates to several tables in SQL.

Now all the fields required by the parent table and all its child tables are kept in a single SQL table. This makes reading and updating much less complex, as there is just one table on the disk to read and write to.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.237.194