You need to sign in to do that
Don't have an account?
Manjusha Maddala
/listViews/describe returns an incorrect query
Salesforce API response to /listViews/id/describe returns an incorrect query when both lead and campaign member filters on status field are used. Anyone else seen this behavior?
Here's our workflow
Here's the query returned to us by Salesforce in step #2 above -
SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1') AND Status != 'Invited' AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null AND Status != 'Attended' ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST
There are three filters on "Status" here, all of which get applied to the lead object:
The actual list definition has an Open filter on Lead Status and the other two (Invited/Attended) on Campaign Member Status. But in the query, the Status filter on Campaign Member doesn't seem to filtering in the Campaign Member part of the query. As a result, the query execution (step #4 above) returns us leads that are not part of the selected list since all of the status filtering is being done on leads, and not campaign member as it was meant to be. The list view in the UI shows the correct/expected data. It is the API that is not returning the expected data.
We see a bug in the query and this is how it should have been -
SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1' AND Status != 'Invited' AND Status != 'Attended') AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST
If you notice, in our query above, Invited/Attended status clause has been made part of the CampaignMember select sub query whereas Open filter check is grouped in the where clause on the Leads table select statement.
Hoping someone here proposes a solution/workaround for this, or if it indeed is a bug, have it fixed for us :)
Thanks!
Manjusha
Here's our workflow
- Our software makes a call to /services/data/v36.0/sobjects/Lead/listviews/00BU0000003VsoeMAC/describe, where 00BU0000003VsoeMAC is a leads list ID
- The response from Salesforce includes a "query" like SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1') AND Status != 'Invited' AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null AND Status != 'Attended' ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST
- Our software url-encodes this query and makes a GET to Salesforce to this url - /services/data/v36.0/query/?q=<encoded query>
- Salesforce responds, and our software parses the results into a list of contact records
Here's the query returned to us by Salesforce in step #2 above -
SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1') AND Status != 'Invited' AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null AND Status != 'Attended' ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST
There are three filters on "Status" here, all of which get applied to the lead object:
- Status != 'Invited'
- Status != 'Open'
- Status != 'Attended'
The actual list definition has an Open filter on Lead Status and the other two (Invited/Attended) on Campaign Member Status. But in the query, the Status filter on Campaign Member doesn't seem to filtering in the Campaign Member part of the query. As a result, the query execution (step #4 above) returns us leads that are not part of the selected list since all of the status filtering is being done on leads, and not campaign member as it was meant to be. The list view in the UI shows the correct/expected data. It is the API that is not returning the expected data.
We see a bug in the query and this is how it should have been -
SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1' AND Status != 'Invited' AND Status != 'Attended') AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST
If you notice, in our query above, Invited/Attended status clause has been made part of the CampaignMember select sub query whereas Open filter check is grouped in the where clause on the Leads table select statement.
Hoping someone here proposes a solution/workaround for this, or if it indeed is a bug, have it fixed for us :)
Thanks!
Manjusha