[SalesForce] SOQL help with subquery where clause

I have a two tables, service order and a child table called status history (1 to M relationship). I am trying to find all service order's that have a status history record matching 'Order Complete'.

Here is my query

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c
WHERE id = (SELECT id FROM Status_History__r WHERE Name = 'Order Completed')

I think it is pretty self explanitory, however I recieve the following error in the debug console….

Unknown error parsing query

UPDATE:
New query but still gives an error…
SOQL

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c
WHERE id IN (SELECT id FROM Status_History__r WHERE Name = 'Order Completed')

Error

WHERE id IN (SELECT id FROM Status_History__r WHERE Name = 'Order
^ ERROR at Row:3:Column:29 sObject type 'Status_History__r' 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.

Best Answer

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c WHERE id IN (SELECT Service_Order__c FROM Status_History__c WHERE Name = 'Order Completed')

The above should work I believe and worth trying

Related Solutions

[SalesForce] SOQL Nulls Last with Orderby Picklist field doesn’t work

Sorting on picklist fields is a bit of a special case. Typically

"picklist sorting is defined by the picklist sort determined during setup." - ORDER BY Docs

You can refine this using the Reorder button on the picklist field.

However, NULLS LAST doesn't appear to be respected on picklists. See (and upvote) - Implement SOQL Order By NULLS LAST. It appears to be a long standing issue.

One possible workaround would be to create a text field that you populate with a workflow rule and field update. This would allow you to sort alphabetically and should respect null values. Note that you could get into issues with this if you multiple languages/translations (which I think is why picklists have sort orders).

Another thing to watch out for could be what are called "inactive picklist values". This is where you have assigned a value to the picklist that isn't in the current list of allowed values. These should appear after the defined values in sorting.

[SalesForce] SOQL to pull out Approval History records

You can Query ProcessInstance and get related details from child records. Here is sample code for you.

for (Account a : [SELECT Id,(SELECT ID FROM ProcessInstances  ORDER BY CreatedDate DESC) FROM Account WHERE ID  =:accId])
{
    for(ProcessInstance pi :a.ProcessInstances)
        processInstanceIds.add(pi.Id);
}

// Now that we have the most recent process instances, we can check
// the moRst recent process steps for comments.  
for (ProcessInstance pi : [SELECT TargetObjectId,LastActorId,LastActor.Name,(SELECT Id, ActorId,OriginalActor.Name,Comments, ProcessInstanceId FROM StepsAndWorkitems WHERE StepStatus != 'Started' AND StepStatus != 'Pending' Order BY ID DESC), (SELECT Id, StepStatus, Comments  FROM Steps ORDER BY CreatedDate DESC LIMIT 1 )
                       FROM ProcessInstance WHERE Id IN :processInstanceIds ORDER BY CreatedDate DESC])
{
    //approvalMap.put(pi.LastActor.Name,'');
    // If no comment exists, then prevent the object from saving.                 
    if (pi.StepsAndWorkitems.size() > 0)
        for(ProcessInstanceHistory pih :pi.StepsAndWorkitems)
            approvalList.add(new wrapperclass(pih.OriginalActor.Name,pih.Comments));
    else
        approvalList.add(new wrapperclass(pi.LastActor.Name,''));

}

Related Topic