Calculated Fields don't leave home without them

Being new to Bamboo and SharePoint, most of my time is spent on self learning and working on custom solutions.  Working on custom solutions means interacting with customers, which is the best part of my job as this is where I get to see how Bamboo Web Parts are being used as part of a process or an overall solution and not just as an end point or tool in themselves. This is the most creative aspect of my job as our first task usually is to understand the real need behind the request which allows us to sometimes suggest a change to the approach and resolve the issue by using out of the box SharePoint or by stringing together some Bamboo web parts. Here are a few examples of how we used calculated fields as the solution for some of our customers business needs.

1.  The custom request that wasn't - it came from a customer using the Bamboo Alert Plus Web Part to send alerts to task assignees one day before the task is due. The web part worked fine, except for the people who had tasks due on Mondays. These people couldn't benefit from the alert because they were rarely in the office on Sunday to see the email message. For tasks due on Monday, the customer wanted to send the alert the preceding Friday. They called and asked for a custom version of our Alert Plus web part

A calculated field came to the rescue (no customization needed), we just needed to add the field to the list and configure the alert differently. Here's how we changed the Tasks List first:

1. Create a calculated column (data type = single line of text) in the Tasks list to store the day (i.e. Monday) the task is due. The calculation for this column is: “=TEXT(WEEKDAY([Due Date]),"dddd")”. Call this column “Day”.
2. Create another calculated column (data type= Date and Time) called “Alert Date” in the Tasks list to store the day the alert should be sent. The calculation for this column is: “=IF(Day="Monday",[Due Date]-3,[Due Date]-1)
3. Create a view in the Tasks list called Alert that includes all incomplete tasks where “Alert Date” = [Today].
4. Create a Bamboo alert for the Task List for items in this view  which will trigger the alert  on the days when an item is present in this view

2.  Jeff, a member of the Bamboo Solutions team recently showed a customer how to solve his dilemma by using a calculated field. This customer was looking for a way to provide a unique incremental identification code for each item in a list. The code needed to have a set prefix followed by an incremental number.  For example with our projects, we have PXXX for our custom project numbers where the XXX is an incremental number, like P001, P003, etc.  You can create an incremental identification code by using calculated fields and the ID column.  Our customer wanted to display PXXXX where XXXX is a 4 digit incremental.  To achieve this you create two columns.  The first one defines how many zeroes you need to prefix the ID with since you always want XXXX to be four digits.  The second one stores the value for the incremental identification code itself. 

In this case the first column was called “ID Set”  and the formula is as follows –

=IF(ID<10,"000",IF(AND(ID<100,ID>=10),"00",IF(AND(ID<1000,ID>=100),"0","")))

As a result if ID is only 1 digit, the result of the column is 000

If ID is 2 digits, the result of the column is 00 - and so on

And if the ID is 3 digits, the results is 0

Else nothing is return.

The next custom column is called “Incremental Form ID” and the formula is as follows –

="P"&[ID Set]&ID

3. Another custom request that wasn't came from a customer wanting to display two lines of information for each event on the Bamboo Calendar Plus Web Part. Since the Web Part allows you to only have one field set as your display field, a calculated field came to the rescue again. In this example I created a calculated field (Display for Calendar) that joined two columns in the Calendar list so I could display the Title and Location for each event in the same field:

 

The result in Calendar Plus now shows the Meeting Title and the Location:

 

I found the logic for calculating "day" on a  Microsoft Office Online page which has a rather extensive list of common formulas that can be used in a list or a library.  So bottom line, sometimes the answer lies in the list itself where a few calculated fields may get you the data you need to group or sort by, set your alert or display on, or filter on and so forth. However, sometimes the data of a calculated field can also limit what you can do with it specially when linking to it or using it as a lookup from another web part so an end to end test of the scenario where you will be applying the data is always the best approach.

---

Posted Apr 24 2008, 05:13 PM by DaisyAnand