Salesforce coding lessons for the 99%
Finally, Apex tutorials for point-and-click admins! Written by a self-taught Google engineer.
  • Beginner Tutorials
    • Apex
    • Certifications
    • Career Info
    • Technical Architect
    • Visualforce
    • Videos
  • Apex Academy
  • Success Stories
  • About Me
  • Misc
    • Mailbag
    • Challenges
    • Links
    • Login to my Org
Follow @dvdkliuor SUBSCRIBE!

Using Apex variables inside a SOQL query

November 4, 2013

Preface – This post is part of the Write Your First Intermediate Trigger series.

Now that you know combining Apex with SOQL is the secret sauce to mastering triggers, let’s learn exactly how to do this!

First off, know that the output of every SOQL query is an Apex list. However, we want to take this one step further. We want to inject Apex directly into the SOQL query itself!

What we want to do is create a bind variable. A “bind variable” is simply the term for an Apex variable used inside a SOQL query.

Salesforce knows you’re using a bind variable when you precede your Apex variable with a colon (:) – here’s an example:

String myFamilyName = 'Liu';
List<Contact> myFamily = [SELECT FirstName, Best_Friend__c
                           FROM Contact
WHERE LastName = :myFamilyName];

Don’t forget the colon (:), it’s small but it’s the most important part!

Now, why use a bind variable when we could’ve simply done LastName = ‘Liu’ instead?
The reason is we don’t always know what the value of our bind variables are! Here’s another example that should make this more obvious:

String familyFriendId = myFamily[0].Best_Friend__c;
List<Contact> friendsOfFriend = [SELECT FirstName, LastName FROM Contact
WHERE Best_Friend__c = :familyFriendId];

See what we did there?
First, we used an index to get the first member of my family. Then, we used dot notation to get the ID of the Best Friend of this family member (“Best Friend” is a lookup field to the Contact object). Finally, in our SOQL query, we used a bind variable to find every other contact in our database that has the same best friend!

We could’ve repeated this with a loop through all of my family members if we wanted to, querying all family friends of friends… aka my third degree connections! LinkedIn… your days are numbered!

Next post: How to write a deduping trigger for leads and contacts!

18 Comments
Pierre D.
January 24, 2018 @ 2:49 pm

Is there a way to do something like this?

SELECT FirstName, LastName
FROM Contact
WHERE FirstName = LastName;

Reply
    David Liu
    January 24, 2018 @ 10:12 pm

    Yup, just store the LastName as a variable, then use the technique in this post to include it!

    Reply
Anonymous
July 11, 2017 @ 10:03 pm

Thanks Usefull

Reply
John doe
June 9, 2017 @ 4:48 am

Hi David thanks for your help, could you help me with this question please :

I have a custom object called Message__c and I am trying to compare a picklist field containing profile names with the current user’s profile in order to fetch an associated text field of this same record. So that is what I tried to do :

Id profileId = userinfo.getProfileId();
String profileName=[Select Id,Name from Profile where Id=:ProfileId].Name;
text = [SELECT Text__c
FROM Message__c
WHERE Profile__c includes (‘profileName’)
LIMIT 1];
but it seems that i should write the where clause differently to get the comparison.
Thanks for your help I really appreciate it!

Reply
    David Liu
    June 12, 2017 @ 10:15 pm

    You want to use LIKE!

    Reply
Thomas Dequesne
June 9, 2017 @ 4:46 am

Hi David thanks for your help, could you help me with this question please :

I have a custom object called Message__c and I am trying to compare a picklist field containing profile names with the current user’s profile in order to fetch an associated text field of this same record. So that is what I tried to do :

Id profileId = userinfo.getProfileId();

String profileName=[Select Id,Name from Profile where Id=:ProfileId].Name;

text = [SELECT Text__c
FROM Message__c
WHERE Profile__c includes (‘profileName’)
LIMIT 1];

but it seems that i should write the where clause differently to get the comparison.

Thanks for your help I really appreciate it!

Stefan

Reply
Prabhu
October 9, 2016 @ 1:11 pm

Hi David,

Please help me in this issue, when I am trying to create a contact its not updating with its associated account record field value. Required your help in this case.

trigger fill on Contact (after insert) {

For (Contact c : Trigger.New) {
Id accId = c.AccountId;
Account acc = [Select Id,acFieldOne__c From Account Where Id = :accId];
String Value = acc.acFieldOne__c;
Contact con = [Select Id, coFieldOne__c From Contact Where Id = :c.Id];
con.coFieldOne__c = Value;

}
}

Reply
    Saurabh Sood
    January 29, 2018 @ 7:41 pm

    Try to use before insert or add update dml operation in the end

    Reply
Anonymous
October 9, 2016 @ 1:05 pm

Hi David,

I need your help, I hope the code below is correct to mu knowledge.
But when I am trying to insert a contact, the trigger is not stamping the lookup field value of an associated account record.
Please help me in this case. Thanks!

trigger fill on Contact (after insert) {

For (Contact c : Trigger.New) {
Id accId = c.AccountId;
Account acc = [Select Id,acFieldOne__c From Account Where Id = :accId];
String Value = acc.acFieldOne__c;
Contact con = [Select Id, coFieldOne__c From Contact Where Id = :c.Id];
con.coFieldOne__c = Value;

}
}

Reply
Gouthami Redlapalli
February 9, 2016 @ 3:17 am

if an object having containing multiple records how can we combine two or three records data using SOQL ??

Reply
    David Liu
    February 10, 2016 @ 4:33 pm

    Can you provide an example? Thanks!

    Reply
    Saurabh Sood
    January 29, 2018 @ 7:44 pm

    Group by is command in SOQL to merge record into one
    Thanks !

    Reply
Bhuvarahan,V.LN.
May 6, 2015 @ 7:57 am

Hi David

I would like to know whether i might be able to insert a SOQL Query inside a Apex trigger which I’ve already programmed on the salesforce Developer console.

Reply
radha
February 12, 2015 @ 11:06 am

How can I assign the result of this query
“SELECT Id, Name, Industry, AnnualRevenue,
( SELECT Name, Email, BirthDate FROM Contacts )
FROM Account
to a “List”?
thank u

Reply
    David Liu
    February 12, 2015 @ 6:30 pm

    List<Account> accs = SOQL

    for (Account a : accs) {
    List<Contact> ctcs = a.Contacts;
    }

    Reply
    radha
    February 13, 2015 @ 11:27 am

    thank u so much

    Reply
Riyaz
June 8, 2014 @ 9:35 am

Hi David

This blog is very helpful. I have learnt allot from this blog and within a day I wrote a trigger for the update the fields in the same object. However, I am not sure yet whether I am ready for advanced level of trigger writing. But it would be really helpful if you can help me out and point to my mistake maybe correct it. I am trying to write a trigger that will create order object when another custom object pen with customer field black pen is updated.So basically the order is created with the information from accounts and contract. Here is the code.

trigger Createorders on pen__c(after insert) {
List createorders = new List {};
for (pen__c o : trigger.new) {
if (o.black_pen__c == ‘black’) {
List obj = [SELECT Name FROM Account Where black_pen__c = ‘black’];
List obj1 = [SELECT Contractnumber FROM Contract where black_pen__c__c = ‘orange’];
createorders.add(new order (
name = obj[0].Name, EffectiveDate = date.today(),status =’Draft’,contract = [SELECT Contractnumber FROM Contract where black_pen__c = ‘orange’]));
} }
try { insert createorders;
} catch (Exception Ex)
{ system.debug(Ex); } }

Error I get in the debug log is

system.dmlexception:Insert Failed.First exception on row 0 ; first error:Required_field_missing required field:[]

I am stuck here. If you can help me please..:)

Reply
    David Liu
    June 10, 2014 @ 9:15 pm

    Required fields are missing on your Order!

    Try making an Order normally through the UI, then make sure to have values for all the required fields in your code!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *


*

*

Theme: Simple Style by Fimply