SharePoint Calculated Columns

转载 2015年07月09日 01:49:06
SharePoint Calculated Columns are powerful tools when creating out-of-the-box solutions. With these columns, we can manipulate other columns in the list item. Below are a few basic functions complete with details on how to utilize them.  

There are some limitations, but we outline what they are and to work around them at the conclusion.


Basic Functions and Common Examples


SharePoint Calculated Columns1. Dates

One common use for a calculated column is to create deadlines and reminders for date fields. For example, a custom calendar where the End Date is always seven days later than the Start Date. In that example we would create a calculated column such that:

=[Start Date]+7

When using dates, adding to that date is done on a day-to-day basis. So in the formula above, we add 7 days.

2. 'If' Statements 
Another common use is setting the value of the column based on another column, usually a choice field. For example, we present the user with a checkbox. If they check the box, we want the status to be "complete", so we have the following formula:



In If Statements, the condition comes first, then the true case and finally the false case.

3. Boolean Operators


Sometimes our IF statements can get complex. To help us along the way, we have Boolean Operators. For example, in a column called Favorite Team, a user selects between SpursRedsCowboys andBengals. We want to determine what state they are probably from, so we have the following formula:


=IF(OR([Favorite Team]="Spurs",[Favorite Team]="Cowboys"),"Texas","Ohio") 

Also at our disposal are 'AND' and 'NOT'. All of these encapsulate their conditions with parentheses and separate them by commas.



4. The HTML Trick

SharePoint pages that utilize Query Strings are very powerful. These pages allow us to create one page that automatically filters based on a user selection. Through that we can create efficiencies and standardization. How do we navigate to those pages? We can do this through a calculated column and an HTML trick. 


Assume we have a list of Projects with a field for Title. We also have a Query String page called Project and the query parameter is ProjectName. In our list, we want to create a calculated column that concatenates an HTML string with the Title field to create our link. See the formula below:


=CONCATENATE("<a href="http://server/sitename/Project.aspx? ProjectName=",Title,"</a>")



Understanding Calculated Columns in SharePoint with HTML 

This formula brings together a typical anchor tag that will create a link to our page with the Query String properly set. The real secret sauce to making this work though is returning the formula as data type Number. This will convert the text to HTML. Now we input this column on a page in a list view and we have all of our Project Dashboard links! Consider using this also for images that are based on a calculation to display indicators.


SharePoint JS Link 之(一) 改变column的颜色

在SharePoint 2013里,新加了一个功能叫JSLink,有了它,不用写任何后台代码,只需要写JS就能修改列表项的显示。本文介绍如何写JS Link。...
  • SPFarm
  • SPFarm
  • 2015年02月05日 20:20
  • 1347

SharePoint Caml query Calculated Fields

Do you have Problems with the SPSiteDataQuery (CAML) query  of Sharepoint computed Fields. You are ...
  • reejayyang
  • reejayyang
  • 2013年04月11日 21:27
  • 570

SharePoint 2013的限制(三)非法字符

SharePoint里有一些字符是不被允许使用的。比如SharePoint默认会阻止上传一些类型的文件,比如exe。   再比如,上传名字含有{}的文件,会得到如下提示:   除此之外,S...
  • SPFarm
  • SPFarm
  • 2015年04月06日 11:49
  • 1067

hdu 1250 高精度+斐波那契

Hat's Fibonacci                                  Time Limit: 2000/1000 MS (Java/Others)    Memory L...
  • yu_ming_cong
  • yu_ming_cong
  • 2015年08月14日 20:22
  • 533

Defining Custom Columns in SharePoint – Field Types and Options

 Defining Custom Columns in SharePoint – Field Types and Options A common task for SharePoint ...
  • lingpaoershiyishiji
  • lingpaoershiyishiji
  • 2014年09月29日 14:29
  • 736

CodeForces 665E Beautiful Subarrays

题意:问你有多少个区间,异或起来大于等于k 思路:显然求个前缀和之后,就等于有多少对数异或起来大于等于k了,每次暴力爬字典树就好了,当k这一位等于0的时候,我们可以直接加上另外一边1的子树大小,因为...
  • qq_21057881
  • qq_21057881
  • 2016年04月23日 00:03
  • 504

多层数据库开发七:字 段 对 象

                                               第七章 字 段 对 象  Delphi 4用TField来操纵数据集中的字段。不过,具体的字段实际上都是T...
  • shuaihj
  • shuaihj
  • 2004年11月05日 11:51
  • 4035


公司在项目设计的时候,有一个需求,就是查出来的表的字段不唯一,一张表的字段可能是三个,也可能是五个,但是却要把它显示到页面,这个给我做ui的带来一点麻烦。因为以前一般用easyui 的datagrid...
  • zhang89xiao
  • zhang89xiao
  • 2016年04月13日 16:38
  • 1118

利用SharePoint Designer开发可循环工作流

利用SharePoint Designer开发可循环工作流 介绍大家都知道SharePoint工作流有两种开发(设计)方式:通过SharePoint Designer和Visual Studio.l ...
  • RainyLin
  • RainyLin
  • 2007年11月15日 13:57
  • 1614

SharePoint 2013 开发——开发并部署第一个APP

SharePoint 2013 开发——开发并部署第一个APP
  • FoxDave
  • FoxDave
  • 2015年06月12日 10:17
  • 6144
您举报文章:SharePoint Calculated Columns