Record Validation before save

Question

What I would like to do is add a duplicate check function on the save changes button, to check the fields that are entered against what is in the database before saving based on parameter fields. If this is not possible I can add it as a feature request.

Answer 1

There are multiple ways to accomplish validation prior to insert or save.

1) Unique Index or other Database Constraint

The simplest and most effective validation solution is to create database constraints that enforce the conditions you want. The database will automatically raise an error if a constraint is violated and dbFront can show the error.

To prevent duplicates create a unique constraint on the selected fields. To prevent customers with duplicate firstname and lastname combinations add the following.

ALTER TABLE Customers ADD CONSTRAINT UC_Customer UNIQUE (FirstName, LastName);

To prevent invalid column data you can add a check contraint. The following validates that any active Customers must be at least 18 years old.

ALTER TABLE Customers ADD CONSTRAINT CHK_CustomerAge CHECK (Age>=18 AND Status='ACTIVE');

The available validation constraint types are:

1. NOT NULL Constraint
2. CHECK Constraint
3. UNIQUE Constraint
4. PRIMARY KEY Constraint
5. FOREIGN KEY Constraint

2) Table Trigger

Another option is to add a table trigger. Depending upon your needs this could be an Insert, Update or an Delete trigger. Triggers need to be very fast so the checks should be as simple as possible.

Table triggers have the ability to look across multiple tables to perform their validation. The table trigger can correct data on the fly and if necessary revert the changes and return an error.

NOTE: The examples are simplistic and could idealy be done via a UNIQUE contraint.
​
A complete MySQL trigger looks as follows.

​​CREATE TRIGGER `customer_BEFORE_INSERT` ​BEFORE INSERT 
​    ​ON `customer` FOR EACH ROW
​BEGIN
​    ​IF exists (select 1 from customer c where c.lastname = NEW.lastname) THEN
    ​​    ​SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT =
​        ​​    ​'Duplicate Customer Lastname not allowed';
​    ​END IF;
​END

A complete SQL Server version of the trigger looks as follows...

CREATE TRIGGER tiuCustomerTrigger ON Customer AFTER INSERT, UPDATE AS 
BEGIN
    -- Prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @counter INT;

    -- Note: With an AFTER INSERT, UPDATE trigger the record is already inserted or updated
    -- so we are looking for existing duplicates. (count > 1)
    SELECT @counter=count(*) FROM saleslt.Customer c JOIN inserted n on c.FirstName = n.FirstName AND c.LastName = n.LastName;
    IF (@counter > 1)
        RAISERROR ('Duplicate Firstname, Lastname found.', 16 /* Severity */, 1 /* State */);  
END

3) Run Procedure Button

You can also use a Stored Procedure to perform validation and call it from a RunProcedure action button. See: Run Procedure

Any form changes will be saved prior to running the Stored Procedure and any error will automatically roll-back all changes.

Similar to a Table Trigger, the table procedure can perform any validation or correction as desired and it can return an error.

In addition, Run Procedure action buttons support multiple return types including a table listing or even a custom report.

Issues:

• RunProcedure buttons are not active until the record is saved.
• Does not hide the Save button. (a separate feature request)

4) Wizard Interface

For very complex forms or validation, it might be a good idea to implement a wizard interface. This basically adds a status field to the table record and allows intermediate saves at any stage.

As the user moves the record closer to the final stage more information is required. There is also the option of a detailed custom listing of record issues.

For implementation details see: Wizard for Data Entry

Issues:

• More complex to setup.