daveh, Author at Dave Helgerson

Add Days to a Salesforce Formula DateTime Field and Adjust for Daylight Savings Time

15 Sep

Adding days to a DateTime field can lead to an unexpected time when the added date passes a daylight savings time. For example, if 7 days is added to 3/9/2018 10:00 AM, the new date will 3/16/2018 11:00 AM because it has passed the daylight savings time date. This occurs because the date is stored in Salesforce’s databases as GMT, and adding days to a GMT date doesn’t adjust for users in zones that observe daylight savings time.

The following works only when your users are always in a timezone that observes daylight savings time.


/* daylight savings starts March 11. Daylight savings ends November 4 */
/* adjust time if 7 day appointment will pass over daylight savings date */
Your_DateTime_Field__c + 7 +
IF(3 = MONTH(DATEVALUE(Your_DateTime_Field__c+7)),
 IF( AND(DAY(DATEVALUE(Your_DateTime_Field__c+7))>=11, DAY(DATEVALUE(Your_DateTime_Field__c+7))<=18),
  -(60/1440),
  0
 )
 ,
0
)
+ 
IF(11 = MONTH(DATEVALUE(Your_DateTime_Field__c+7)),
 IF(AND(DAY(DATEVALUE(Your_DateTime_Field__c+7))>=4, DAY(DATEVALUE(Your_DateTime_Field__c+7))<=11),
  (60/1440),
  0
 )
,
0
)

/* daylight savings starts March 11. Daylight savings ends November 4 */

/* adjust time if 7 day appointment will pass over daylight savings date */

Your_DateTime_Field__c + 7 +

IF(3 = MONTH(DATEVALUE(Your_DateTime_Field__c+7)),

IF( AND(DAY(DATEVALUE(Your_DateTime_Field__c+7))>=11, DAY(DATEVALUE(Your_DateTime_Field__c+7))<=18),

-(60/1440),

)

IF(11 = MONTH(DATEVALUE(Your_DateTime_Field__c+7)),

IF(AND(DAY(DATEVALUE(Your_DateTime_Field__c+7))>=4, DAY(DATEVALUE(Your_DateTime_Field__c+7))<=11),

(60/1440),

)

Salesforce Date Formula for Finding the Next Business Day that Skips Holidays

3 Feb

Finding the next business day using a Salesforce formula field can be challenging, and making the formula skip holiday dates increases the complexity. Below is what I’ve come up with after digging through many examples on the web, and borrowing logic that made sense. I would not call this great because it requires updating the formula field each year, and it is likely that this will be forgotten. But, here it is.


CASE(
 CASE(
  MOD( DATEVALUE(My_Date_Field__c) - DATE(1900, 1, 7), 7),
  0, DATEVALUE(My_Date_Field__c)+1, /*if Sun,CloseDate+1=Monday */
  1, DATEVALUE(My_Date_Field__c)+1, /*if Mon,CloseDate+1=Tuesday */
  2, DATEVALUE(My_Date_Field__c)+1, /*if Tues,CloseDate+1=Wednesday */
  3, DATEVALUE(My_Date_Field__c)+1, /*if Wed,CloseDate+1=Thursday*/
  4, DATEVALUE(My_Date_Field__c)+1, /*if Thurs,CloseDate+1=Friday */
  5,DATEVALUE(My_Date_Field__c)+3, /*if Fri,CloseDate+3=Monday next week */
  6,DATEVALUE(My_Date_Field__c)+2, /*if Sat,CloseDate+2=Monday next week */
  null
 ) 
, DATEVALUE("2018-01-01"),DATEVALUE("2018-01-02") /* New Year's Day */
, DATEVALUE("2018-01-15"),DATEVALUE("2018-01-16") /* Martin Luther King Jr.'s Birthday */
, DATEVALUE("2018-02-19"),DATEVALUE("2018-01-20") /* Washington's Birthday */
, DATEVALUE("2018-05-28"),DATEVALUE("2018-01-29") /* Memorial Day */ 
, DATEVALUE("2018-07-04"),DATEVALUE("2018-07-05") /* Independence Day */
, DATEVALUE("2018-09-03"),DATEVALUE("2018-09-04") /* Labor Day */
, DATEVALUE("2018-10-08"),DATEVALUE("2018-10-09") /* Columbus Day */
, DATEVALUE("2018-11-22"),DATEVALUE("2018-11-23") /* Thanksgiving Day */
, DATEVALUE("2018-12-25"),DATEVALUE("2018-12-26") /* Christmas Day */
, DATEVALUE("2019-01-01"),DATEVALUE("2019-01-02") /* New Year's Day */
, CASE(
  MOD( DATEVALUE(My_Date_Field__c) - DATE(1900, 1, 7), 7),
  0, DATEVALUE(My_Date_Field__c)+1, /*if Sun,CloseDate+1=Monday */
  1, DATEVALUE(My_Date_Field__c)+1, /*if Mon,CloseDate+1=Tuesday */
  2, DATEVALUE(My_Date_Field__c)+1, /*if Tues,CloseDate+1=Wednesday */
  3, DATEVALUE(My_Date_Field__c)+1, /*if Wed,CloseDate+1=Thursday*/
  4, DATEVALUE(My_Date_Field__c)+1, /*if Thurs,CloseDate+1=Friday */
  5,DATEVALUE(My_Date_Field__c)+3, /*if Fri,CloseDate+3=Monday next week */
  6,DATEVALUE(My_Date_Field__c)+2, /*if Sat,CloseDate+2=Monday next week */
  null
 )
)

CASE(

MOD( DATEVALUE(My_Date_Field__c) - DATE(1900, 1, 7), 7),

0, DATEVALUE(My_Date_Field__c)+1, /*if Sun,CloseDate+1=Monday */

1, DATEVALUE(My_Date_Field__c)+1, /*if Mon,CloseDate+1=Tuesday */

2, DATEVALUE(My_Date_Field__c)+1, /*if Tues,CloseDate+1=Wednesday */

3, DATEVALUE(My_Date_Field__c)+1, /*if Wed,CloseDate+1=Thursday*/

4, DATEVALUE(My_Date_Field__c)+1, /*if Thurs,CloseDate+1=Friday */

5,DATEVALUE(My_Date_Field__c)+3, /*if Fri,CloseDate+3=Monday next week */

6,DATEVALUE(My_Date_Field__c)+2, /*if Sat,CloseDate+2=Monday next week */

null

)

, DATEVALUE("2018-01-01"),DATEVALUE("2018-01-02") /* New Year's Day */

, DATEVALUE("2018-01-15"),DATEVALUE("2018-01-16") /* Martin Luther King Jr.'s Birthday */

, DATEVALUE("2018-02-19"),DATEVALUE("2018-01-20") /* Washington's Birthday */

, DATEVALUE("2018-05-28"),DATEVALUE("2018-01-29") /* Memorial Day */

, DATEVALUE("2018-07-04"),DATEVALUE("2018-07-05") /* Independence Day */

, DATEVALUE("2018-09-03"),DATEVALUE("2018-09-04") /* Labor Day */

, DATEVALUE("2018-10-08"),DATEVALUE("2018-10-09") /* Columbus Day */

, DATEVALUE("2018-11-22"),DATEVALUE("2018-11-23") /* Thanksgiving Day */

, DATEVALUE("2018-12-25"),DATEVALUE("2018-12-26") /* Christmas Day */

, DATEVALUE("2019-01-01"),DATEVALUE("2019-01-02") /* New Year's Day */

, CASE(

MOD( DATEVALUE(My_Date_Field__c) - DATE(1900, 1, 7), 7),

0, DATEVALUE(My_Date_Field__c)+1, /*if Sun,CloseDate+1=Monday */

1, DATEVALUE(My_Date_Field__c)+1, /*if Mon,CloseDate+1=Tuesday */

2, DATEVALUE(My_Date_Field__c)+1, /*if Tues,CloseDate+1=Wednesday */

3, DATEVALUE(My_Date_Field__c)+1, /*if Wed,CloseDate+1=Thursday*/

4, DATEVALUE(My_Date_Field__c)+1, /*if Thurs,CloseDate+1=Friday */

5,DATEVALUE(My_Date_Field__c)+3, /*if Fri,CloseDate+3=Monday next week */

6,DATEVALUE(My_Date_Field__c)+2, /*if Sat,CloseDate+2=Monday next week */

null

)

Get Record Type ID by Name for a Salesforce Object

14 Jan

Salesforce provides an Apex method for retrieving an object’s record type record id. The method accepts the record type label instead of the developer/api name, and this makes the Apex method sensitive record type label changes. Here is a typical example of the method in use. It strings together methods for the object’s schema, record type info, and record type id retrieval.


Id recTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('Industrial Deal').getRecordTypeId();

Id recTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('Industrial Deal').getRecordTypeId();

There are a few workarounds to prevent hard coding the record type’s label. The most popular is to use a custom label. A custom label can easily be updated when the record type label changes. Also, it won’t require changing Apex code, and it will come over when building/refreshing a sandbox. The downside is that you must remember to change both if the record type’s label changes. Other alternatives to a using a custom label include custom settings and custom metadata.


// with a custom label. This assumes a custom label by the name OpportunityRecTypeIndustrialDeal exists
Id recTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get(Label.OpportunityRecTypeIndustrialDeal).getRecordTypeId();

// with a custom setting. This assumes there is a custom setting object ObjectRecordTypeLabel__c that contains a field RecordTypeLabel__c
ObjectRecordTypeLabel__c ortl = ObjectRecordTypeLabel__c.getInstance('Opportunity-Industrial-Deal');
Id recTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get(ortl.RecordTypeLabel__c).getRecordTypeId();

// with custom metadata. This assumes there is a custom metadata object ObjectRecordTypeLabel__mdt 
// querying against a custom metadata object doesn't count against soql limits
ObjectRecordTypeLabel__mdt ortl = [SELECT DeveloperName, MasterLabel 
                                     FROM ObjectRecordTypeLabel__mdt 
                                    WHERE DeveloperName='Opportunity-Industrial-Deal' 
                                    LIMIT 1];
Id recTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get(ortl.MasterLabel).getRecordTypeId();

// with a custom label. This assumes a custom label by the name OpportunityRecTypeIndustrialDeal exists

Id recTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get(Label.OpportunityRecTypeIndustrialDeal).getRecordTypeId();

// with a custom setting. This assumes there is a custom setting object ObjectRecordTypeLabel__c that contains a field RecordTypeLabel__c

ObjectRecordTypeLabel__c ortl = ObjectRecordTypeLabel__c.getInstance('Opportunity-Industrial-Deal');

Id recTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get(ortl.RecordTypeLabel__c).getRecordTypeId();

// with custom metadata. This assumes there is a custom metadata object ObjectRecordTypeLabel__mdt

// querying against a custom metadata object doesn't count against soql limits

ObjectRecordTypeLabel__mdt ortl = [SELECT DeveloperName, MasterLabel

FROM ObjectRecordTypeLabel__mdt

WHERE DeveloperName='Opportunity-Industrial-Deal'

LIMIT 1];

Id recTypeId = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get(ortl.MasterLabel).getRecordTypeId();

** You will want to handle exceptions with all of these techniques. First, if the record type is not found. Second, if the Custom Setting or Custom Metadata record was not found.

Querying the RecordType sobject is also possible, but not recommended because the query will count against the SOQL limits in the processing transaction.


Id RecTypeId;
try {
  RecTypeId = [SELECT Id, SobjectType, Name, DeveloperName
                 FROM RecordType
                WHERE SobjectType='Opportunity'
                      and DeveloperName='Industrial_Deal'
                LIMIT 1];
} catch (exception e) {
  // do appropriate error handling
}

Id RecTypeId;

try {

RecTypeId = [SELECT Id, SobjectType, Name, DeveloperName

FROM RecordType

WHERE SobjectType='Opportunity'

and DeveloperName='Industrial_Deal'

LIMIT 1];

} catch (exception e) {

// do appropriate error handling

}

The record type information can also be acquired through the global describe methods. This can be useful if you are retrieving other information about the object in addition to the record type. Describe methods return a lot of information about the object itself, the object’s fields, and relationships. Below is an example of using describe methods to get the record type Id.


// a chain of methods to get the record type Id by label
Id recTypeId = Schema.getGlobalDescribe().get('Opportunity').getDescribe().getRecordTypeInfosByName().get('Industrial Deal').getRecordTypeId();

// break the describe methods apart at each step
Schema.SObjectType objType = Schema.getGlobalDescribe().get('Opportunity');
Schema.DescribeSobjectResult objTypeDesc = objType.getDescribe();
map<Id, Schema.RecordTypeInfo> recTypeMap = objTypeDesc.getRecordTypeInfosById();
Schema.RecordTypeInfo rtByName =  recTypeMap.get('Industrial Deal');

// a chain of methods to get the record type Id by label

Id recTypeId = Schema.getGlobalDescribe().get('Opportunity').getDescribe().getRecordTypeInfosByName().get('Industrial Deal').getRecordTypeId();

// break the describe methods apart at each step

Schema.SObjectType objType = Schema.getGlobalDescribe().get('Opportunity');

Schema.DescribeSobjectResult objTypeDesc = objType.getDescribe();

map<Id, Schema.RecordTypeInfo> recTypeMap = objTypeDesc.getRecordTypeInfosById();

Schema.RecordTypeInfo rtByName = recTypeMap.get('Industrial Deal');

Salesforce Dashboard Rotation and Refresh with Tampermonkey

13 Jan

Have you ever wanted to rotate through a series of Salesforce dashboards and have them auto refresh? There are multiple questions on the success community and other forums requesting exactly this. The easiest way to rotate through dashboards is to use a browser plugin that switches between pages. However, data in the dashboards can only be scheduled to refresh once per day. A more frequent refresh requires a user to click the refresh button on the dashboard page.

Enter the Tampermonkey browser plugin and a little Javascript. Tampermonkey is a plugin that allows you to execute Javascript code when a page loads in the browser. To solve the problem of getting a refreshed rotation of dashboards, I wrote a little Javascript code that will redirect to a series of dashboard URLs and automatically click the refresh button without user interaction.
Continue reading →

Convert a number to a string left padded with 0s in Apex

5 Nov


Integer MyInt = 54;
String MyString = String.valueOf(MyInt).leftPad(5, '0');

system.assertEquals('00054', MyString);

Integer MyInt = 54;

String MyString = String.valueOf(MyInt).leftPad(5, '0');

system.assertEquals('00054', MyString);