2

I'm trying to formulate a query to use in an API call to return a json document for integration with my ERP. I have the following query, that pulls from the 'order line' object, but need to formulate it to pull from the 'order' object.

select 
Product_Code__c, 
SVMXC__Expected_Quantity2__c,
Line_Cost__c,
SVMXC__Line_Price2__c,
SVMXC__RMA_Shipment_Order__r.Name,
SVMXC__RMA_Shipment_Order__r.SVMXC__Destination_Location__r.Account_Number__c,
SVMXC__RMA_Shipment_Order__r.SVMXC__Destination_Location__r.A_Ship_to__c
from
SVMXC__RMA_Shipment_Line__c
where
SVMXC__RMA_Shipment_Order__r.Order_Exported_to_A__c=false
order by SVMXC__RMA_Shipment_Order__r.Name

I tried to just flip that query to read like the following:

select 
SVMXC__RMA_Shipment_Line__r.Product_Code__c, 
SVMXC__RMA_Shipment_Line__r.SVMXC__Expected_Quantity2__c,
SVMXC__RMA_Shipment_Line__r.Line_Cost__c,
SVMXC__RMA_Shipment_Line__r.SVMXC__Line_Price2__c,
SVMXC__RMA_Shipment_Line__r.SVMXC__RMA_Shipment_Order__r.Name,
SVMXC__RMA_Shipment_Order__r.SVMXC__Destination_Location__r.Account_Number__c,
SVMXC__RMA_Shipment_Order__r.SVMXC__Destination_Location__r.A_Ship_to__c
from
SVMXC__RMA_Shipment_order__c
where
SVMXC__RMA_Shipment_Order__r.Order_Exported_to_A__c=false
order by SVMXC__RMA_Shipment_Order__r.Name

But I am getting the following error:

INVALID_FIELD:
SVMXC__RMA_Shipment_Line__r.Product_Code__c
^
ERROR at Row:2:Column:1
Didn't understand relationship 'SVMXC__RMA_Shipment_Line__r' in field path. 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.

How can I get my json output to be grouped by SVMXC__RMA_Shipment_Order__r.Name?

4
  • 1
    Welcome to SFSE =), if you are new to SOQL and Salesforce, Trailheads is a great learning platform. for example, Write SOQL Queries Unit | Salesforce Trailhead is a great starting point for learning SOQL. if possible, provide where exactly you are stuck, for Example, by providing an Error message
    – glls
    May 29 '18 at 19:38
  • 3
    There is also the Relationship Queries | SOQL and SOSL Reference Guide to better help understand how to formulate relationship queries
    – glls
    May 29 '18 at 19:39
  • @glls thanks for the welcome. I've been utilizing Trailheads a ton. I've updated above to include my query rework as well as the error message.
    – AlliDeacon
    May 29 '18 at 19:47
  • 1
    Thanks! Adrian has provided an elaborate answer to get you started on the right foot. Feel free to open new posts if you are stuck trying to figure Soql's out. Just remember to include what you have tried and where you are stuck =)
    – glls
    May 29 '18 at 19:49
2

The specific functionality that is tripping you up is called a Left Outer Join, often just called a subquery. The syntax looks like:

SELECT (SELECT Name FROM Children__r) FROM Parent__c

You need to be able to identify the child relationship name (here Children__r), as you use this value instead of the child object's API Name in this join.


If you want to query on the parents, you would have to make two changes:

  • include the fields you currently query for directly as a sub-query instead
  • query the fields you currently join via the parent directly instead

That would look something like:

SELECT
    Name,
    SVMXC__Destination_Location__r.Account_Number__c,
    SVMXC__Destination_Location__r.A_Ship_to__c,
    (
        SELECT
            Product_Code__c,
            SVMXC__Expected_Quantity2__c,
            Line_Cost__c,
            SVMXC__Line_Price2__c
        FROM SVMXC__RMA_Shipment_Lines__r
    )
FROM SVMXC__RMA_Shipment_Order__c
WHERE Order_Exported_to_A__c = false
ORDER BY Name

Note that you have to use the proper child relationship name, which may not be SVMXC__RMA_Shipment_Lines__r (that was just a guess on my part). There are numerous posts on this site about how to get the child relationship name, if you get stuck on that particular aspect you should peruse those.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.