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.

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

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.

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

The cardinality should be OneMore as it is mandatory. On Delete should be Restricted on foreign key relations to main tables.

11. Drag the Name EDT onto our table from Application Explorer.
12. 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

13. Add the following elements:

Element	Label
None	No label so that it appears empty in the UI
Confirmed	Confirmed
Complete	Complete
Cancelled	Cancelled

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

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

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

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

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

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

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

24. Save the table and close the editor tabs.

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.