Salesforce coding lessons for the 99%Finally, Apex tutorials for point-and-click admins! Written by a self-taught Google engineer.
Salesforce coding lessons for the 99%
Preface: this post is part of the Bulkify Your Code series.
If Apex could be defined by a single sentence, it would be this Governor Limit error message:
Too many SOQL queries: 101
Every Apex developer, without exception, must face this error. This is the monster that you must slay in order to become an adult developer. It’s so important that I’ve dedicated an entire chapter to it.
Know that this limit doesn’t get its own chapter because it’s complicated – it just occurs so often!!
In this chapter I’ll teach you why you’re getting this error and how you can beat it!
And what better way to do this than to create a “broken” trigger and fix it!
// Email-to-Case always defaults the creator - this fixes that! trigger FixCreator on Case (before insert) {for (Case newCase : Trigger.new) {if (newCase.SuppliedEmail != null) {User trueCreator = [SELECT Id FROM User WHERE Email = :newCase.SuppliedEmail ORDER BY LastLoginDate DESC LIMIT 1];newCase.CreatedById = trueCreator.Id; } } }
(Ask Salesforce for the Create Audit Fields permission in order to set system fields like CreatedById!)
Even though you won’t get any errors when you save this trigger, you will get the 101 SOQL query limit if your trigger runs under certain conditions.
Try to figure out why this limit can happen with this trigger – we’ll talk about how to get around it next! I’ve given some hints by highlighting important lines in green!
Next post: Bulkifying your code!
Hi David,
Is it possible to turn on Create Audit Fields on Developer Edition? I keep clicking on contact us, but it takes prompts me to login just keeps going on a loop. Any direction would be helpful.
Probably not possible, I doubt Salesforce will support tickets for non-paying customers!
No need to contact support any more, this is now a feature that can be turned on in the user interface settings.
David,
Why are we assuming that the person who sends an Email will always be a user in our system? I’m asking because as far as I know, in most business cases/companies , the ‘customers or Email senders’ are not a part of your Salesforce User List and they have no log-ins.
If I’m understanding it right, for your code to work, the sender must be a user of the system and multiple such users are linked to the same E-mail ID. Please correct me if I’m wrong.
I think this is very dependent on your org. Every org I’ve worked in uses cases internally only, but that’s not to say that a lot of orgs use it externally as well.
In any case, it’s more an exercise to understand the code, not the business context behind it ha ha ha.
Understood. Thanks!
Is there an alternate example to use if one is too impatient to wait to ask salesforce to allow editing of CreatedById? :) i’m in the zone with your blog posts. I guess I could try my own …. Hmmmm
You can use a custom Created_By_ID__c lookup field instead =)
Please help to get it resolve:: Error when click on save button
/ Email-to-Case always defaults the creator – this fixes that!
trigger FixCreator on Case (before insert) {
for (Case newCase : Trigger.new) {
if (newCase.SuppliedEmail != null) {
User trueCreator = [SELECT Id FROM User
WHERE Email = :newCase.SuppliedEmail
ORDER BY LastLoginDate DESC
LIMIT 1];
newCase.CreatedById = trueCreator.Id;
}
Error: Compile Error: Field is not writeable: Case.CreatedById at line 8 column 7
You need to ask Salesforce to turn on the “Audit System Fields” permission for you!
Hi David, This is the best of example and explanation I found on internet about clear cut demarcation of bulkification.Previously, I was always getting stuck @ this point.Excellent post and excellent precised explanation.
Hi David,
You are correct that the “Too many SOQLs” error occurs very frequently. No doubt that the code should be written efficiently to avoid this error but just in case you encounter this in future here is something that should be helpful.
One of my colleague shared a nice tip to debug this error message, he asked me to extract all the SOQLs in the debug log, get their execution counts and the objects on which these SOQLs were fired. It was quite a tedious task as he asked me to extract all the SOQLs from debug logs into excel and then I had to use some delimiters and formulas to get the query counts.
Luckily, I managed to find a “One Click” solution for this. I wrote a google chrome extension that does the following :
It helps you extract and analyze the SOQL queries from the debug logs of Salesforce. All you need to do is open up any Debug Log on Salesforce and then on just “One-Click” of the extension it would display the following information:
1. All the SOQL queries along with their execution count i.e. the number of times each query was executed.
2. The extension would also display all the Objects (Standard/Custom) on which these SOQL queries are executed.
For ex: If you have got queries that hit the Account object 5 times, you might want to have a relook at the code and optimize the code in a way that you get all the data from the Account object in single SOQL query (instead of querying it 5 times).
These statistics would help in debugging the error “Too Many SOQLs”. and they would also help you to optimize the performance of your application.
You may get it from : https://chrome.google.com/webstore/detail/soql-extractor-and-analyz/deihalhihjdilndoidoclmhmgllebkfj
Just thought of sharing this you as you might find it helpful. I will appreciated your thoughts and feedback for this one.
Thanks!
Thank you Rajiv, very helpful for those needing to clean up some legacy code!
Eagerly waiting for next posts….Finger cross…
Hello David,
I will write this below Query above the for loop and retrieve all the newly inserted accounts contacts and then perform the operation in for loop
List contacts = [SELECT Id,BillingStreet,Billingcity,BillingState,BillingCountry from contact c where c.accountid IN Trigger.New)
Is this correct?
Neha, Its not correct “List ct = [SELECT Id,BillingStreet,Billingcity,BillingState,BillingCountry from contact c where c.accountid IN Trigger.New]”
Good job to both of you! Don’t forget to include the “:” before Trigger.new!
“Using Apex variables in a SOQL query”
https://www.sfdc99.com/2013/11/04/using-apex-variables-in-a-soql-query/
[SELECT Id,BillingStreet,Billingcity,BillingState,BillingCountry from contact c where c.accountid IN :Trigger.New];
I’ve changed up the trigger actually because the new one is a little better at explaining my focus for this chapter (you’ll see in the next few posts!)
David
Don’t forget to include the semi-colon (:) before Trigger.new!
“Using Apex variables in a SOQL query”
https://www.sfdc99.com/2013/11/04/using-apex-variables-in-a-soql-query/
[SELECT Id,BillingStreet,Billingcity,BillingState,BillingCountry from contact c where c.accountid IN :Trigger.New];
Also, I changed the trigger up because the new one does a better job of hitting governor limits!
David
I think.. we should not write a SOQL Query inside the for loop as the SOQL query executes once for each iteration and may surpass the 100 SOQL queries limit per transaction
Excellent observation Neha =)
You’re on your way to becoming a great developer!!
Thank you so much :)
How do we achieve this scenario without writing SOQL query inside for loop? is there any way?
Keep reading and you’ll find out!
Hey David,
I’m waiting eagerly for your next posts. Your tutorials definitely changed the way I look at apex today. Thank you so very much.