Joel's SharePoint Architecture Blog

SharePoint Server, SharePoint Online and Office 365 Training, Architecture, Administration and Development

You are here: Home / SharePoint / SharePoint Thing a Day – 018 – Information Architecture. Calculated Columns.

SharePoint Thing a Day – 018 – Information Architecture. Calculated Columns.

by

Calculated Columns

#spthingaday 018 – Information Architecture. Calculated Columns. #SharePoint, #Office365.. SharePoint Lists and Libraries come alive when you add your own columns to them. You can add columns for any number or reasons… To track sales dates, part numbers, author names, due dates. Pretty much anything. And the columns you add can be shown in List and Library views. But sometimes you want to show a calculation based upon one or more other columns in your list. For that purpose, we can create Calculated Columns. (http://1jj.uk/spcalccols) The syntax is very straightforward, and is based upon Excel formulas. We can include conditions: IF([Column A] > [Column B], "Fine", "Badness") We can include maths: [Quantity] * [Cost] We can even do date maths: DATEDIF([Date A], [Date B], "d") But, no matter how hard you try, you can use that formula for "how many days old". This is because Calculated Columns are only evaluated every time your List Item gets saved. Unless you save your List Item every day, your "days old" column just means "how many days old this item was when it was last saved". Which is almost useless. DO NOT: write a workflow to nudge this value every day for, each List Item in your list. DO: consider JSLink or Client Side Rendering (http://1jj.uk/spcsr) for Classic views; and Column Formatting (http://1jj.uk/spocolfmt) or Field Customizers (http://1jj.uk/spfxfldcust) for Modern views. #microsoft #microsoftoffice #microsoftsharepoint #microsofttraining #officetraining #freetraining #trainingvideos #contentmanagement #enterprisecontentmanagement

A post shared by JFDI Consulting #spthingaday (@jfdiconsulting) on

SharePoint Lists and Libraries come alive when you add your own columns to them.

You can add columns for any number or reasons… To track sales dates, part numbers, author names, due dates. Pretty much anything.

And the columns you add can be shown in List and Library views.

But sometimes you want to show a calculation based upon one or more other columns in your list.

For that purpose, we can create Calculated Columns. (http://1jj.uk/spcalccols)

The syntax is very straightforward, and is based upon Excel formulas.

We can include conditions: IF([Column A] > [Column B], “Fine”, “Badness”) We can include maths: [Quantity] * [Cost]

We can even do date maths: DATEDIF([Date A], [Date B], “d”) But, no matter how hard you try, you can use that formula for “how many days old”. This is because Calculated Columns are only evaluated every time your List Item gets saved.

Unless you save your List Item every day, your “days old” column just means “how many days old this item was when it was last saved”. Which is almost useless.

DO NOT: write a workflow to nudge this value every day for, each List Item in your list.

DO: consider JSLink or Client Side Rendering (http://1jj.uk/spcsr) for Classic views; and Column Formatting (http://1jj.uk/spocolfmt) or Field Customizers (http://1jj.uk/spfxfldcust) for Modern views.