Hi all,

I'm currently working on a trigger for preventing duplicate Leads from being inserted during the mass data upload. The trigger should treat two leads equal if their Company and Email fields are equal. Since the trigger would fire during the mass data upload (custom), I had to minimize the number of executed SOQL queries in order to cope with SF limits.

Here's how the code looks like atm:

trigger MyTrigger on Lead (before insert) {

Map<String, Lead> leadMap = new Map<String, Lead>();

List<String> fieldNames = new List {'Email', 'Company'}; // it will actually be obtained from somewhere else, but I'm hardcoding it atm

String leadQueryString = '';
String leadDupeError = 'A Lead with the same {0} already exists: {1}';
String fieldSet = '';
Integer i = 0;

List<Set<String>> leadValues = new List<Set<String>>(); // referencing a set by its index in the list doesn't work in dynamic query

for (String fieldName : fieldNames)
{
	Set<String> thisFieldValues = new Set<String>();
	
	for (Lead lead : System.Trigger.new)
	{
		if(String.isNotBlank((String)lead.get(fieldName))) thisFieldValues.add((String)lead.get(fieldName));
	}
	// keeping a corresponding set of values for this field
	leadValues.add(thisFieldValues);
	
	// constructing my query filters
	leadQueryString += (leadQueryString == '' ? '' : ' AND ') + fieldName + ' IN :leadValues[' + i + ']'; // this index doesn't work	
	fieldSet += (fieldSet == '' ? '' : ', ') + fieldName;
	
	i++;
}

List<Lead> possibleDupes = Database.query('SELECT Id, ' + fieldSet + ' FROM Lead WHERE ' + leadQueryString);

List<Lead> dupes = new List<Lead>();

for (Lead lead : System.Trigger.new)
{
	for (Lead pd : possibleDupes)
	{
		boolean match = true;
		
		for(String fieldName : fieldNames)
		{
			if (((String)lead.get(fieldName)) != (String)pd.get(fieldName)) {
				match = false;
				break;
			}
		}
		if (match) {
			lead.addError(String.format (leadDupeError, new List<String> { fieldSet, pd.Id})); // this will prevent the insert
			dupes.add(lead);
			break;
		}
	}
}

}

 Actually, referencing leadValues[i] in query doesn't work :( It gives me the following error:  System.QueryException: unexpected token: '['
To get it work, I had to remove the [] clauses from the query and add two additional lists: 

leadValues0 = leadValues[0]
leadValues1 = leadValues[1]

This way it does work, but it's not dynamic (I want the number of filter fields be more flexible).

So, the question is: is there a way to bypass this error without having to keep the exact number of field values sets?

I would also appreciate any other suggestions on improving my trigger :)