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 ContactWHERE 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 ContactWHERE 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!
Is there a way to do something like this?
SELECT FirstName, LastName
FROM Contact
WHERE FirstName = LastName;
Yup, just store the LastName as a variable, then use the technique in this post to include it!
Thanks Usefull
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!
You want to use LIKE!
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
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;
}
}
Try to use before insert or add update dml operation in the end
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;
}
}
if an object having containing multiple records how can we combine two or three records data using SOQL ??
Can you provide an example? Thanks!
Group by is command in SOQL to merge record into one
Thanks !
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.
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
List<Account> accs = SOQL
for (Account a : accs) {
List<Contact> ctcs = a.Contacts;
}
thank u so much
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..:)
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!