4

When I try the following in the Query Editor of the Developer Console:

SELECT WhoID, COUNT(Id), MIN(ActivityDate), MAX(ActivityDate)
FROM Task
GROUP BY WhoID
LIMIT 1000

I get the following error:

SELECT WhoID, COUNT(Id), MIN(ActivityDate), MAX(ActivityDate)
                         ^
ERROR at Row:1:Column:30
field ActivityDate does not support aggregate operator MIN

One would think that, given that Task.ActivityDate is a field of type Date, it should be possible to calculate the minimum/maximum value.

Actually, according to Salesforce's documentation on Support for Field Types in Aggregate Functions, the use of MIN() and MAX() with DateTime is allowed.

At least I know I am not alone, there is a post in Ideas about it.

Any idea on how I can make this work?

2
  • N.B. per the doc on Task, ActivityDate is of type Date but your question is still a good one!
    – cropredy
    Commented Apr 12, 2018 at 0:53
  • @cropredy: The plot thickens. If you go to the Object Manager, ActivityDate appears as "Date/Time". I went by that instead of opening the object in the Developer Console. Amended. Commented Apr 12, 2018 at 4:10

1 Answer 1

5

I have found a workaround by creating a custom field of type date under the Activity object called ActivityDate__c. Thus we can reformulate the initial query as

SELECT WhoID, COUNT(Id), MIN(ActivityDate__c), MAX(ActivityDate__c)
FROM Task
GROUP BY WhoID
LIMIT 1000

It is not ideal, but it's not the first time that I have had to hack around the Task/Event/Activity object to make things work as they should.

I am going to consider turning it into a "trigger formula" that gets updated before insert/update, and make the field visible only when the code is executing in G-d mode.

1
  • creating a formula field and then use this formula field for min/max, that worked. thanks! Commented Dec 5, 2018 at 12:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .