Lead ListView SOQL query with CampaignMember.Status fails through REST API

Question

I'm trying to retrieve results from a Lead List View SOQL query via the REST API and found that the API always fails when the List View includes references to CampaignMember.Status. Based on the documentation around 1 to 1 relationships like Leads and CampaignMembers, it seems like the query should work.

As an example, I created a List View using options available in the UI and that shows results in both Salesforce Lightning and Classic, but when I attempt to use the rest API, it throws an error.

I use this call to retrieve the query:

https://na124.salesforce.com/services/data/v39.0/sobjects/Lead/listviews/${LIST_VIEW_ID}/describe

Receive this query back:

SELECT Name, Email, toLabel(CampaignMember.Status), Id, CreatedDate, LastModifiedDate, SystemModstamp 
  FROM Lead USING SCOPE mine 
  WHERE IsConverted = false 
    AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = 'CAMPAIGN_ID') 
    AND CampaignMember.Status = 'Sent' 
  ORDER BY Name ASC NULLS FIRST, Id ASC NULLS FIRST

When I attempt to retrieve results directly using https://na124.salesforce.com/services/data/v39.0/sobjects/Lead/listviews/${LIST_VIEW_ID}/results or by using the above SOQL query, I get the following error:

INVALID_FIELD: 
SELECT Name, Email, toLabel(CampaignMember.Status), Id, CreatedDate
                            ^
ERROR at Row:1:Column:29
Didn't understand relationship 'CampaignMember' 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.

Is this a difference in what's supported in the Salesforce UI directly vs the API, or is there some way I need to change the queries? I've tested this in Salesforce v39.0 through v51.0

Answer 1

The error message is highly suspicious, but I do see in the SOQL docs that toLabel cannot be used with the ORDER BY clause in your query. Would it be possible to remove that clause and see if the same error message is returned?

Here is the relevant excerpt from the SOQL documentation:

The toLabel() method cannot be used with the ORDER BY Clause. Salesforce always uses the order defined in the picklist, just like reports

Answer 2

An executable query would be:

SELECT Name, Email,  Id, CreatedDate, LastModifiedDate, SystemModstamp 
  FROM Lead USING SCOPE mine 
  WHERE IsConverted = false 
    AND Id IN 
      (SELECT LeadId FROM CampaignMember WHERE CampaignId = 'CAMPAIGN_ID' 
              AND Status = 'Sent') 
     
  ORDER BY Name ASC NULLS FIRST, Id ASC NULLS FIRST

There is no lookup field from Lead to CampaignMember - CampaignMember is a junction between Lead and Campaign

So, the clause Status = 'Sent' needs to be in the join filter, not the outer filter. Thus, you can't use toLabel(CampaignMember.Status) in the outer query

A different way to write this query would be:

SELECT LeadId, Lead.Email, toLabel(Status) 
  FROM CampaignMember 
  WHERE CampaignId = 'CAMPAIGNID'
    AND Lead.OwnerId = 'SomeUserRecordId'

Now I appreciate you are trying to do a REST Lead List View query but the query you get back is unexecutable