Using the creation timestamp is only useful if all your Cases stay opened until resolved and never reopen. Consider any case that was closed a few days ago. If that case is reopened now then, because it has a old creation date it will appear BEFORE cases that arrived earlier in the day.
If you use the last update timestamp and the Case is touched for any reason then the timestamp will be updated. If you are trying to process the oldest cases first then this old case will now be pushed to the bottom of the list.
The solution is to sort based on the time the case was added to the Queue.
1. Create a custom field QueuedAtTimestamp__c
2. Create a before insert or update trigger on Case.
3. Create unit test.
4. Sort your Case view on the new timestamp.
Trigger code
When working with objects in triggers you can't access information on a related object. In this 'case', the owner id field relates the Case to a User or Group. To test if the Owner is a Queue we need to make a second query to see if the owner is in the Group table. If yes then the owner is a queue.But introducing a query inside a trigger can cause problems when the time comes to update 100's of Case objects. First I'll show you the code without worrying about these limits. To protect for bulk processing I chose to add a test to only run the for loop if the size of the trigger.new list is less than 100.
trigger CaseBeforeUpsertTrigger on Case (before insert, before update) {
for(Case theCase: trigger.new)
{
if(trigger.isUpdate) {
Case beforeUpdate = System.Trigger.oldMap.get(theCase.Id);
if(theCase.OwnerId != beforeUpdate.OwnerId)
{
List gp = [select Id, Type from Group where Id = :theCase.OwnerId and Type = 'Queue' ];
if(!gp.isEmpty()){
system.debug(Logginglevel.ERROR,'isUpdate is queue: ' );
theCase.QueuedAtTimestamp__c = System.now();
}
}
} else if(trigger.isInsert) {
List gp = [select Id, Type from Group where Id = :theCase.OwnerId and Type = 'Queue' ];
if(!gp.isEmpty()){
system.debug(Logginglevel.ERROR,'isInsert is queue: ' );
theCase.QueuedAtTimestamp__c = System.now();
}
}
}
}
Unit test code:static testMethod void CaseQueuedTimestampTest() {
Group g = [select Id, Name from Group where Type = 'Queue' limit 1];
// Test 1. no owner so no queue timestamp
Case testCase = new Case();
testCase.Description = 'Case 1 created as part of unit test of Case Queued and Timestamped';
insert testCase;
String caseId = testCase.Id;
// Test 2 change owner to queue to set the timestamp
testCase = [select Id, OwnerId, QueuedAtTimestamp__c from Case where id = :caseId];
System.assertEquals(true, testCase.QueuedAtTimestamp__c == null);
testCase.OwnerId = g.Id;
update testCase;
testCase = [select Id, OwnerId, QueuedAtTimestamp__c from Case where id = :caseId];
System.assertEquals(true, testCase.OwnerId == g.Id);
System.assertEquals(true, testCase.QueuedAtTimestamp__c != null);
// Test 3 create with queue as owner, so has timestamp
testCase = new Case();
testCase.Description = 'Case 2 created as part of unit test of CaseQueued and Timestamped';
testCase.OwnerId = g.Id;
insert testCase;
caseId = testCase.Id;
testCase = [select Id, OwnerId, QueuedAtTimestamp__c from Case where id = :caseId];
System.assertEquals(true, testCase.OwnerId == g.Id);
System.assertEquals(true, testCase.QueuedAtTimestamp__c != null);
}
