+ Start a Discussion
Noor FazliNoor Fazli 

SOQL: How to retrieve fields from grand parent

We are trying to retrieve value from grand parent's name field and parent field using dot notation. We can successfully retrieve fields from both parent and grandchild but need some fields from grand parent also.
 
Select  PA__r.Customer_Name__r.Name,PA__r.Name,PA__r.Id,PA__r.GCompany__c,PA__r.Customer_Name__c,PA__r.JobNo__c,
 PA__r.Project_Manager__c,Name,Billing_Amount_Per_Period__c,Total_Value__c from bills__c
We are unable to get results from hihlighted field, grand parent field. Any pointers will be highly appreciated.
 
Best Answer chosen by Noor Fazli
Noor FazliNoor Fazli
Thanks Manoj and Sura. @sura the name of  relationship name  from grandchild to Parent is  PA__r and the name of relationship from Parent to grand Parent is Customer_Name__r .
@ Manoj I tried to follow your approach of retrieving grandParent from grandChild and exactly got the same value as  PA__r.Customer_Name__r.Name. 
I tried to run my SOQL on Workbench,developer Console and Eclipse query Results window. All of them have different results.
On workbench it is displaying the required results now after turning on relationship queries ,before (default settings) it was ignoring that column. On developer console and Eclipse its saying 'Parent  PA__c' for all the values.(Looks like some setting issues with relationships)
I tried the code in Apex and its working fine without any modification. (didn't tryApex before)
Thanks

All Answers

ManojjenaManojjena
Hi Noor ,

You are  following the right track but you are doing a small mistake to refer the relationship name . It is possibel to retrive grand parent record in Query .Do one thing Go to your grand child object create a forrmula field select the return type of your grand parent field .try to crrate the formula by select ting the parent >grand parent>grand parent field then copy the formula and use in your query it will definitly work .

Finaly don't save your formula .

Thanks 
Manoj
surasura
Is Customer_Name__c is the api name of the realtionship from your parent object to grand parent object ? if your query is not working it is not , please find api name of the field that links parent to grandparent and replace the __c with __r
Noor FazliNoor Fazli
Thanks Manoj and Sura. @sura the name of  relationship name  from grandchild to Parent is  PA__r and the name of relationship from Parent to grand Parent is Customer_Name__r .
@ Manoj I tried to follow your approach of retrieving grandParent from grandChild and exactly got the same value as  PA__r.Customer_Name__r.Name. 
I tried to run my SOQL on Workbench,developer Console and Eclipse query Results window. All of them have different results.
On workbench it is displaying the required results now after turning on relationship queries ,before (default settings) it was ignoring that column. On developer console and Eclipse its saying 'Parent  PA__c' for all the values.(Looks like some setting issues with relationships)
I tried the code in Apex and its working fine without any modification. (didn't tryApex before)
Thanks
This was selected as the best answer