+ Start a Discussion
Greg RohmanGreg Rohman 

Using visualforce for lead/contact deduplication

Hello.

 

I'm building a simple system to look for similar last names in Leads and Contacts. Basically, I want to retrieve a list of all of our Leads, and for each Lead, see a list of Contacts with similar last names.

 

My theoretical approach is to first create lists of all of the prospects and contacts:

// Get all active prospects
SoqlQueryString = 'SELECT Name,LastName,Company,Owner.Name,Status,LastActivityDate FROM Lead WHERE IsConverted=false ORDER BY LastName';
ProspectResults = Database.query(SoqlQueryString);

// Get all contacts
SoqlQueryString = 'SELECT Name,LastName,Account.Name,Salesperson__c,Type__c FROM Contact ORDER BY LastName';
ContactResults = Database.query(SoqlQueryString);

 I'd then iterate over both lists, to find the count of potential matches for each prospect (to display on theVForce page, along with each prospect):

 

 

// Iterate over each prospect, checking all of the contacts for similar last names
for (Lead pr:ProspectResults) {
Integer intCount = 0;
for (Contact cr:ContactResults) {

if (cr.LastName == pr.LastName) {
intCount += 1;
mapContactCount.put(cr.Id,intCount);
}

}
}

 

 Here's where I'm stumped. As I said, on my page, I'd like to display a list of the Leads, along with the count of how many potential matches. I'd then like to display those potential contact matches either automatically or via click (AJAX). I'll try to illustrate below:

 

 

  • Joe Smith (2 matches) click to view matches
    • Joe Smith
    • Mike Smith
  • Steve Jones (0 matches)
  • John Doe (1 match) click to view matches
    • Jane Doe



The "click to view matches" could either programmatically show the Contact list underneath the Prospect (via AJAX), or the Contact lists could always be displayed (although the page would get very long). I'd prefer the former.


On the VF page, I've gotten to the point of being able to display the list of Prospects, but have no idea how to display the count, and then not a clue as to how to then go about displaying the potential Contact matches. I imagine it would involve passing each of the Prospect ID back to an APEX function that returns the Contacts, but not sure how to implement that.

 

 

 

 

 Any help or starting point is greatly appreciated. Thanks.

 

-Greg

 

bob_buzzardbob_buzzard

If you want to display information about the Lead with some additional information that isn't present in the Lead sObject, you will need to use wrapper classes.

 

In this case the wrapper class would wrap the lead and the match count.  Store these in a list and use an apex repeat to iterate over them and extract the information to display.    

 

In order to output a link associate with a particular id, you need use a command link in conjuction with a param component.  In your controller, you'd have a public property of 'selectedLeadId'.  Then when the link is clicked on, 'selectedLeadId' would be populated with the appropriate id and your actionmethod 'showMatches()' would be invoked.  

 

Your action method would then need to build the appropriate objects to show the matches.  I'd add these into the wrapper class also, along with a boolean that indicated whether to show the matches or not.  

 

The VF page would then end up something like the following: 

 

 

<apex:outputPanel id="matchsection">

<table>

<apex:repeat value="{!leadWrappers}" var="leadWrapper">

<tr>

<td><apex:outputText value="{!leadWrapper.lead.Name}"/></td>

<td><apex:outputText value="{!leadWrapper.matchCount}"/></td>

<td>

<apex:commandLink action="{!showMatches}" reRender="matchsection" value="Show Matches">

<apex:param name="leadId" assignTo="{!selectedLeadId}" value="{!leadWrapper.id}"/>

</apex:commandLink>

</td>

</tr>

<apex:outputPanel rendered="{!leadWrapper.showMatches}">

<apex:repeat value="{!leadwrapper.matches}" var="{!match}">

<tr>

<td><apex:outputText value="{!match.Name}"/></td>

</tr>

</apex:repeat>

</apex:outputPanel>

</apex:repeat>

</table></apex:outputPanel>

 

 

 

 

 

Message Edited by bob_buzzard on 10-16-2009 08:18 AM
Greg RohmanGreg Rohman

Thank you for the response. I was able to setup the wrapper class and VF page to perform exactly what I needed, and it worked great on my Developer account. However, when I deployed it to my live account, where I have > 1000 prospects and contacts, I was hitting governor limits.

 

First, I was hitting the 1,000 limit on the number of items in a list. So, I broke my list of all contacts down into smaller lists by last initial. Now, however, I'm getting a "Too Many SOQL Queries: 101" error, and Im not sure how to get past it. The Apex code is below:

 

 

public class AT_ProspectContactDuplicateSearch { // New public List<cProspects> getProspectTree() { return retProspects; } public List<cProspects> retProspects = new List<cProspects>{}; public class cProspects{ public Id ProspectId {get; set;} public String FirstName {get; set;} public String LastName {get; set;} public String Company {get; set;} public String Email {get; set;} public String Owner {get; set;} public List<Contact> contacts {get; set;} cProspects(Lead l){ ProspectId=l.Id; FirstName=l.FirstName; LastName=l.LastName; Company = l.Company; Email = l.Email; Owner = l.Owner.Name; contacts=[select id,Name,FirstName,LastName,Account.Name,Email,Salesperson__r.Name from Contact where LastName =:l.LastName]; } } public PageReference RetrieveAccts() { Set<String> conLName1 = new Set<String>{}; Set<String> conLName2 = new Set<String>{}; Set<String> conLName3 = new Set<String>{}; Set<String> conLName4 = new Set<String>{}; for (contact c:[SELECT LastName FROM contact WHERE (LastName LIKE 'a%' OR LastName LIKE 'b%' OR LastName LIKE 'c%' OR LastName LIKE 'd%' OR LastName LIKE 'e%' OR LastName LIKE 'f%' OR LastName LIKE 'g%') ORDER BY LastName]) { conLName1.add(c.LastName); } for (Lead l:[SELECT id,Name,FirstName,LastName,Company,email,Owner.Name FROM Lead WHERE LastName IN :conLName1 AND IsConverted=false ORDER BY LastName]){ retProspects.add(new cProspects(l)); } return null; } public string getServerUrl(){ return 'https://' + ApexPages.currentPage().getHeaders().get('Host') + '/'; } }

 Thank you in advance for any assistance.

 

-Greg