I have a confession. One of my fears when I left Fight Colorectal Cancer to join Convio is that I would lose my hands-on experience in Salesforce. That I would get too much distance from the day-to-day of actually using Salesforce/Common Ground at an organization. Crazy. Truth is, every day I’m faced with a new and interesting challenge to solve.

Our application, Common Ground, doesn’t have any membership functionality (yet). So when an organization wants to track membership, they need to either install another 3rd party app or add custom functionality to their Salesforce instance. Recently two different organizations wanted to track on a contact/account records the date when membership will expire based on when a membership payment was last received. The first wanted the date to be exactly one year later, the other wanted the date to be the last day of the month a year later.

In both cases, I didn’t want to advise creating any new custom objects or record types since it would make it harder for our team to provide support later. I came up with a rather simple solution that uses the brand new in Salesforce Spring ’12 cross-object workflow field update feature and just a couple of custom fields.

Start with a custom date field on the account object (ex: Membership Expiration) and a custom formula date field on the Contact that simply pulls the value from the account custom field.

We will use a cross-object workflow field update to fill in the field on the account based on the close date of an opportunity, which will then automatically be duplicated on all contacts in the account. Lost you yet? Don’t worry, it’s easier than it may sound.

Rather than create a new record type, I just added a new picklist value to the Common Ground Transaction Type field to indicate that it’s a Membership payment. We just need something in the opportunity to differentiate a membership payment from other gifts since we only want our workflow rule to fire for membership payments.

Now, the workflow rule. In this sample case, the rule will fire when the Transaction Type equals Membership and will be evaluated when the record is created or edited and didn’t meet the criteria before.

Now the fun part. Set a Field Update and tell it to update the custom date field you created on the Account. New in Spring ’12!

Select the custom date field you already created, and have it fill in a formula to set the date. In this case, this will calculate the last day of the month a year later.

DATE(YEAR(CloseDate)+1,MONTH(CloseDate)+1,1)-1

At first I was figuring out which months had 30, 31 or 28 days and figuring out how to account for leap years, etc. Then it dawned on me…just set it to the first day of the following month and then roll back a day. Tada! I love when the tricky problems are solved by simple solutions.

If you want the date to be exactly a year later, then use:

DATE(YEAR(CloseDate)+1,MONTH(CloseDate),DAY(CloseDate))

And there you have it. Update a date field from opportunity to account to contact with a single workflow rule and a couple of custom fields! (The Last Membership date field below is just a roll-up summary field)