Implementing Global Search for identifying Duplicate Records

Question

I am implementing a VF page which will be on the record contact detail page. I am making use of current record id to pull the matched records on to this VF page. Controller class for the VF page:

...
public Contact curCont{get; set;}
public List<Contact> relatedList {get; set;}
...
List<Contact> conList =[SELECT Id, Name, Email FROM Contact 
                            WHERE Id = :ApexPages.currentPage().getParameters().get('Id')];
    
    if(conList.size() > 0){
        curCont = conList[0];
        
        relatedList = [SELECT Id, Name, Email, AccountId
                       FROM Contact
                       WHERE (Email =: curCont.Email OR Name =: curCont.Name) 
                       AND Id !=: curCont.Id];
     }
return relatedList;
...

This does the job very well, but my only concern is: We may have Contact records with very slight differences, such as: Roger Williams is same person as Roger J. Williams. End users might end up creating so many records in this fashion.

How can I query all these extra-character-added records(the way Salesforce Global Search functions) and display on to my page, that way I can provide the end users extra capability to identify duplicates more further.

Update:

Current Global Search results when I search for word: john

If I open up contact: John Test 1, my vf page pulls only two records based on my above query: (Email =: curCont.Email OR Name =: curCont.Name)

Problem trying to solve:

How can I get the same results as from the Global Search results? Like 'john' bring up all contacts that has john in any part of the name?

Answer 1

Let me try to add a little more context here in the hope of being more useful.

Global Search is analogous to SOSL, the query format Salesforce provides for free-text searches that span one or more objects and whole sets of fields. SOSL is highly applicable for doing "fuzzy" searches, like in the Salesforce Global Search function. When you perform a SOSL search, you specify some broad set of fields (or IN ALL FIELDS to cover everything SOSL can index) to search, and a much more limited set of fields to return. In the examples below, the field names in parentheses after the object name are the fields being returned to you in your search results.

The SOQL query you're currently using can also provide a certain level of fuzzy matching, but it is implemented very differently. A SOQL query only matches against a specific object, and matches specific fields in exactly the way it's told to - so if you utilize a wildcard search like

SELECT Id, Name FROM Contact WHERE Name LIKE '%John%'

(where % is the wildcard character), you'll get back any contact whose Name field contains John, at any position. Conversely, you will not get back a Contact whose Email field contains John - you would need to run a separate query, or OR together criteria, to get that behavior.

You can perform Dynamic SOSL (and similarly Dynamic SOQL) to use user input in the WHERE or FIND clauses of your queries. You do this by constructing a query as a string using either static or dynamic search parameters, and wild-cards, in Apex, and passing it to the Database.query or Search.query method.

The examples below show how to do this for SOSL, because it seems to be closer to what your objective is. You can also construct queries using strings in a similar manner to be run as SOQL.

String q = 'FIND \'John*\' IN ALL FIELDS RETURNING Contact(FirstName, LastName)';
List<List<SObject>> results = Search.query(q);

or, with dynamic data:

(Note that searchTerm represents whatever Apex variable you are using to store your desired search term).

String q = 'FIND \'' + String.escapeSingleQuotes(searchTerm) + '*\' IN ALL FIELDS RETURNING Contact(FirstName, LastName)';
List<List<SObject>> results = Search.query(q);

SOSL gives you back a List<List<SObject>>, since you can return results from more than one type of object. The lists are in the same order as they are listed in the RETURNING clause. In a situation where you're only returning one kind of object, you can extract the first List<Sobject> and cast it to a list of the appropriate type like this (where results is as above).

List<Contact> contacts = (List<Contact>)results[0];

This can then be acted upon as any other List<Contact>, including iteration.

For more details on Dynamic SOSL, I'd recommend the Apex Developer Guide under Dynamic SOSL. There's also a great reference on wildcards from the SOSL and SOQL Reference, which covers all aspects of search syntax.

Now, which of these solutions do you need to adopt? I'm not sure. Adding more detail to your objectives in your question might help. If what you want is a fuzzy match on one or two fields on a single object, SOQL is going to be the way to go. If, conversely, you want a Global Search-style, broad-based search across multiple fields and one (or more) object(s), you probably want SOSL. Either way, you will likely need to construct a dynamic query.