Saturday 17 October 2020

How to retain formula field value on record update?

We have formula function ISNEW() to identify record is created or updated. ISNEW() is available in workflow and Process builder, not in formula field. 

What if we want to execute formula field value only in record creation and value should not be changed on record update. Let me share a business scenario.

Assume we have status icon image formula field on case shows different image based on the Entitlement End date. There was chance case record can refer to different entitlement, at that time business does not want to change status image formula on entitlement change , Instead there should be new status icon field which should be calculated based on different Entitlement.

If you are thinking to implement workflow or process builder, we cant use Image formula field and ISNEW function is not available in formula field.

Here is the workaround for this.

Create a new formula field which will be only executed when record created, there will be other formula field which will be executed all the time. Now business can easily report on both field values.

Workaround for ISNEW function in formula field is 

IF(MINUTE(TIMEVALUE( Craeteddate))== MINUTE(TIMEVALUE( LastModifiedDate))

Formula Field for Record creation 

IF(MINUTE(TIMEVALUE( Created_Date__c))== MINUTE(TIMEVALUE( LastModifiedDate)), IF(ISBLANK(AccountId),'',IMAGE(IF(( Primary_user_Entitlement_End_Date__c> Today()),'/img/msg_icons/confirm24.png','/img/msg_icons/error24.png'),'Entitlement Flag')), IMAGE(IF(( End_user_Entitlement_End_Date__c> Today()),'/img/msg_icons/confirm24.png','/img/msg_icons/error24.png'),'Entitlement Flag'))

Formula field for record update 

IF(ISBLANK(AccountId),'', IMAGE(IF((Entitlement.EndDate > Today()),'/img/msg_icons/confirm24.png','/img/msg_icons/error24.png'),'Entitlement Flag'))