Author: Rishabh Dubey
Automation 1- To calculate the duration of picklist fields in Days
For Example, We have 4 opportunity stage Picklist values (Qualification, Prospecting, proposal, negotiation ).
For 4 Picklist values, we have to create 5 fields in total — 4 Number fields (1 number field for 1 picklist value), 1 Date field, and 1 process builder to update all the duration fields.
Please follow the details mentioned below:
1. Create custom Date field: Setup | Object Manager | Opportunity| Fields, and relationship | For Data Type Select Date | Field Label: Time Stamp.
AND
2. Create custom Number field for first picklist value (Qualification): Setup | Object Manager | Opportunity| Fields, and relationship | For Data Type Select Number | Field Label: “Qualification Duration”(Decimal Places= 0).
Note: Follow the second step for other picklist values.
3. Create a Process builder which will Update the “Time Stamp” field and “duration fields”, whenever a picklist has been changed.
3.1 Setup| In Quick Find Search for Process Builder and click on it.
3.2 Click on New | Process Name “Update Time Stamp”| The process starts when: “A Record Changes”.
3.3 Click + to Add Object, Select Opportunity from the Object drop-down list.
3.4 To Start the process select when a record is created or edited | Click Save.
3.5 Click + Add Criteria.
3.6 Enter a name for this criteria node.- Qualification Time Stamp.
3.7 Criteria for Executing Actions: Formula evaluates to true.
3.8 Add a Formula: OR( AND( ISNEW(),ISPICKVAL([Opportunity].StageName,” Qualification “)) ,AND(ISCHANGED([Opportunity].StageName ),ISPICKVAL([Opportunity].StageName,” Qualification “)) ) | Click Save
3.9 In Immediate Action, Click + Add Action and Choose Field Update | Action Name “Qualification Time Stamp”| Record Type: Select the Opportunity record that started your process.
3.10 Set new Field Value: Field = “Time Stamp”, Type = “Formula”, Value = Today().
3.11 Click Use this formula and Save and Activate the Process
Note: Follow the same steps from 3.5 for other picklist values in the next nodes.
The above nodes trigger every time the Opportunity Stage changes, it will update with the current date value.
4. Create new nodes under the above nodes in the process builder which will Update the “TimeStamp” field, whenever a picklist value has been changed.
4.1 Click + to Add Criteria.
4.2 Enter a name for this criteria node. Qualification Duration.
4.3 Criteria for Executing Actions: Formula evaluates to true.
4.4 Add a Formula: — AND (ISCHANGED ([Opportunity].StageName ),ISPICKVAL(PRIORVALUE([Opportunity].StageName),”Qualification”)) | Click Save.
4.5 In Immediate Action Click +Add Action and Choose Field Update | Action Name “Qualification Duration”| Record Type: Select the Opportunity record that started your process.
4.6 Set new Field Value: Field = “Qualification Duration”, Type = “Formula”, Value = IF(OR(ISNULL([Opportunity].Qualification_duration__c ), [Opportunity].Qualification_Duration__c <= 0), Today()- PRIORVALUE([Opportunity].Time_stamp__c ), [Opportunity].Qualification_Duration__c +(Today()- PRIORVALUE([Opportunity].Time_stamp__c )))
4.7 Click Use this formula | do the same for rest picklist values.
4.8 Once all the nodes are created select “Evaluate the next criteria” in the “Specify What Happens After Evaluating This Criteria” section for all the nodes. Save and Activate the process.
5. The above process, calculates the overall duration of how long the Opportunity Stage was under Qualification and in other stage values.
6. Say, the Opportunity Stage was under “Qualification” for 3 days and then moved onto “Proposal” stage and was again changed back to “Qualification” and stayed for 2 more days; so the process adds the previous 3 days duration and the current 2 days giving an Overall Duration of 5 Days. This is how the data will look like on the record Page.
Automation 2 — To calculate duration in Minutes
For Example, We have 4 Case Status Picklist values (New, Working, Waiting on the customer, Escalated).
For 4 Picklist values, we have to create 5 fields in total — 4 Number fields (1 number field for 1 picklist value), 1 Date/ Time field, and 2 process builders to update all the duration fields.
1. Create custom Date field: Setup | Object Manager | CASE| Fields, and relationship | For Data Type Select Date/Time | Field Label: Time Stamp.
AND
2. Create custom Number field for first picklist value (New): Setup | Object Manager | CASE| Fields, and relationship | For Data Type Select Number | Field Label: “New Duration”(Decimal Places= 0).
Note: Follow the second step for other picklist values.
3. Create a Process builder which will Update the “Time Stamp” field and duration fields, whenever a picklist has been changed.
a. Setup| In Quick Find Search for Process Builder and click on it.
b. Click on New | Process Name “Update Time Stamp”| The process starts when: “A Record Changes”.
c. Click + to Add Object, Select Case from the Object drop-down list.
d. To Start the process select when a record is created or edited | Click Save.
e. Click + Add Criteria.
f. Enter a name for this criteria node.- Case Time Stamp.
g. Criteria for Executing Actions: — Formula evaluates to true.
h. Add a Formula: OR(AND (ISNEW(),ISPICKVAL([Case].Status , “New”) ),
i. AND (ISCHANGED([Case].Status), ISPICKVAL([Case].Status , “New” )) ) | Click Save
j. In Immediate Action, Click + Add Action and Choose Field Update | Action Name “New Time Stamp”| Record Type: Select the Case record that started your process.
k. Set new Field Value: Field = “Time Stamp”, Type = “Formula”, Value = NOW() .
l. Click Use this formula and Save and Activate the Process
Note: Follow the same steps from e. for other picklist values in the next nodes.
The above nodes trigger every time the Case Status changes, it will update with the current Date\Time value.
4. Create new nodes under the above nodes in the process builder which will Update the “Time Stamp” field, whenever a picklist value has been changed.
4.1 Click + to Add Criteria
4.2 Enter a name for this criteria node.- New Duration
4.3 Criteria for Executing Actions: Formula evaluates to true
4.4 Add a Formula: AND (ISCHANGED ([Case].Status ),ISPICKVAL(PRIORVALUE([Case].Status ),”New”)) | Click Save
4.5 In Immediate Action Click +Add Action and Choose Field Update | Action Name “New Duration”| Record Type: Select the Case record that started your process
4.6 Set new Field Value: Field = “New Duration”, Type = “Formula”, Value = IF(OR(ISNULL([Case].New_Duration__c ),[Case].New_Duration__c <= 0),(NOW()- PRIORVALUE([Case].Time_stamp__c ))*1440 ,( [Case].New_Duration__c +((NOW()- PRIORVALUE([Case].Time_stamp__c ) )*1440 )))
4.7 Click Use this formula. Do the same for the rest picklist values.
4.8 Once all the nodes are created select “Evaluate the next criteria” in the “Specify What Happens After Evaluating This Criteria” section for all the nodes. Save and Activate the process.
5. The above process calculates the overall duration of how long the Case Status was under New and in other stage values.
6. Say, the Case Status was under “New” for 3 minutes and then moved onto “Working” Status and was again changed back to “New” and stayed for 2 more Minutes; so the process adds the previous 3 Minutes duration and the current 2 Minutes giving an Overall Duration of 5 Minutes.
