Wednesday, October 29, 2008

How to use Calculated value column

This article focus on three SharePoint functionality.

  1. 1.) How to use Calculated value column.
  2. 2.) How to format text using LOWER() and PROPER () functions.
  3. 3.) How to Merge two column value.

Let's begin, We seen from our past experience that, Individual users use to enter data their own style or non-standard format. Since this is part of end users habit, its very difficult to train everyone in the organization.

Given below example shows how end user's entered SharePoint contact list information.

User Data Input

image

SharePoint List View

image

Above scree shots show that the data entered inconsistence way and display in same manner.

Since this known problem in the industry we need to manage these issue.

Let's see how can manage below requirements

  1. 1.) Display eMail Address to lower case,
  2. 2) Merge First and Last name and Display as Full Name.

SharePoint : Go to Contact List and Click Settings and then Click List Settings.

image

Create new column called "FormatedeMailAddress" and click "Calculated" On Formula text box type following formula.

=LOWER (eMailAddress)

image

Save and Go back to Contact List view you can see the email address display on Lower case format.

image

Let us look how to merge two column value "First and Last" as "Full Name" and format text in to proper case.

SharePoint : Go to Contact List and Click Settings and then Click List Settings. Create new column called "Full Name" and click "Calculated" On Formula text box type following formula.

image

=CONCATENATE(PROPER(First)," ",PROPER(Last)) 

Save and Go back to Contact List view you can see the email address display on Lower case format.

image

CONCATENATE () - Joins several text strings into one text string.

PROPER() - Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

LOWER() - Converts all uppercase letters in a text string to lowercase

No comments: