Example of solving complex grouping with SharePoint 2007 Calculated Value Column

Okay let me put the cards on the table: the syntax of calculated value column in SharePoint is a mess.

Recently I had to deal with creating views based on a field called Topic. Topics were grouped in to logical areas. Each area had so many topics under it. Each file in the Document Library would have a Topic from a pre-set list.

I simply wanted to created views that could show all records that had one of a set of topics. Problem in SharePoint is you can only have 10 OR filters in a view!!!! So I had to think creatively.


So what I decided to do was to create a new column on a document library which would assign an Area based on the Topic. Views could then be against this "Area" column rather than having to use filters on Topic.

The logic would be like this.

If Topic 1 or Topic 2 or Topic 3 then Area 1 Else
If Topic 4 or Topic 5 or Topic 6 or Topic 7 then Area 2 Else...

Simple enough to write in say VB or C# but next to impossible to manage in the formula you have to use for calculated values.

SharePoint does provide a CONDITIONAL calculated value that lets you check the value of another column as part of a formal. Its syntax is

=IF(Field=VALUE, True Value, False Value)

If a field is equal to a given value it returns the True Value, else it returns the false value. In the example I was working with I have a column called Topic with a set of pre-defined possible values. So I could write

=IF(Topic="Health Care", "Government", "")

Such a value simple puts the value of "Government" in a column if the Topic column is "Health Care". Not very useful. I extended this using an OR command. An example of using the OR command for a calculated column in SharePoint 2007 is:

=IF(OR(Topic="Health Care", Topic="Education", Topic="Defense"),"Government","")

It is critical you not put any returns or extra spaces in to formula. Yes I said that right.

So now imagine you wanted to create something like the logic above:

If Topic is Health Care or Education or Defense then set value to
Government Else
If Topic is Banking or Investment then set value to Finance Else
If Topic is Retail or Wholesale then set value to Commercial.

You can do this by embedding IF commands in to your argument, but it will quickly produce a really ugly mess which is very hard to even imagine not to say code.

What I did was a bit simpler. In the above example I would create 4 new columns. The first three columns would be of this form

Column1 Calculated Value

=IF(OR(Topic="Health Care",Topic="Education",Topic="Defense"),"Government","")

Column2 Calculated Value

=IF(OR(Topic="Banking",Topic="Investment"),"Finance","")

Column3 Calculated Value

=IF(OR(Topic="Retail",Topic="Wholesale"),"Commercial","")

So I have created 3 columns which are either empty or contain an area depending on the Topic. Now to create the Area I want I just created a fourth column which combined the text of the other 3

Column4 Calculated Value

=Column1&Column2&Column3

This may seem a strange way of doing thing but it avoids the almost impossible to maintain code of embedded logic in SharePoint column formulas. Even though the code is not pretty it is far less ugly than code you would produce the other way. I spent about one hour trying to make a single formula and found I could not manage it, the code was just far too ugly.