function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Doug BurkhartDoug 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
Suraj PSuraj P
You could do this without Apex if you can make your "Recognition" object a detail of the Contact object. (Master-Detail relationship instead of a lookup relationship)
Doug BurkhartDoug Burkhart
Hi Suraj,

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
Suraj PSuraj P
Assuming that the Recognition records are added one at a time against each Contact, and in ascending order of date:
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)
Suraj PSuraj P
If these assumptions may not, necessarily be true, you may have to use an Apex Trigger.