function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
IanM1963IanM1963 

Triggers and Governor Limits

Hi, I know this will have been posted before but I have quite a complicated issue here (for me cos I'm new to Force dev) that I'd like to share with you guys to see if there is anything I am missing.

 

I have a requirement for a bulk upload of records from another system that will be uploaded using the DataLoader from a csv file. No problem so far, have that covered and working.

 

The problem is that there may or may not be lookup field values in the file rows that can be used to form relationships within the SalesForce system and if so they need hooking up on import.

 

So this means that somehow, for every line of the import record I need to look at an Account record to see if certain values match. If not I then need to link the import record to know values for dummy Account objects marked as "UNKNOWN".

 

The spec is they want a trigger to do this and I have come up with three possible solutions, all of which run into Governor Limits pretty quickly. I cannot find a way of having any more than 99 records in the import file at a time?

 

OK so here is my code for each solution. I'd like you to cast your eyes on the code and see if there is anything simple that I have missed that would allow me to do this without running into the Governor Limits.

 

Here's the code...

 

First idea I had was to use a MAP so I would get a list of Accounts in one hit. From there I'd go for a match on Account Id. Unfortunately I have only one AccountId in the file(possibly). The other numbers are from a legacy system so I can only match on those which means using SOQL?

 

trigger customUpload on Custom__c (before insert, before update) 
{
	

// Map for THE MAP WAY //

Map<Id,Account> accountMap = new Map<Id, Account>([select Id,  Customer_Number__c from Account]);




	
	for (Custom__c customObj : trigger.new)
	{
				
		
		/*###################################################################################################################################################################################*/
		/*																																																																																										*/
		/*THE MAP WAY - it only solves on problem partially in that we can easily look for a match on Id in the MAP. However, we can only have a MAP of the form <Id,SOBject> in other 
		/* words, we can only use an actual Id to search on Account key field. We only have a key field for one attribute which is Lookup1__c or Lookup1 in the import file. Another issue is that this works
		/* by relying on catching exceptions which are thrown when no match shown. Don't like doing that but still works.
		/
		/* SOLUTION if using this method would be to ensure that instead of Lookup2 and Lookup3 in the import file we actually have SalesForce Ids as Lookups that we can use to get matches from the 
		/* map using those Account Numbers for Lookup1 and 2 e.g. instead of Lookup2__c matching on Account.Customer_Number__c we match on Lookup__c (an Account number due to it being a lookup). I do not know if this is possible though...
		/*   
		/* AT 100 ROWS ON INPUT FILE THIS FAILS WITH TOO MANY SOQL QUERIES                                                                                                                   */
		/*###################################################################################################################################################################################*/
		
		
		try
		{
			Account Lookup1Match = accountMap.get(customObj.Lookup1__c);
			
		}
		catch (Exception e)
		{
			Lookup1Unknown();
		}
		
		
		// Cannot use this technique for the the other two because these are not SalesForce Ids and we can only build a map with an Id and an SObject to enable searches like this as far as I am aware.
		// Thus we have to resort to using SOQL queries...
		
		// Get the Account with the matching Lookup2__c to Account.Lookup2__c
		List<Account> Lookup2Match = [Select Id from Account where Customer_Number__c = :customObj.Lookup2__c];
			if (Lookup2Match.size() == 0)
			{
				Lookup2Unknown();
			}
		// Get the Account with the matching Lookup3_RID to the Account.Lookup3__c
		List<Account> Lookup3Match = [Select Id from Account where Customer_Number__c = :customObj.Lookup3__c];
			if (Lookup3Match.size() == 0)
			{
				Lookup3Unknown();
			}	
	
	
	}
	
	public void Lookup1Unknown()
	{
		system.debug('Lookup1Unknown');
	}
	
	public void Lookup2Unknown()
	{
		system.debug('Lookup2Unknown');
	}
	
	public void Lookup3Unknown()
	{
		system.debug('Lookup3Unknown');
	}	
	
	
	
}

 So I thought I'd try the SOQL way.... Just look for a match using SOQL for every line in the import file...

 

trigger testingUpload on Custom__c (before insert, before update) 
{
	






	
	for (Custom__c customObj : trigger.new)
	{
		// Get the Account with the matching Lookup1 to Account Id...
		
		
		/*###################################################################################################################################################################################*/
		/*THE SOQL WAY - runs into SOQL limits very quickly due to invoking the 3 queries for every line in file to insert */
		/* 
		/* FOR 100 LINES IN THE INPUT FILE THIS FAILS BY GOING OVER THE 100 LIMIT ON SOQL QUERIES.                                                                                */
		/*###################################################################################################################################################################################*/
	
		
		
		


		// Get the Account with the matching Lookup1__c to Id on Account...
		List<Account> Lookup1Match = [Select Id from Account where Id = :customObj.Lookup1__c];
			if (Lookup1Match.size() == 0)
			{
				Lookup1Unknown();
			}
		
		// Get the Account with the matching Lookup2__c to Account.Lookup2__c
		List<Account> Lookup2Match = [Select Id from Account where Customer_Number__c = :customObj.Lookup2__c];
			if (Lookup2Match.size() == 0)
			{
				Lookup2Unknown();
			}
		// Get the Account with the matching Lookup3_RID to the Account.Lookup3__c
		List<Account> Lookup3Match = [Select Id from Account where Customer_Number__c = :customObj.Lookup3__c];
			if (Lookup3Match.size() == 0)
			{
				Lookup3Unknown();
			}
		
	
	}
	
	public void Lookup1Unknown()
	{
		system.debug('Lookup1Unknown');
	}
	
	public void Lookup2Unknown()
	{
		system.debug('Lookup2Unknown');
	}
	
	public void Lookup3Unknown()
	{
		system.debug('Lookup3Unknown');
	}
	
	
	
	
}

 As you can see, it runs into the 100 query limit pretty quick...

 

So then I thought I'd try the CODE way. Basically get an Account list in one hit using SOQL then loop through the list for every line in the input file. Yes I know, very innefficient! :-\

 

trigger testingUpload on Custom__c (before insert, before update) 
{
	

List<Account> accountList = new List<Account>([select Id,  Customer_Number__c from Account]);




	
	for (Custom__c customObj : trigger.new)
	{
		// Get the Account with the matching Lookup1 to Account Id...
		
		
		/*###################################################################################################################################################################################*/
		/*THE CODE WAY - runs into scripting lines over 200,0000 */
		/*###################################################################################################################################################################################*/
	
		
		for (Account acct : accountList) 
			{
				
				
				if (customObj.Lookup1__c == acct.Lookup1__c)
				{
					
					customObj.Lookup1__c = acct.Id;				
				}
				else 				
				{
					
					lesseeUnknown();
				}
				
				
				if (customObj.Lookup2__c == acct.Lookup2__c)
				{

					customObj.Lookup2__c = acct.Id;				
				}
				else
				{
					
					shipperUnknown();
				}
				
				
				
				if (customObj.Lookup3__c == acct.Lookup3__c)
				{
					
					customObj.Lookup3__c = acct.Id;
				}
				else
				{
					Lookup3Unknown();
				}
			}
		
	
	}
	
	public void Lookup1Unknown()
	{
		system.debug('Lookup1Unknown');
	}
	
	public void Lookup2Unknown()
	{
		system.debug('Lookup2Unknown');
	}
	
	public void Lookup3Unknown()
	{
		system.debug('Lookup3Unknown');
	}
	
	
	
	
}

 I would be very grateful if someone could point me in the right direction with this one. It seems I can get code that works but not for many input file lines... The requirement is to eventually automate this process you see so the uploads are done unattended.

 

My thoughts are that it's asking a trigger to do a lot of work BUT this is what the client specified. I value your thoughts on this.

 

Thanks and regards,

 

Ian

Best Answer chosen by Admin (Salesforce Developers) 
BritishBoyinDCBritishBoyinDC

You're on the right track I think...bascially, use Maps as an in-memory join, and then I think you can do this...

 

Couple of things to note to make things a bit easier for you...

 

A Map is just a Key-Value construct - and the key can be any primitive (e.g. a String)

 

To check if map contains a key, you can use the containskey() method on map...then if true, you can do the get...

 

So I would do an intial loop through the records in the trigger, and then build a map for all the Lookup Keys in your Trigger set...

 

Then loop again, and look for the keys...

 

I haven't compiled this or anything, but I think it will give you the right idea...

 

trigger customUpload on Custom__c (before insert, before update) {

//If we use a string as key, we can have ids and customer numbers in same map
Map<String,Account> accountMap = new Map<String, Account>();
				
//Two sets for two different queries
Set<Id> Lookup1 = New Set<Id> ();
		
Set<String> Lookup2and3 = New Set<String> ();
	
//First loop, to build list of lookups for this trigger iteration
//Note - we're checking all three, but if lookup1 trumps 2, then uncomment break to be more efficient

for (Custom__c customObj : trigger.new) {

//First look for Id matches in Lookup1
If (customObj.Lookup1__c != null) {
Lookup1.add(customObj.Lookup1__c);
//break;
}

//and then look for customer number matches
If (customObj.Lookup2__c != null) {
Lookup2and3 .add(customObj.Lookup2__c);
//break;
}

If (customObj.Lookup3__c != null) {
Lookup2and3 .add(customObj.Lookup3__c);
}


} //end first loop

//now query for lookup data - first for matching Ids
For (Account a: [Select Id, Customer_Number__c  from Account where Id IN :Lookup1]) {

	accountMap.put(a.Id, a);
}

//Then for matching customer numbers
For (Account a: [Select Id, Customer_Number__c  from Account where Customer_Number__c  IN :Lookup2and3]) {

	accountMap.put(a.Customer_Number__c, a);
}


//now loop again, and we can check if any of the three lookup fields are in the map...
	for (Custom__c customObj : trigger.new)	{
		Account LookupMatch;
	
		if (accountMap.containskey(customObj.Lookup1__c) {
		LookupMatch = accountMap.get(customObj.Lookup1__c);
		}

		else if (accountMap.containskey(customObj.Lookup2__c) {
		LookupMatch = accountMap.get(customObj.Lookup2__c);
		}

		else if (accountMap.containskey(customObj.Lookup3__c) {
		LookupMatch = accountMap.get(customObj.Lookup3__c);
		}

		else {
		//Not Found...
		}
//do something with LookupMatch 
} //end loop

} //end trigger

 

All Answers

BritishBoyinDCBritishBoyinDC

You're on the right track I think...bascially, use Maps as an in-memory join, and then I think you can do this...

 

Couple of things to note to make things a bit easier for you...

 

A Map is just a Key-Value construct - and the key can be any primitive (e.g. a String)

 

To check if map contains a key, you can use the containskey() method on map...then if true, you can do the get...

 

So I would do an intial loop through the records in the trigger, and then build a map for all the Lookup Keys in your Trigger set...

 

Then loop again, and look for the keys...

 

I haven't compiled this or anything, but I think it will give you the right idea...

 

trigger customUpload on Custom__c (before insert, before update) {

//If we use a string as key, we can have ids and customer numbers in same map
Map<String,Account> accountMap = new Map<String, Account>();
				
//Two sets for two different queries
Set<Id> Lookup1 = New Set<Id> ();
		
Set<String> Lookup2and3 = New Set<String> ();
	
//First loop, to build list of lookups for this trigger iteration
//Note - we're checking all three, but if lookup1 trumps 2, then uncomment break to be more efficient

for (Custom__c customObj : trigger.new) {

//First look for Id matches in Lookup1
If (customObj.Lookup1__c != null) {
Lookup1.add(customObj.Lookup1__c);
//break;
}

//and then look for customer number matches
If (customObj.Lookup2__c != null) {
Lookup2and3 .add(customObj.Lookup2__c);
//break;
}

If (customObj.Lookup3__c != null) {
Lookup2and3 .add(customObj.Lookup3__c);
}


} //end first loop

//now query for lookup data - first for matching Ids
For (Account a: [Select Id, Customer_Number__c  from Account where Id IN :Lookup1]) {

	accountMap.put(a.Id, a);
}

//Then for matching customer numbers
For (Account a: [Select Id, Customer_Number__c  from Account where Customer_Number__c  IN :Lookup2and3]) {

	accountMap.put(a.Customer_Number__c, a);
}


//now loop again, and we can check if any of the three lookup fields are in the map...
	for (Custom__c customObj : trigger.new)	{
		Account LookupMatch;
	
		if (accountMap.containskey(customObj.Lookup1__c) {
		LookupMatch = accountMap.get(customObj.Lookup1__c);
		}

		else if (accountMap.containskey(customObj.Lookup2__c) {
		LookupMatch = accountMap.get(customObj.Lookup2__c);
		}

		else if (accountMap.containskey(customObj.Lookup3__c) {
		LookupMatch = accountMap.get(customObj.Lookup3__c);
		}

		else {
		//Not Found...
		}
//do something with LookupMatch 
} //end loop

} //end trigger

 

This was selected as the best answer
IanM1963IanM1963

Hi,

 

Thanks ever so much for this! It works like a treat!

 

I had thought that there must be a way of building up some in memory lists or maps that I could use but wasn't sure how to do it because for some reason I couldn't figure out a way of building a map without using an actual SF Id plus a value and I had become locked into the idea I had to do it all in one go which of course I didn't.

 

Thanks for the pointers, it works very well indeed and only uses a few thousand scripting lines per 100 lines of input file and just a few lines of SOQL so it scales very well indeed!

BritishBoyinDCBritishBoyinDC

Happy to help...for me, mastering Maps in Apex was the key to unlocking everything else...