Use of SharePoint Calculated Columns

Why Calculated columns important?

Assume we need to send out an automated company-wide birthday greeting email whenever it was an employee's birthday. The option that will come to our mind suddenly :

1.  Use SharePoint Designer to create a workflow that sends an email.
2.  Run a timer job every night.  This timer job would pull all employees that have a birthday that day from a SharePoint list, then trigger the workflow for each birthday found.

Then we may have one requirement that, in the body of the email for it to say “Happy Birthday to [employee name] on October 2nd” for example.  But if it’s the 1st, then there should be an “st” on the end of the birthday day.  Or “th” if it’s the 4th, and so on.

This is where the Calculated columns come into play.

Calculated Columns to the Rescue

We can create a few calculated columns to handle the day suffix, and well as displaying the day and the name of the birthday month, as below.

Birthday Month Name

It’s pretty easy to derive the name of the month from a particular date field using a simple formula.  The second parameter passed to the TEXT function is what tells it how to format the month, in this case it is formatting as the full month name, for example, October.

=TEXT([Date of Birth],”mmmm”)

Birthday Day

Very similar to the first example.  We will just grab the day from the date using the DAY function.

=DAY([Date of Birth])

Birthday Day Suffix

This is the tricky one.  We can use a combination of nested IF functions and the OR function in order to achieve the desired result.  Since a formula for SharePoint calculated columns need to be written in one line of code, we can’t do traditional if-then-else statements.  However, we can use nested IF statements, but if you have a lot of “else” clauses, it can get very messy.

=IF(OR([Birthday Day]=”11″,[Birthday Day]=”12″,[Birthday Day]=”13″),”th”,(IF(RIGHT([Birthday Day])=”1″,”st”,(IF(RIGHT([Birthday Day])=”2″,”nd”,(IF(RIGHT([Birthday Day])=”3″,”rd”,”th”)))))))

To create a calculated column, simply go into the list’s List Settings page and add a new column.  Then choose Calculated for the column type and fill out the Formula field.

List all Site Collections in a Farm and show in which Content DBs they are stored

In some cases, for example when analyzing or optimizing the distribution of Site Collections in a SharePoint farm, it may be helpful to see which Site Collections are stored in which Content Databases.
This can easily achieved by using this SharePoint Management Shell cmdlet:


Get-SPSite -Limit All | Select Url,ContentDatabase | Format-Table -Wrap -AutoSize > D:\Output\ShowAllSCs.txt

Alternatively it may also be helpful to dispaly all Site Collections in a certain Content Database:

Get-SPSite -Limit All -ContentDatabase | Format-Table -Wrap -AutoSize > D:\Output\ShowSCsInContentDB.txt

Or display all Site Collections in a certain Web Application:

Get-SPWebApplication https://webapplication.domain.com | Get-SPSite -Limit All | Select Url,ContentDatabase | Format-Table -Wrap -AutoSize > D:\Output\ShowAllSCsInWA.txt

Versioning in SharePoint

 In my opinion, versioning in SharePoint is one of its strongest features. It allows for tracking the activity of any item (document, event, task, etc.) and following the audit trail. In this post, I would like to highlight the most important features of versioning and how it can assist you, the end user, with your daily routine. Let’s first look at what versioning is and how it works.

Any time you change a document in a document library or edit any list item (in a custom list, calendar, task list, etc.) – a new version is created. Any change is recorded, whether it is a physical change to the document or just a metadata change. If you just view an item or a document by clicking on it – the new version is not created, it is only created when you actually change something.

WHAT ARE THE MAJOR BENEFITS OF VERSIONING?

1. Ability to audit the history of an item

Version history allows you to retrace the history of an item or a document, since the date and time it was first created till present. If it is a document you are checking, you can see all the changes taking place (physical changes to the document or metadata). If it is an item, like a task or calendar event, you can see the metadata changes made, who made them and when.

2. Ability to track metadata changes

Any metadata changes are perfectly visible in the version history log from above. Say, you have enabled versioning on an Issue Tracking to track help desk tickets. With version history, you can easily see what has happened to the ticket from the time it was submitted to present.

3. Ability to track content changes

If you enable versioning on a document library, you can access any previous versions of the document just by clicking on a previous version link.

4. Ability to restore previous versions

You can also restore older versions if need be. Say you created an important document and then you boss decided to edit and made changes that make no sense. No problem at all. Just click on a drop-down next to the version you would like to restore and click Restore. It will take this version and create a new one for you, making it the latest and greatest.

Document Sets - New Feature in SharePoint

WHAT IS A DOCUMENT SET?

Document Set is a special Content Type. To explain in basic terms, Document Set is a folder with metadata. Except, this folder can have metadata assigned to it. And the documents you put inside of that folder can also have metadata assigned to them. Awesome!

WHAT MAKES DOCUMENT SETS SPECIAL?

In addition to being able to have metadata combined with folders, document sets have these special and unique features that make them standout from other document management methods.

1. Ability to inherit metadata

You can set the Document Set such that any files uploaded to the Document Set (folder) will inherit the metadata from that folder.

2. Unique permission for each document set

You can also assign unique permission to each document set. You can do the same with regular folders, but the user experience is much better with document sets.

3. Ability to search across the document sets

All the document sets (folders) you create reside in a single library. That means that you can easily search across document sets since you will essentially be searching in a single document library.

DOCUMENT SET LIMITATIONS

1. It is all about documents

It is a limitation by default. But If you are thinking of using document sets to manage small projects, all you have on a welcome page is a document set. While you can add other web parts to the welcome page, they will be common to all document sets (in other words, you cannot have each welcome page contain its own task list or calendar for example).

2. Document Library can get really large

Say you configured a document library to house client data and are using document sets for it. With time, this library might get really large, impacting performance and ability to find stuff.

3. Metadata sync

If you decide to sync the document library with document sets to your desktop (link) – you would loose the metadata when you try to access the files on your PC. Metadata will stay in SharePoint, but will not get copied onto your computer.