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

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

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.

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:

Property	Value
Name	ConVMSVehicleServiceTable
Related Table	ConVMSVehicleServiceTable
Cardinality	ZeroMore
Related Table Cardinality	ZeroOne
Relationship Type	Association
On Delete	Cascade

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

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:

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

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.

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

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