## Using Calculated Value Fields to obtain the first character in a string

8/3/2012

I frequently receive requests for displaying contents of a list in an A-Z index of sorts where items also need to be grouped by the first character in their title or name or some other value. I know there are different ways of solving this. This post provides a "crude" way of satisfying this request using Calculated columns.

Let's say that the Title column is the one that needs to be sorted alphabetically. I then create a calculated column (for our example, we're going to name it IndexCode) and I use the formula:

code([Title])

This returns the code for the first character in the string contained in the Title column. In some cases, that's all I need. However, since this formula returns a numeric value (65 for A, 66 for B, 67 for C, and so forth), it might not be useful when you need to display it on a page. So my next step is to convert that number back to a character. For this, I created yet another calculated column (continuing with our example, we're going to call Index) and this time I use the following formula:

char([IndexCode])

This formula returns the character specified by the code number in the IndexCode column (A for 65, B for 66, C for 67, etc.). Now I can use this one in, for example, Content Query Web Parts for grouping my items.

Lastly, users may not care for typing things in upper case. If this is something you may want to use for your index, you may want to use the upper([ColumnName]) formula.

For more formulas that you can use in SharePoint, you may want to visit this other web site.

## Concatenating Columns in a SharePoint List

2/13/2012

We have used the formula below to concatenate information such as document name and version, or to create unique IDs in SharePoint.

1. Create a Calculated value column

2. Use the formula below,

=CONCATENATE([ColumnName1],[ColumnName2])

Note:
Replace the generic ColumnName1 and ColumnName2 in the example above with the name of your respective columns.

## SharePoint Calculated Values: Displaying the Day of the Week for a Given Date

2/13/2012

Use the solution below if you are asked to provide a column in a SharePoint custom list that displays the day of the week based on another column that contains an actual date.

In the example to the left, the first column (Date) displays a date in the M/D/YYYY format, while the second column (Day) displays the day of the week for the date to the left.

The column to the left consists of a Calculated column.

The formula needed to translate the information in the Date column is:

=TEXT(WEEKDAY([Date]),"dddd")

Just replace Date with the (internal) name for the column applicable to your own list.

Use dddd to display the full name of the week (for example, 'Sunday').

Use ddd to display the abbreviated name of the week (for example, 'Sun').

## Lookups to SharePoint Choice Columns

2/13/2012

For some reason that Microsoft probably only knows, you cannot create lookup columns to Choice columns within other custom lists. Lookups seem to only work on Single Line of Text type of columns, which doesn't make much sense to me. However, there is a work-around using Calculated values.

I discovered this once when I was trying to add a lookup column pointing to a very extensive list of countries, and a few other things, such as regions, divisions, etc.

The purpose of using Choice columns is for most part to force the user to make selections from a predefined list, so as to avoid duplicate values or mispellings.

Why would Microsoft not allow pointing lookup columns to choice columns is beyond me. But well, let's dive in the work-around I found. By the way, this works for both MOSS 2007 and SharePoint 2010.

So the first thing we need to do is create a custom list where to host the Choice column.
As you can see in the example image to the left, I've created the Country column as a Choice column.

The next step is to create the column that will actually be used for the lookup. I'm going to call this column LookupCountry.

I'm going to designate is a Calculated value column. And then I'm going to insert the Country column.

The next thing you need to do is determine the data type that will be returned from the column. For my example, I'm good using Single line of text; therefore, I won't change the default value suggested by SharePoint.

So now we need to go to the second list, from where we want to reference the list of countries.

Create a new column on this second list and select Lookup as the type of information.

Make any additional selections you may need (such as whether to require that this column must contain information).

Select your first custom list, the one where you created the calculated value column at the Get information from drop-down list.

And then select the Calculated value column from the list (where it says In this column).

Save everything and voilá! Once some values are input in the first list, the lookup drop-down on the second list should display some values.

## Author

I'm still amazed at how many people think that SharePoint can be used as a transaction tool or complain about things that should be private or confidential are not so much so when hosted on SharePoint. SharePoint is a collaborative platform, people!!! The name says it all: Share... Point... See?