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 SOQL: A Beginner’s Guide series.
SOQL is so basic I almost don’t need to explain these examples!
The barebones query:
SELECT Id, Name, Sex__c, BirthDate FROM Contact
This is your basic query that pulls three standard fields and one custom field from every contact.
The WHERE clause:
SELECT Id, Name, Sex__c FROM Contact WHERE DoNotCall = false
You’ll learn to love the WHERE clause – you’ll almost always use one!
Using OPERATORs:
SELECT Id, Name, Sex__c FROM Contact WHERE Phone != null
!= is an operator that simply means “not equal to”.
Using AND and OR in the WHERE clause:
SELECT Id, Name, Sex__c, BirthDate, Likes_Ice_Cream__c FROM Contact WHERE Phone != nullAND DoNotCall = false
SELECT Id, Name, Sex__c, BirthDate, Likes_Ice_Cream__c FROM ContactWHERE ( Phone != null AND DoNotCall = false )
OR Email != null
Notice the use of parenthesis when using multiple AND’s and OR’s! Also – spacing doesn’t matter.
Using TEXT, PICKLIST, or ID values:
SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
FROM Contact WHERE Phone != nullAND Sex__c = 'Female' AND RecordTypeId != '012i0000000ES3H'
Always wrap those three data types in single quotes! That record type is for married contacts…
Using Numbers:
SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
FROM Contact WHERE Phone != null AND Sex__c = 'Female'AND Weight__c > 100
Let’s promote healthy eating guys! Notice how we didn’t use quotes for the number.
Using Dates:
SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
FROM Contact WHERE Phone != nullAND BirthDate = TODAY
TODAY is a special date keyword that makes working with date fields much easier!
Fuzzy Matching:
SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
FROM Contact WHERE Sex__c = 'Female'AND Phone LIKE '%(650)%'
Two things to note here:
Sorting the results:
SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
FROM Contact WHERE Phone != nullORDER BY Name ASC
ASC stands for ascending. Just one of your many sorting options!
Limiting your results:
SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
FROM Contact WHERE Phone != nullLIMIT 5
This simply enforces a max of five records, even though the query might usually return thousands.
Putting it all together:
SELECT Id, Name, Sex__c, BirthDate, Likes_Ice_Cream__c FROM Contact WHERE BirthDate = TODAY AND Sex__c = 'Female' AND RecordTypeId != '012i0000000ES3H' AND Weight__c > 100 AND ( ( Phone != null AND DoNotCall = false AND Phone LIKE '%(650)%' ) OR Email != null ) ORDER BY Name ASC LIMIT 5
Let’s hope this query gets some lucky guy a date!
Login to sfdc99 and try out these queries yourself!
Next post: Example: How to write a cross-object SOQL query (“upwards”)
Hi David! Thanks for the blog post, I certainly learned an item or two on my journey of learning how I can perform a SOQL query on Accounts that do not have a contact, in the related list, that is not marked “Yes” in our picklist field called “Primary Contact”.
Phrased another way… I’m trying to locate Accounts that do not have a Primary Contact, via “Yes” in their Primary Contact field, identified so we can work just those Accounts. Would you happen to have any insights? Thanks!
You’ll want the downwards SOQL query later in this chapter!
Hi David,
when I was started to learn about triggers, apex and soql
why did we want to use triggers in apex and soql used in triggers
and also please tell me how many ways we can use(triggers in apex and soql used in triggers) as like this
Hi David,
when I was started to learn all these topics about triggers, apex and soql
i am confused because triggers can use in apex and soql can used in triggers
so, please give me clarity to which code languages are used in which language
as like triggers can used in apex
soql can used in triggers
Hi David,
when I was started to learn all these topic about triggers apex and soql
i am confused because triggers can use in apex and soql can used in apex
so, please give me clarity to which code languages are used in which language
Both are essential. =). SOQL is a lot easier, you can learn the basics in a day
Hi David ,
Just starting off on here but for the first query had to change Sex_c to regalray1__Sex__c for it to work.
All the custom fields have ‘regalray1_’ prefix in your SFDC99 LEARNER org.
Can run last query by running the below :
SELECT Id, Name, regalray1__Sex__c , BirthDate, regalray1__Likes_Ice_Cream__c
FROM Contact
WHERE
BirthDate != TODAY
AND regalray1__Sex__c = ‘Female’AND RecordTypeId != ‘012i0000000ES3H’
AND regalray1__Weight__c> 100
AND (
( Phone != null
AND DoNotCall = false
AND Phone LIKE ‘%(650)%’ )
OR
Email != null
)
ORDER BY Name ASC
LIMIT 5
Hey David,
would you please tell me how to access picklist values (not label or API name) from our Salesforce org through SOQL query. I have searched and found that one should use “Schema.describeFieldResult”. It would be really awesome if SOQL can do that.
Thanks in advance for your help.
Can a Select * (list ALL columns) from tablename be done using SOQL?
Sadly, no. There are ways to hack this but it involves Apex. Basically you get a list of all possible fields, then include them into your query.
Hi David,
Could you please tell why an IN has been used in this query
Map accounts = new Map(
[select id, name, billingState from Account where id in :accountIds]);
Thanks
Ankita
So the accountIds variable is probably a list or a set of IDs.
Using the IN in the way you’ve written is a shortcut for writing this:
SELECT Id, Name, BillingState
FROM Account
WHERE Id = ‘theFirstIdInTheList’
OR Id = ‘theSecondIdInTheList’
OR Id = ‘theThirdIdInTheList’
etc etc etc
Hope this helps!
David
Hi David,
I know this post is older now, but it seems like it may be better to use the record type name versus the recordtypeid as that id will change between environments like sandbox and prod I believe.
The record type IDs would actually be the same across environments! Especially if you use Change Sets to create new ones!
Good to know! Thanks David!
public class SimpleLogic{
public static void myTest(){
List acc = new List([select name ,(select name from contacts) from account limit 10]);
for(Account acct : [SELECT Name, (SELECT Name FROM Contacts) FROM Account]) {
// Integer count=0;
for (Contact c : acct.Contacts) {
// count++;
system.debug(c);
}
}
}
}
HOW CAN I PASS THIS CONTACT TO ANOTHER METHOD TELL ME ANSWER
If you wanted to write a query tied to a date field that is not a birthday, but was of annual importance, like an anniversary. Is there a “TODAY” option that works for that? I’ve noticed that the birthdate field seems to be special in that regard.
There sure is =)
http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm
Hi David,
Thanks for this; it’s a great quick summary.
I want to extract data from SFDC to store in another database for some analysis; I would like to include the current date in the extraction, but it seems that the TODAY keyword can only be used in a filter condition. Is there any way to do a query like this that will include the current date (or datetime) in the output?
SELECT Id,IsDeleted,MasterRecordId,Name,Type,ParentId,…..,TODAY FROM Account
Obviously, it doesn’t like TODAY or TODAY()… Haven’t been able to find anything in Google…
Thanks!
Dylan
Not available in SOQL but people would normally do this on the Apex side!
In Apex you can do a SOQL query, then stamp the time using Date.today() or DateTime.now()
That’s what I was starting to figure. So, with Apex, would we be able to have every record tagged with the current date right before midnight and then I could do my extraction just after?
Actually that’s a very interesting point!
You can make a formula field on the object that is simply NOW(), then make sure to include that field in your query.
The Apex way I was thinking of was much harder – this idea that you basically came up with is much better! =)
Saying I came up with it is very generous… Now, I also will eventually need to have every record also tagged with the OrgID, and I can’t seem to refer to it in the select statement, so I suppose I could probably do the same thing – create a formula field on the object that will return OrgID and then reference that field in the SOQL query?
That one might be a bit more difficult! Don’t know a good way off the top of my head to do it, but you should know that the ID never changes so you might be able to just save it somewhere else!
Hi David,
How can I get second highest salary from Employee table using SOQL in Salesforce?
“Select max(salary) from employee” will give max salary but i need second highest salary using single query and single record output. How can i get it?
Don’t aggregate the SOQL query, but sort it by Salary DESC. Then get myEmployees[1] to get the second one.
David,
Under Fuzzy Matching , you say ‘Three things to note here’ but list only two ;)
LOL!!!
Updated!
You have no idea how much I appreciate these kind of comments. Typos drive me NUTS and I’d rather someone tell me than finding out the hard way months later!!! THANK YOU Mayank!!!
Hi David,
I am having some trouble with an SOQL Query and was hoping you could shed some light on what I may be doing wrong. I have created an email service and apex class to handle inbound emails. The idea is to mimic email to case functionality for my custom object. The apex class creates a task when the email subject matches a custom field on my custom object. This is working fine at the moment but the problem is I need to check if the email subject contains the value of my custom field rather than is equal to it.
So Basically I need it to check
where custom field contains email.subject
instead of
where custom field = email.subject
I have tried using a combination of wildcards and the Like Operator but cannot figure it out. I have posted my Apex Class code below.
The frustrating thing is I am so close as it works when the subject is an exact match. I just can’t figure out how to use the Like operator correctly or if that is even the correct operator to be using.
Any help would be appreciated.
Thanks for all your efforts on the site. It has been a massive help to me.
global class CreateTaskEmailExample1 implements Messaging.InboundEmailHandler {
global Messaging.InboundEmailResult handleInboundEmail(Messaging.inboundEmail email,
Messaging.InboundEnvelope env){
// Create an InboundEmailResult object for returning the result of the
// Apex Email Service
Messaging.InboundEmailResult result = new Messaging.InboundEmailResult();
String myPlainText= ”;
// Add the email plain text into the local variable
myPlainText = email.plainTextBody;
// New Task object to be created
Task[] newTask = new Task[0];
// Try to look up any Request based on the email subject
Request__c vCon;
try {
vCon = [SELECT Id, Name, Number__c, Test__c
FROM Request__c
WHERE Test__c = :email.subject];
//LIMIT 1];
// Add a new Task to the Request record we just found above.
newTask.add(new Task(Description = myPlainText,
Priority = ‘Normal’,
Status = ‘Inbound Email’,
Subject = email.subject,
IsReminderSet = false,
WhatId = vCon.Id));
// Insert the new Task
insert newTask;
System.debug(‘New Task Object: ‘ + newTask );
}
// If an exception occurs when the query accesses
// the Request record, a QueryException is called.
// The exception is written to the Apex debug log.
catch (QueryException e) {
System.debug(‘Query Issue: ‘ + e);
}
// Set the result to true. No need to send an email back to the user
// with an error message
result.success = true;
// Return the result for the Apex Email Service
return result;
}
}
This is the portion I am having trouble with
try {
vCon = [SELECT Id, Name, Number__c, Test__c
FROM Request__c
WHERE Test__c = :email.subject];
//LIMIT 1];
Thanks.
This one may be better to post on the SFDC99 forums as it’s a bit more involved!
(Also, try to use the code tags so it formats nicely there!)
Hi David
I’m working on creating custom workflow
I’m trying to get multiple records of single rule into a single how can i get it
for eg
I’ve created a new “Rule name’ & i’ve given 2 rule criteria so how can i get those records into a single string
please guide me how to do it
if not too much please give an example as well
Thanks in advance
Very confused on your requirement!
Can you give an example?
Hi David
In my first page I have used getDescribe() function to get all the object of the org & I’m passing that selected object to another page using map where I will get the list of related fields of that object. On another page I have 5 rows as we have in standard workflow for rule criteria
When I’m selecting the field, operator & entering the value & saving it, each row is getting saved as a single record, by appending these fields using formula field example. “accountnumber = 123456” (Formula field of text type).
I’ve created a master detail relationship between the 2 custom objects “Rule” & “Rule Criteria”. The records of the Rule criteria are appearing under the appropriate ‘Rule Name’ related list.
Now my requirement is I need to fetch the records under ‘Rule Name’ one by one & from the object “Rule Criteria” & append them in a single string separating them by AND , example
This is how my page look like
Object : Account (output label for getting the selected object from the previous page)
Rule Name : My Example Rule (Text Field to enter rule name)
Evaluation Criteria : Record Is Created (Dropdown List)
Field Name Operator Value
Account Number Equals 123456
Email Contains example
Select Field None
Select Field None
Select Field None
Now the records in the object will look like
Record 1
Rule Name : My Example Rule
Field : accountnumber
Operator : =
Value = 123456
Formula String : accountnumber = 123456
Record 2
Rule Name : My Example Rule
Field : email
Operator : LIKE \’%{val}%\ (i.e contains)
Value = test
Formula String : email LIKE \’%{val}%\ test
So the query will look like “ String str = select accountnumber, email from account where Record1.formula_string AND Record2.formula _string; “
So how can I get the functionality of the operators selected &
How can I write the string
Please assist
On second thought this is too much code for me to process, sorry!!
Try asking the forums on this site =)
Hi David,
I am a beginner in salesforce and just trying to learn from your materials.
Loving to read it. Hope I will learn it quickly with your help.
Thanks a lot.
You are masterpiece :)
Thank you for the kind words!
Hi David,
I observe the API request were increasing,while querying using workbench.
At the same time if we write query in the developer console will it fall under API request.
Workbench uses the API so it will take up API calls!
It’s a daily limit so it’ll refresh the next day. The limit is relatively high so you must be using Workbench A LOT!!
Hi David I am a Big Fan For youuuuuuuuuuu, nice job thank you so much david i learned so much from this website
Thxxxxxxxxxxxx ^_^
Hi David,
I dont have words to praise you buddy.The way of your explanation is Awesome.
A BiG tHaNkS tO yOu.
SrIk@NtH.
Love you too bud!
Hi David,
Can you give me example for how to query all the fields of a sObject at a time?
In Salesforce there is no SELECT * since you shouldn’t be querying all fields (it will slow down your code and you’ll hit governor limits)!
It’s best to list them out, even if it’s many fields! This way you know your code will not break if an admin suddenly adds many fields.
Hi
I’m trying to do this :
and (A OR B) and C but it doesn’t work.
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions,Affaire,
‘Affaire__c’, ‘Mission_affaire__c’,’Mission__c’, ‘and Type_d_affaire__c = \’Dossier\’ or Type_d_affaire__c = \’PEC\’ and Etape__c = \’Gagnée\”);
Can you help me.
Thanks
Try something like this =)
((x + y > 2) OR (a + b = 3)) AND (c – d < 5)
Hi
thank you for your help. I’ve try what you tell me, with the different field:
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions1, Affaire,// trigger.new,
‘Affaire__c’, ‘Mission_affaire__c’,’Mission__c’, ‘(( Type_d_affaire__c = \’Dossier\’)or( Type_d_affaire__c = \’PEC\’) and Etape__c \’Annulée\’ and Etape__c \’Perdue\’ and Etape__c \’Gagnée\”);
I’ve this error:
MissionRollUpAffairePECTEST: execution of AfterUpdate caused by: System.QueryException: unexpected token: ‘(‘: Class.RollUpSummaryUtility.rollUpTrigger: line 50, column 1
I think my RollupSummaryUtility doesn’t like the filter.
Perhaps I didn’t have the choice of doing the same trigger with my new type of opportunity(affaire)
Thank you very much for your time.
Not familiar with there’ll up code you’re using but it looks like you just have some issues with where you’re placing your parenthesis and single quotes. Double check these and your code will work!
U r so cool David very smart work from you Thanks:)
Hey you are awesome too!
David
DAVID u are really a good tutor…..u just know how to begin the things for a beginner and make them sound on the topics…….kudos to u man……thanks a lot from my side…hope a lot of work like this from u…have a long life
Very kind of you hahaha!
David,
Your site has been invaluable for my first foray into apex. Thanks!
I have created a trigger that essentially creates an autonumber by record type. Using SOQL I get the records by record type and order them such that the largest number is in list position [0]. Works great except the related list Name field is text and on the SOQL order it only sorts by string not number. Thus if there is a string such as 3000000 and the number 9, the order considers the 9 the larger value. I tried using valueOf(Name) to no avail. I prefer to be able to overwrite the name with the new number and thus prefer not to create an additional number field just for the ordering. Is there a solution to this issue?
Thanks again!!
You can simply create a formula field that converts the text string into a number, then sort by that field =)
“Let’s hope this query gets some lucky guy a date!”
Uh…let’s hope not! : )
WHERE
BirthDate = TODAY
Morning David,
LOVE your tutorials. For a complete newb they read simple and straightforward. I do have a question about a trigger I am trying to write. I have an object, Territory Plan, and I need to capture:
YTD Sales by rep
Count of opportunities in each rep’s pipeline
Pipeline by rep
Factored pipeline by rep
I took your suggestion and used Workbench to write the SOQL code which formed perfect:
SELECT Amount FROM Opportunity WHERE StageName != ‘closed won’ AND StageName != ‘closed lost’ AND StageName != ‘scheduled’ AND StageName != ‘shipped’ AND StageName != ‘demo’ ORDER BY Territory_Name__c ASC NULLS FIRST
but I am trying to figure out how to get the totals I need. Do I aggregate the results? Can I total them by rep? Where will the results end up? Am I even barking up the right tree?
If you could just let me know if I am headed in the right direction.
Thanks in advance,
Joey
Excellent job Joey and I’m glad you like the site!
There are two ways to do this:
1. Loop through all the results and total the amounts individually:
https://www.sfdc99.com/2013/10/06/loops-for-and-foreach-loops/
2. Use a GROUP BY function in your SOQL query (by rep) to aggregate the data (best method):
http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm
http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_agg_functions.htm
These queries should happen directly inside your trigger – read on to at least Chapter 4 to see exactly how to combine SOQL and Apex!
David
David,
Thanks so much for taking the time to respond to my question and confirm that my initial thoughts were in the right direction.
Joey
No problem – I enjoy it!
Dlo, i’ll have you know that I searched Google for “salesforce SOQL fuzzy select” and I saw sfdc99 as the top hit. =)
-Pdro
Sweet!!! My SEO sucks and I spend zero time on that so I am happy to hear this hahaha.
Keep on truckin PDro!!!!
Hi David,
Excellent explanation.
Thanks
Srikanth
Hi David,
Simple explanation with some good examples.. The “Sorting Options” link appears to be a bad link..
Pranav
Thanks Pranav, link is fixed! –David