Boosting Your CRM Productivity

How to Show Time Zone Information on Salesforce Field Service Records? – Part 2

Posted on by Posted in Local Time

As mentioned in the previous blog post, the approach discussed there has a limitation. If the Scheduled Start or Scheduled End values are in the future (crossing the DST), the formula still uses the current time zone and UTC Offset fields, instead of using the time zone and UTC Offset values that are relevant for the future date. As a consequence, the calculated Scheduled Start Date or Scheduled End Date values could be potentially off by an hour, and thus inaccurate.

In this post, we will discuss a code based approach that overcomes this limitation. We will use the ServiceAppointment object as an example. The ServiceAppointment has lookup fields to both Account and Contact objects. We will use the Account field in our example and you can easily extend this logic to Contact field if needed.

Process

  1. Create the custom fields, trigger code, and test class in the sandbox. Test thoroughly.
  2. Create a change set with all the relevant components from the sandbox (custom fields, trigger code, and test class) and send the change set to production.
  3. Deploy the change set in production by using the inbound change set.

Create Custom Fields

From Setup -> Object Manager -> Field Service, select the ServiceAppointment object and create the following fields.

Label API Name Data Type Comments
Scheduled Start Date Scheduled_Start_Date__c Formula (Text) Scheduled start date in customer local time zone.
Scheduled End Date Scheduled_End_Date__c Formula (Text) Scheduled end date in customer local time zone.
Scheduled Start Timezone Scheduled_Start_Timezone__c Text(10) Timezone of the scheduled start date.
Scheduled End Timezone Scheduled_End_Timezone__c Text(10) Timezone of the scheduled end date.
Scheduled Start UTC Offset Scheduled_Start_UTC_Offset__c Number(3, 2) UTC offset for the scheduled start date.
Scheduled End UTC Offset Scheduled_End_UTC_Offset__c Number(3, 2) UTC offset for the scheduled end date.
Scheduled Start Date Formula
IF(ISBLANK(Scheduled_Start_UTC_Offset__c),
"Unknown",
MID( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 6, 2 ) & "/" &
MID( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 9, 2 ) & "/" &
LEFT( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 4 ) & " " &
TEXT(
IF(
OR(
VALUE( MID( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 12, 2 ) ) = 0,
VALUE( MID( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 12, 2 ) ) = 12
),
12,
VALUE( MID( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 12, 2 ) )
-
IF(
VALUE( MID( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 12, 2 ) ) < 12,
0,
12
)
)
)
&":"&
MID( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 15, 2 )
&" "&
IF(
VALUE( MID( TEXT( SchedStartTime+ Scheduled_Start_UTC_Offset__c /24 ), 12, 2 ) ) < 12,
"AM",
"PM"
)
& " " & Scheduled_Start_Timezone__c
)
Scheduled End Date Formula
IF(ISBLANK(Scheduled_End_UTC_Offset__c),
"Unknown",
MID( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 6, 2 ) & "/" &
MID( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 9, 2 ) & "/" &
LEFT( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 4 ) & " " &
TEXT(
IF(
OR(
VALUE( MID( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 12, 2 ) ) = 0,
VALUE( MID( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 12, 2 ) ) = 12
),
12,
VALUE( MID( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 12, 2 ) )
-
IF(
VALUE( MID( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 12, 2 ) ) < 12,
0,
12
)
)
)
&":"&
MID( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 15, 2 )
&" "&
IF(
VALUE( MID( TEXT( SchedEndTime+ Scheduled_End_UTC_Offset__c /24 ), 12, 2 ) ) < 12,
"AM",
"PM"
)
& " " & Scheduled_End_Timezone__c
)

Create Trigger code

Implement the following trigger on the ServiceAppointment object.

trigger ServiceAppointmentLocalTimeTrigger on ServiceAppointment (before insert, before update) {
	ServiceAppointmentLocalTimeHelper.populateTimezoneInfo(Trigger.new);
}

Create Trigger Helper Class

Implement the following apex class which is called from the trigger.

public with sharing class ServiceAppointmentLocalTimeHelper {
    public static void populateTimezoneInfo(List<ServiceAppointment> lstSA) {
        // The following logic is built based on timezone info from related accounts.
        // STEP 1 - get the related accounts
        Set<Id> acctIds = new Set<Id>();
        for(ServiceAppointment sa : lstSA) {
            if(sa.AccountId != null) acctIds.add(sa.AccountId);
        }
        
        if(acctIds.IsEmpty()) {
            return;
        }
        
        // STEP 2 - Query the standard and DST timezone fields from the related accounts
        Map<Id, Account> mapAccts = new Map<Id, Account>();
        for(Account acct : [SELECT Id, tz__UTF_Offset__c, tz__Timezone__c, tz__UTC_Offset_DST__c, tz__Timezone_DST__c, tz__Timezone_SFDC__c FROM Account WHERE Id IN :acctIds AND tz__UTF_Offset__c != null]) {
            mapAccts.put(acct.Id, acct);
        }
        
        // STEP 3 - Populate the timezone fields on the ServiceAppointment records
        for(ServiceAppointment sa : lstSA) {
            if(sa.AccountId == null) continue;
            Account acct = mapAccts.get(sa.AccountId);
            if(acct != null) {
                populateTimezone(sa, acct);
            }
        }
    }

    // check if the datetime value is observing DST or not. If DST is on, use the DST fields from the related account. Else use the standard (non-DST) fields.
    private static void populateTimezone(ServiceAppointment sa, Account acct) {
        if(sa.SchedStartTime != null) {
            Decimal startUTC = tz.LocalTimeV2.getUTCOffset(sa.SchedStartTime, acct.tz__Timezone_SFDC__c);
            if(startUTC == acct.tz__UTF_Offset__c) { // DST is off
                sa.Scheduled_Start_UTC_Offset__c = acct.tz__UTF_Offset__c;
                sa.Scheduled_Start_Timezone__c = acct.tz__Timezone__c;
            } else { // DST is on
                sa.Scheduled_Start_UTC_Offset__c = acct.tz__UTC_Offset_DST__c;
                sa.Scheduled_Start_Timezone__c = acct.tz__Timezone_DST__c;
            }
        }
        
        if(sa.SchedEndTime != null) {
            Decimal endUTC = tz.LocalTimeV2.getUTCOffset(sa.SchedEndTime, acct.tz__Timezone_SFDC__c);
            if(endUTC == acct.tz__UTF_Offset__c) { // DST is off
                sa.Scheduled_End_UTC_Offset__c = acct.tz__UTF_Offset__c;
                sa.Scheduled_End_Timezone__c = acct.tz__Timezone__c;
            } else { // DST is on
                sa.Scheduled_End_UTC_Offset__c = acct.tz__UTC_Offset_DST__c;
                sa.Scheduled_End_Timezone__c = acct.tz__Timezone_DST__c;
            }
        }
    }
}

Create Test Class

Create a test class to get adequate coverage for your trigger code. Following is a sample test class with some guidelines. Ensure that you make any changes to it where necessary for the test class to pass successfully.

// NOTE: It is important to set SeeAllData=true to ensure that the test class has the visibility to the Local Time App configuration custom settings
@isTest(SeeAllData=true)
private class ServiceAppointmentLocalTimeHelperTest {
    static testmethod void testTrigger(){
        // Create the test account record
	// Create any other records, such as Work Order, Work Type, Service Territory etc. needed by the ServiceAppointment object
	// Create/Insert the ServiceAppointment record (sa). Doing so will fire the trigger on this object and populate the time zone related custom fields

        List<ServiceAppointment> lstSA = [SELECT Id, Scheduled_Start_Timezone__c FROM ServiceAppointment WHERE Id=:sa.Id]; // where sa is the ServiceAppointment record you created earlier
        system.assert(lstSA.size() == 1);
        system.assert(lstSA[0].Scheduled_Start_Timezone__c != null);
    }
}

Modify Page Layout

Add the custom fields to the ServiceAppoinment page layout. Whenever you create or edit the ServiceAppoinement record, the trigger will fire and calculate the values for these fields.

Example 1

The related Account record in this example is in the US Pacific time zone. The calculated Scheduled Start Date and Scheduled End Date values are in the same time zone, namely Pacific Standard Time (PST).
ServiceAppointment Example 1

Example 2

The related Account record in this example is in the US Pacific time zone. The Scheduled End value is changed from 3/11/2023, 12:00 PM to 3/14/2023, 12:00 PM. The calculated Scheduled Start Date value is in PST but the calculated Scheduled End Date value is in Pacific Daylight Time (PDT), because in the US, the clocks are forwarded by an hour on March 12, 2023 to adjust for DST (Daylight Saving Time).
ServiceAppointment Example 2

Deploy to Production

Create the outbound change set in the sandbox and add the above components to it. Send the change set to the production org. Login to production org and then deploy the inbound change set.

Summary

In this article, we presented a more robust solution that takes into consideration DST changes while computing the Datetime values on the ServiceAppointment records. We illustrated this concept by using the Scheduled Start and Scheduled End Datetime fields. You can extend this solution to other Datetime fields in the ServiceAppointment object if needed. Moreover, you can implement similar code for other Salesforce objects where you have a similar requirement to show Datetime values in different time zones.

This site uses cookies. Find out more about this site’s cookies. ☒