Advanced Duplicate Checking

Updated on

Duplicate database records can cause issues both for staff and members and can be a nuisance to clean up after the fact. To prevent accounts from being created, you can code your Create Account forms to perform either a simple or advanced duplicate check.

The simple duplicate check only checks if the email entered is on file already exists and returns an error. The advanced duplicate check option allows you to pass specific parameters from your Create Account form to a special duplicate check procedure (va_CheckForDuplicateAccount) which allows you to have fine-grained control over whether or not a contact is considered a duplicate or not.

Admin Panel Options

Advanced Duplicate Checking is controled via the Sonic Forms Admin Panel.
  • Duplicate Account Message - This option allows you to use a specific duplicate message rather than the default.
  • Enable Proceed Anyway Option - This option allows the user the choice of bypassing the duplicate check and creating a new account after the duplicate check returns a suspected duplicate record.
  • Duplicate Check - Advanced - Choosing this option will call the stored procedure VA_CheckForDuplicateAccount and will send all the fields that are checked with "Use for duplicate check" to the stored procedure.
  • Fields used for Duplicate Check: This area on the Admin Panel allows you to view which fields you have identified for use with the Duplicate check using the “Use for duplicate check” option when you added the Form Part to your page layout. These fields are passed in the order shown to the duplicate check procedure when the form is submitted.

Example

Here is an example of this feature in use The message is changed from default to a custom phrase, the proceed anyway option is off, advanced duplicate checking is enabled and the Admin Panel is reporting the fields that will be passed to the duplicate check stored procedure.

In this case First Name, Last Name and Email will be passed to the Stored Procedure called VA_CheckForDuplicateAccount. This stored procedure is called after the page is submitted, i.e. after clicking the "Save" button.



Below is an example of the va_CheckForDuplicateAccount stored procedure with the three parameters needed. In this case the stored procedure is checking to see if the First Name, Last name and Email exist in the table “Name” and all exact matches. If they exist, it will return 101. Any positive number returned by the stored procedure is an error, if the procedure does not detect a duplicate then it should return a negative value such as "-1".



If the user fails the duplicate check then the custom duplicate message is displayed by Sonic Forms and the form does not submit.