Calculated columns in SharePoint are powerful tools that help create state-of-the-art solutions. Other columns can also be manipulated by using calculated columns.
How Calculated Columns in SharePoint Work
Dates:
One of the common uses of these columns is to generate reminders and deadlines for date fields. A custom calendar is an example where there is always a difference of seven days between Start and End date. In that specific example, we can generate a calculated column:
= [Start Date] + 7p>
While using dates, date addition is being carried out on day-to-day basis. Therefore, in the above formula, we add 7 days.
Boolean Operators:
Boolean operators hold a significant importance, especially when IF statements get complicated. For instance, a user has the option to choose between Spurs, Cowboys, Reds, and Bengals in a column known as Favorite Team. Moreover, you also have AND’ and NOT’ at your disposal. All of these can be separated by Commas, and their conditions encapsulated by parenthesis.
IF Statements:
To assign value to a column with reference to another column, is another common use of the calculated columns, commonly known as a choice field i.e., where the user is presented with a check-box. When a user checks the box, the status has to be complete, so there is the following formula:
= IF ([Checkbox] = TRUE, “Complete”, “Incomplete”)
In these statements, the conditions always come first, followed by the true case, which is succeeded by the false case.
Nested IF’s:
In one IF statement, there is a limitation of 7 nested IF’s. Commonly used nested IF’s are used to produce an output that is based on different kinds of results.
The HTML Trick:
SharePoint pages that make use of Query Strings are extremely powerful. These pages let us create a page that automatically carries out the filtering process, depending on the user selection. These help ensure standardization and efficiencies. This can be done viaa HTML trick and this type of column.
Today Function:
When it comes to calculated columns, Today Function is of great importance. Although SharePoint does not permit you to use this function, there are some ways around that. If you are looking to update with reference to Today’s date, a timer job or a workflow is suggested that can automatically update each item at a specific time and date.
Look-up Columns:
While looking to create the calculated column in SharePoint, you would find out that look-up fields do not exactly show up below the Insert Column heading. It is not possible to reference these columns in a this kind of column. The recommended step is to utilize a workflow that first assigns a value to a text field and then includes that field in the formula.
With that said, calculated columns in SharePoint are similar to formulas in Excel spreadsheet and it is a powerful tool you should understand.
