You need to sign in to do that
Don't have an account?
Doug Burkhart
Evaluate the number of Consecutive Months in which there is a related record
We have a custom object "Recognition" related to Contacts. This helps us track our employees and if they were recognized for performance each month. If I am recognized during June 2017, I will get a Recognition record created with custom field Date Issued = 6/30/17 (We always list it as the last day of the month for that month) and Recognition checkbox = True. I need to create a field on the Contact object that evaluates for only those who have a recognition during the prior month, how many consecutive months prior they have been recognized. Example:
Jane Smith has recognition for:
- June 2017
- May 2017
- April 2017
- March 2017
- February 2017
John Smith has recognition for:
- June 2017
- April 2017
- March 2017
- February 2017
- January 2017
Bob Smith has recognition for:
- March 2017
- February 2017
- January 2017
This field would then evaluate to the following:
Jane Smith - 5 (Last 5 Months but not the 6th prior month)
John Smith - 1 (Last Month but not two months ago)
Bob Smith - 0 (Not Last Month)
I think this can be done through apex but I'm not a true developer so I wouldn't be able to write it myself. I would need it to basically look through all the recognitions and find the most recent month they didn't have one where recognition (checkbox) = true and count the number that have occurred since then.
Any suggestions/help would be appreciated.
Doug
Jane Smith has recognition for:
- June 2017
- May 2017
- April 2017
- March 2017
- February 2017
John Smith has recognition for:
- June 2017
- April 2017
- March 2017
- February 2017
- January 2017
Bob Smith has recognition for:
- March 2017
- February 2017
- January 2017
This field would then evaluate to the following:
Jane Smith - 5 (Last 5 Months but not the 6th prior month)
John Smith - 1 (Last Month but not two months ago)
Bob Smith - 0 (Not Last Month)
I think this can be done through apex but I'm not a true developer so I wouldn't be able to write it myself. I would need it to basically look through all the recognitions and find the most recent month they didn't have one where recognition (checkbox) = true and count the number that have occurred since then.
Any suggestions/help would be appreciated.
Doug
The Recognition Object is a detail in a master/detail relationship with the Contact object. We already have fields on the Contact that summarize the total number of Recognitions but I'm not sure how I can use a summary field to count backwards not knowing when/if there is a recognition record missing for a specific month. Do you have any recommendation on how to do that?
Doug
a) Create a roll-up summary Date field (Max_Recognition_date__c) on Contact, that gets the maximum date of recognition
b) Define a worflow rule on Contact that fires when the Max Recognition Date field advances ( Max Recognition Date > priorvalue(Max Recognition Date), or was blank before (ISBLANK(priorvalue(Max Recognition Date)))
c) When the rule triggers, invoke a field update that updates the recognition count (Recognition_Count__c) field on the Contact record using this formula:
IF(Max_Close_Date__c - priorvalue( Max_Close_Date__c )>31,1,blankvalue( Recognition_Count__c,0)+1)