In this chapter, we will cover the tasks required to write the data dictionary elements commonly used within Supply Chain Management (SCM) development.

Most development in SCM is based on patterns, even if we are not aware of it. This classically means code patterns, but SCM unifies the code, data model, and user interface design. So we have patterns to follow when creating the data model. For example, the vendor and customer lists are very similar and are called main tables. Purchase orders and sales orders are also very similar, which are worksheet tables; in this case, order headers and lines.

Tables in SCM have a property that defines the type of table, and each type is associated with a particular style of form for the user interface. We could, therefore, consider these types as patterns. If we think of them as patterns, it is far easier to apply the recipes to our own development.

The following list is of the common table types, listed with the usual form design and the typical usage:

In this chapter, we will create types and tables for the common types of table, but we will complete the patterns when we cover the user interface in Chapter 3, Creating the User Interface.

In this chapter, we will cover the following recipes:

• Creating enumerated types
• Creating extended data types
• Creating setup tables
• Creating a parameter table
• Creating main data tables
• Creating order header tables
• Creating order line tables

You can find the code files for this chapter on GitHub at https://github.com/PacktPublishing/Extending-Microsoft-Dynamics-365-Finance-and-Supply-Chain-Management-Cookbook-Second-Edition/blob/master/Chapter%202.axpp.

Enumerated types are called base enums in SCM, which are similar to enumerated types in C#. They are commonly referred to as enums. An enum is an integer referenced as a symbol, which can be used in code to control logic. It can also be used as a field in a table that provides a fixed drop-down list to the user. When used as a field, it has the ability to provide user-friendly labels for each symbol.

All enums have to be defined in the data model before we use them and can't be defined within a class or method.

The following tasks continue the vehicle management project, ConVehicleManagement, which was created in Chapter 1, Starting a New Project, although this is not a prerequisite. We just need an SCM project that was created as an extension project.

In order to create a new Enum, follow these steps:

1. Either right-click on the project (or any sub folder in the project) and choose Add New item....

2. This will open the Add New Item window. From the left-hand list, select Data Types, which is under the Dynamics 365 Items node.
3. This will filter the available options in the right-hand list. From this list, choose Base Enum.
4. Enter a name, prefixed appropriately. In this example, we are creating an enum to store the type of vehicle; so, we will enter ConVMSVehicleType as Name and click Add.

5. We will now have a new tab open with our empty enum. Before we continue, we must create labels for the Label and Help properties. Double-click on the label file so we can add a new label.

6. This will add an empty line for us. On this empty line, click on Label ID and enter a short name, in the style of an identifier, such as VehicleType.

7. Enter Vehicle type in the Label column. You can add a comment in order to provide context for other developers, in case it is reused.
8. Press New and repeat for the next label. Use VehicleTypeHT for the Label ID and enter the text: Defines the basic type of vehicle, used to govern static vehicle type specific properties for the Label property. It would be tempting to just write The type of vehicle, but this provides nothing to the user in addition to what the field name suggests. The result should be shown as follows:

9. Without left-clicking first, right-click on Label ID for VehicleType, and choose Copy. Left-clicking first might select the text in the field, which is undesirable. We should end up with @ConVMS:VehicleType in the paste buffer.
10. Reselect the editor tab for the Enum, and look for the property sheet. If this isn't visible - it's usually at the lower right of the screen - right-click on the Enum inside the tab and select Properties.
11. In the property sheet, click in the Value field for the Label property and choose paste (or press Ctrl + V).

12. Since we just added HT to the label for the help text label, select the Repeat this process for the Help property and paste again. Then, click on the property's value control and add HT to the end of the label; remember that Label ID is case-sensitive.

13. This enum is used to define the type, and if we want the end-user customer (or other partners if we are an ISV), we should make this an extensible enum. Locate the Is Extensible property and set it to true. See the There's more... section for more information on this.
14. We will no longer be able to set the Use Enum Value property, so we will now add the elements to the Enum. The options we will add should be created as follows:

15. To add each symbol, or element as it is referred to in the editor, right-click on the Enum and choose New Element.
16. Complete the property sheet as per the table, and repeat for each required element.

17. Press the Save or Save all buttons in the toolbar.

InventTable.ABCRevenue = 1;
InventTable.ABCRevenue = ABC::B;

Of course, we would never write the first option—we use enumerated types specifically to avoid having to remember what each number means. If the enum was created as extensible, the values associated with the symbol are not assigned at design time, they are installation-specific. Extensible enums are implemented as a class, and we, therefore, have a slight performance hit as we are no longer dealing with numbers.

We use enums for many purposes, ranging from table inheritance (different fields are relevant to a type of vehicle) to providing an option list to a user, and even controlling which type of class is created to handle logic specific to the vehicle type.

Base enums are therefore a great way to provide a link between logic and the user interface. However, there are limitations; the options are defined in code and the user cannot add to the list. The problem with making the options user-definable is that we can't use the values in code, unless we want to add the options to a parameter form, which is not very extendable.

What we can do is use the code pattern exemplified in the item model group form. Here, we have a user-definable list, with an Enum that determines the inventory model that the items will use.

It is very common to use an enum to define the status of a record, and we will cover state machines in Chapter 15, State Machines. When defining an enum for status, we order the elements so that we can compare them in code.

Given that standard enums are essentially a symbol representing an integer, we can use relative comparisons. These are commonly used in status fields. Let's look at the following vehicle status:

If we want all vehicles not removed from service, we could write the following:

select * from vehicles where vehicles.VehicleStatus < VehicleStatus::Removed;

If we want a list of vehicles that have ever been active (for example, every status on or after Active), we could write the following:

select * from vehicles where vehicles.VehicleStatus >= VehicleStatus::Active;

If we want all vehicles not yet active, we could write the following:

select * from vehicles where vehicles.VehicleStatus < VehicleStatus::Active;

We could also use query ranges to create lists so that users can see pertinent lists of vehicles using the same concept.

There are two notable limitations when an enum is created as extensible.

One is that we can't use it in conjunction with the mandatory property on fields in order to force a user to select a value. The first element is probably zero (which is not valid for mandatory fields), and will usually work—we can't guarantee this behavior and must not use this technique.

The other limitation when using comparisons on enums is using relative comparisons, such as greater than. For example, the options for the SalesStatus base enum are as follows:

• None
• Backorder
• Delivered
• Invoiced
• Canceled

This enum was changed from a standard to an extensible enum in an early application update. Prior to the update, the following code would normally return a list of the SalesTable records that are not yet invoiced with SalesId and SalesName populated:

select SalesId, SalesName from salesTable where salesTable.SalesStatus < SalesStatus::Invoiced;

This will now fail with a compilation error. Extensible enums are designed to be extended by third parties, who may add their own options to the type. This means that there is no ranking in extensible enums, which means that we can't use greater than or less than expressions. We must, therefore, write code such as the following:

select SalesId, SalesName 
    from salesTable 
    where salesTable.SalesStatus == SalesStatus::Backorder
        || salesTable.SalesStatus == SalesStatus::Delivered;

The code upgrade tool in the Life Cycle Service (LCS) would look for this and assist the developer in correcting the code so that it is built correctly.

The actual values of the SalesStatus enum are stored in tables in SQL, where the values are set when the database is synchronized. The values will not change after this, but new elements that are subsequently added will be appended to this table. Since the development, OneBox VMs come with SQL Server Management Studio; you can open this, and use the following Transact-SQL against the database AxDB in order to see the way in which this data is stored:

SELECT  E.[NAME], V.NAME AS SYMBOL, V.ENUMVALUE AS VALUE
  FROM ENUMIDTABLE E
  JOIN ENUMVALUETABLE V ON V.ENUMID = E.ID
  WHERE E.[NAME] = 'SalesStatus'

This will show the following result if this enum has not been extended:

This can occasionally be useful during development and testing when extensible enums are used.

The lesson here is that we are free to use comparisons on the enums that we control, but we can't assume that a third party (ISV or Microsoft) will not change a standard enum to be extensible. This also means when referencing enum values in query ranges, we must use the SalesStatus::Backorder format and not 1. Even if the enum is not extensible, we should still use this format.

All fields should be based on an EDT or an enum, but they are not just used to enforce consistency in the data model but are used as types when writing code.

The EDT in this example will be a primary key field for a table that we will use later in the chapter.

We just need to have an SCM project open in Visual Studio. To look at standard examples, ensure that Application Explorer is open by selecting View | Application Explorer.

We will create an EDT for a vehicle number. A vehicle table is of a similar pattern to customers and vendors, and we will extend the Num EDT for this type.

To create the EDT, follow these steps:

1. Creating an EDT starts in the same way as all new Dynamics 365 items: by pressing Ctrl + Shift + A or right-clicking on a folder in the Solution Explorer and choosing Add | New Item.
2. Select Data Types from the left-hand list, and then select EDT String.
3. In the Name field, enter ConVMSVehicleId and click Add.
4. Next, we need to complete the property sheet; the main properties are covered in the following table:

5. If this EDT is to be used as a primary key field, we will need to populate the Table References node.

6. Press Save (Ctrl + S) or Save all (Ctrl + Shift + S) in the toolbar to save the changes.

There is a back and forth element to EDT creation when we are creating a primary key field. We can't create the field without the EDT, yet we can't complete the EDT when the field is on the table.

EDTs are types. Therefore, they must be globally unique among all other types, such as tables, views, data entities, enums, classes, and other EDTs. The EDT properties aren't just defaults, but they control behavior too. Should we add an unbound control to a form based on an EDT, the EDT can use the Table Reference property to provide a drop-down list, and the contents will be taken from a field group on the table.

EDTs can also extend other EDTs; although, child EDTs can only affect appearance properties. This is useful when we want to enforce the physical storage attributes of a range of types, but have a different label depending on the context. If we change the size of a base EDT, all the EDTs that extend it will be affected and, consequently, all of the fields that are based on them.

We often extend specific EDTs when creating an EDT for certain types of fields.

The typical EDTs we use for this are shown in the following table:

There are many more. Rather than listing them all here, a good practice is to locate a pattern used in standard SCM and follow the same pattern.

In this section, we will create a group table. A group table is used as a foreign key on main tables, such as the customer group on the customer table and the vendor group on the vendor table; the customer and vendor tables are examples of main tables. Group tables have at least two fields, an ID and a description field, but can contain more if required.

In this case, to aid the flow, we will create the group table first.

We just need our SCM project open in Visual Studio.

We will create a vehicle group table. We don't have much choice about the name in this as it has to start with our prefix, and end with Group; therefore, it will be ConVMSVehicleGroup. To create the table, follow these steps:

1. Using the recipe for creating EDTs, create a vehicle group EDT using the following parameters:

2. Save the EDT, but don't close the designer.
3. From within the project, choose to create a new item.
4. Choose Data Model from the left-hand list, and select Table from the right.
5. Enter ConVMSVehicleGroup in the Name field and click Add.

6. This opens the table designer in a new tab. From the project, drag the ConVMSVehicleGroupId EDT on top of the Fields node in the table, as shown in the following screenshot:

7. This creates the field with the same name as the EDT. As this is our table, we should remove the prefix and name it VehicleGroupId.
8. Click Save.
9. We can now complete our EDT, open the ConVMSVehicleGroupId EDT (or select the tab if it is still open), and enter ConVMSVehicleGroup in the Reference Table property.
10. Right-click on the Table References node, and select New | Table Reference.
11. In the property sheet, select the Related Field property, and then select VehicleGroupId from the drop-down list.

12. Check that the result is shown as follows:

13. Save the EDT, and close its designer. This should make the active tab the ConVMSVehicleGroup table designer; if not, reselect it.
14. From Application Explorer, which is opened from the View menu, expand Data Types, and then expand Extended Data Types.
15. Locate the Name field, and drag it onto the Fields node of our table. You can also just type the Name field directly into the property value.
16. We will now need to add an index; even though this table will only have a few records, we need to ensure that the ID field is unique. Right-click on the Indexes node, and choose New Index.
17. With the new index highlighted, press the F2 function key and rename it to GroupIdx. Change the Alternate Key property to Yes. All unique indexes that will be the primary key must have this set to Yes.
18. Drag the VehicleGroupId field on top of this index, adding it to the index.

19. Open the VehicleGroupId field properties, and set the Mandatory property to Yes, AllowEdit to No, and leave AllowEditOnCreate as Yes.

20. We can now complete the table properties select the table node in the table design (the table name), and complete the property sheet as follows:

21. All visible fields should be placed in a field group. Since this is a group table with two fields, we only need an Overview field group. Right-click on the Field groups node, and choose New Group.

22. Press F2 to rename the group to Overview and enter Overview in the label property before clicking the ellipsis button in the value. This opens the Label Lookup form.
23. Select Match exactly, and click search (the magnifying glass icon). Scroll down to find the first @SYS label with no description or one that exactly matches our intent, as shown in the following screenshot:

24. Select the @SYS9039 label and click Paste label.

25. Drag the two fields onto the group, and order them so that VehicleId is first in the list.
26. In order for any automatic lookups to this table to show both the ID and Description fields, add both fields to the AutoLookup field group.
27. We can skip to the Methods node, where best practice dictates we need to provide the Find and Exist methods.

28. Right-click on the Methods node, and choose New Method.
29. This will open the code editor, which now contains all methods, and will create a simple method stub, as shown in the following block:

/// <summary>
///
/// </summary>
private void Method1()
{
}

30. Remove the XML documentation comment section and the method declaration, and then create the Find method as follows:

public static ConVMSVehicleGroup Find(ConVMSVehicleGroupId _groupId, boolean _forUpdate = false)
{
    ConVMSVehicleGroup vehGroup;
        
    if (_groupId != '')
    {
        vehGroup.selectForUpdate(_forUpdate);
        select firstonly * from vehGroup
            where vehGroup.VehicleGroupId == _groupId;
    }
    return vehGroup;
}

31. Create a blank line above the method declaration and type three slashes (///), which causes SCM to create the XML documentation based on the method declaration. Fill in this documentation as follows:

/// <summary>
/// Returns a record in <c>ConVMSVehicleGroup</c>based on the _groupId
/// parameter
/// </summary>
/// <param name = "_groupId">The Vehicle group ID to find</param>
/// <param name = "_forUpdate">True if the record should be selected for
/// update</param>
/// <returns>The <c>ConVMSVehicleGroup</c> record</returns>

32. Now, to create the Exist method, go to the end of our Find method and create a new line after the method's end brace and just before the final brace for the table, and type as follows:

/// <summary>
/// Checks if a record exists in <c>ConVMSVehicleGroup</c>
/// </summary>
/// <param name = "_groupId">
/// The Vehicle group ID to find
/// </param>
/// <returns>
/// True if found
/// </returns>
public static boolean Exist(ConVMSVehicleGroupId _groupId)
{
    ConVMSVehicleGroup vehGroup;
    if (_groupId != '')
    {
        select firstonly RecId
            from vehGroup
            where vehGroup.VehicleGroupId == _groupId;
    }
    return (vehGroup.RecId != 0);
}

33. We will have two tabs open, the code editor and the table designer. Close the code editor and save the changes. Then close and save the table designer.

Creating a table creates a definition that SCM will use to produce the physical table in the SQL server. Tables are also types that contain a lot of metadata at the application level.

When creating the fields, we don't specify the label, size, or type. This comes from the EDT. We can change the label and give it a specific context, but the size and type cannot be changed.

The relations we created are used at the application level and not within SQL. They are used to generate drop-down lists and handle orphan records. Within the client, you can navigate to the main table. It determines the table via the relation, and uses the FormRef property on the table to work out which form to use.

The Find and Exist methods are a best practice rule, and should always be written and used. For example, although Select * from PurchLine where PurchLine.InventTransId == _id may appear to be correct as InventTransId is a unique key, it would be wrong as there is now a field on PurchLine to flag whether it is marked as deleted. Using PurchLine::findInventTransId would only find a record if it was not marked as deleted.

There are also many methods that we can override to provide special handling. When overriding a method, it creates a method that simply calls the super() method. The super() method calls the base class's (Common) method, which for update, insert, and delete is a special method that starts with do. The do methods cannot be overridden but can be called directly. The do method is a method on a base class called xRecord that performs the database operation.

The methods for validation, such as validateField, validateWrite, and validateDelete, are only called from events on a form data source; this is covered in Chapter 3, Creating the User Interface.

If you are following this chapter step by step, the following steps will cause a compilation error as we have not yet created the ConVMSVehicleGroup display method as specified in the FormRef property of the ConVMSVehicleGroup table. You can remove this property value for now and complete it when the menu item is created.

It may seem odd to do add this property at this stage, but this is because of the way the recipes have been split in order to aid readability. When creating a table, we would normally create the table, form, menu item, and security privileges all at the same time.

This process has not created the physical table, which is done by the database synchronization tool. The database synchronization is performed against the metadata created when the package is built.

To perform a full database synchronization, we would follow these steps:

1. From the menu, select Dynamics 365 and then Build models....
2. Check ConVehicleManagement [Contoso - vehicle management] and click Build.
3. This will take a few minutes to complete, depending on the speed of the VM and the size of the package. When it finishes, you can click Close. Any errors will be reported in the Error list pane in Visual Studio.

4. Then select Synchronize database... from the same menu. This can easily take 20 minutes to complete.

For incremental changes, we can save a lot of time after the first build by taking these steps as we want to test our progress as we develop our solution:

1. Right-click on the project in the Solution Explorer and choose Build. Monitor the Output pane to see when it is complete. Again, any errors are shown in the Error list pane.
2. Next, right-click on the project again and choose Synchronize ConVehicleManagement (USR) [Contoso – vehicle management] with database.

This process should only take a few minutes to complete.

You may notice several warnings that state a best practice deviation. Some are to help follow good code standards, such as method header documentation, and others are warnings that could mean a possible error.

There are always two messages that are safe to ignore:

• Assembly "Microsoft.Xbox.Experimentation.Contracts, Version=1.0.0.0, Culture=neutral, PublicKeyToken=d91bba2b903dc20f" failed to load because it was not found.
• Assembly "System.Xml, Version=2.0.5.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e" failed to load because it was not found.

All best practices aside from the above should be dealt with. Some typical code that BP will highlight is shown in the following sections.

The warnings are generated depending on what is enabled in the Dynamics 365 | Options | Best Practices list. You can navigate to this to see the rules that the compiler will check. When suppressing a warning, which should only happen because the rule is a false positive (and not simply to make it go away), you add the following attribute to the method:

[SuppressBPWarning('BPErrorSelectUsingFirstOnly', 'A list is required as the result is processed using next')]

In this case, we are suppressing a warning where we have written the following code:

private CustTable GetCustomers(CustGroupId _custGroupId)
{
    CustTable custTable;
    select * from custTable where custTable.CustGroupId == _custGroupId;
    return custTable;
}
public void ProcessCustGroup(CustGroupId _custGroupId)
{
    CustTable custTable = this.GetCustomers(_custGroupId);
    while (custTable.RecId != 0)
    {
        // do stuff
        next custTable;
    }
}

We would add the declaration just above the method declaration for GetCustomers.

Other errors include the following:

• Updating parameter values directly: If this is needed, copy the parameter to a local variable instead; this tells the compiler it was deliberate.
• Assigning an extensible enum to an int: This should never be done, as the integer value is environment-specific and can vary.
• Adding a field list to a select call and using select custTable: This is treated as select * from custTable. The compiler is telling us to check whether we really need all of the fields from custTable.

There are hundreds of checks like this, and when they refer to any element we have written, we should always take action.

A parameter table only contains one record per company. The table contains a list of fields, which can be defaults or company-specific options used in code to determine what should happen. The parameter table is usually created first, and the various fields that act as parameters are added as we create the solution.

This follows on directly from the Creating setup tables recipe.

To create the parameter table, follow these steps:

1. Create a new table called ConVMSPararameters; again, the prefix and suffix are based on best practice. Usually, the name will only be <Prefix>+<Area - if required to scope table>+Parameters.
2. Set the table parameters as follows:

3. Drag the ConVMSVehicleGroupId EDT onto the Fields node and rename it to DefaultVehicleGroupId.
4. Drag the ParametersKey EDT from the Application Explorer to our Fields node.
5. Rename it to Key and change the Visible property to No.

6. Create a field group named Defaults and set the Label property. Use the label lookup (the ellipsis button) to locate a suitable label. Note that @SYS334126 is suitable in this case. As always, check the description of the label to understand its usage.

7. Drag the DefaultVehicleGroupId field to the new Defaults field group.

8. Right-click on the Relations node, and select New | Foreign Key Relation. Rename the relation to ConVMSVehicleGroup.
9. Complete the parameters as follows; if not specified, leave them as the default:

10. Right-click on the ConVMSVehicleGroup relation and choose New | Normal.
11. In the Field property, specify the foreign key (the field in this table): DefaultVehicleGroupId.
12. In the Related Field property, specify the key in the parent table: VehicleGroupId.
13. Create a new index called KeyIdx and add the Key field to it. It is unique by default, so it acts as a constraint index.

14. We can now create the Find and Exist methods. There is a difference for parameter tables, in that the Find method creates a record in a particular way. Create the Find method as shown in the following piece of code:

public static ConVMSParameters Find(boolean _forUpdate = false)
{
    ConVMSParameters parm;
    parm.selectForUpdate(_forUpdate);
    select firstonly parm where parm.Key == 0;
    if (!parm && !parm.istmp())
    {
        Company::createParameter(parm);
    }
    return parm;
}

15. We will use a slightly different select statement where we can write the select statement inline, which means that we don't have to declare the type as a variable; write the Exist method as follows:

public static boolean Exist()
{
    return (select firstonly RecId from ConVMSParameters).RecId != 0;
}

16. We want to ensure that the record cannot be deleted. So, we will override the Delete method. Press Return at the start of the Find method to create a blank line at the top. Right-click on this blank line and choose Insert Override Method | validateDelete. Change the method so that it reads as follows:

public boolean validateDelete()
{
    return checkFailed("@SYS23721"); //Cannot delete transaction
}

17. We set the Table Cache property to EntireTable. Whenever this table is updated, we will need to flush the cache so that the system uses the updated values. Override the update method as follows:

public void update()
{
    super();
    flush ConVMSParameters;
}

This tells SCM to write the record buffer with the super() call and then flush the cache in order to force the system to read it from the database when it is next read.

The build operation will validate and compile the package into a Dynamic Link Library (DLL). This must be done before we synchronize the database. This can fail, and at this stage, it is normally due to missing references. Within the Application Explorer, each element shows the package to which it belongs. We must ensure that our model references all types that we use within our project. If we don't, we will get build errors like this:

To add the required references, we can follow these steps:

1. Locate the type with the error in Application Explorer.
2. Note the package it is in, which is in square brackets.
3. Navigate to Dynamics 365 | Model Management | Update model parameters....
4. Select the ConVehicleManagement model.
5. Click on Next.
6. Check if the required package is checked, and then press Next.

7. Press Finish.
8. Navigate to Dynamics 365 | Model Management and select Refresh models.
9. Try the build operation again; you may need to repeat this as one error can mask another.

You may notice that the Find method started with a capital letter, yet the overridden methods did not. When SCM was first released as AX 7, new methods were created in the same way as with C# and started with a capital letter. Existing methods were then refactored. There is no direct advice as yet as to whether to capitalize the first letter of a method, although it is my preference as it helps to more easily differentiate between methods and public variables when using IntelliSense. So the reason I choose to do this is for readability and because new methods in SCM were created with the first letter capitalized.

Whichever route you take, it is important to be consistent. When overriding methods or implementing a method from an interface, it is critical. In this case, if you implement a method from an interface and change the casing, this will build without error, but will fail at runtime. You may find strange behavior even when overriding methods if the case is different. This is not just the first letter, of course, so using IntelliSense is a much safer way to override methods.

Be careful when copying the Find and Exist methods to other tables as a template. As they are static, the methods can technically be on any class or table—that is, check the return type. This can cause some confusion when they behave strangely. As EDTs can be used interchangeably, we won't get a type error unless the base type of the EDT is different. This means that you could pass a variable of the ConVMSVehicleGroupId type to InventItemGroup::Find() and it would simply return a record (or empty buffer) of the InventItemGroup type. So, if we copied the Find method from InventItemGroup to our table, the following scenarios would be possible:

There are several system fields that are always created on all tables. One of which is RecVersion, which is used by Optimistic Concurrency (OCC). Optimistic concurrency is enabled by default on new tables. We select a record "for update" by adding a forUpdate clause to a select or while select statement, or by using the selectForUpdate(true) method that exists on all tables.

When we select a record for update, a physical lock is not placed on the record, and it is therefore possible for two different processes to select the same record for update.

As the record or records are read, they are read from the database into an internal record buffer. When the record is written back, it will check that the value of the RecVersion field in the physical table is the same as when the record was fetched into the internal buffer.

If RecVersion is different, an exception will be thrown. If this is thrown whilst editing data, the user is given a message that the record has changed and is asked to refresh the data. If the error is thrown within code, we will get an update conflict exception that can be caught. Should the update succeed, the RecVersion field will be changed to a different number.

If we are using OCC, we can make the call to selectForUpdate() even after the record has been fetched from the database. This is because it does not lock the selected records but states intent that we wish to do so.

The following is some further reading on properties used when creating elements such EDTs and tables:

• Application Explorer properties: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/dev-ref/application-explorer-aot-properties

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:

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

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.

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:

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

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

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.

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:

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.

12. Do not make the VehicleType field mandatory.

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:

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:

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;
}

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.

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.

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.

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

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.

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

Order and line tables are used whenever we need a worksheet to enter data that is later acted upon. Once they have been processed, they should no longer be required. Reports should act upon the transactions that the order created, such as inventory transactions, sales ledger transactions, invoices, and more.

Although we will be using the tables created earlier, this pattern can be followed with your own solution.

We will first create the worksheet header table, which will be a vehicle service order table:

1. Create a new table named ConVMSVehicleServiceTable.
2. Create a primary key EDT, ConVMSVehicleServiceId; this time, extend Num. Complete the Label and Help Text properties with appropriate labels.

3. Drag the EDT from Solution Explorer to the Fields node of our table and rename it ServiceId.
4. Complete the ServiceId field as an ID field: Mandatory = Yes, Allow Edit = No, and Allow Edit On Create = Yes.
5. Complete the relation information on the ConVMSVehicleServiceId EDT.
6. Create the primary key index as ServiceIdx with ServiceId as the only field.
7. Set the Clustered Index and Primary Index properties as ServiceIdx.
8. Drag the ConVMSVehicleId EDT to our table and rename it VehicleId.
9. Make the VehicleId field mandatory set Ignore EDT relation to Yes.

10. Create a foreign key relation for ConVMSVehicleId to ConVMSVehicleTable.VehicleId. Dragging the table on to the Relations node can save some time, but this creates a normal relation and not a foreign key relation.

11. Drag the Name EDT onto our table from Application Explorer.
12. Create a new Base Enum for the service status, as defined here:

13. Add the following elements:

14. Save and drag the new ConVMSVehicleServiceStatus enum to our table and rename it ServiceStatus.
15. Make the ServiceStatus field read only. Allow Edit and Allow Edit On Create should be No. This is because Status fields should be controlled through business logic.
16. Create the date EDTs ConVMSVehicleServiceDateReq "Requested service date" and ConVMSVehicleServiceDateConfirmed "Confirmed service date." The dates should extend TransDate. Label them appropriately and drag them to the new table.
17. Rename the fields to ServiceDateRequested and ServiceDateConfirmed.
18. Complete the table properties as shown here, which are common for all tables of this type:

19. Create the fields groups as follows:

20. Create the now usual Find and Exist methods using ServiceId as the key.
21. You can also create your own validation on the service dates, using validateField. For example, check that the service dates can't be before today.
22. We can also validate that the record itself can be saved. This introduces the validateWrite method. This is to enforce the requirement that only service orders at status confirmed or less can be changed; the method should be written as follows:

public boolean validateWrite()
{
    boolean ret;
    ret = super();
    ret = ret && this.CheckCanEdit();
    return ret;
}
public boolean CheckCanEdit()
{
    if (!this.CanEdit())
    {
        //Service order cannot be changed.
        return checkFailed("@ConVMS:ServiceOrderCannotBeChanged");
    }
    return true;
}
public boolean CanEdit()
{
    switch (this.ServiceStatus)
    {
        case ConVMSVehicleServiceStatus::None:
        case ConVMSVehicleServiceStatus::Confirmed:
            return true;
    }
    return false;
}

23. Finally, we will write a method that initializes the defaults from the main table record, that is, vehicle, when it is selected. Write the following two methods:

public void InitFromVehicleTable(ConVMSVehicleTable _vehicle)
{
    this.Name = _vehicle.Name;
}
public void modifiedField(FieldId _fieldId)
{
    super(_fieldId);
    switch(_fieldId)
    {
        case fieldNum(ConVMSVehicleServiceTable, VehicleId):
            this.InitFromVehicleTable(ConVMSVehicleTable::Find(this.VehicleId));
            break;
    }
}

24. Save the table and close the editor tabs.

There are few new concepts here. I'll start with the code structure at the end of the step list.

The most important part of this code is that we didn't write this.ServiceStatus <= ConVMSVehicleServiceStatus::Confirmed. This is an extensible enum, and we can't be sure of the numeric value that the symbols have.

The other part is that we have split what may seem to be a simple if statement in validateWrite into three methods. The reason is reusability. It is nicer to make a record read-only in the form than it is to throw an error when the user tries to save. So, we can use CanEdit to control whether the record is editable on the form, making all controls greyed out.

Check methods are written to simplify the creation and maintenance of validation methods, and also to make the checks reusable, ergo consistent. Check methods are expected to return a silent true if the check passes, or to display an error should the check fail. The error is sent to the user using the checkFailed method, which does not throw an exception.

The next method is the InitFrom style method. This is a very common technique and should always be used to initialize data from foreign tables. It may seem odd that we don't check that it exists first.

This is deliberate. Records in SCM initialize so that all the fields are empty or zero (depending on the field type). So, if the record is not found, the values that are initialized will be made to be empty, which is desirable. Also, modifiedField occurs after the field is validated. So, the method won't be triggered should the user enter an invalid vehicle ID. If the vehicle is not mandatory, we may find the vehicle ID is empty; however, again, this is fine.

The On Delete property for table relations is similar to the functionality controlled by the Delete Actions node on the table. The difference is that the Delete Action is placed on the parent table. This is a problem if the parent table is a standard table, as this is now locked for customization (over-layering). Using the On Delete property is therefore controlled in a much better location, even if the result is the same. Because of this, we should always use the same place for this, which should be the relation.

We have the following options for both Delete Actions and the On Delete property:

• None
• Restricted
• Cascade
• Cascade + Restricted

None has no effect, and effectively disables the delete action; this is useful if you want to specifically state "Do nothing" so someone else doesn't try to correct what seems to be an omission.

Restricted will prevent the record from being deleted, if there are records in the related table that match the selected relation. This occurs within the validateDelete table event, which is called by the validateDelete form a data source event.

Cascade will delete the record in the related table based on the relation; it is no use having a sales order line without a sales order. This is an extension to the delete table event.

Cascade + Restricted is a little special. In a two-table scenario, it is the same as Restricted; it will stop the record from being deleted if a related record exists. However, if the record is being deleted as part of a cascade from a table related to it, which records will be deleted.

This recipe continues from the Creating order header tables recipe. The example in this recipe is that we will have service order lines that reflect the work required on the vehicle. The concepts in this recipe can be applied to any order line table; to follow along exactly, the previous recipes should be completed first.

To create the order line table, follow these steps:

1. Create a new table named ConVMSVehicleServiceLine.
2. Drag the following EDTs onto the table:

• ConVMSVehicleServiceId (set Ignore EDT relation to Yes)
• LineNum
• ItemId (set Ignore EDT relation to Yes)
• ItemName
• ConVMSVehicleServiceStatus

3. Remove the ConVMSVehicle prefixes.
4. The ServiceId and LineNum fields are usually controlled from code, so make them read-only and mandatory (this ensures that the code that sets them has run before the user saves the line).

5. Make ItemId mandatory and only allow it to be edited on creation.

6. Create a unique index called ServiceLineIdx, and add the ServiceId and LineNum fields. We will use this as a clustered index as it will naturally sort the lines on the form.
7. Add a relation to ConVMSVehicleServiceTable, but service lines are contained within a service order record, so complete it as follows:

8. Ensure that this relates to ServiceId, and then add a relation to InventTable on ItemId, using the following properties:

9. Create an Overview group to control what appears on the lines and add all fields. In our case, this is sufficient. We would usually have many more fields on a line, and we would organize the fields into logical groups that are used in the form design. We wouldn't usually add the foreign key or line number; these would be in a group called Identification (@SYS5711).
10. Update the table properties as follows:

11. The Find and Exist methods will need two keys in this case, ServiceId and LineNum. The select statement clause should be written as follows:

select firstonly *
    from line 
    where line.ServiceId == _id 
        && line.LineNum == _lineNum;

12. Finally, we need to initialize the ItemName field, and the user selects an item; write the following two methods:

public void InitFromInventTable(InventTable _inventTable)
{
    this.ItemName = _inventTable.itemName();
}
public void modifiedField(FieldId _fieldId)
{
    super (_fieldId);
    switch (_fieldId)
    {
        case fieldNum(ConVMSVehicleServiceLine, ItemId):
         this.initFromInventTable(
         InventTable::find(this.ItemId));
            break;
    }
}

13. Once complete, save and close the code editor and designer tabs.

The first new concept is the use of the clustered index to control the order in which the records are displayed in grid controls. This is simply using the fact that SQL will return records in the order of the clustered index. Composite keys are fine for this purpose, but we just wouldn't usually use them as a primary key. See the There's more... section on surrogate keys.

One point to be highlighted here is to look at the initFromInventTable method. The pattern is straightforward, but the call to inventTable.itemName() is a method, hence the parentheses. The declaration for the method is as follows:

public ItemName Display itemName([Common]).

As all tables derive from Common; we can pass in any table, which is as true as it is pointless. If we look at the method, it can actually only handle InventDim. The reason isn't obvious, but it could be used to handle a different table through the extension of a pre-post handler method. Reading through the methods is always a good investment, taking time to understand the reason why the code was written that particular way.

Surrogate keys have some history, which is important to understand. These were introduced in AX 2012 as a performance aid and allowed features like the ledger account lookup when entering general ledger journals. The problem is that they are hardwired to be RecId. So, when we added foreign key relations, the field created contained an unhelpful 64-bit integer. To solve this, an alternate key was added, which is a property on the index definition. This allows a more meaningful relation to be used for a foreign key. The primary key could only be unique indexes that have the Alternate Key property set.

The other type of key introduced was the replacement key. The replacement key is a way to show a meaningful key, other than the numeric RecId based SurrogateKey.

What SurrogateKey still allows us to do is to use RecId as the foreign key, but shows meaningful information from a field group on the parent table. An example is that we could add a foreign key relation to ConVMSServiceOrderLine, which should use SurrogateKey. When we add the foreign key, containing the meaningless number, we add a ReferenceGroup control that can display fields from a field group on the ConVMSServiceOrderLine table; the user is oblivious to the magical replacement that is going on behind the scenes.

Performance is no longer a reason to use surrogate keys, and they should be seldom used. The following are the main drawbacks of surrogate keys:

• Tables that don't have a natural index as a primary key cannot be used in a data entity (unless they are manually crafted).
• It will not be possible to use the table using the Open in Excel experience.
• Transferring data between systems is more complicated.
• Reporting and business intelligence is made more complex.

In the case of our service line table, a new natural key would be needed if we wanted to use it with data entities or to edit the data in Excel.

For more information on the history surrogate keys from AX 2012, please read:

• Table Keys: Surrogate, Alternate, Replacement, Primary, and Foreign: https://docs.microsoft.com/en-us/dynamicsax-2012/developer/table-keys-surrogate-alternate-replacement-primary-and-foreign