Creating main data tables

In this section, we will create a main table, similar to the customer table. The steps are similar to the vehicle group, and we will abbreviate some of the steps we have already done. The pattern described in this recipe can be applied to any main table using your own data types.

The table in this example will be to store vehicle details. The table design will be as follows:

Field	Type	Size	EDT (: indicates extends)
VehicleId	String	20	*ConVMSVehicleId : Num
VehicleGroupId	String	10	ConVMSVehicleGroupId
RegNum	String	10	* ConVMSRegNum
AcquiredDate	Date		*ConVMSAcquiredDate : TransDate

Note that (*) means we will create the marked EDTs later in this section.

Getting ready

In order to follow these steps, the elements created earlier in this chapter must have been created.

If you haven't created the ConVMSVehicleId EDT, follow the Creating extended data types recipe before starting this recipe.

How to do it...

We will first create the required new EDTs, which is done by taking the following steps:

1. Create the ConVMSVehRegNum string EDT with the following properties:

Property	Value
Name	ConVMSVehRegNum
Size	10
Label	Registration—add a comment that this is a vehicle registration number
Help Text	The vehicle registration number

2. We now need the date acquired EDT, so create a date EDT named ConVMSAcquiredDate with the following properties:

Property	Value
Name	ConVMSAcquiredDate
Extends	TransDate
Label	Date acquired
Help Text	The date that the vehicle was acquired

Although we created this EDT as a date, this is mainly for the way it appears. It is created in the database as a date time, and compiles to a Common Language Runtime (CLR) date time type.

When creating labels, create the help text label with the same name as the main label, but suffixed with HT. You can use copy on the main label (putting, for example, @ConVMS:DateAcquired in the paste buffer) and paste it in the Label property as usual, but we can simply paste our label into the Help property and add HT on the end by clicking on the value, pressing Ctrl + V, End, and typing HT. Note that label IDs are case-sensitive!

3. Create a new table and name it ConVMSVehicleTable. The convention for main and worksheet header tables is that they starts with a prefix, followed by the entity name as a singular noun, and suffixed with Table. Remember tables are types and can't have the same name as other types, such as classes and data types.
4. Drag the following EDTs on to the Fields node in this order:

• ConVMSVehicleId
• Name
• ConVMSVehicleGroupId
• ConVMSVehicleType
• ConVMSVehRegNum
• ConVMSAcquiredDate

The reason for the order is specifically for the ID, description, and group fields. These are usually placed as the first three fields, and the ID field is usually first.

5. Remove the ConVMS prefix from the fields as they are on a table that is in our package. An efficient way is to use the following technique:
   
   1. Click on the field.
   2. Press F2.
   3. Left-click just after ConVMS.
   4. Press Shift + Home.
   5. Press Backspace.
   6. Click on the next field, and repeat from the F2 step (step 2) for each field.

6. On the VehRegNum field, change the AliasFor property to VehicleId.

The AliasFor property allows the user to enter a registration number in the VehicleId field in foreign tables, causing SCM to look up a vehicle and replace the entry with VehicleId. This concept is common on most main tables.

7. Make the VehicleGroupId field mandatory.
8. Save the table, and open the ConVMSVehicleId EDT. Complete the Reference Table property as ConVMSVehicleTable, right-click on the Table References node, select New Table Reference, and complete the Related Fields property as VehicleId from the drop-down list. If the drop-down list does not show the field, we have either not entered the correct table in the Reference Table property or we forgot to save the table.
9. Close the designer table for the EDT and navigate back to the table designer.
10. Change the VehicleId field properties as an ID field like so:

Property	Value
AllowEdit	No
AllowEditOnCreate	Yes
Mandatory	Yes

The preceding properties only affect the way the field behaves on a form.

11. A main table GroupId field usually has an impact on logic, and is usually mandatory. Even if it does not, we should still make the VehicleGroupId field mandatory.

Careful consideration must be taken when deciding on whether the field is mandatory or when it can be edited. In some cases, the decision on whether it can be changed is based on data in other fields or tables. This can be accomplished in the validateField event methods.

12. Do not make the VehicleType field mandatory.

Enums start at zero and increment by one each time. SCM validates this using the integer value, which would make the first option invalid. Since enums always default to the first option, the only way to force a selection from the list would be to make the first element, called NotSet, for example, with a blank label. Note that extensible enums cannot be used this way as we can't be certain what the numeric value of the first element is.

13. Create a unique index called VehicleIdx with the VehicleId field.
14. Group fields are often used for aggregation or search queries; create an index called VehicleGroupIdx and add the VehicleGroupId field to it. The index must not be unique, which is the default setting for this property.
15. Complete the table's properties as follows:

Property	Value
Label	The vehicles label ID should be VehicleTable
Title Field 1	VehicleId
Title Field 2	Name
Cache lookup	Found
Clustered Index	VehicleIdx
Primary Index	VehicleIdx
Table Group	Main
Created By Created Date Time Modified By Modified Date Time	Yes
Developer documentation	ConVMSVehicleTable contains vehicle records. If there is anything special about this table, it should be added here.
Form Ref	Leave this blank until we have created the form.

16. Create a field group named Overview, labeled appropriately (for example, @SYS9039), and drag in the fields you wish to show on the main list grid on the form: for example, VehicleId, Name, VehicleGroup, and VehicleType. This is to give the user enough information to select a vehicle before choosing to view the details of it; if we add too many fields, it becomes confusing as there is too much information to easily digest.
17. Create a field group, Details, and find an appropriate label. Drag in the fields that should show on the header of the form when viewing the details of the vehicle. This should repeat the information from the overview group, as these field groups are not visible to the user at the same point; Overview is for the list of records, and Details is placed at the top of the details form, where the user would want to review the full details of a vehicle.
18. Main tables are usually referenced in worksheet tables, and SCM will create a lookup for us based on the relation on the foreign table. To control the fields in the automatic lookup, drag the fields you wish to see into the AutoLookup field group, and ensure that VehicleId is first.
19. Create a foreign key relation for the VehicleGroupId field using the following properties:

Parameter	Value
Name	ConVMSVehicleGroup
Related Table	ConVMSVehicleGroup
Cardinality	OneMore: The field is mandatory
Related Table Cardinality	ZeroOne
Relationship Type	Association
On Delete	Restricted

20. Add a normal field relation to the relation, connecting the VehicleGroupId fields.
21. It is common to initialize main tables from defaults, held in parameters. The initValue method is called when the user creates a new record. Right-click on the Methods node and select Override | initValue.
22. In the code editor, adjust the code so that it reads as follows:

public void initValue()
{
    super();
    ConVMSParameters parm = ConVMSParameters::Find();
    this.VehicleGroupId = parm.DefaultVehicleGroupId;
}

There is another method, using the defaultField method, which is shown in the There's more... section.

23. Next, add the Find and Exist methods using the table's primary key field as usual.
24. Finally, we will add a field validation method to ensure that the acquisition date is not before today. Override the validateField method and add the following code between the ret = super(); line and return ret;:

switch (_fieldToCheck)
{
    case fieldNum(ConVMSVehicleTable, AcquiredDate):
        Timezone clientTimeZone = 
         DateTimeUtil::getClientMachineTimeZone();
        TransDate today = 
         DateTimeUtil::getSystemDate(clientTimeZone);
        if(this.AcquiredDate < today)
        {
            // The acquisition date must be today or later
            ret = checkFailed("@ConVMS:AcqDateMustBeTodayOrLater");
        }
    break;
}

25. Create a label for the error message returned by checkFailed and replace the literal with the label ID.
26. Once complete, save and close the table code editor and designer tab pages.
27. Should we try to build, we may get the following error:

A reference to 'Dynamics.AX.Directory, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' is required to compile this module.

28. The error might also read similar to The identifier Name does not represent a known type. This means that our package does not reference the Directory package. Use Dynamics 365 | Model Management | Update model parameters. Select our package, and then add the missing package on the next page. Then choose Refresh models from Dynamics 365 | Model Management.

How it works...

We have introduced a couple of new concepts and statements in this recipe.

The switch statement should always be used on the validateField method, even if we only ever intend to handle one case. An if statement might seem easier, but it will make the code less maintainable. This goes for any check like this, where the cases have the possibility to increase.

The next new concept is that we can now declare variables as we need them. This helps with scope, but shouldn't be overused. The initValue and validateField methods are good examples of explaining where the code should be declared.

The AX 2012 systemGetDate() function is deprecated in this release. DateTimeUtil provides better handling for time zones. The date can be different across time zones, and can differ between the client's machine (the browser) and the server where SCM is hosted. With SCM the user is completely unaware of where the server is, and could be working anywhere in the world.

In the validateField method, we will allow the standard code to run first; the standard call will validate the following:

• That the value is valid for the type, such as a valid date in a date field.
• If the field is a foreign key, check the value exists in the parent table.
• If the field is mandatory, check that it is filled in or that it is not zero for numeric and enum fields.

There's more...

Every element (table, table field, class, form, and so on) has an ID. Tables and fields are commonly referenced by their ID and not by their name. The validateField method, for example, uses the field ID as the parameter and not the field name. As we can't know the ID, SCM provides intrinsic functions, such as tableNum and fieldNum to assist us. The peculiar nature of these functions is that they do not accept a string; they want the type name.

Other intrinsic functions, such as tableStr, fieldStr, and classStr, simply return the type as a string. The reason is that these functions will cause a compilation error should the type be typed incorrectly. If we don't use them, not only do we fail a best practice check, but we make any future refactoring unnecessarily difficult.

Using the defaultField and initValue methods for setting field defaults

When the user presses New on a form, the form's data source will create a new empty record buffer for the user to populate prior to saving. A number of events are fired when this occurs and eventually results in a call to initValue on the table. This is traditionally where all defaulting logic is placed, and when trying to determine what defaults are set, this is the first place to look.

There seems to be another way in which developers can accomplish this defaulting logic, which is to override the defaultField method. This is called as a result of a call to defaultRow. The defaultRow method is called when a data entity creates a record, and is not called as part of X++ nor the form engine (the events that fire as part of creating a new record on a form's data source). Data entities are used in the office add-in (to enable editing records in Excel, for example) or when importing and exporting data.

We do not use defaultField to initialize fields as part of data entry in a form.

Sample code to default the vehicle group field is as follows:

public void defaultField(FieldId _fieldId)
{
    super(_fieldId);
    switch (_fieldId)
    {
        case fieldNum(ConVMSVehicleTable, VehicleGroupId):
            this.VehicleGroupId = 
             ConVMSParameters::Find().DefaultVehicleGroupId;
            break;
    }
}

We would usually create a method called defaultVehicleGroup to allow code reuse.

To default field values on new records, we would use initValue. The defaultField option is described here as it is sometimes used in standard code, such as PurchReqTable. It is unusual to look for this method, and can, therefore, cause confusion when fields magically get a default value.

For more information on this, refer to the following:

• Validations, default values, and unmapped fields: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/validations-defaults-unmapped-fields

More on indexes

Table indexes are a physical structure that are used to improve read performance, ensure the uniqueness of records, and for the ordering of data in the table. When records are inserted, updated, or deleted, the index is also updated. We must therefore be careful when adding indexes, as they can carry a performance hit when writing data back to the table.

A typical index is an ordered collection of keys and a bookmark reference to the actual data. Finding a record matching a given key involves going to the appropriate location in the index where that key is stored. Then, you will have to follow the pointer to the location of the actual data. This, of course, requires two SCM: an index seek and a lookup to get the actual data.

When we search for a record, SQL Server is able to determine the best index, or indexes, to use for that particular query. If we realize that we often require the same set of fields from a specific query, we can create an index that contains the keys we wish to search on, and the fields we wish to fetch. This improves performance considerably, as SQL will use that index and can then simply return the values that already exist in the index.

We can improve this further by marking the fields we simply wish to return as IncludedColumn (a property of the fields in an SCM index). So, in our case, we may wish to select the description from the vehicle table where the vehicle group is Artic, for example. Therefore, a solution can be to add the Name field to our VehicleGroupIdx index and mark it as IncludedColumn. However, there is a better solution in this case, which is to use clustered indexes.

A clustered index is similar to indexes with included columns, but the clustered index will contain the entire record, avoiding a lookup in the data for any field in the table. Clustered indexes are sorted by their keys; as the index contains the entire record, it can add a significant load to the SQL Server if records are inserted, as opposed to being appended at the end of the table.

For setup tables, where the number of records is small and changes infrequently, this isn't a problem, and the read benefit far outweighs any drawback. For transaction tables, we must be careful. We should always have a clustered index, but the key must be sequential and the records must be added at the end of the table.

An example of this is the sales order table, which has a clustered index based on SalesId. This is a great choice as we will often use this key to locate a sales order record, and the field is also controlled by a number sequence; records should always be appended at the end. However, should we change the number sequence so that records are inserted "mid-table," we will experience a delay in inserting records, and we will be adding unnecessary load to the SQL Server.

See also

The following links provide further reading on the topics covered in this recipe:

• X++ compile-time functions (also referred to as Intrinsic functions): https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/dev-ref/xpp-compile-time-functions
• X++ variables and data types: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/dev-ref/xpp-variables-data-types

The following link focuses on modeling aggregate data for business intelligence applications, but also contains useful information on Non-Clustered Column store Indexes (NCCI), which are in-memory indexes used for analyzing aggregate data:

• Model aggregate data: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/model-aggregate-data