More information

SOUNDEX

Duplicate Checking

co_individual_dup_check

Duplicate Check Child Form

An Object may specify a Duplicate Check Child Form. If a child form is selected as the Dup-Check Child Form, then after any form belonging to this Object is saved, then netFORUM runs the select sql in the specified child form to search for possible duplicate records. If any records are returned by SQL statement, then netFORUM will show those records and present a message to the user. The user may then evaluate the possible duplicates. If the user sees a potential duplicate, then they may back out of the Save and thereby not add a duplicate.

See Duplicate checking for end-user documentation on this feature.

If the checkbox for allow dup save anyway? is checked, then the user may click the Save Anyway button to ignore the duplicate check; otherwise, the user may not save the record.

Child Form SQL Template

The following is a template for the select sql in a child form that is used for duplicate checking purposes. In practice, a duplicate check child form will be used only for the duplicate check process; it is not suitable for use as an ordinary child form. 

  1. SELECT 
  2. 	zzz_key,
  3. 	[Code]   = zzz_code,
  4. 	[Description]   = zzz_description
  5. 	[ADD User]   = zzz_add_user,
  6. 	[ADD Date]   = zzz_add_date,
  7. 	[CHANGE User]   = zzz_change_user
  8.  
  9. FROM
  10. 	zzz_table (nolock)
  11.  
  12. WHERE
  13. 	zzz_code =   {zzz_code}
  14. 	AND zzz_key {<>zzz_key}

In the SELECT clause, we return enough information to let the user view the suspected duplicates. You may pull in other columns as well, and JOIN to other tables. It is a good practice to display the add user and add date to give the user an idea of how the suspected duplicate record was added initially.

In the WHERE clause, on line 13, we see the most important part of the duplicate check select sql. In this section, you will compare existing records in the table to the values entered by the end user on the form. The values the end user entered on the form will be expressed as parsed values. You may use any legal SQL statements to compare.

Line 14 of the WHERE clause is a special syntax used to prevent the duplicate check SQL from comparing the same record in the table as the one we are editing, in the case of editing an existing record and not adding a new record.

Usage

Only child forms that belong to forms of the object may be selected. As a naming convention, add the words "duplicates" to the end of the child form description so you will know its purpose, for example Events -> Duplicates.

Changing a Duplicate Check Child Form

If you want to change a baseline duplicate check child form, we recommend the following:

  • Navigate to the duplicate check child form, edit the SQL, and save and Lock the record to prevent it from being updated in an Upgrade.
  • An alternate is to add a new child form, and then point the Object to the new child form. This, however, will require you to lock the Object, which should be avoided.
  • If you want to add a duplicate check child form to an Object that does not have one, then add the child form, select your child form in the Object, and lock the object.
  • If you want to remove a duplicate check child form, then edit the child form, change the sql select statement to select 1 = 0 (which will return 0 records) and then save and lock the child form.

Specific Duplicate Check Child Forms

In baseline netFORUM, approximately 50 objects have duplicate check child forms. Here is a T-SQL statement to see these: 

SELECT obj_name, dyc_description, dyc_sql
FROM md_object (nolock)
JOIN md_dynamic_form_child (nolock) ON dyc_key = obj_dup_check_dyc_key
WHERE obj_dup_check_dyc_key IS NOT NULL
ORDER BY 1

List of Duplicate Check Child Forms

To document specific duplicate algorithms, do so on the specific group item. You may reference it from here.

eWeb

To force duplicate checking to happen on an eWeb form, a parameter must be passed in the query string - DupCheck=yes. This will cause duplicate checking to be run in the same manner as in iWeb. This will also allow users to Save Anyway just as in iWeb based on the object configuration.

Disabling duplicate check

To disable duplicate checking on a particular form, add &Confirm=ByPass to the query string.