We saw that there is a lot of automation when we tested editing the odometer value and looked at the results in SQL. The system created a new version on every save (should anything have changed), and the fact the user has no control over this makes it great for auditing changes. It can also add significant overhead, depending on the volume.
A customer or consultant might request that date-time effectiveness is implemented on a table, and the request might seem reasonable if the requirement is to track a history of changes that have been made to a record. We also need to consider that it also adds complications to the code that we write and a significant overhead to the database – every change has to update the ValidTo field with the current date-time and create a new record for the change. This happens on every update to the record that's made through the user interface.
This should, therefore, be reserved for scenarios where the version is a requirement and the table type is Main or Group. This shouldn't be used for the worksheet (orders) or transaction tables. There are other patterns we would employ for this, such as the history of sales order delivery note being stored in the delivery note journal tables, that is, CustPackingSlipJour and CustPackingSlipTrans. The sales order only stores the current state of the order.
When we changed the table to a Valid Time State table, it added the ValidFrom and ValidTo fields automatically and forced us to create an index that contained the ValidFrom field. We added the ValidTo field in order to improve query efficiency when records are selected for a data range. The SQL statement is similar to the following when we select a valid record at a point in time:
SELECT *
FROM CONVMSVEHICLEODOMETERTABLE
WHERE '2019-05-19 14:05:45.000' BETWEEN VALIDFROM and VALIDTO
AND VEHICLEID=N'V000000018' -- DataAreaId AND Partition have
been omitted for clarity
When we wrote the Find method, we used an additional clause to ensure we selected the correct record based on the required date-time. The ValidTimeState clause has one mandatory parameter of the required date-time and an optional parameter for the valid date-time. When specifying one parameter, we only want one record – the one value at that point in time. This is why we added firstonly. This is to assist the database engine by telling it we only want the first result. This is good practice when we expect one result.
The firstonly clause was omitted when we selected for a range of dates as this can return more than one record.
The concurrency option was added in case we wanted to use pessimistic concurrency. If we are updating records in code, we sometimes want to ensure that no other process can interfere with our process. Since valid time state tables update more than one record at a time (the current and previous state records), we are more likely to get a message stating Another user has changed the current record. This occurs because optimistic concurrency lets more than one process select a record to update, and the first to commit updates the table's RecVersion field. When the subsequent processes try to write back the system checks, if the RecVersion has changed since the record was selected, it throws an error.
By using pessimistic concurrency, we physically lock the record and make other processes wait until we have completed the transaction. This is not good for performance but is sometimes required.
The ConVMSVehicleOdometerTable child form that we created to view the history of the changes was largely standard, except we implemented the IDateEffectivenessPaneCaller interface.
There are three important Boolean parameters that are passed to this class:
| Parameter | Explanation |
| Use plural labels | Changes the dialog caption to Display the records as at. |
| Allow show all records | When the data source is displayed as a grid, set this to true to allow the user to see a history of every change that was made to the record. |
| Use Date-time | Must match the table's Valid Time State Field type. If this property is UtcDateTime, this parameter must be set to true. |
We could have also added this to the vehicle form, but since we are only showing a single field on the vehicle table, we would construct the controller as follows:
dePaneController =
DateEffectivenessPaneController::constructWithForm(this,
ConVMSVehicleOdometerTable_ds, false, false, true);
The preceding code means that the dialog will be set to Display the record as at and there will not be an option to show all records.