Salesforce AppExchangeStatus tracking on Opportunities in Salesforce
The goal is to implement a scalable solution to accurately track status changes on the Opportunity object. This solution involves creating a custom intermediate object to log each status change, including the start time, end time, and time spent in each status. The solution ensures all status transitions are captured and recorded for reporting and analysis.
Requirements
Custom Object: TT_Status_Change__c
Fields:
TT Detail Lookup (dftlytime__dftly_Timesheet_Detail__c): Lookup to the TT Detail record.
Status (Status__c): Stores the status value (Text or Picklist).
Status Start Time (Start_Time__c): DateTime field to record when the status started.
Status End Time (End_Time__c): DateTime field to record when the status ended.
Time Spent (Minutes) (Time_Spent__c): Formula or numeric field to calculate the duration spent in the status.
TT Detail Fields
Status (dftlytime__TimeApproval_Status__c): Field on TT Detail where the status is tracked (e.g., Picklist or Text field).
Existing Fields: Any pre-existing relevant fields on TT Detail should remain unchanged.
Trigger Logic
Trigger Name:TrackStatusChanges
Trigger Object:Opportunity
Trigger Events:before update, after update
Trigger Functionality
Trigger Workflow
Capture Initial Status on TT Details Creation:
When a TT Detail (dftlytime__dftly_Timesheet_Detail__c) record is created, capture the current status (dftlytime__TimeApproval_Status__c) and store it in a TT_Status_Change__c record with:
Start_Time__c set to the current timestamp.
End_Time__c left as null.
Detect Status Change on Opportunity Object:
Compare the status field (Status) in the Trigger.old and Trigger.new contexts.
Proceed only if the status has changed.
Close Previous Status in TT Status Change:
Query the latest TT_Status_Change__c record for the related TT Detail where End_Time__c is null.
Update the End_Time__c field with the current timestamp.
Calculate and store the time spent in minutes using the formula:
Time_Spent__c = (End_Time__c – Start_Time__c) / (1000 * 60);
Create New Status Entry:
Insert a new TT_Status_Change__c record with the following fields:
TT Detail Lookup: Reference to the current TT Detail record.
Status: Set to the new status value fromStatus.
Start_Time: Set to the current timestamp.
Insert/Update Operations:
Insert the new status change record.
Update the TT_Status_Change__c record for the previous status, if applicable.
Trigger Code (Example):
trigger TrackStatusChanges on Opportunity (before update, after update) {
if (Trigger.isUpdate) {
List<TT_Status_Change__c> statusChanges = new List<TT_Status_Change__c>();
for (Opportunity newRecord : Trigger.new) {
Opportunity oldRecord = Trigger.oldMap.get(newRecord.Id);
// Check if the status has changed
if (oldRecord.RAC_Status__c != newRecord.RAC_Status__c) {
// Query the latest TT Detail record linked to this RAC
TT_Detail__c ttDetail = [
SELECT Id
FROM dftlytime__dftly_Timesheet_Detail__c
WHERE RAC_Lookup__c = :newRecord.Id
ORDER BY CreatedDate DESC LIMIT 1
];
// Close the previous status
TT_Status_Change__c existingChange = [
SELECT Id, Start_Time__c
FROM TT_Status_Change__c
WHERE TT_Detail__c = :ttDetail.Id
AND End_Time__c = null
LIMIT 1
];
if (existingChange != null) {
existingChange.End_Time__c = DateTime.now();
existingChange.Time_Spent__c =
(DateTime.now().getTime() – existingChange.Start_Time__c.getTime()) / (1000 * 60);
update existingChange;
}
// Log the new status
TT_Status_Change__c newStatusChange = new TT_Status_Change__c(
TT_Detail__c = ttDetail.Id,
Status__c = newRecord.RAC_Status__c,
Start_Time__c = DateTime.now()
);
statusChanges.add(newStatusChange);
}
}
// Insert new status change records
if (!statusChanges.isEmpty()) {
insert statusChanges;
}
}
}
Reporting Requirements
Reports:
Create a report type that includes TT Detail and TT Status Change records.
Provide reports showing:
Time spent in each status for a specific TT Detail record.
Average time spent across all statuses for multiple records.
Enable filtering by date, TT Detail, or specific statuses.
Dashboards:
Create charts to show status duration distributions (e.g., pie chart for percentage time in statuses).
Show total time spent on each status across all records.
Optional Enhancements
Validation Rule: Prevent users from directly modifying the Time_Spent__c field on TT_Status_Change__c.
Archival: Create a batch process to archive old TT_Status_Change__c records to improve performance for long-term data.
Real-Time UI:
Build a Lightning component to show the timeline of status changes for each TT Detail record.
Add a progress bar visualization to show the cumulative time spent in different statuses.
Testing Requirements
Unit Tests for Trigger:
Validate correct creation of TT_Status_Change__c records on status change.
Test time calculation accuracy for different scenarios (e.g., rapid status changes, no previous status).
Ensure coverage for edge cases (e.g., null values in fields).
UI Testing:
Verify that the related list of TT_Status_Change__c is correctly populated on the TT Detail record page.
Performance Testing:
Ensure trigger efficiency with bulk updates (200+ RAC records at once).
That's how you can track time on Status Changes
