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

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.

If the drop-down list is blank, it means that the table is not saved or the Reference Table was typed incorrectly.

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.

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.

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

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:

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.

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.

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

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>

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.

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.

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.

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.

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.