Saturday, May 11, 2013

Calculated Columns In SharePoint 2010

Introduction

Listed here are common functions used in SharePoint 2010 calculated columns.  See the references for additional details.

Definitions

     
Conditional Functions    
Functions Format Example {Result}
Return value at number index CHOOSE(Num, Val1, Val2, ...) CHOOSE(3, "A", "B", "C", "D") {"C"}
Simple If - Then IF(Condition, Val1, Val2) IF([Modified] > [Created], "Changed",
"Original") {"Changed" or "Original"}
     
Date and Time Functions    
Functions Format Example {Result}
Get Year YEAR (Date) YEAR([Created]) {2011}
Get Month MONTH (Date) MONTH([Created]) {3}
Get Week WEEKDAY (Date) WEEKDAY([Created]) {2}
Get Day DAY (Date) DAY([Created]) {29}
Get Hour HOUR (DateTime) HOUR([Created]) {13}
Get Minute MINUTE (DateTime) MINUTE([Created]) {51}
Get Second SECOND (DateTime) SECOND([Created]) {26}
Create Date DATE(Year, Month, Day) DATE(2011, 3, 29) {5/10/2013}
Get Date Difference DATEDIF (Date1, Date2, Unit) DATEDIF("5/5/2013","5/10/2013","M") {5}
Create Date DATEVALUE (Text) DATEVALUE("5/10/2013") {5/10/2013}
Create Time TIME (Hour, Minute, Second) TIME(19,45,21) {1/1/1900 7:45:21 PM}
Get Formatted Time TEXT([DateTimeField],"hh:mm:ss") TIMEVALUE("19:45") {1/1/1900 7:45 PM}
Get Formatted Weekday TEXT([DateField],"dddd")
TEXT([DateField],"ddd")
Tuesday
Tue
Get Formatted Month TEXT([DateField],"mmmm")
TEXT([DateField],"mmm")
September
Sep
Get Formatted Year TEXT([DateField],"yyyy")
TEXT([DateField],"yy")
2011
11
Get Formatted Date TEXT([DateField],"mmmm dd, yyyy" September 17, 2011
Get AM/PM IF(HOUR([Time]) < 12,"AM","PM") AM
Get Day/Night IF(AND(HOUR([Time])>6,HOUR([Time])<18),"Day","Night") Day
Get Wk commencing [Date]-WEEKDAY([Date])+1 1/2/2011
Get Week number ROUNDDOWN(([Date]-DATE(YEAR([Date]),1,1)+WEEKDAY(DATE(YEAR([Date]),1,1))-
WEEKDAY([Date])+1)/7,0)+1
5
     
Formatting Function    
Functions Format Example {Result}
Format Number TEXT([Sales],"$#,###.00") $1,234.56
     
Logical Functions    
Functions Format Example {Result}
AND AND (Condition1, Condition2) AND(4>=3,3>2) {True}
OR OR (Condition1, Condition2) OR(4>=3, 3<2) {True}
NOT NOT (Condition1) NOT(1=1) {False}
     
Math Functions    
Functions Format Example {Result}
Take Absolute ABS (Number) ABS(-1) {1}, ABS(1) {1}
Round to Even EVEN (Number) EVEN(0.5) {2}, EVEN(-1) {-2}
Round to Odd ODD (Number) ODD(1.5) {3}, ODD(-2) {-3}
Calculate Factorial FACT (Number) FACT(5) {5*4*3*2*1 = 120)
Find remainder MOD (Number1, Number2) MOD(5, 4) {1}, MOD(2010, 20) {10}
Get PI PI () PI() {3.14159265358979}
Round to specific number ROUND (Number1, Number2) ROUND(327.598, 1) {327.6}
Round up ROUNDUP (Num1, Num2) ROUNDUP(52.0001, 0) {53}
Round down ROUNDDOWN (Num1, Num2) ROUNDDOWN(327.598, 1) {327.5}
Get the sign SIGN (Number) SIGN(-5.9786) {-1}, SIGN(20678) {1},
SIGN(0) {0}
Calculate Square Root SQRT (Number) SQRT(64) {8}
Truncate TRUNC (Number) TRUNC(37.999999) {37}
Convert text to number VALUE (Text) VALUE("57") {57}, VALUE("00:05") {00:05}
Get the average AVERAGE (Num1, Num2, ...) AVERAGE(10, 0, "", "0") {5}, AVERAGE(5, 0,
TRUE) {2.5}
Get the average, ignore nonnumbers AVERAGEA (Num1, Num2, ...) AVERAGEA(10, 0, "", "0") {2.5}, AVERAGEA(5,
0, TRUE) {2}
Get the number of numbers COUNT (Num1, Num2, ...) COUNT(10, 0, "", "0") {2}, COUNT(5, 0,
TRUE) {2}
Count all items COUNTA (Value1, Value2, ...) COUNTA(10, 0, "", "0") {4}, COUNTA(5, 0,
TRUE) {3}
Get Max value MAX (Number1, Number2, ...) MAX(0, 2, "76", 100) {100}
Get largest number MAXA (Number1, Number2, ...) MAXA(0, 2, "76", 100, "Telephone", TRUE)
{100}
Get Min value MIN (Number1, Number2, ...) MIN(0, 2, "76", 100) {0}
Get smallest number MINA (Number1, Number2, ...) MINA(0, 2, "76", 100, "Telephone", TRUE)
{0}
Total all numbers SUM (Number1, Number2, ...) SUM(0, 2, "76", 100, TRUE) {179}
     
Text Functions    
Functions Format Example {Result}
get length of text LEN (Text) LEN("Hello") {5}
Get N characters from left LEFT (Text, Number) LEFT("I quite like camping", 6) {"I quit"}
Get N characters from right RIGHT (Text, Number) RIGHT("I quite like camping",4) {"ping"}
Get N1 characters starting at N2 MID (Text, Num1, Num2) MID("I quite like camping", 9, 11) {"like
campin"}
Gets index of search text in string SEARCH (Text1, Text2, Num) SEARCH("Banana", "Banana Banana", 4) {8}
Convert text to lower case LOWER (Text) LOWER("uRGEnT") {"urgent")
Convert text to upper case UPPER (Text) UPPER("uRGEnT") {"URGENT")
Capitalize first character each word PROPER (Text) PROPER("uRGEnT TICket") {"Urgent Ticket")
remove spaces from text TRIM (Text) TRIM(" some text ") {"some text"}
Cleans text of nonprintable characters CLEAN (Text) CLEAN("Line1?? Line2??") {"Line1 Line2"}
Replaces Number2 characters starting at Number1 from Text1 with Text2 REPLACE (T1, N1, N2, T2) REPLACE("Hello",2,4,"i") {"Hi")
Join text strings CONCATENATE (T1, T2, ...) CONCATENATE("A","B","C") {"ABC"}
Converts a number to a currency text, with the given number of decimals DOLLAR (Number, Decimals) DOLLAR(12.567,2) {"$12.57"}
Determine whether two values are identical EXACT (Text1, Text2) EXACT("Hi","hi") {False}
Returns Number with the given number of decimals as text (commas optional) FIXED (Num, Dec, NoCommas) FIXED(1057.26,1,TRUE) {"1057.3"},
FIXED(1057.26,0,FALSE) {"1,057"}
Repeat text N times REPT (Text, Number) REPT("Hi",3) {"HiHiHi"}
Convert text to value TEXT (Value, Format) TEXT([Created], "yyyy|mm") {"2011|03"}
     
Operators    
Functions Format Example {Result}
Add, Subtract, Multiply, Divide +, -, *, /  
Power (e.g. [Field]^2 = Squared) ^ 4^3 {64}
Concatanate (put two text values or
fields together)
& "4" & "3" {"43"}, [Field1] & [Field2]
Equal =  
Not Equal To <>  
Greater Than >  
Less Than <  
Greater than or equal to >=  
Less than or equal to <=  
     
Type / Error Checking Functions    
Functions Format Example {Result}
Determine if blank value ISBLANK (Value) IF(ISBLANK([Assignee]), "Needs assigning",
"Assigned")
Determine whether value returns an error
(except #N/A), False otherwise
ISERR (Value) ISERR(#REF!) {True}
Determine whether value returns error ISERROR (Value) ISERROR(#REF!) {True}
Determine whether value is logical ISLOGICAL (Value) ISLOGICAL(FALSE) {True}
Returns True if Value returns error #N/A,
False otherwise
ISNA (Value) ISERR(#N/A) {True}
Returns True if Value is not text or is
empty, False otherwise
ISNONTEXT (Value) ISNONTEXT(2) {True}, ISNONTEXT("Two")
{False}
Returns True if Value is a Number, False
otherwise
ISNUMBER (Value) ISNUMBER(2) {True}, ISNUMBER("Two") {False}
Returns True if Value is Text ISTEXT (Value) ISTEXT(2) {False}, ISTEXT("Two") {True}

References

Sunday, May 5, 2013

SharePoint 2010 SPSecurityTrimmedControl Permissions Enumeration

Introduction

When working with the SPSecurityTrimmedControl, it can be tricky knowing what combination of permissions to employ in order to accomplish the desired security trimming.  The Enumeration List below can help you make that determination easily.  This listing shows the combination of permissions to use to enable Visitors, Contributors, Designers, and/or Owners (full control) to view the items trimmed by this control.

Enumeration List
 
Group Permissions Name Description V C D O
List ManageLists Create and delete lists, add or remove columns in a list, and add or remove public views of a list.       X
List CancelCheckout Discard or check in a document which is checked out to another user.     X X
List AddListItems Add items to lists, add documents to document libraries, and add Web discussion comments.   X X X
List EditListItems Edit items in lists, edit documents in document libraries, edit Web discussion comments in documents, and customize Web Part Pages in document libraries.   X X X
List DeleteListItems Delete items from a list, documents from a document library, and Web discussion comments in documents.   X X X
List ViewListItems View items in lists, documents in document libraries, and view Web discussion comments. X X X X
List ApproveItems Approve a minor version of a list item or document.     X X
List OpenItems View the source of documents with server-side file handlers. X X X X
List ViewVersions View past versions of a list item or document. X X X X
List DeleteVersions Delete past versions of a list item or document.   X X X
List CreateAlerts Create e-mail alerts. X X X X
List ViewFormPages View forms, views, and application pages, and enumerate lists. X X   X
Site ManagePermissions Create and change permission levels on the Web site and assign permissions to users and groups.       X
Site ViewUsageData View reports on Web site usage.       X
Site ManageWeb Grant the ability to perform all administration tasks for the Web site as well as manage content. Activate, deactivate, or edit properties of Web site scoped Features through the object model or through the user interface (UI). When granted on the root Web site of a site collection, activate, deactivate, or edit properties of site collection scoped Features through the object model. To browse to the Site Collection Features page and activate or deactivate site collection scoped Features through the UI, you must be a site collection administrator.       X
Site ManageSubWeb Create subsites such as team sites, Meeting Workspace sites, and Document Workspace sites.       X
Site AddAndCustomizePages Add, change, or delete HTML pages or Web Part Pages, and edit the Web site using a SharePoint Foundation–compatible editor.     X X
Site ApplyThemeAndBorder Apply a theme or borders to the entire Web site.     X X
Site ApplyStyleSheets Apply a style sheet (.css file) to the Web site.     X X
Site CreateGroups Create a group of users that can be used anywhere within the site collection.       X
Site BrowseDirectories Enumerate files and folders in a Web site using Microsoft Office SharePoint Designer 2007 and WebDAV interfaces   X X X
Site CreateSSCSite Create a Web site using Self-Service Site Creation.        
Site ViewPages View pages in a Web site. X X X X
Site EnumeratePermissions Enumerate permissions on the Web site, list, folder, document, or list item.       X
Site BrowseUserInfo View information about users of the Web site. X X X X
Site ManageAlerts Manage alerts for all users of the Web site.       X
Site UseRemoteAPIs Use SOAP, WebDAV, or Microsoft Office SharePoint Designer 2007 interfaces to access the Web site. X X X X
Site UseClientIntegration Use features that launch client applications; otherwise, users must work on documents locally and upload changes. X X X X
Site Open Allow users to open a Web site, list, or folder to access items inside that container. X X X X
Site EditMyUserInfo Allows a user to change his or her user information, such as adding a picture.   X X X
Site ManagePersonalViews Create, change, and delete personal views of lists.   X X X
Site AddDelPrivateWebParts Add or remove personal Web Parts on a Web Part Page.   X X X
Site UpdatePersonalWebParts Update Web Parts to display personalized information.   X X X

References