Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
billkbillk 

Can't access Date fields via SOQL / Relationship query?

In the 14.0 API, SOQL seems to return inconsistent results for date fields such as "BirthDate" and "LastActivityDate."

For example, I've submitted this query via both the Data Loader and the API directly:

Code:
SELECT Id, Contact.FirstName, Contact.LastActivityDate, Contact.BirthDate
FROM CampaignMember
WHERE CampaignId = '70120000000Edd4AAC' and ContactId != NULL

In both cases, no value is returned for the LastActivityDate and BirthDate fields, even though I can verify that these fields are populated in the UI for the contacts returned.  If I examine the XML returned from the API, I find

Code:
<sf:LastActivityDate xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sf="urn:sobject.partner.soap.sforce.com" />
 
so Salesforce is definitely returning a null value for this field; it's not a parsing problem.

If I remove the "relationship" part of the query and just query against one of the contacts directly

Code:
SELECT Id, FirstName, BirthDate, LastActivityDate
FROM Contact
WHERE Contact.Id = '0032000000HaHxf'

 BirthDate is returned correctly, but not LastActivityDate.

Any idea what's going on?  The objective of the original query was to return a list of campaign member names, addresses, and the last activity date for the contact or lead.  I can retrieve other, non-date fields from contacts and leads just fine.


Message Edited by billk on 11-24-2008 01:52 PM
advlgxpmadvlgxpm
Try something like....

Select c.Id, c.Contact.FirstName, c.Contact.LastName from CampaignMember c
where c.ContactId <> '' and c.CampaignId = 'xxxxxxxxxx'
billkbillk
I tried

Code:
SELECT cm.Id, cm.Contact.FirstName, cm.Contact.LastActivityDate, cm.Contact.BirthDate
FROM CampaignMember cm
WHERE cm.CampaignId = '70120000000Edd4AAC'
and got the correct number of columns and rows, but no data at all (at least, not in Data Loader).
 
SuperfellSuperfell
Are you sure the contacts in the campaign are the same contacts your other query or UI check are looking at? I just tried this with SoqlX and it worked fine for me. The current version of the ApexDataLoader is unfortunately overly case sensitive, so this might be the problem, e.g. its Birthdate not BirthDate
billkbillk
That did it!  Thanks Simon.

For the record, there were actually two issues:
  • Data Loader is indeed overly case sensitive; when I ran the test queries in the force.com IDE, things started working a lot better.
  • Some of the tasks associated with the leads and contacts I was testing against were lacking Due Dates; since Last Activity Date is calculated off of due dates, no information was returned.  That behavior matches the docs, but is still somewhat misleading, since our users consider creating a task to be "activity" -- but anyways, problem solved!