SharePoint, Technical

Calculated Column Nested Function Limit

Most sites that have posted on the nesting function limit for calculated columns in SharePoint (at least the one’s I found) state that the limit for nesting is eight. Those sites include Intro to Data Calculations on Microsoft.com. My experience shows it’s higher than eight, but not as high as I need it. Sad smile

Create a list and add a column for “Likelihood” and a column for “Consequence”, each a number field. Now add a “Score” column as a calculated column and use the below as the formula:

=IF(AND(Likelihood=1,Consequence=1),1,IF(AND(Likelihood=1,Consequence=2),3,IF(AND(Likelihood=1,Consequence=3),6,IF(AND(Likelihood=1,Consequence=4),10,IF(AND(Likelihood=1,Consequence=5),15,IF(AND(Likelihood=2,Consequence=1),2,IF(AND(Likelihood=2,Consequence=2),5,IF(AND(Likelihood=2,Consequence=3),9,IF(AND(Likelihood=2,Consequence=4),14,IF(AND(Likelihood=2,Consequence=5),19,IF(AND(Likelihood=3,Consequence=1),4,IF(AND(Likelihood=3,Consequence=2),8,IF(AND(Likelihood=3,Consequence=3),13,IF(AND(Likelihood=3,Consequence=4),18,IF(AND(Likelihood=3,Consequence=5),22,IF(AND(Likelihood=4,Consequence=1),7,IF(AND(Likelihood=4,Consequence=2),12,IF(AND(Likelihood=4,Consequence=3),17,IF(AND(Likelihood=4,Consequence=4),21,”Unknown”)))))))))))))))))))

SharePoint will save the calculation just fine. Count the parens at the end there…19. Now, if you add one more, blammo:

=IF(AND(Likelihood=1,Consequence=1),1,IF(AND(Likelihood=1,Consequence=2),3,IF(AND(Likelihood=1,Consequence=3),6,IF(AND(Likelihood=1,Consequence=4),10,IF(AND(Likelihood=1,Consequence=5),15,IF(AND(Likelihood=2,Consequence=1),2,IF(AND(Likelihood=2,Consequence=2),5,IF(AND(Likelihood=2,Consequence=3),9,IF(AND(Likelihood=2,Consequence=4),14,IF(AND(Likelihood=2,Consequence=5),19,IF(AND(Likelihood=3,Consequence=1),4,IF(AND(Likelihood=3,Consequence=2),8,IF(AND(Likelihood=3,Consequence=3),13,IF(AND(Likelihood=3,Consequence=4),18,IF(AND(Likelihood=3,Consequence=5),22,IF(AND(Likelihood=4, Consequence=1),7,IF(AND(Likelihood=4,Consequence=2),12,IF(AND(Likelihood=4,Consequence=3),17,IF(AND(Likelihood=4,Consequence=4),21,IF(AND(Likelihood=4,Consequence=5),24,”Unknown”))))))))))))))))))))

Results in an error “Cannot complete this action. Please try again.” Go ahead…try again. Just kidding, don’t bother. So it looks like the limit is 19. I’ve tried this in two separate environments, but please post a comment if you see different results.

Edit (Solution): Here’s how I solved it, by the way. Split it out and “&”ed them together.

=IF(AND(Likelihood=1,Consequence=1),1,IF(AND(Likelihood=1,Consequence=2),3,IF(AND(Likelihood=1,Consequence=3),6,IF(AND(Likelihood=1,Consequence=4),10,IF(AND(Likelihood=1,Consequence=5),15,IF(AND(Likelihood=2,Consequence=1),2,IF(AND(Likelihood=2,Consequence=2),5,IF(AND(Likelihood=2,Consequence=3),9,IF(AND(Likelihood=2,Consequence=4),14,IF(AND(Likelihood=2,Consequence=5),19,IF(AND(Likelihood=3,Consequence=1),4,IF(AND(Likelihood=3,Consequence=2),8,IF(AND(Likelihood=3,Consequence=3),13,IF(AND(Likelihood=3,Consequence=4),18,IF(AND(Likelihood=3,Consequence=5),22,””)))))))))))))))&IF(AND(Likelihood=4,Consequence=1),7,IF(AND(Likelihood=4,Consequence=2),12,IF(AND(Likelihood=4,Consequence=3),17,IF(AND(Likelihood=4,Consequence=4),21,IF(AND(Likelihood=4,Consequence=5),24,IF(AND(Likelihood=5,Consequence=1),11,IF(AND(Likelihood=5,Consequence=2),16,IF(AND(Likelihood=5,Consequence=3),20,IF(AND(Likelihood=5,Consequence=4),23,IF(AND(Likelihood=5,Consequence=5),25,””))))))))))

PowerShell, SharePoint, Technical

SharePoint 2010 Web Templates

To get a list of all of the web templates in SharePoint 2010 you can run cmdlet Get-SPWebTemplate, which will get you:

Name Title LocaleId Custom
GLOBAL#0 Global template 1033 FALSE
STS#0 Team Site 1033 FALSE
STS#1 Blank Site 1033 FALSE
STS#2 Document Workspace 1033 FALSE
MPS#0 Basic Meeting Workspace 1033 FALSE
MPS#1 Blank Meeting Workspace 1033 FALSE
MPS#2 Decision Meeting Workspace 1033 FALSE
MPS#3 Social Meeting Workspace 1033 FALSE
MPS#4 Multipage Meeting Workspace 1033 FALSE
CENTRALADMIN#0 Central Admin Site 1033 FALSE
WIKI#0 Wiki Site 1033 FALSE
BLOG#0 Blog 1033 FALSE
SGS#0 Group Work Site 1033 FALSE
TENANTADMIN#0 Tenant Admin Site 1033 FALSE
ACCSRV#0 Access Services Site 1033 FALSE
ACCSRV#1 Assets Web Database 1033 FALSE
ACCSRV#3 Charitable Contributions Web Database 1033 FALSE
ACCSRV#4 Contacts Web Database 1033 FALSE
ACCSRV#6 Issues Web Database 1033 FALSE
ACCSRV#5 Projects Web Database 1033 FALSE
BDR#0 Document Center 1033 FALSE
OFFILE#0 (obsolete) Records Center 1033 FALSE
OFFILE#1 Records Center 1033 FALSE
OSRV#0 Shared Services Administration Site 1033 FALSE
PPSMASite#0 PerformancePoint 1033 FALSE
BICenterSite#0 Business Intelligence Center 1033 FALSE
SPS#0 SharePoint Portal Server Site 1033 FALSE
SPSPERS#0 SharePoint Portal Server Personal Space 1033 FALSE
SPSMSITE#0 Personalization Site 1033 FALSE
SPSTOC#0 Contents area Template 1033 FALSE
SPSTOPIC#0 Topic area template 1033 FALSE
SPSNEWS#0 News Site 1033 FALSE
CMSPUBLISHING#0 Publishing Site 1033 FALSE
BLANKINTERNET#0 Publishing Site 1033 FALSE
BLANKINTERNET#1 Press Releases Site 1033 FALSE
BLANKINTERNET#2 Publishing Site with Workflow 1033 FALSE
SPSNHOME#0 News Site 1033 FALSE
SPSSITES#0 Site Directory 1033 FALSE
SPSCOMMU#0 Community area template 1033 FALSE
SPSREPORTCENTER#0 Report Center 1033 FALSE
SPSPORTAL#0 Collaboration Portal 1033 FALSE
SRCHCEN#0 Enterprise Search Center 1033 FALSE
PROFILES#0 Profiles 1033 FALSE
BLANKINTERNETCONT… Publishing Portal 1033 FALSE
SPSMSITEHOST#0 My Site Host 1033 FALSE
ENTERWIKI#0 Enterprise Wiki 1033 FALSE
SRCHCENTERLITE#0 Basic Search Center 1033 FALSE
SRCHCENTERLITE#1 Basic Search Center 1033 FALSE
SRCHCENTERFAST#0 FAST Search Center 1033 FALSE
visprus#0 Visio Process Repository 1033 FALSE

If you also need to get the ID (for instance, to selectively apply functionality through a WebProvisioned event based on WebTemplateId), you can use the following:

$globalAdmin = New-Object -TypeName “Microsoft.SharePoint.Administration.SPGlobalAdmin”;
$webTemplates = $globalAdmin.VirtualServers[0].GetWebTemplates(“1033”);
$webTemplates | ft ID, Name, Title -AutoSize

Which would get you:

   ID Name Title
0 GLOBAL#0 Global template
1 STS#0 Team Site
1 STS#1 Blank Site
1 STS#2 Document Workspace
2 MPS#0 Basic Meeting Workspace
2 MPS#1 Blank Meeting Workspace
2 MPS#2 Decision Meeting Workspace
2 MPS#3 Social Meeting Workspace
2 MPS#4 Multipage Meeting Workspace
3 CENTRALADMIN#0 Central Admin Site
4 WIKI#0 Wiki Site
9 BLOG#0 Blog
15 SGS#0 Group Work Site
16 TENANTADMIN#0 Tenant Admin Site
2764 ACCSRV#0 Access Services Site
2764 ACCSRV#1 Assets Web Database
2764 ACCSRV#3 Charitable Contributions Web Database
2764 ACCSRV#4 Contacts Web Database
2764 ACCSRV#6 Issues Web Database
2764 ACCSRV#5 Projects Web Database
7 BDR#0 Document Center
14483 OFFILE#0 (obsolete) Records Center
14483 OFFILE#1 Records Center
40 OSRV#0 Shared Services Administration Site
3100 PPSMASite#0 PerformancePoint
3200 BICenterSite#0 Business Intelligence Center
20 SPS#0 SharePoint Portal Server Site
21 SPSPERS#0 SharePoint Portal Server Personal Space
22 SPSMSITE#0 Personalization Site
30 SPSTOC#0 Contents area Template
31 SPSTOPIC#0 Topic area template
32 SPSNEWS#0 News Site
39 CMSPUBLISHING#0 Publishing Site
53 BLANKINTERNET#0 Publishing Site
53 BLANKINTERNET#1 Press Releases Site
53 BLANKINTERNET#2 Publishing Site with Workflow
33 SPSNHOME#0 News Site
34 SPSSITES#0 Site Directory
36 SPSCOMMU#0 Community area template
38 SPSREPORTCENTER#0 Report Center
47 SPSPORTAL#0 Collaboration Portal
50 SRCHCEN#0 Enterprise Search Center
51 PROFILES#0 Profiles
52 BLANKINTERNETCONTAINER#0 Publishing Portal
54 SPSMSITEHOST#0 My Site Host
56 ENTERWIKI#0 Enterprise Wiki
90 SRCHCENTERLITE#0 Basic Search Center
90 SRCHCENTERLITE#1 Basic Search Center
2000 SRCHCENTERFAST#0 FAST Search Center
61 visprus#0 Visio Process Repository