Release Calculation

In our Department, we do a release twice a month, noted as "release a" and "release b". We have several tasks that go into each release, and several systems in the department. We need a way to track which tasks go into each release. So we have a task list.

This is where the title field becomes important. After a few releases, the lookup list becomes unbearable because several people on the team are entering releases into the release list but not creating any sort of standard title so that they are easy to find (and sorted properly) in the release lookup field in the task list.

You know how people are. People who enter information into lists sometimes have crazy ideas about how the 'title' field should be populated. Sometimes, strict standards are followed by everyone except that crazy lady who has a full jar of mayonnaise on her desk and a spoon (---shudder---), who enters crazy shit into your forms. This post will help you manage that crazyness so that some consistency is achieved.

I created a release list (just a simple custom list with a start date and a system name and a title field.) We go into the task list and create a lookup field so that we can assign each task into a release.

In the release list, I created a crazy-ass calculated field to calculate the title:

="["&System&" "&YEAR([Start Date])&"."&IF(MONTH([Start Date])<10,"0"&MONTH([Start Date]),MONTH([Start Date]))&"."&IF(DAY([Start Date])<15,"a","b")&"] "&System&" "&TEXT([Start Date],"mmm")&" "&YEAR([Start Date])&" "&IF(DAY([Start Date])<15,"First","Second")&" Release"

This gives us data that looks like this:

[Portal 2009.02.a] Portal Feb 2009 First Release
[Salesforce 2009.02.b] Salesforce Feb 2009 Second Release
[Portal 2009.02.b] Portal Feb 2009 Second Release
etc...

Now, we look at the fields from the task lookup field:
[Portal 2009.02.a] Portal Feb 2009 First Release
[Portal 2009.02.b] Portal Feb 2009 Second Release
[Salesforce 2009.02.b] Salesforce Feb 2009 Second Release
etc... all in the proper order!

Now, I create a SPD workflow that changes the title:

Attach the workflow to the release list.
Have the workflow start when a new item is created or changed.
Condition: IF TITLE IS NOT EQUAL TO CALCULATEDTITLE
VERY IMPORTANT: we don't want to create an endless loop.
Action: SET TITLE to RELEASE CALENDAR:CALCULATEDTITLE

All set!