Skip to content

Making Sharepoint’s Calculated Fields Work for You

July 7, 2010

Calculated fields can be great in Sharepoint lists. They can quickly determine appropriate values and keep them up to date, reducing overhead and a source of error at the same time. Unfortunately, they have a few limitations, although most of them would only be encountered by those of us who are always pushing Sharepoint’s limits. For example, in the Content Query Web Part, it’s not possible to group by a calculated field. This is because the values are calculated on the fly every time data is requested, and the CQWP’s logic doesn’t allow items to be grouped by a dynamically generated value. In reality, the value isn’t going to change unless the data changes in almost all cases, so it seems like it would make more sense to have a static field containing that calculated value, and simply have Sharepoint recalculate every time the item is modified. Luckily, we can duplicate this behavior using workflows.

Microsoft pushes workflows as ways to move documents around to the appropriate people in an organization so that a prescribed set of steps can be followed. They can do so much more than that, though. Recently, I was working on a glossary feature in Sharepoint, and I wanted to output the entire list of glossary terms with definitions. Naturally, this entails a gigantic list with thousands of items (or at least it needs to be able to scale to that magnitude), so using a single list with a view is not an option given the 2,000 item ceiling, so the Content Query Web Part was the best path to pursue. Because of the volume of data, I didn’t just want to output a list of items, as that would be almost unusable. The approach I wanted to take was to group the items by their first letter. For this, I made a Calculated field called Letter. The formula is =LEFT(Title,1) to get the first letter from the Title field. Great! That wasn’t too bad. But as we know about Sharepoint, nothing is ever simple. It is (of course) not possible to group by this column due to the reason I stated above.

Enter the workflow. You need Visual Studio 2005 for this part. Workflows can be created in Sharepoint Designer 2007, but they have to be tied to a single list which isn’t very functional in this case because there are going to be multiple lists pulling into one view (the whole reason I’m using a CQWP). To create your workflow in VS2005, follow Sahil Malik’s tutorial, starting with the first step. This walkthrough helped me make a very effective workflow having never created one before.

Now, on to what my workflow actually does. It takes that calculated field (Letter) and copies the value to a static text field named GroupLetter, and also converts that letter to its corresponding number (1-26) so that I can filter using < or > later. Here’s the code for my workflow:

// Set the letter from the Letter field to this variable
// Sometimes Sharepoint appends string;# so remove that
// .ToUpper() to have consistent capitalization

string newLetter = workflowProperties.Item["Letter"].ToString().Replace("string;#", String.Empty).ToUpper();
Hashtable nums = new Hashtable(); // Kind of like an array to store values

nums.Add("A", 1);
nums.Add("B", 2);
nums.Add("C", 3);
// [Do this for the entire alphabet]
nums.Add("X", 24);
nums.Add("Y", 25);
nums.Add("Z", 26);

// Set GroupLetter to the newLetter value
workflowProperties.Item["GroupLetter"] = newLetter;

// Set GroupNum to the corresponding number
workflowProperties.Item["GroupNum"] = nums[newLetter];

// Update the value in the list
workflowProperties.Item.Update();

That does exactly what I want it to do, fairly elegantly. To make it work, you need to have a list with all the fields the workflow references; I like to build the list and save it as a template so I can easily create another just like it. The workflow also needs to be deployed, which the tutorial explains.

Once it’s deployed to Sharepoint, you need to add it to the list so it’ll start doing its thing. To do this, open the list in View All Site Content, go to Settings, and click List Settings. Under the Permissions and Management column, choose Workflow settings. On this screen, you’ll see all the workflows (if any) that are currently attached to this list. Click Add a workflow and choose your newly created workflow from the workflow templates. You’ll have to give it a unique name, and at the bottom you can choose how it runs. Because I want it to update every time an item is changed, I selected the bottom two options to start the workflow when an item is changed or a new one is created. You can also allow users to start it manually on items if you like.

Once you finish that step, you’re done! Your workflow will now run how you set it to. I now have a Content Query Web Part displaying my glossary items grouped by letter, and if I want to have a page for A-D, for example, I can simply filter when 0 < GroupNum < 5 and I’ll get just those items. I hope this shed some light on Sharepoint workflows, as they are an incredibly powerful tool to automate list tasks that would otherwise create a great deal of overhead.

Resources I Used:

Advertisements
2 Comments leave one →
  1. Sylvain Girard permalink
    February 23, 2011 5:18 am

    Hi,

    This is a different take on a problem I’m also facing. You’re solution pointed me in an “event receiver” direction. Is there any obvious reason why you didn’t use an event receiver to set that GroupLetter field? It seems to me that a workflow would creat a big overhead in this case or am I missing something?

    Best regards,
    Sylvain Girard

  2. George Mead permalink
    July 24, 2012 1:31 pm

    Thank you for writing this. I stumbled on this when looking for a way to have the SharePoint View grouped by A, B, C, etc based on a LastName field. While you may not be able to group by a calculated field in the Content Query Web Part, you can group on a calculated field in a SharePoint View and this solution worked beautifully for me without needing to write any code. I simply created the Calculated Column as described, then created a SharePoint View and grouped on the newly created Calculated Column – wonderful!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: