Extending Microsoft Dynamics 365 Finance and Supply Chain Management Cookbook - Second Edition

By Simon Buxton
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Data Structures

About this book

Dynamics 365 Finance and Supply Chain Management is Microsoft’s ERP solution, which can be implemented as a cloud or on-premise solution to facilitate better decision-making with the help of contemporary, scalable ERP system tools. This book is updated with the latest features of Dynamics 365 Finance and Supply Chain Management including Chain of Command (CoC), Acceptance Test Libraries (ATL), and Business Events. The book not only features more than 100 tutorials that allow you to create and extend business solutions, but also addresses specific problems and offers solutions with insights into how they work.

This cookbook starts by helping you set up a Azure DevOps project and taking you through the different data types and structures used to create tables. You will then gain an understanding of user interfaces, write extensible code, manage data entities, and even model Dynamics 365 ERP for security. As you advance, you’ll learn how to work with various in-built Dynamics frameworks such as SysOperation, SysTest, and Business Events. Finally, you’ll get to grips with automated build management and workflows for better application state management.

By the end of this book, you’ll have become proficient in packaging and deploying end-to-end scalable solutions with Microsoft Dynamics 365 Finance and Supply Chain Management.

Publication date:
March 2020
Publisher
Packt
Pages
534
ISBN
9781838643812

 

Data Structures

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

Data structures in SCM are not just tables and views—they also include the ability to define a data dictionary. This is now called the data model in SCM. The data model hasn't changed much in structure since AX 2012, but is much more refined, with many more features to aid development and minimize the footprint when extending standard tables and types.

This chapter does not cover creating extensions of standard types, but it does cover how to create types that allow your structures to be extensible. Extensibility is covered in Chapter 5, Application Extensibility.

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:

Table group Form design pattern Usage
Miscellaneous None This is essentially "undefined" and shouldn't be used, other than for temporary tables.
Parameter Table of contents This is used for single-record parameter forms.
Group

Simple list

Simple list and details-ListGrid

This is used for the backing table for drop-down lists. The Simple list design pattern is suitable for when only a few fields are required; otherwise, the Simple list and Details patterns should be used so the fields are presented in a useful way to the user.
Main Details Master This is used for main tables, such as customers, suppliers, and items.

Transaction header

Transaction Line

Simple List and Details with standard tabs

This is used for datasets, such as the invoice journal form, which contain posted transactional data with a header and lines.

Transaction

Simple List and Details w/ standard tabs This is used for tables, such as the inventory transaction form, which contain posted transactional data but at a single level.

Worksheet header

Worksheet line

Details transaction This is used for data entry datasets, such as the purchase order, where the dataset is made up of header and line tables. The pattern has two views, a list view of header records, and a detail view where the focus is the lines.

Worksheet

Details Master

This is a single-level data entry dataset, which is rarely used in SCM.


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
   

Creating enumerated types

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.

Base enums are usually only used as a drop-down list if we need to understand, in code, what each value means. They should contain a small number of options, and when used as a field, the list cannot be searched or extended by the user.

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

Base enums are given the ability to be extensible in this release; the mechanics of this are covered in more detail in the There's more... section.

Getting ready

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.

How to do it...

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....
Always try to use keyboard shortcuts, which are displayed next to the option. In this case, try Ctrl + Shift + A.
  1. This will open the Add New Item window. From the left-hand list, select Data Types, which is under the Dynamics 365 Items node.
  2. This will filter the available options in the right-hand list. From this list, choose Base Enum.
  3. 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.
This should have created a folder called Base Enums or added the new Enum to the folder if it already existed. This is regardless of the folder we selected when the Enum was created. If this did not happen, the Organize projects by element type setting in Dynamics 365 | Options was not checked. You can remove the base enum and add it back from Application Explorer after the setting is checked.
  1. 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.
This was created in Chapter 1, Starting a New Project; if you don't have a label file, one must be created before we continue. We should always maintain the en-us label file, as this drives the labels displayed on the designers in Visual Studio (for example, the form designer). Since we develop in English, we would maintain en-gb, en-ie, and en-gb as standard, as it is usually just a copy and paste with minor changes for en-us.
  1. 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.
  1. 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.
  2. 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:
We could repeat this for each label file language we created, but this is more efficiently done in bulk. We could also use a translator to assist after project completion in order to avoid translation errors. Most online translation tools aren't appropriate for this task as they won't have the necessary context or industry knowledge.
  1. 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.
  2. 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.
  3. In the property sheet, click in the Value field for the Label property and choose paste (or press Ctrl + V).
You could instead type in Vehicle type in the Label field, click on the ellipsis (...), and search for the label. This is OK when trying to use standard labels, but when we are creating labels, copy and paste is much faster.
  1. 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.
These properties do not have to be populated, but it will cause a best practice deviation warning when the project is built if these properties are left empty. The time spent here is of great value to a user, reducing mistakes and subsequent support calls.
  1. 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.
  2. 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:
Name (symbol) Label
NotSelected Not selected
Bike Motorbike
Car Car
Truck Truck
The Label column in the preceding table shows the literal to use; the actual value will be the label ID, such as @ConVMS:Motorbike.
  1. To add each symbol, or element as it is referred to in the editor, right-click on the Enum and choose New Element.
  2. Complete the property sheet as per the table, and repeat for each required element.
For non-extensible enums, we can control the ranking, which is done by setting the Enum value property. For readability, we would alter the order in the designer with the Alt + up and Alt + down keys. This property is not visible in our case as we set the Is Extensible property.
  1. Press the Save or Save all buttons in the toolbar.

How it works...

Enums are stored as integers in the database, and we can assign an integer value to a field based on an enum.

When the values of the enum are shown to the user, SCM will look this up using the enum's definition from the field in the user's language. If a label is not defined for the user's language, the label ID (@Con:MyLabelId) is shown to the user. If a label was not defined in the enum element, the symbol (MyEnum:Value) is shown.

For standard, non-extensible enums, the following lines of code are effectively the same:

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.

There's more...

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.

Using enums for comparison and status

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:

Symbol Value
Created 0
Review 1
Active 2
Removed 3


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.

Extensibility in base enums

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:

NAME SYMBOL VALUE
SalesStatus None 0
SalesStatus Backorder 1
SalesStatus Delivered 2
SalesStatus Invoiced 3
SalesStatus Canceled 4


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.

 

Creating extended data types

Extended data types are commonly referred to as EDTs. They extend base types, such as Strings and Integers by adding properties that affect the appearance, behavior, data (size), and table reference/relationships. This means that we can have types like CustAccount that have a label, size, table relation information, and other properties that provide consistency and greater understanding within the data model.

Another example of an EDT is Name. Should we change the StringSize property of this field, all fields based on this EDT will be adjusted; and if we reduce the size, it will truncate the values to the new size.

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.

Getting ready

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.

How to do it...

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.

The Num EDT is used (at some level in the type hierarchy) for fields that will use a number sequence.

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:
Property Value Description

Label

Vehicle ID

This is the label that will be presented to the user on the user interface when added as a field to a table.

Help Text

A unique reference for the vehicle record

This is the help text shown to the user when this field is selected or the mouse hovers over the controls based on this EDT.

Extends

Num

This should be completed for all EDTs, as we are usually following a pattern, such as ID, name, and grouping fields. This is explained in the There's more... section.

Size

This will be read-only, as we have based this EDT on another EDT. Although this is under the Appearance section, it controls the physical size of the associated fields in the database.

Reference Table

For types used as a primary key field on a table, this property should be populated. Along with the table references, it can be used to create a foreign key relation on child tables.

As always, remember to create labels for the Label and Help Text properties for each of your supported languages.
  1. If this EDT is to be used as a primary key field, we will need to populate the Table References node.
We will complete this later in the chapter, but you can see a good example by looking at the standard AssetId EDT. Navigate through the Application Explorer to AOT | Data Types | Extended Data Types, right-click on AssetId and select Open designer.
  1. Press Save (Ctrl + S) or Save all (Ctrl + Shift + S) in the toolbar to save the changes.

How it works...

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.

There's more...

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:

EDT Base type Size Reason
SysGroup String 10 This is used for the primary key fields for group tables. Group tables are those used for backing tables for drop-down lists. They may provide further definition to a record, or just be used for reporting. Examples include the following:
  • Item group
  • Customer group
  • Item model group
Num String 20 This is used for primary keys on worksheet tables, such as the sales order table (SalesTable). These fields are usually numbered based on a number sequence, which must have a string size of 20 characters.
Examples include the following:
  • Sales order number
  • Purchase order number
AccountNum String 20 This is used for primary key fields for main tables, such as the customer table. These tables are also, usually, based on a number sequence.
Examples include the following:
  • Customer account
  • Vendor account
Name String 60 All name fields should be based on this EDT, such as vehicle name, customer name, and so on. This EDT can be used as is, unless we wish to specify a label and help text.
Description String 60 This is used as the description field on group tables. This EDT is usually used as is, and isn't usually extended.
AmountMST Real All monetary value EDTs that store the amount in local currency should be based on this EDT. MST stands for Monetary Standard.
AmountCur Real All monetary value EDTs that store the amount in the transaction currency should be based on this EDT.
Qty Real All fields that store a quantity should be based on this EDT.

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.

 

Creating setup tables

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.

Getting ready

We just need our SCM project open in Visual Studio.

How to do it...

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:
Property Value
Name ConVMSVehicleGroupId
Label Vehicle group
Help Text Used to group vehicles for sorting, filtering, and reporting
Extends SysGroup
  1. Save the EDT, but don't close the designer.
  2. From within the project, choose to create a new item.
  3. Choose Data Model from the left-hand list, and select Table from the right.
  4. Enter ConVMSVehicleGroup in the Name field and click Add.
  1. 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:
  1. 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.
  2. Click Save.
  3. 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.
  4. Right-click on the Table References node, and select New | Table Reference.
  5. In the property sheet, select the Related Field property, and then select VehicleGroupId from the drop-down list.
If the drop-down list is blank, it means that the table is not saved or the Reference Table was typed incorrectly.
  1. Check that the result is shown as follows:
  1. Save the EDT, and close its designer. This should make the active tab the ConVMSVehicleGroup table designer; if not, reselect it.
  2. From Application Explorer, which is opened from the View menu, expand Data Types, and then expand Extended Data Types.
  3. 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.
  4. 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.
  5. 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.
  6. Drag the VehicleGroupId field on top of this index, adding it to the index.
The default for indexes is to create a unique index, so they are correct in this case. Indexes will be discussed later in this chapter.
  1. Open the VehicleGroupId field properties, and set the Mandatory property to Yes, AllowEdit to No, and leave AllowEditOnCreate as Yes.
Since we will leave AllowEditOnCreate as Yes, we can enter the ID, but not change it after the record is saved; this helps enforce referential integrity. The Mandatory, AllowEdit, and AllowEditOnCreate field properties only affect data manipulated through a form. These restrictions aren't enforced when updating data through code.
  1. 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:
Property Value Comment
Label Vehicle groups This is the plural name that appears to the user. VehicleGroupTable is a good label ID for this, as it gives context to others that might want to reuse this label.
Title Field 1 VehicleGroupId These two fields appear in automatic titles generated when this table is used as a title data source.
Title Field 2 Name
Cache Lookup Found This is linked to the table type, and warnings will be generated should an inappropriate cache level be selected.
None: no caching is fetched from the DB every time.
NotInTTS: Fetched once per transaction.
Found: Cached once found, not looked up again.
EntireTable: The entire table is loaded into memory.
The cache is only invalidated when records are updated or flushed.
Clustered Index GroupIdx This index is created as a clustered index.
Clustered indexes are useful as they include the entire record in the index, avoiding a bookmark lookup. This makes them efficient, but the key should always increment, such as a sales order ID; otherwise, it will need to reorganize the index when records are inserted. This table is small, so it won't cause a performance issue. It will also sort the table in Vehicle Group order.
Primary Index GroupIdx This defines the primary index and is used when creating foreign key joins for this table.
Table Group Group This should always be Group for a group table. Please refer to the table of table groups in the Introduction section.

Created By

Created Date Time

Modified By

Modified Date Time

Yes This creates and maintains the Created by tracking fields and is useful if we want to know who created and changed the record, and when.

Developer Documentation

The ConVMSVehicleGroup table contains definitions of
vehicle groups.
This is required for best practice and should contain information that other developers should understand about the table.

FormRef

ConVMSVehicleGroup This is a reference to the display menu item that references the form that is used to view the data in this table. When you choose View details in the user interface, it is this property that is used to determine which form should be opened. It is fine to fill this in now for speed, but the build will fail unless we have created the form and menu items.
  1. 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.
  1. 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.
  2. 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:
  1. Select the @SYS9039 label and click Paste label.
There are some labels provided by the system for purposes like this, and it is useful to remember them. Two common ones are @SYS9039 for Overview field groups and @SYS318405 for Details field groups (with the Description [group]Details).
  1. Drag the two fields onto the group, and order them so that VehicleId is first in the list.
  2. 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.
  3. We can skip to the Methods node, where best practice dictates we need to provide the Find and Exist methods.
  1. Right-click on the Methods node, and choose New Method.
  2. 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()
{
}
  1. 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;
}
  1. 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>
Should the supplied vehicle group not be found, it will return an empty buffer (where the system RecId field is zero). The _forUpdate parameter is explained in the There's more... section.
  1. 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);
}
  1. 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.

How it works...

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.

There's more...

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.
  1. 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.

A note on best practices

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.

 

Creating a parameter table

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.

How to do it...

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:
Property Value
Label Vehicle management parameters
Title Field 1
Title Field 2
Cache Lookup EntireTable
Table Group Parameter

Created By, Created Date Time

Modified By, Modified Date Time

Yes

Developer Documentation

The ConVMSParameters table contains settings used within the vehicle management solution.
  1. Drag the ConVMSVehicleGroupId EDT onto the Fields node and rename it to DefaultVehicleGroupId.
  2. Drag the ParametersKey EDT from the Application Explorer to our Fields node.
  3. Rename it to Key and change the Visible property to No.
This is only used as a constraint to limit the table to only having one record. All visible fields need to be in a field group.
  1. 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.
  2. Drag the DefaultVehicleGroupId field to the new Defaults field group.

We will use this on the parameter form so that it has the heading as Defaults. This is why we don't need to change the field's label to specify the context.
  1. Right-click on the Relations node, and select New | Foreign Key Relation. Rename the relation to ConVMSVehicleGroup.
  2. Complete the parameters as follows; if not specified, leave them as the default:

Property Value Description

Related Table

ConVMSVehicleGroup The table to which our foreign key relates.
Cardinality ZereOne There will be either one or no parameter record relating to the vehicle group record. A one-to-many relationship would use ZeroMore or OneMore.
Related Table Cardinality ZeroOne The value is not mandatory, so we can therefore relate to zero vehicle group records, or one.
Relationship Type Association The parameter record is associated with a vehicle record. Composition would be used in header/lines datasets, where deleting the header should delete the lines records.
On Delete Restricted This will prevent a vehicle group record from being deleted, if it is specified on this table. See the There's more section for more information on delete actions.
Role This is the role of the relation, and it must be unique within this table. We will need to specify this if we have two foreign key relations to the same table. For example, on the SalesTable table, this has two relations to CustTable as there are two fields that relate to this table. In that case, the Role will need to be populated in order to differentiate them.
  1. Right-click on the ConVMSVehicleGroup relation and choose New | Normal.
  2. In the Field property, specify the foreign key (the field in this table): DefaultVehicleGroupId.
  3. In the Related Field property, specify the key in the parent table: VehicleGroupId.
  4. 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.
  1. 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;
}
  1. 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;
}
  1. 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
}
This is called to check whether the record can be deleted when the user tries to delete the record in the UI. We could also override the delete method should we wish to prevent the record from being deleted in the code. This is done by either commenting out the super() call or replacing it with throw error("@SYS23721").
  1. 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.

There's more...

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.
We normally reference the ApplicationPlatform, ApplicationFoundation, and ApplicationSuite packages, as we often use elements from these packages.
  1. Press Finish.
  2. Navigate to Dynamics 365 | Model Management and select Refresh models.
  3. Try the build operation again; you may need to repeat this as one error can mask another.

Capitalization of method names

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.

Copying and pasting methods to save time

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:

Code Result
ConVMSVehicleGroup group = CustGroup::find(_VehGroupId); This would cause a compilation error, as you can't assign an object of the CustGroup type to the ConVMSVehicleGroup type.
return CustGroup::find(_vehGroupId).Name; This would compile without error as the compiler only checks that the base type is correct. ConVMSVehicleGroupId and CustGroupId are both strings. It will just not behave as expected and will return an empty string as the customer group record will not be found: records are never null.

Optimistic concurrency and selectForUpdate

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.

See also

 

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
  1. 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!
  1. 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.
  2. 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.
  1. 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.
  1. 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.
  1. Make the VehicleGroupId field mandatory.
  2. 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.
  3. Close the designer table for the EDT and navigate back to the table designer.
  4. 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.
  1. 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.
  1. 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.
  1. Create a unique index called VehicleIdx with the VehicleId field.
  2. 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.
  3. 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.
  1. 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.
  2. 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.
  3. 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.
  4. 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
  1. Add a normal field relation to the relation, connecting the VehicleGroupId fields.
  2. 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.
  3. 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.
  1. Next, add the Find and Exist methods using the table's primary key field as usual.
  2. 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;
}
  1. Create a label for the error message returned by checkFailed and replace the literal with the label ID.
  2. Once complete, save and close the table code editor and designer tab pages.
  3. 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.
  1. 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:

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:

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:

 

Creating order header tables

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.

Getting ready

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

How to do it...

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.
  1. Drag the EDT from Solution Explorer to the Fields node of our table and rename it ServiceId.
  2. Complete the ServiceId field as an ID field: Mandatory = Yes, Allow Edit = No, and Allow Edit On Create = Yes.
  3. Complete the relation information on the ConVMSVehicleServiceId EDT.
  4. Create the primary key index as ServiceIdx with ServiceId as the only field.
  5. Set the Clustered Index and Primary Index properties as ServiceIdx.
  6. Drag the ConVMSVehicleId EDT to our table and rename it VehicleId.
  7. Make the VehicleId field mandatory set Ignore EDT relation to Yes.
The decision to make the field editable depends on the associated logic (referential integrity) and the business requirements. Ignoring the EDT relation is the best practice method, and forces us to create a relation on the table.
  1. 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.
The cardinality should be OneMore as it is mandatory. On Delete should be Restricted on foreign key relations to main tables.
  1. Drag the Name EDT onto our table from Application Explorer.
  2. Create a new Base Enum for the service status, as defined here:
Property Value
Name ConVMSVehicleServiceStatus
Label Status (for example, @SYS36398 will suffice)
Help The service order status
Is Extensible True: remember we cannot use this for the ranking of relative comparisons (> or <) with this set
  1. Add the following elements:
Element Label
None No label so that it appears empty in the UI
Confirmed Confirmed
Complete Complete
Cancelled Cancelled
    1. Save and drag the new ConVMSVehicleServiceStatus enum to our table and rename it ServiceStatus.
    2. 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.
    3. 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.
    4. Rename the fields to ServiceDateRequested and ServiceDateConfirmed.
    5. Complete the table properties as shown here, which are common for all tables of this type:
    Property Value
    Label Vehicle service orders
    Title Field 1 ServiceId
    Title Field 2 Name
    Cache lookup Found
    Clustered Index ServiceIdx
    Primary Index ServiceIdx
    Table Group WorksheetHeader

    Created By

    Created Date

    TimeModified By

    Modified Date Time

    Yes
    Developer Documentation ConVMSVehicleServiceTable contains vehicle service order records
    Form Ref Blank until we have created the form
    1. Create the fields groups as follows:
    Group name Label Fields
    Overview Overview (@SYS9039)
    • ServiceId
    • VehicleId
    • Name
    • ServiceStatus
    Details

    Details (@SYS318405)

    You could also create a more helpful label of service details
    • ServiceId
    • VehicleId
    • Name
    • ServiceStatus
    ServiceDates Service dates
    • ServiceDateRequested
    • ServiceDateConfirmed
      1. Create the now usual Find and Exist methods using ServiceId as the key.
      2. 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.
      3. 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;
      }
      1. 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;
      }
      }
      1. Save the table and close the editor tabs.

      How it works...

      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.

      There's more...

      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.

       

      Creating order line tables

      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.

      How to do it...

      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
      1. Remove the ConVMSVehicle prefixes.
      2. 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).
      The LineNum field is usually used to order the lines, and can be made not visible if it isn't to be displayed in the user interface. All visible (non-system) fields should either be in a field group or made not visible.
      1. Make ItemId mandatory and only allow it to be edited on creation.
      1. 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.
      2. Add a relation to ConVMSVehicleServiceTable, but service lines are contained within a service order record, so complete it as follows:
      Property Value
      Name ConVMSVehicleServiceTable
      Related Table ConVMSVehicleServiceTable
      Cardinality ZeroMore
      Related Table Cardinality ZeroOne
      Relationship Type Association
      On Delete Cascade
      1. Ensure that this relates to ServiceId, and then add a relation to InventTable on ItemId, using the following properties:
      Property Value
      Name InventTable
      Related Table InventTable
      Cardinality OneMore
      Related Table Cardinality ExactlyOne
      Relationship Type Association
      On Delete Restricted
      1. 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).
      2. Update the table properties as follows:
      Property Value
      Label Vehicle service order lines
      Title Field 1 ItemId
      Title Field 2 ItemName
      Cache lookup Found
      Clustered Index ServiceLineIdx
      Primary Index SurrogateKey (default)
      Table Group WorksheetLine

      Created By

      Created Date

      TimeModified By

      Modified Date Time

      Yes
      Developer documentation ConVMSVehicleServiceLine contains vehicle service order line records
      1. 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;
      1. 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;
      }
      }
      1. Once complete, save and close the code editor and designer tabs.

      How it works...

      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.

      There's more...

      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.

      See also

      About the Author

      • Simon Buxton

        Simon Buxton has worked with Dynamics 365 Finance and Supply Chain Management since its earliest incarnations, starting with the product in early 1999 when Dynamics 365 Finance and Supply Chain Management was known as Damgaard Axapta 1.5.

        Simon has been the technical lead on many highly challenging technical projects in countries all around the world. These projects included complex integrations with on-premises and external systems, ISV solutions, and many technically challenging customer solutions.

        Now working with Binary, he was part of a team that implemented the first Dynamics 365 Finance and Supply Chain Management implementation as part of the Community Technical Preview (CTP) program, which led to the close working relationship with Microsoft that made this book possible

        Browse publications by this author
      Extending Microsoft Dynamics 365 Finance and Supply Chain Management Cookbook - Second Edition
      Unlock this book and the full library for FREE
      Start free trial