SharePoint Lists Column Validation

In our day to day development life, we often come across validating business rules & some common validation related to Email, Phone numbers & Zip Code. In SharePoint foundation 2010, we can achieve validation using formula under Column Validation section. We will check in this post on how to validate Email, Phone numbers & Zip Code using SharePoint out of Box Column Validation formulas.

1.        Email Column Validation

Open list and go to List settings

Click on Email column.

Insert below formula for validating email

Formula:

=AND(

    ISERROR(FIND(" ", [Email],1)),

    IF(ISERROR(FIND("@", [Email],2)),

        FALSE,

        AND(

            ISERROR(FIND("@",[Email], FIND("@", [Email],2)+1)),

            IF(ISERROR(FIND(".", [Email], FIND("@", [Email],2)+2)),

                FALSE,

                FIND(".", [Email], FIND("@", [Email],2)+2) < LEN([Email])

            )

        )

    )

)

Enter [Email] = [Enter your SharePoint Column Name]

The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this:
=OR(ISBLANK([YourColumnName]), And Formula From Above)

2.       Phone Number Column Validation

Open list and go to List settings.

Click on Phone Number Column.

Insert below formulas into Column Validation

Formula

=AND(

    LEN([Phone])=14,

    IF(ISERROR(FIND("(", [Phone],1)),

        FALSE,

        (FIND("(", [Phone]) = 1)

    ),

    IF(ISERROR(FIND(")", [Phone],5)),

        FALSE,

        (FIND(")", [Phone], 5) = 5)

    ),

    IF(ISERROR(FIND(" ", [Phone],6)),

        FALSE,

        (FIND(" ", [Phone], 6) = 6)

    ),

    IF(ISERROR(FIND("-", [Phone],10)),

        FALSE,

        (FIND("-", [Phone], 10) = 10)

    ),

    IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4))),

        FALSE,

        AND(

            1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000,

            1*MID([Phone], 2, 3) <> 911,

            1*MID([Phone], 7, 3) <> 911,

            1*MID([Phone], 7, 3) <> 555

        )

    )

)

Here [Phone] = [Enter your column name]

The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this: =OR(ISBLANK([YourColumnName]), And Formula From Above)

3.       Zip/Postal Code Validation

Open list and go to List settings.

Click on Zip/Postal Code Column.

Insert below formula into Column Validation

Formula

=OR([ZIP/Postal Code]="",LEN([ZIP/Postal Code])=5,

AND(EXACT(UPPER([ZIP/Postal Code]),LOWER([ZIP/Postal Code])),

LEN([ZIP/Postal Code])=10,NOT(ISERROR(FIND("-",[ZIP/Postal Code],6)))))

Here [Zip/Postal code] = [Enter your column name]

The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this: =OR (ISBLANK([YourColumnName]), And Formula From Above)

 References

 http://sharepointempower.com/2012/11/sharepoint-foundation-column-validation/

 http://adicodes.com/column-validations-and-list-validations-in-sharepoint-2010/

 http://thechriskent.com/2012/08/16/validate-email-address-columns-in-sharepoint/

Hide / Disable My Site, My Profile and Social Tags in SharePoint 2010

Hide / Disable My Site, My Profile and Social Tags

To hide My Site, My Profile and disable Social Tags

·                     Go to Central Administration > Application Management > Manage service applications > User profile service application

·                     From the ribbon, Click “Manage User Permissions” under People

·                     In the “Permissions for User Profile Service Application” pop up, select a user group, e.g. All authenticated Users. 

Uncheck the “Permissions” item, based on what you want to disable. Click OK to save the settings.

Information
Unchecking Use Personal Features will hide My Profile
Unchecking Create Personal Site will hide My Site 
Unchecking Use Social Features will disable Tagging, Note Board, and Ratings


Hiding the social tags across the farm
Since unchecking the above feature will only disable the tags, it is still visible on your web pages. You can completely remove them by opting for one of the below methods..
To completely remove from all web applications.. go to CA > System Settings > Farm Management > Manage farm feature
Deactivate the feature "Social Tags and Note Board Ribbon controls" feature.

Hiding the social tags for specific site or web
If some reason, you didn’t want the social tag to be shown in a particular site or web, you can use css styling to hide it. Following CSS will hide the tags...

1 2 3

.s4-socialdata-notif { display:none; }

You can put this directly on the page using a content editor web part or use a custom css file and include it in the master page

Calculated column to display month name from a date

I was having a requirement to display the month name from the selected date like January, February etc. I have created a calculated column and used a formula to display the month name.

Create a custom SharePoint list and a date column, a calculated column to it and other columns to it. For the demonstration purpose, I have created a list “Employee List”, with “Emp ID”, “Employee Name”, “Joining Date”, and “Month Calculated” columns. Here “Month Calculated” is the Calculated Column.

Above image shows the column details of "Employee List".

In the Calculated Column, use the following formula to get the month name from the “Joining Date” column.

=TEXT([Joining Date],"MMMM") -- To Display “January”, “February”, etc

Or

=TEXT([Joining Date],"MMM") -- To Display “Jan”, “Feb”, etc

Above image shows the “Month Calculated” calculated column.

Once the columns added to the List, Add new items by clicking “New”.

After selecting the “Joining Date” Click ok. Now the calculated column will display the month name for the selected “Joining Date”.

Add User’s Email Address to Their Profile via PowerShell

After deploying the SharePoint template for a client, I was doing some testing and noticed that the workflow failed because of an error. I checked the Workflow History and saw the following error message:

The e-mail message cannot be sent. Make sure the e-mail has a valid recipient

I searched around the SharePoint user interface and noticed the user that I had been testing the workflow with in fact did not have an email address. For some reason active directory (AD) did not pull the user’s email address into their user profile. I clicked on the edit profile link in SharePoint to try to edit their profile, but of course I wasn’t able to add/edit the email address.

I came to the conclusion that the only way I could add the user’s missing email address was via PowerShell. Here is the code that I used:

Set-SPUser -Identity "DOMAINusername" -Email "email@yourdomain.com"

-Web http://yourdomain.com

After adding the user’s email address to their user profile, I no longer received an error on the workflow. The issue was fixed!

SharePoint:DateTimeControl Access denied

In one of my project's implementations the SharePoint DateTimeControl has stopped working altogether and users with "Contribute" access were getting access denied to the field. In other words, the Calendar control was not opening, see below

It was referred like this in my application page

<SharePoint:DateTimeControl ID="dtselDate" runat="server" DateOnly="true" IsRequiredField="true" CssClassTextBox="ms-input" />

The Problem Background:

Actually DateTimeControl field uses the iframe calender page to render the actual calender. The location of iframe.aspx is C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS

The problem appeared to be with managed paths in the site collections, the Users with "Contribute access" were accessing the following path:

http://myserver/_layouts/iframe.aspx (Path to Root site collection where user doesn't have access)

However, the actual path should be http://myserver/sites/sitecollection/_layouts/iframe.aspx (Path to actual site collection where user HAVE valid access)

The solution:

The solution is pretty simple i.e. to set DatePickerFrameUrl to use the magic tokens $SPUrl SiteCollection (see below), which will ensure Contributers will access the right Site Collection.

DatePickerFrameUrl = "<% $SPUrl:~SiteCollection/_layouts/iframe.aspx %>"  DateOnly="true" runat="server" />

~site/ is an equivalent to SPContext.Current.Web.ServerRelativeUrl

~sitecollection/  is an equivalent to SPContext.Current.Site.ServerRelativeUrl

Reference

http://falakmahmood.blogspot.in/2010/05/sharepointdatetimecontrol-access-denied.html#.Ugt_mpIwfWI
http://chayadigital.wordpress.com/2011/10/04/error-with-spurl-tokens/

Programmatically Add/Upload a file to Document Library in SharePoint 2010

Simple steps to upload a file programmatically to SPDocumentLibrary in SharePoint 2010

SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                    using (SPSite site = new SPSite(guidSiteID))

                    {

                        using (SPWeb web = site.OpenWeb())

                        {

                            SPDocumentLibrary documentLib = web.Lists.TryGetList(strAFE_Attachments) as SPDocumentLibrary;

                            Stream fStream = fupAttachment.PostedFile.InputStream;

                            byte[] _byteArray = new byte[fStream.Length];

                            fStream.Read(_byteArray, 0, (int)fStream.Length);

                            fStream.Close();

                            web.AllowUnsafeUpdates = true;

                            string _fileUrl = documentLib.RootFolder.Url + "/" + Path.GetFileNameWithoutExtension(fupAttachment.PostedFile.FileName) + DateTime.Now.ToString("MMddyyyyHHmmss") + Path.GetExtension(fupAttachment.PostedFile.FileName);

                            bool IsOverwriteFile = false;

                            SPFile file = documentLib.RootFolder.Files.Add(_fileUrl, _byteArray, IsOverwriteFile);

                            SPListItem item = file.Item;

                            item["Title"] = _strAFE_No;

                            item.Update();

                            file.Update();

                            web.AllowUnsafeUpdates = false;

                        }

                    }

                });

Adding Folder and SubFolder to document library in SharePoint 2010

SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                    using (SPSite site = new SPSite(guidSiteID))

                    {

                        using (SPWeb web = site.OpenWeb())

                        {

                            SPDocumentLibrary documentLib = web.Lists.TryGetList(strAFE_Attachments) as SPDocumentLibrary;

                           

                            #region  Folder Creation

                            web.AllowUnsafeUpdates = true;

                            SPListItem newFolder = documentLib.Items.Add("", SPFileSystemObjectType.Folder, "TestRootFolder");

                            newFolder.Update();

                            //Creating Subfolder inside folder

                            string subFolderUrl = web.Url + "/" + newFolder.Url;

                            SPListItem subFolder = documentLib.Items.Add(subFolderUrl, SPFileSystemObjectType.Folder, "TestSubFolder");

                            subFolder.Update();

                            web.AllowUnsafeUpdates = false;

                            #endregion

                        }

                    }

                });