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:

 
=IF([Checkbox]=TRUE,"Complete","Incomplete")


 

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 2010 At Work--Taming the Elusive Calculated Column:Logic Functions

对于新人来说,在SharePoint中使用公式

[转]SharePoint: Group By on more than 2 columns in a view (SP 2010)

原文地址: http://techtrainingnotes.blogspot.com/2011/01/sharepoint-group-by-on-more-than-2.html     ...

How v$recovery_file_dest.space_used is calculated from v$asm_file.bytes in ASM

 Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]      ...

jffs2_scan_inode_node(): Data CRC failed on node at ... Read ... calculated ...

【问题】jffs2_scan_inode_node(): Data CRC failed on node at ... Read ... calculated ... Empty flash at ...
  • junllee
  • junllee
  • 2012年08月03日 23:43
  • 3362

HOW SHIPPING DATES ARE CALCULATED. (文档 ID 1076040.1)

HOW SHIPPING DATES ARE CALCULATED. (文档 ID 1076040.1) 转到底部 In...
  • kikiwhq
  • kikiwhq
  • 2014年09月04日 15:10
  • 712

zabbix自定义key类型之计算(Calculated items)

说明: 计算类型的key定义主要是根据已定义过的key值来计算的。注意是已定义过的key值。 如这里我要创建一个计算linux服务器内存实际使用大小的监控项(计算方法为:vm.memory.siz...
  • dulgao
  • dulgao
  • 2016年12月01日 15:18
  • 1736

calculated-column-cheat-sheet

  • 2012年05月13日 11:25
  • 236KB
  • 下载

DataFrame包含两列特征,转换成Index不变,将其中一列value作为columns,对应的另一列作为values

DataFrame包含两列特征,转换成Index不变,将其中一列value作为columns,对应的另一列作为values我们的原始数据是: 很明显这是一个有两列数据的DataFrame。而这时...

bootstrap-table-fixed-columns.js

  • 2017年08月16日 17:10
  • 13KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SharePoint Calculated Columns
举报原因:
原因补充:

(最多只允许输入30个字)