3

I'm building a script using the API so I can mass re-assign records with the following criteria.

I need to find all leads that: a) Are owned by Person X b) were created before 9/1/2012 AND c) Either have no activity on them or have no activity since 9/1/2012.

A and B are easy, and I can query them no problem. However, C is throwing me for a loop. The report feature in the CRM makes this seem deceptively simple, so I could use some help.

I'm pretty sure the ActivityHistories table will be involved in this query some how, but I'm really just not sure how to build it to give me the data I want. Here's what I've got so far -

SELECT Id, Name, (SELECT ActivityDate FROM ActivityHistories)
    FROM Lead
    WHERE OwnerId IN (
        SELECT Id
        FROM User
        WHERE 
            User.Name = 'John Doe'
    )
    AND CreatedDate < 2012-09-01T00:00:00.000Z AND IsConverted != true

But the problem I can't seem to suss out is: how do I make the inner query only return the latest activity date on record or b) give the inner query a name that I can use to evaluate it in the WHERE clause of the query.

Try #1 - When I tried this, I got the error 'only root queries support aggregate expressions'

SELECT Id, Name, (SELECT MAX(ActivityDate) FROM ActivityHistories)
    FROM Lead
    WHERE OwnerId IN (
        SELECT Id
        FROM User
        WHERE 
            User.Name = 'John Doe'
    )
    AND CreatedDate < 2012-09-01T00:00:00.000Z AND IsConverted != true

Try #2 - When I tried this, I got the error 'unexpected token: AS'.

SELECT Id, Name, (SELECT ActivityDate FROM ActivityHistories) AS ActivityDate
    FROM Lead
    WHERE OwnerId IN (
        SELECT Id
        FROM User
        WHERE 
            User.Name = 'John Doe'
    )
    AND CreatedDate < 2012-09-01T00:00:00.000Z AND IsConverted != true

I know I'm missing something here, and it's probably very simple and fundamental to how all this works, but I just can't seem to figure it out.

Thanks! --Lisa

  • 1
    Can you use LastActivityDate? That field is on the lead and is defined as "Value is one of the following, whichever is the most recent: Due date of the most recent event logged against the record. Due date of the most recently closed task associated with the record." – Daniel Hoechst Jun 3 '13 at 15:29
  • Oh man, I didn't even know about that field. That works perfectly, thank you! – Lisa Jun 3 '13 at 15:38
3

Can you use LastActivityDate?

That field is on the lead and is defined as

"Value is one of the following, whichever is the most recent: Due date of the most recent event logged against the record. Due date of the most recently closed task associated with the record."

|improve this answer|||||

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.