Hi,
We seem to have a fair number of duplicate Contacts created in NetSuite and I would like to PREVENT the creation of duplicates as much as possible since it seems like the "ounce of prevention" here would definitely be more valuable than the cures.
I have previously written a simple Client Script (for the Contact Form, but I guess the same sort of logic could also be used for server side operations/an import suitelet) which does field validation on the Contact e-mail field(s) (the built-in one and two additional Custom ones) by searching for any duplicates in the system and generating a warning.
It seems to me that this kind of thing should be built-in. If NetSuite went through the trouble of creating a Duplicate Detection scheme for duplicates AFTER they are created, shouldn't there be something in place to stop them in the first place??
In any event, have I missed some sort of feature which does this? Is there a better method than my naive implementation below?
function onFieldChanged(type, field) { var type = 'edit'; if (nlapiGetRecordId() == '') { type = 'create'; } //When a Contact is being Created or Edited, on-save ALERT the user if ANY existing NetSuite Contacts on ANY Customer/Prospect/Lead has an exact EMAIL match and request CONFIRMATION that this is indeed //non-duplicate Contact. This effectively FORCES users to check for Contact duplicates before they make a new one, even if the name is not already in use. if ( field == 'email' || field == 'altemail' || field == 'custentity_additional_email_1' || field == 'custentity_additional_email_2' ) { var email; if (field == 'email') { email = nlapiGetFieldValue('email'); } else if (field == 'altemail') { email = nlapiGetFieldValue('altemail'); } else if (field == 'custentity_additional_email_1') { email = nlapiGetFieldValue('custentity_additional_email_1'); } else if (field == 'custentity_additional_email_2') { email = nlapiGetFieldValue('custentity_additional_email_2'); } if (email != '') { //Search ALL Contact e-mail fields for the specified e-mail address. var filters = new Array(); filters[0] = new nlobjSearchFilter('formulanumeric', null, 'equalto', 1); filters[0].setFormula('CASE WHEN ({email} = '' + email + '' OR {custentity_additional_email_1} = '' + email + '' OR {custentity_additional_email_2} = '' + email + '') THEN 1 ELSE 0 END'); filters[1] = new nlobjSearchFilter('isinactive', null, 'is', false); if (type == 'edit') { filters[2] = new nlobjSearchFilter('internalid', null, 'noneof', nlapiGetRecordId()); } /* //https://usergroup.netsuite.com/users/showthread.php?t=19580&highlight=setOr //setOr seems to be undefined for client scripts! filters[0] = new nlobjSearchFilter('email', null, 'is', email).setOr(true); //altemail is a SPECIAL BUILT IN FIELD WHICH IS ALWAYS SEARCHED ALONGSIDE 'email' DO NOT INCLUDE IT SEPARATELY OR AN EXCEPTION IS THROWN! //In other words, a match on 'email' field implicitly includes an "OR match on altemail" every time. //filters[] = new nlobjSearchFilter('altemail', null, 'is', email).setOr(true); filters[1] = new nlobjSearchFilter('custentity_additional_email_1', null, 'is', email).setOr(true); filters[2] = new nlobjSearchFilter('custentity_additional_email_2', null, 'is', email); if (type == 'edit') { filters[3] = new nlobjSearchFilter('internalid', null, 'noneof', nlapiGetRecordId()); } */ var columns = new Array(); columns[0] = new nlobjSearchColumn('internalid'); columns[1] = new nlobjSearchColumn('entityid'); columns[2] = new nlobjSearchColumn('company'); var searchResults = nlapiSearchRecord('contact', null, filters, columns); if (searchResults != null) { alert('At least one other Contact in the NetSuite database (Name: ' + searchResults[0].getValue('entityid') + ' ID: ' + searchResults[0].getValue('internalid') + ' at company: ' + searchResults[0].getText('company') + ') shares this e-mail! Are you sure this is not a duplicate record? Please confirm before saving this record.'); } } } } EDIT: In other words, do I have to, for example, implement this sort of code on entityid, addresses, phone number, AND add all the fuzzy logic to do similarity comparisons (like John Doe vs. Johnathan Doe) or is there some EASY way to do this?