Duplicate Record Protection

Introduction

This page describes the implementation of Open Petra's standard code that handles constraint exceptions that arise when the user tries to add a record to a data table that violates the unique primary key constraint. Open Petra only implements duplicate record protection on the client side at present. There will be a need in the future to handle server side exceptions, but the action taken by the client and server will be different. On the client it is necessary to handle the exception on a record by record basis and notify the user that the record cannot be added in its present state. On the server the transaction should probably fail after attempting to add a complete batch of records and report all of the records that triggered an exception, so that the batch can be examined as a whole.

The remainder of this page refers to client-side duplicate record protection.

Constraint Exceptions

Where do constraint exceptions get handled in code?

A constraint exceptions arises when the code calls the EndEdit() method on a DataRow with a non-unique set of primary key fields. In practice this occurs when data is transferred from the editing controls to the grid, which, for controls associated with primary keys, is as each primary key control is validated (loses focus). The exception bubbles up to the central method for all screens: ValidateAllData(). This method is the single place where the code handles constraint exceptions.

There is a new validation method called ValidateNonDuplicateRecord() that is part of the TControlExtensions class in Ict.Petra.Client.CommonControls.Gui. This is because this validation is not shared with the server but is specifically client Gui related and needs access to references to both our common controls and to our validation methods.

How do constraint exceptions get handled?

Constraint exceptions are handled by adding a new TVerificationResult to the standard screen verification result collection. As a result a duplicate record will usually trigger a tool-tip as the user leaves a control and then a message box if the user attempts to leave the row.

When the screen loads the code attempts to work out which controls on the pnlDetails panel are associated with the primary key columns in the data table. Some tables may have two or three or more primary key columns. It may not be the case that all these columns have a control on pnlDetails. For example, if the first primary key column is the ledger number, the grid and details panel may refer just to the content of a pre-defined ledger number. But this does not matter - the user cannot enter a ledger number anyway so we do not need to mention it when we are trying to construct some hint text to assist in working out why a particular set of fields is making a duplicate record. However, it should always be possible to find at least one control in pnlDetails that is associated with a primary key. The first one found is designated the 'prime' primary key column/control for the purposes of validation. This control becomes the one that will get the tool-tip.

When the data is transferred from the controls to the grid, the code checks to see if a constraint exception occurred. If it did, it raises a standard TVerificationResult with variable error text. This text contains a hint which should specify all the label text and control text for all the primary key controls on pnlDetails. Notice that a result with variable error result text is new for this class of error. In order to accommodate variable text a special case has been made for the AreVerificationResultsIdentical() method. For the case where the error code is equal to CommonErrorCodes.ERR_DUPLICATE_RECORD, the method returns true whatever the result text, so that the error can be removed from the collection when there is no exception or updated if the result text changes because the user made a duplicate record twice with different entered data.

What message will the user see?

If the code located a 'prime' primary key control at initialisation

The tool-tip will apply to the 'prime' control. If there is only one control that matches a primary key column the message will state that a duplicate record error has occurred and will provide a 'hint' along the lines of the following (when shown as a message box):

The currently edited record contains invalid data:
A record with these key value(s) already exists: 
Int Postal Type Code: AFR  [GENC.00016V]  - followed by the usual screen context information.

If the data table has multiple primary keys all the information appears on multiple rows...

The currently edited record contains invalid data:
A record with these key value(s) already exists: 
Country code: USA
Contact attribute code: NEWCODE
Contact detail attribute code: UNKNOWN  [GENC.00016V]  - followed by the usual screen context information.

The standard generated code has to locate the relevant controls in pnlDetails and use a relevant method to acquire the screen text depending on the type of control. So text boxes are simple - they have a Text property. But we use many different types of comboBox, partner find box, date and time control and so on. It may be that a primary key is associated with a control for which we have not included a method to get at its text. In that case the hint text could look like this...

The currently edited record contains invalid data:
A record with these key value(s) already exists: 
Country code: USA
Contact attribute code
Contact detail attribute code: UNKNOWN  [GENC.00016V]  - followed by the usual screen context information.

Notice that the contact attribute code label is present but there is no data control text associated with it. If you see this when you get a duplicate record exception, please alert the programming team to the whole message box text.

If the code did not locate a 'prime' primary key control at initialisation

The automatically generated code has an ultimate fall-back position if it could not find a 'prime' control anywhere in pnlDetails. If this happens it will display a generic error message in the message box that will look like this....

The currently edited record contains invalid data:
A record with these key value(s) already exists: 
No hint text is available for the following screen:
Ict.Petra.Client.MCommon.Gui.Setup,  Text: Maintain International Postal Type Codes.
Please inform the Open Petra team if you see this message.  [GENC.00016V]  - followed by the usual screen context information.

Summary

• All the code to handle duplicate record exceptions is contained within the auto-generated code.
• It should work to protect the user from seeing the general exception handler on every client GUI screen.
• It should provide the user with a helpful hint - one hint item row for each primary key column in 'pnlDetails'
• If an exception occurs on a screen that has a control for which the code does not have a GetTextFromControl() method, the label text will be present but not the control text. Please report this if you find it so we can be sure to include the control type in the method.
• If an exception occurs on a screen where we cannot find any primary key control, please report this also. The message will state that 'no hint text is available for the screen'