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.
Downwards traversal is when you’re pulling records from a related list. For example, if we’re doing a SOQL query on accounts, downwards traversal could pull data from the account’s contacts. We’ll cover this type of traversal in this post.
As a refresher, upwards traversal is the exact opposite. If you’re querying contacts, upwards traversal could pull data from the contact’s account.
Let’s take a look at a downwards traversal SOQL query:
SELECT Id, Name, Industry, AnnualRevenue,( SELECT Name, Email, BirthDate FROM Contacts )FROM Account
We essentially have two queries in one SOQL statement. One to pull the general account data, and one to pull the contact related list.
Don’t forget these three things when using downwards traversal:
Pro tip: If you’re using custom relationships, add “__r” to the keyword: “Contacts__r”
SELECT Id, Name, Account.Description, CreatedBy.Name,(SELECT Amount FROM Opportunities WHERE Amount > 100),FROM Contact WHERE Account.Secret_Notes__c LIKE '%Tacos%' AND (Sex__c = 'Male' OR Likes_Ice_Cream__c = true)
(SELECT Name FROM Best_Friends__r WHERE Phone != null)
This is a ridiculous query and I’m not even gonna try explaining it. Just know that you can query for some crazy things using SOQL!
Next post: Data types – strings, dates, numbers, and sObjects!
Hello,
How to write query when you’re pulling records where has a related list record?
for example, pull an Accounts that has at least one Contact record?
For that, you would need to do a cross-filter in the WHERE clause using IN. It would look something like this:
SELECT Id, Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact)
The query above would return the ID and Name of all accounts that had a least one contact. If you want to see those contacts in the results, you can add a contact subquery in the SELECT clause like this:
SELECT Id, Name, (SELECT Name FROM Contacts)
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact)
David, Hi – wonderful resource. I’m super new to SOQL and have been playing around with some very simple queries which is working great but I’m stumped on this one I need to do – not sure if it all can be combined into a single statement or if I need to break it into 2 (which both work today, but I’m trying to get as elegant as possible – :) )
So, I can pull detail from an Account record like so:
SELECT Account.Name, Account.Support_Type__c, Account.Assigned_CSM_2__c, Account.TAM_CSM_Assignment__c from Account where Id = ‘xxxxxxxxxxxxxxx’
That works fine, but the Account.TAM_CSM_Assignment__c is a User ID from which I can get the User.Name and User.Email of that user via:
SELECT User.Name, User.Email from User where Id = ”
But, what I’d like to do is see if it’s possible to do both of these with a single query – possible?
Thanks!
One correction to what I posted above the second SELECT should read:
SELECT User.Name, User.Email from User where Id = ‘xxxxxxxxxxx’
where xxxxxxxxxxx is the value returned by the Account.TAM_CSM_Assignment__c query above.
Thanks!
Best resource for me to understand the cross object query. I was getting so frustrated as my query wasn’t working but after going through this page I was able to formulate the query and got the records I was expecting.
Thanks a lot David!
Hello David,
I am just readinG your blog about how to write a cross two object on soql query because I have to do an extraction on Contact and Account object. You will find bellow my request based on your blog :
SELECT Id, MDM_Id__c, OwnerId,
( SELECT Email, Id, MDM_Id__c, Salutation, FirstName, LastName, OwnerId, Name, AccountId FROM Contacts) FROM Account
1) I have put an “s” on Contact object but it is strange… When I click on “list”, I see all my data but I can not bulk CSV, I have this error message : InvalidJob: Unable to find object: Contacts
2) So, I deleted the letter “s” on Contact (because the contact object has not an “s”), instead of it does not working when I click on “List” or “BULK CSV”. I have this error message :
Error message from List
Error message from BULK csv
Could you please help me please ?????
Thank so much in advance for your help
Hi Mouna,
I am having the same issue, did you find something how we can download the CSV file from the workbench?
Thanks
Hello David,
I am just readinG your blog about how to write a cross two object on soql query because I have to do an extraction on Contact and Account object. You will find bellow my request based on your blog :
SELECT Id, MDM_Id__c, OwnerId,
( SELECT Email, Id, MDM_Id__c, Salutation, FirstName, LastName, OwnerId, Name, AccountId FROM Contacts) FROM Account
1) I have put an “s” on Contact object but it is strange… When I click on “list”, I see all my data but I can not bulk CSV, I have this error message : InvalidJob: Unable to find object: Contacts
2) So, I deleted the letter “s” on Contact (because the contact object has not an “s”), instead of it does not working when I click on “List” or “BULK CSV”. I have this error message :
Error message from List
Error message from BULK csv
Could you please help me please ?????
Nb : I am added you on linked , my name is hannachi mouna
Thank so much in advance for your help
Hi David,
I have a parent custom object(Team__c) to the Contact object with Lookup relationship
I need a SOQL query to retrieve the records from Team__c Custom object which at least have one contact associated with it.
I know we can do this via APEX, but just checking Is there a direct way in SOQL?
Thanks in advance
The “downwards” query on this post is what you want!
If you don’t want to return the child object fields but just want to ensure that only Team__c objects with contacts are returned, you can use a cross-filter query in the WHERE clause using IN like this:
SELECT Id, Name
FROM Team__c
WHERE Id IN (SELECT TeamId__c FROM Contact)
Replace TeamId__c with the actual field name of your Team__c lookup field on Contact.
Hi David,
I have a query on the SOQL eg that you have provided –
[SELECT Id, Name, Account.Description, CreatedBy.Name,
(SELECT Amount FROM Opportunities WHERE Amount > 100),
(SELECT Name FROM Best_Friends__r WHERE Phone != null)
FROM Contact
WHERE Account.Secret_Notes__c LIKE ‘%Tacos%’
AND (Sex__c = ‘Male’ OR Likes_Ice_Cream__c = true) ]
My question is how is this query handled if we get more than one value returning from the lines –
(SELECT Amount FROM Opportunities WHERE Amount > 100),
(SELECT Name FROM Best_Friends__r WHERE Phone != null)
Most queries will return multiple lines. Keep reading the next posts in the chapters to see what happens! Basically the results are a list
Hi David,
Thanks so much for this! I wanted to ask though, how do you find if a contact has related lists and, find out what those lists are?
Hi ,
I am new to SOQL and have written the below query and this is not returning data for ‘contact.Name’
“select id,contact.id,contact.Name,account.id from case”
I have also tried
“select id,(select Name from contacts) from case”
and both are not working.
Please help on this.
Thanks.
Hi Akhilesh,
You need to use the below query to retrieve contact name on case
Select case.contact.name from case
Hi!
I’m trying to run a query to check if records from a Custom Object, don’t have an associated account (account is a related list for this custom object). I know that the easiest way would be to create a rollup field in the custom object and check for when it’s zero, but as it’s a one time thing I’m trying to do it with a SOQL query.
Is there any way to do this? Any help would be great!
Thanks a lot! :)
SELECT Name FROM Custom_Object__c WHERE Custom_Object__c_Account__c = null
I created a lookup relationship between custom object and account. Then tried to track if there is related account associated with my custom object.
Understood this way but let me know if question is different.
Hi David,
So if i want to get the count of opportunity line items on account then we should write 2 different soql queries correct?
Also the recalculation should take place whenever we try to delete/add/update the opportunity line items.
Thanks in advance!
There’s a few ways to do this properly! You might be able to do this with one SOQL query but two is fine too =)
Hi David,
I am trying to query User login history object and check for login attempts with status= invalid password.
Can i do this?
I tried but it using the query below in the query editor:
SELECT Id, UserId, LoginTime, LoginType, Browser, Status FROM LoginHistory where Status = ‘Invalid Password’
But i get an error that:
Status FROM LoginHistory where Status = ‘Invalid Password’ ^ ERROR at Row:1:Column:82 field ‘Status’ can not be filtered in a query call
Why is that so..how should i query this?
Kindly let me know
Guess you gotta remove that filter =) Just one of those random Salesforce limits!
[SELECT FirstName,LastName,Email, MobilePhone,You_are_intrested_in__c,Lead_Type_del__c, Courses__c
(SELECT Payment_status__c,Payment_Offline__c,Mode_Of_Payment__c FROM Payment__c where Payment_status__c=’Done’ )FROM Lead];
Please can anyone help me
She is trying to get all the account details (or customer details) who has “Done” the payment.
Hi there! I am trying to query the relationship between Product2 and OpportunityLineItems. So far I’ve found out that there is a Pricebook Entry in between those two, but I haven’t been able to figure out querying down multiple levels.
Any hints? (Awesome site btw, so helpful!)
You should be able to get to the product from the opportunity line item
Hi
How can I query the profile names of opportunity owner , somewhat like this :
select id, profile.name, (select id from opportunities) from user
-but we do not have a “child relationship name” on owner lookup field on opportunity (i have used ‘opportunities’ which is invalid)
Try querying the opp object and doing Owner.Profile.Name instead!
Hi David,
while executing this query :
SELECT Id, Name, Account.Description, CreatedBy.Name,
(SELECT Amount FROM Opportunities WHERE Amount > 100),
(SELECT Name FROM regalray1__Best_Friend__r WHERE Phone != null)
FROM Contact
WHERE Account.Secret_Notes__c LIKE ‘%Tacos%’
AND (Sex__c = ‘Male’ OR Likes_Ice_Cream__c = true)
I am getting this error :
(SELECT Name FROM regalray1__Best_Friend__r WHERE
^
ERROR at Row:3:Column:22
Didn’t understand relationship ‘regalray1__Best_Friend__r’ in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
Could you please explain .
Thanks
The relationship name for that one is “Best_Friends” – you can find this out by navigating to that field and looking at its description!
God bless you! I’ve spent hours on a query that I knew should work and could not figure out way. Thank you for making it clear that we have to use the “Child Relationship Name”.
My pleasure Terry!
When I execute the query “select name, industry, (select email from contacts)from Account where name=’Burlington Textiles Corp of America” i got output : (Account:{Name=Burlington Textiles Corp of America, Industry=Apparel, Id=0012800000ehtcaAAA}). Why i am not getting email of contact present in the account. I am new to sfdc. Could anyone suggest?
There might be no contacts on that account
Why this query is not running
SELECT Id, Name, Industry, AnnualRevenue,
( SELECT Name FROM cases)
FROM Account
Post the error message!
select id,name,Industry,
(Select Name,Email from contacts)
from acccount
error is
Name,Email from contacts) from acccount
^
ERROR at Row:1:Column:64
sObject type ‘acccount’ is not supported. If you are attempting to use a custom object, be sure to append the ‘__c’ after the entity name. Please reference your WSDL or the describe call for the appropriate names.
‘acccount’ – with 3 ‘c’ characters?
Triple ‘c’ in acccount
Great examples, Have watched all your dreamforce videos and this site.. everything is inspiring!!
Thanks!
hi David,
Hi!
Is this the “query equivalent” of creating a report and using cross-filters with the related objects? Thanks!
Pretty much!!!
hi David,
I understood the concept of retrieving the related list data from its parent object , But didnt get how to access the related list records in that query result.
For ex :List acclist = SELECT id, count_contacts__c, (SELECT id, name FROM contacts) FROM Account WHERE id =’0019000001NQXWcAAP’;
how to access the child records.For ex : contacts
Say your account record variable (not the list of accounts) is acc. You’ll access the contacts via acc.Contacts
Thanks David . Thats anwered my query. Ur a champ!!!!!
festlist = [select ID,(select ID,attendie_number_for_festival__c from attendy__r) from festival__c];
this is the error
Didn’t understand relationship ‘attendy__r’ in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
sorry ignore this doubt i got it
Hi David!
Great post! How would l build a SOQL query that would give me the email address of parents with 2nd graders? I am using accounts as households and have contacts related with head of household, spouse, child (if child, separate field tracks their grade).
Thanks!
Peter
I think I’m going to make a challenge out of this!
One solution is to query all 2nd graders, loop through them, and add all their parent’s email addresses to a set. Maybe there is a more efficient way of doing it though =)
David
Thanks David! I’m new to your site (which is great, by the way), what do you mean by challenge and where can I find this?
Thanks!
Peter
I was going to make it a public challenge for others to try to solve =)
https://www.sfdc99.com/challenges/
Hi David,
Maybe you could do a post on advanced SOQL?
->Possible general ideas
count(id) (Saw on a blog, but not sure how to use and when.)
sum(pid= ) (Saw on a blog, but not sure how to use and when.)
union all (Saw on a blog, but not sure how to use and when.)
“AND id IN” (I have used.)
GROUP BY ROLLUP (I have used, but not exactly how I want.)
Is addition/subtraction possible?
->Possible specific examples (the first three I can’t solve)
***Not asking for help solving, just want to take my SOQL to the next level!***
1 Accounts that only purchase products belonging to select Product Families (or have not purchased)
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000AvuYIAS
2 QTY purchased per Product for a specific Account
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000Av7lIAC
3 Orders shipped (invoiced) 5 days or more after the order was entered.
Tried using addition/subtraction with no luck, ie WHERE Order_Date__C = 2014-12-15 AND Order_Date__C <= 2014-12-19
AND id IN (select Order_header__c FROM Order_Detail__c WHERE Warehouse__C = '57')
ORDER BY ORDER_DATE__C
Coming up in Chapter 10!
To be honest, even with many years of coding I’ve never needed to use some of the crazier things, like group by rollup. I will cover the ones that you’ll likely see like SUM, COUNT, GROUP BY, dynamic, etc though =)
Hi David,
I am trying to delete contacts in bulk. However, i am not able to delete contacts which are associated with cases.
Can you help me with a query to find out contacts not related to any case.
Try posting this one on the forums =) Great beginner question!
Hi Nick –
How about this:
*******
//1. Query all Cases in system
List query = [SELECT Id, CaseNumber, ContactId
FROM Case];
System.debug(query);
//2. Create a Set to store Id’s called “contactIdsInCases”, cycle thru the query from #1 above, and insert Case Contact Id’s into “contactIdsInCases”.
Set contactIdsInCases = new Set();
For (Case c: query){
contactIdsInCases.add(c.ContactId);
}
//3. Create a List of all Contacts who aren’t in the “contactIdsInCases” Set above from #2.
List contactsWNoCases = [SELECT Id, Name
FROM Contact
WHERE Id NOT IN :contactIdsInCases];
System.debug(contactsWNoCases);
Hmm….my code looks different from what I typed.
Looks like anything between a less than () sign disappeared. So in other words, it’s not clear what type of object is in my collections (Case, Id, Contact).
Hi David, Your blog has helped teach me the very basics of coding. I have been reading your site for about a week and have “Created” some SOQL queries based on your examples and tips. Two questions. I can’t figure out how to determine relationships. I assume I need to do that see Sales Order Lines tied to a specific account. How do I do these things?
Maybe this information helps: If I go to Order_Detail__C (Object) I see Order_Header_C (Field.) If I go to Order_Header__C (Object) I see Syspro_AccountId__C (Field) which I also see in Account (Object.)
This also might help. I came up with this query and it said Parent Relationships Disabled In Workbench. I fixed it, and now it says “Bind variables only allowed in Apex code”
SELECT Syspro_AccountId__c, NAME, ( SELECT NAME, Line_Value__C, Product_ID__c FROM Order_Detail__c WHERE NAME LIKE = ‘US.8001850%’, Line_Value__C < 5 ) FROM Account WHERE Syspro_AccountId__c LIKE 'US.8001850%' Thanks for any suggestions. Aron
Change this:
LIKE = ‘US.8001850%’, Line_Value__C < 5 To this: LIKE = ‘US.8001850%’ AND Line_Value__C < 5 Then you should be good to go =)
No, same issue. I tried
SELECT Syspro_AccountId__c, NAME, ( SELECT NAME, Line_Value__C, Product_ID__c FROM Order_Detail__c WHERE NAME LIKE = ‘US.8001850%’ AND Line_Value__C < 5 ) FROM Account WHERE Syspro_AccountId__c LIKE 'US.8001850%'
and it still says
MALFORMED_QUERY:
Order_Detail__c WHERE NAME LIKE = 'US.8001850%' AND Line_Value__C
^
ERROR at Row:1:Column:115
Bind variables only allowed in Apex code Any other ideas?
OH make sure to check out bullet #2 in this post and choose the Child Relationship Name instead!
Note that after finding the child relationship name, you need to make it a __r instead of __c =)
Thanks David, still having problems determining this info. Let me see if I can do some more digging on this but I appreciate the guidance!
Hi David-I am still struggling with this. Let me post extra information. If you have time hopefully you can make sense and help me with the query or possibly this isn’t something that I can do. I am trying to tie the Rep directors with products to see who selling more of select items. I think the relationship issue is confusing or maybe a bit advanced for me.
SOME INFORMATION:
If I go to Standard & Custom Objects
1 Account-it says this under Child Relationships:
Order_Header__c.Account__c
-cascadeDelete: true
-childSObject: Order_Header__c
-deprecatedAndHidden: false
-field: Account__c
-relationshipName: Orders__r
For Account-it says this under Fields:
Rep_Director__c
2 Order_Header__c-it says this under Child Relationships:
Order_Detail__c.Order_Header__c
-cascadeDelete: true
-childSObject: Order_Detail__c
-deprecatedAndHidden: false
-field: Order_Header__c
-relationshipName: Order_Detail__r
3 Order_Detail__C-It says this under Fields
Order_Header__c
I HAVE TRIED A FEW VARIATIONS WITH NO LUCK:
1
SELECT Rep_Director__C, ( SELECT Description__c, Order_Quantity__c, Stock_Code__c FROM Order_Detail__C WHERE Stock_Code__C = ‘6195’ ) FROM Order_Header__r.Account__c
Error-“First SObject of the FROM statement must be a valid SObject type.”
2
SELECT Rep_Director__C, ( SELECT Description__c, Order_Quantity__c, Stock_Code__c FROM Order_Detail__C WHERE Stock_Code__C LIKE = ‘6195’ ) from Order_Header__C
Error-“Bind variables only allowed in Apex code”
3
SELECT Description__c, Order_Header__C, Order_Quantity__c, Stock_Code__c, ( SELECT Rep_Director__C from Order_Detail__c.Order_Header__c ) FROM Order_Detail__C WHERE Stock_Code__C LIKE ‘6195’
“Due to issues rendering query results, parent relationship queries are disabled by default. If you understand these limitations, parent relationship queries can be enabled under Settings. Alternatively, parent relationship queries can be run with REST Explorer under the Utilities menu without issue.”
When I change the settings it says
“Didn’t understand relationship ‘Order_Detail__c.Order_Header__c’ in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.”
Thanks so much!
Hhmm let’s see how I can help!
I think it’s a lot more confusing if you look at the field definitions from your UI vs. the normal Salesforce website.
Look at it this way. You have an Order_Header__c object and an Account object. The two are related, which means a lookup/master-detail field exists on the Order_Header__c object pointing to the Account, or vice versa.
If the lookup field is on the Order_Header__c object, this means if you’re doing a SOQL query on Order_Header__c, you use dot notation to get Account fields. IE SELECT xyz, Account__r.Name FROM Order_Header__c. If you’re doing a SOQL query on Account, you use a nested query IE SELECT Name, (SELECT Order_Field_1__c FROM Order_Headers__r) FROM Account
Note two things:
1. You can’t use dot notation in an object that’s immediately after the “FROM”
2. You use __r in nested SOQL queries. See the post for more details on this!
Hope this helps! Don’t give up!
David
Ha, I like your comment “I think it’s a lot more confusing if you look at the field definitions from your UI vs. the normal Salesforce website.” Everything about my SalesForce background is confusing! I don’t think I can see this via the normal website as I am not an admin, atleast I can’t see the page shown in the image on this page. I have never been formally trained with SalesForce (just given an id and pw) and I am not sure exactly what I can and can’t do since I am not an admin. We don’t have a traditional admin at our company, as the person with access has other responsibilities, though hopefully that changes in the next year. SalesForce is not yet a priority. I also have no programming background. You basically have taught me everything I know!
I gave up but I posted in the Success and Developer communities. I was told this couldn’t be done by someone in Success but someone in the Developer helped me out.
SELECT Description__c, Order_Header__C, Order_Quantity__c, Stock_Code__c,Order_Header__r.Rep_Director__C FROM Order_Detail__C WHERE Stock_Code__C LIKE ‘%6195%’
I had never seen a comma used in the code before “Stock_Code__c,Order_Header__r.Rep_Director__C” Never would have figured it out. Thanks for the help!
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000AtSlIAK
https://success.salesforce.com/answers?id=90630000000CmaZAAS
hahaha the guy who helped you is a BALLER!!
You definitely have the curiosity to become a great admin/dev, keep up the good work and you will make Salesforce a priority at your company!
Account is a standard object then why Account__r?
It’s a custom field/relationship!
got it ,just confused
thank u so much
Sorry, just saw this while trying to write a downward SOQL. I think your “Order_Detail__c” should be “Order_Details__r”.
Can you do something like this with User and UserLogin?
You sure can =)
In fact, you’d be hard pressed to find something you CAN’T SOQL query!
I’m also wondering about this, I can’t make this work for User and UserLogin:
select Id, IsFrozen, IsPasswordLocked, UserId, (Select Email from Users) from UserLogin
select Id, IsFrozen, IsPasswordLocked, UserId, User.Email from UserLogin
etc etc, nothing works, it all says it doesn’t understand the relationship ‘User’, event though the UserId field is documented as type reference to, just ike account is for contact. What am I missing?
Strange!
Maybe try the other way – base query on User
Is it possible to do a downward query from a parent object to a grandchild? I have 2 Master-Detail branches to query. On one branch I need the child object, but on the other I need to look 2 levels down. Appreciate any help. Great series of tutorials here!
This is possible with two separate SOQL statements (you can’t go downwards two levels in one SOQL)
Here’s an example:
Account –> Opportunities –> Opportunity Contact Roles
You can’t do this: [SELECT Id, (SELECT Amount, (SELECT Id FROM OpportunityContactRoles) FROM Opportunities) FROM Account];
But you can do this: [SELECT Id, (SELECT Amount FROM Opportunities) FROM Account];
[SELECT Id, (SELECT Id FROM OpportunityContactRoles) FROM Opportunity WHERE Id IN :myOppList];
so, can we move downwards to any level using the second approach and also is there any limitation for upward traversal also?
You are 100% correct =) The limitations going upwards are very generous, something like 5 levels.
thank u , I am really happy that i found this site.
Very well explained David. Hugely appreciate the guidance !
David is the Ultimate Saviour!!!!
David,
Thank you so much for this fantastic, concise tutorial. This easily just saved me who-knows-how-much time crawling through SalesForce documentation to find the straightforward solution to a straightforward problem. I hugely appreciate your having taken the time to write this out. Thanks again!
No probs!
DAVID,
CAN U PLZ tell..how to fetch data from a object field to a another object field every time data is inserted…like if there is a cuctom object ..FACT…and in this object there is a field NAMED….. TOTAL FACT…IF WE WANT TO populate this field every time record is created from a field values on a different object…..how will we accomplish this/…???
David you are AWESOME!!!
Can’t wait for your next chapters!
please do quickly….
how to write query to get account name from quote object and product name from relative quote line item???? can you help me…i got stucked here??????????
So the traversal goes like this:
Quote Line Item >> Quote >> Opportunity >> Account
Quote Line Item >> Product2
All upwards SOQL queries, check this post out!
https://www.sfdc99.com/2013/06/09/example-how-to-write-a-cross-object-soql-query/
Hi David,
How to fetch/access/retreive the values “Amount” & “Name” (double quoted) in the nested queries of the below query for comparison purpose. Like if we need to compare the Amount returned is equals 200 or something else ?
List abc = [SELECT Id, Name, Account.Description, CreatedBy.Name,
(SELECT “Amount” FROM Opportunities WHERE Amount > 100),
(SELECT “Name” FROM Best_Friends__r WHERE Phone != null)
FROM Contact
WHERE Account.Secret_Notes__c LIKE ‘%Tacos%’
AND (Sex__c = ‘Male’ OR Likes_Ice_Cream__c = true)]
abc is List of Contact which has been erased by this page formatting rule
What you have there actually looks good! (Except for the double quotes!)
Then you’d do…
for (Contact c : abc) {
for (Opportunity o : c.Opportunities) {
Integer amt = o.Amount;
// …
}
}
Thank you very much Dave … you are awesome man!!!!
I have intentionally added those double quotes to highlight the content. so that you can spot it easily :)
David,
i am trying to write some cross object SOQL in the workbench and i am struggling with getting it to recognize the relationships. I am trying to pull fields from the lead and from the campaign member i have tried both upwards and downwards queries (i am pretty sure it is a downward) but still no luck. I also tried to write a a query based on some custom objects i have in production and the relationship was not recognized
Any help would be greatly appreciated, what i have is below
SELECT Id, Lastname, createddate,
(SELECT status, FirstRespondedDate FROM CampaignMembers__r)
FROM Lead
You are right, you need a downwards query!
You only need to use __r for custom relationships, since Campaign Members are standard objects you can exclude that =)
So this query is the one you’re looking for!
SELECT Id, Lastname, createddate,
(SELECT status, FirstRespondedDate FROM CampaignMembers)
FROM Lead
David
THANK YOU – that did the trick when i am hero of leads i will give you credit. So because the relationship is native to sales force vs a relationship i created i don’t need __r or __c but if it was a custom object i would need __r or if it was a Anne made relationship i would need __c – i am having trouble getting my head around when i need these things
You are 100% right Anne!
Anne made relationships = __r
Native relationship = no __r
Hi,
U have given very good information. I like it the way it is given.
Thanks
Swapnil
Hi David,
Thank you so much for your site. I’ve been gone for a while, but am back again and determined to stick to this. Your site is awesome!!!
In your query (SELECT Name FROM Best_Friends__r WHERE Phone != null) – should it be Best_Friends__c? Just wondering.
Thank you again!
Welcome back, it’s great to have you!! Lots of new content up on the site to get you to a point where you can get a job as a dev!
The __r syntax is correct in this case since it’s a “nested” query inside the larger Contact query. The “__r” stands for “related” since it’s querying Best Friends related to each Contact in the outer query. If we were simply querying the Best Friend object and nothing else, we’d use __c!
Hi David,
Thank you for clarifying this. Appreciate it! Keep up the good work. I did notice all the new contents, including the videos – love them. Awesome work!!!
Hi David,
Awesome series you are doing here.. still waiting on you to post more tutorials.
Any info on when to expect them?
Why aren’t you posting anymore? This series is really well done and helpful.
Matt – happy to see people are getting value from this site! There are so many tutorials in my mind that I must do – stay tuned!