1

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

2
  • As far as I can see, Lead is a parent of CampaignMember... so maybe that's some internal relationship that us plebs can't use? May 17, 2021 at 18:28
  • Yeah, that's what's confusing to me. It seems like the API should return valid queries, right? May 19, 2021 at 15:32

2 Answers 2

0

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
1
  • 1
    Great point - removing that clause triggers the same error unfortunately. May 19, 2021 at 15:26
0

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

2
  • Thanks for the detail here. This was the direction I was leaning. I think what's confusing to me is why the Salesforce API would return a query that can't be executed? May 19, 2021 at 15:29
  • I surmise it is because the list view you can construct on Lead is convenient for application needs but underneath, before rendering the result, SFDC is essentially doing what I answered; the REST API doesn't know that. 99% of listviews are queries on an object and its fields (no relationships) - but for Lead and certain other objects, SFDC provides "convenience" list views/fields that traverse relationships behind-the-scenes
    – cropredy
    May 19, 2021 at 15:57

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.