How would you display your Contacts with upcoming birthdays? I’ve seen people use “Birthdays This Week,” “Birthdays This and Next Week,” and other reports to display the list.
I’ve also seen requirements for showing a person’s next birthday, to trigger an automatic email to each Contact on his/her birthday. Let’s see how it’s done:
[sourcecode title=”Formula: Next Birthday”]if( Month (Birthdate) < = Month(Datevalue( NOW() ) ), if ( Month (Birthdate) < Month(Datevalue( NOW() ) ), DATE( YEAR( DATEVALUE( NOW() ) ) + 1 , MONTH( Birthdate ) , DAY( Birthdate ) ), if ( Day (Birthdate) < Day ( Datevalue ( NOW() ) ), DATE( YEAR( DATEVALUE( NOW() ) ) + 1 , MONTH( Birthdate ) , DAY( Birthdate ) ), DATE( YEAR( DATEVALUE( NOW() ) ) , MONTH( Birthdate ) , DAY( Birthdate ) ) ) ), DATE( YEAR( DATEVALUE( NOW() ) ) , MONTH( Birthdate ) , DAY( Birthdate ) ) ) [/sourcecode]Next, use Batch Apex to send an email daily to all people where
|
1 |
Next_Birthday__c == date.today() |
. Easy!
Now we can put this into a report and a dashboard showing the next “n” birthdays. Sure, it’s possible that more than n people will have a birthday on a given day, but at least you know that the emails will go out to each of them! If you want to see everyone with birthdays in a certain range, make a custom report and click through from a dashboard or a custom link.
Enjoy!
RSS Feed
Andy Ognenoff says
July 6, 2011 at 20:07Nice formula David! Saved for future reference and I would think could be used for finding next occurrence of any date just by swapping out the Birthdate field.
Have you tested what happens with Feb 29th?
For my own use, I also reformatted a bit. Commas at the beginning of lines in if statements make it easy to select an entire condition to add or remove for testing. Posting it back here will probably mangle it though so let me know if you want to see how how I reformatted it.
Tal Frankfurt says
July 10, 2011 at 17:19Great formula! Did you look at the Birthday eMailer app on the AppExchnage (by force.com labs)?
http://appexchange.salesforce.com/listingDetail?listingId=a0N30000003I77zEAC
David Schach says
July 11, 2011 at 11:54Thanks for sharing that! I took a look, and it’s great for automatically emailing birthdays and anniversaries; it doesn’t, however, provide visibility into upcoming birthdays. A great complement to this formula, though. And a great example of using Batch Apex with email templates.
David Langan says
May 10, 2013 at 08:58Thank you for the formula! You made my life easier.
For the click-not-code inclined (like myself) you can also achieve the equivalent to the Apex trigger by adding in a hidden toggle field (I use a number field. Checkbox boolean values cannot be used in workflows). I trigger the initial time-based email workflow action when the toggle value equals 1; I also add a field update to this workflow to set the toggle value to zero.
A separate field update workflow is triggered when the toggle is updated to zero and sets the toggle to back 1 re-triggering the time based email workflow.