Recent

SharePoint Calculated columns to show Active Items

Creating filtered views with multiple conditions can usually be made simpler by using Calculated Columns to group any OR conditions together.

For example, let’s say you have an Issues list with a Status choice field with the following choices:
Pending
In Progress
Waiting on Someone
Completed
Cancelled

If we wanted to create an ‘Active’ items view, we would probably want to show all items that were either Pending, OR In Progress, OR Waiting on Someone.  Instead of having three conditions in our Filtered view, instead we can create an ‘Active’ Calculated Column that marks all Pending, In Progress, and Waiting on Someone items as Active.  This would make the filtered view much simpler:
Calc-Field-Filtered-View-16-1

This would also make it much easier to add additional active Status choices later, as all you have to do is update your calculated column instead of having to update all your views.

Creating a Calculated Column for a Filtered View

1. Go to the list or library, click on the list/library tab, select the list/library settings option
Calc-Field-Filtered-View-16-2

2. Under the Columns section, click on the Create column link
Calc-Field-Filtered-View-16-3

3.  Enter a column name, select the Calculated type:
Calc-Field-Filtered-View-16-4

4.  Create the Calculated Column Formula, for this type of calculated formula you will want
to combine an IF and OR function together like in this example if you have 2 values:

=IF(OR([Status]=”Pending”,[Status]=”In Progress”),”Yes”,”No”)

Breaking down this structure, the image below shows the text explanation of the part of the color coded part of the formula below:
Calc-Field-Filtered-View-16-9

We actually have to put the OR before the fields with the comma actually representing the OR.  Another example is if you have 3 Status values:

=IF(OR([Status]=”Pending”,[Status]=”In Progress”,[Status]=”Waiting on Someone”),”Yes”,”No”)

=IF(OR(
Then click on field you want to evaluate
Calc-Field-Filtered-View-16-5

5.  Add an equals sign and then the value of the field inside of double quotes:
Calc-Field-Filtered-View-16-11

6.  Add a comma for the OR, then click on the 2nd field you to evaluate, followed by an equals sign and then the value of the field inside of double quotes:
Calc-Field-Filtered-View-16-12

7.  Add a closing bracket, a comma followed by the value you want the field to be if the IF statement is true inside of double quotes:
Calc-Field-Filtered-View-16-13

8.  Add a comma followed by the value you want the field to be if the IF statement if false inside of double quotes, then add another closing bracket:
Calc-Field-Filtered-View-16-14

9.  Once your function is completed, select the single line of text data type and click the OK button.
Calc-Field-Filtered-View-16-6

10.  Your list will now have a new column on it, in our example Waiting on Someone and Pending items are a Yes for Active, while Completed and Cancelled items are a No:
Calc-Field-Filtered-View-16-7

This will make it much simpler to create views as you can now just filter on the Active column:
Calc-Field-Filtered-View-16-8

About Nick Hurst (84 Articles)
SharePoint and Nintex developer who strives to find easy to deploy and maintain solutions for business problems.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: