There is an implementation restriction on ActivityHistories

Question

I have a query that I wanted to add additional sorting to the nested query ActivityHistories. The sorting works with ORDER BY ActivityDate DESC, LastModifiedDate DESC but when I add a third condition ORDER BY ActivityDate DESC, LastModifiedDate DESC, CreatedDate DESC I get a permission error stating:

"There is an implementation restriction on ActivityHistories. When you query this relationship, security evaluation is implemented for users who don't have administrator permissions, and you must use a specific sort order: ActivityDate DESC, LastModifiedDate DESC"

I understand from these posts that is has to do with admin permissions:

https://community.skuid.com/t/salesforce-implementation-restriction-on-activityhistories/4939

https://documentation.conga.com/grid/latest/activity-history-and-open-activities-implementation-restriction-143886917.html

The solution suggests that I have to give my users "View All Data" permissions which I do not want to do.

Is there a way around this limitation without having to give view all data permissions to people we do not want to have this access?

Here is the query we want:

SELECT Id, Name, (SELECT WhoId, WhatId, Subject, Status, Response_Channel__c, Reason__c, RPC__c, Priority, OwnerId, Owner.Name, Location, IsTask, IsClosed, Id, DurationInMinutes, Description, CallType, CallObject, CallDurationInSeconds, CallDisposition, ActivityType, ActivityDate,CreatedDate, LastModifiedDate, AccountId,Date_Activity_Logged__c FROM ActivityHistories ORDER BY ActivityDate DESC, LastModifiedDate DESC, CreatedDate DESC limit 50), (SELECT n.Title, n.ParentId, n.OwnerId,  n.Owner.Name, n.Id, n.CreatedById, n.CreatedDate, n.Body, LastModifiedDate FROM Notes n ORDER BY n.LastModifiedDate DESC limit 50),(SELECT a.ParentId, a.OwnerId, a.Owner.Name, a.Name, a.Id, a.Description, a.ContentType, a.CreatedDate, a.CreatedById, LastModifiedDate FROM Attachments a ORDER BY a.LastModifiedDate DESC limit 50) FROM Lead WHERE Id =: leadId ];

Answer 1

Create a formula field on Activity called Sortable_Activity_LastMod_CreatedDate__c

TEXT(UNIXTIMESTAMP(ActivityDate)) &'-'& 
TEXT(UNIXTIMESTAMP(LastModifiedDate))&'-'&
TEXT(UNIXTIMESTAMP(CreatedDate)) 

This will generate a string that will look like

1632441600-1632522839-1632522839
1632441600-1632530526-1632530526
...

that you can sort.

Notes -- Assumes ActivityDate has a value, if not, the formula will generate

-1635469551-1635469551

You may want to change the formula to use ...BLANKVALUE(ActivityDate,DATEVALUE(someVal))

where someVal is either a constant date in the past or deep future, depending on your business use case