+ Start a Discussion
Matthew Williams 38Matthew Williams 38 

SQOL Lookup Relationship Errors

Hi, 
I am using the Workbench SOQL Query tool but am having some issues getting a lookup relationship to work. 

Object: TimecardSplit has a relationship to Project listed within the Fields and Relationships section of the Object Manager.  I have fields from both TimecardSplit and Project that I am trying to pull with the same query.  I've tried subqueries as well as using the dot operator to no avail.  For both the subquery and dot operator methods I have tried using both TimecardSplit and Project as the main object. 

Example of code below, any help would be greatly appreciated.

SELECT Quote_ID__c, pse__Stage__c, pse__Start_Date__c, pse__End_Date__c
(SELECT pse__Billing_Event__c,pse__Total_Billable_Amount__c
FROM pse__Timecard__c )
FROM pse__Proj__c

Thank you!
Matt
Best Answer chosen by Matthew Williams 38
Pawel BorysPawel Borys
Right, ok. I think the relationship name still requires the package prefix. pse__TimecardSplits__r

All Answers

Pawel BorysPawel Borys
When using a subquery you need to use the relationship plural name that is set on the lookup field and not the object name. For example if the child object was Opportunity you would have "FROM Opportunities" instead of "FROM Opportunity". So for you it will be "FROM Timecards" of whatever the plural relationship label is. 
Danish HodaDanish Hoda
Hi Matthew,
If pse__Proj__c is your parent Object, then go to Object Manager -> child Object -> fields and Retaionships -> search for the lookup field on the object to pse__Proj__c -> get Child Relationship Name.

If the Child Relationship Name is timecards, this is how your SOQL shoud be:
SELECT Quote_ID__c, pse__Stage__c, pse__Start_Date__c, pse__End_Date__c
(SELECT pse__Billing_Event__c,pse__Total_Billable_Amount__c
FROM timecards__r)
FROM pse__Proj__c

Please mark my answer as Best if it solves your issue.

 
Matthew Williams 38Matthew Williams 38
Thank you both very much for taking the time to answer.  I have looked into the relationship and determined the Child Relationship Name is "TimecardSplits".  With that in mind I used the following code:

SELECT Quote_ID__c, pse__Stage__c, pse__Start_Date__c, pse__End_Date__c
(SELECT pse__Billing_Event__c,pse__Total_Billable_Amount__c
FROM TimecardSplits__r)
FROM pse__Proj__c

Unfortunately I still receive an error:

MALFORMED_QUERY:
(SELECT pse__Billing_Event__c,pse__Total_Billable_Amount__c
^
ERROR at Row:2:Column:1
unexpected token: 'SELECT'

Thanks,
Matt
Pawel BorysPawel Borys
You're missing a comma after end date field
Matthew Williams 38Matthew Williams 38
Thank you much, Pawel.  I greatly appreciate you help.  I added the comma but am now getting a different error

SELECT Quote_ID__c, pse__Stage__c, pse__Start_Date__c, pse__End_Date__c,
(SELECT pse__Billing_Event__c,pse__Total_Billable_Amount__c
FROM TimecardSplits__r)
FROM pse__Proj__c

INVALID_TYPE:
FROM TimecardSplits__r)
^
ERROR at Row:3:Column:6
Didn't understand relationship 'TimecardSplits__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.
Pawel BorysPawel Borys
Right, ok. I think the relationship name still requires the package prefix. pse__TimecardSplits__r
This was selected as the best answer
Matthew Williams 38Matthew Williams 38
That worked.  Thank you so much for all your help!