SharePoint 2010 – Boundaries and Limits

Discovered this one with one of my clients today.

Error Message: This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (8) enforced by the administrator.

Cause: SharePoint 2010 lists limit the number of fields that can be displayed in a list view. In this case, it was a 2007 list that was upgraded to 2010. The list worked just fine in 2007, but failed to render with this error in 2010. It turned out the list view contained 100+ fields. The 2010 limit is 80.

Fix: None

Workaround: Split the view into two views, the first view containing the core data and the first half of the extra fields and the second view containing the core data and the rest of the extra fields.



Cheers
C

How do I – Create a recursive escalation workflow in SharePoint using Visio 2010 Premium

One of my attendees to my Human Workflow with Visio 2010 and SharePoint Designer 2010 session in Las Vegas recently, emailed me with an interesting question. Eric was looking to recursively check if a CSR ticket was resolved and then resend the email until it was completed. After responding to Eric with my recommendation on how to address his specific needs, I started thinking about escalation workflows in SharePoint. Escalation is always interesting and usually more complex to implement that most people expect when trying it for the first time. So I modified my model I generated for Eric and grew it into an escalation workflow for a fictitious company.

If you’ve ever tried doing something similar, you probably found out quickly that SharePoint won’t allow you to create a circular relationship in the workflow back to itself. This fact alone had drive many people to more advanced workflow tools like Sourcecode’s K2[blackpearl]. We do not however, have to establish a visible circular relationship in the workflow. Here is my design:



You will note the following:
EscalationCount – This is a metadata field that we add to the form data which keeps track of how many times we’ve escalated.
The workflow terminates after incrementing the EscalationCount value.
Our workflow is published with settings that restarts the workflow if any values in the active item changes. THIS IS THE IMPORTANT SETTING TO REMEMBER!!!

This is how this workflow would function:
When the workflow first starts, we will have an EscalationCount value of 0. Checking this value would force it down the first row of the workflow path.
An email is sent to the CSR
The workflow dehydrates (goes to sleep) for 7 days.
When it wakes up, it checks if the task is complete. It should be noted that this check can be anything you want it to be, as long as it signifies that the work has been completed. If after 7 days the work is found to have been completed, the workflow ends normally. If however the work isn’t complete, the EscalationCount is incremented at #25. Because the workflow is configured to restart itself upon change, incrementing the count will restart the workflow from the top thus creating the circular relationship we need.
Check that EscalationCount is 1.
Look up the CSR’s manager.
Send an email to both parties.
Dehydrate for 2 days.
Check if task is completed. If not completed, increment EscalationCount again.
Check that EscalationCount is 2.
Look up the manager’s manager. In this case we called it the Department Manager.
Send an email to all three.
Dehydrate for 1 day.
Check if task is completed. If not completed, increment EscalationCount again.
Check that EscalationCount is 3.
Look up the Department Manager’s manager, in our case, the CIO.
Send and email to all four.
Dehydrate for 1 day.
Check if task is completed. If not completed, increment EscalationCount again.
Check if EscalationCount is 4 or more.
Look up the CIO’s manager, the CEO.
Send and email to all 5. (Honestly, if the workflow ever makes it to here, this company should NOT be in business!!!)
Dehydrate for 1 day.
Check if task is completed. If not completed, increment EscalationCount again.

At this point, the workflow will continue to send email to all 5 people on a daily basis. That’ll continue until the task is completed, or (more likely) the workflow is removed from the list altogether.

So that’s how we do recursive escalation workflow using Visio 2010 Premium. From here you simply export the workflow to SharePoint Designer 2010 and implement it on your target list.



Cheers
C

A must do in Las Vegas

Two weeks ago, I was honored to be presenting several sessions at the SharePoint Connections Fall Conference held at Mandalay Bay in Las Vegas. After the conference, my wife and I had the opportunity to hang out with our friends Joe and Mellissa. Joe introduced us to the Biscayne in the Tropicana casino. Assistant Food & Beverage Manager, Brian Dermody, personally saw to our needs and the service was top notch. I took the opportunity to order the T-Bone steak and I have to say it was hands down the best steak I’ve had in a long time. In a town where there’s so much frilly food around, it’s hard to find a good quality steak for us carnivore types, but I will certainly be back at Biscayne on my next trip to Vegas.

If you’re in the area and are looking for some great food with great service and a nice ambiance, I would highly recommend a trip to Biscayne at Tropicana.



Cheers
C

How do I solve – This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold enforced by the administrator?

If you’ve ever migrated site collections from one farm to another, or upgraded from a 2007 farm to a 2010 farm, you may encounter this error the following error:

“This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (8) enforced by the administrator.”





This is caused by Resource Throttling that was introduced in SharePoint 2010. The settings can easily be adjusted to cater to your specific environment and are set for each web application. To adjust the limits, follow these steps:
Open Central Administration
Click “Application Management” in the left navigation menu.
Click “Manage Web Applications” on the Application Management page.
A list of all your web applications in the target farm will be displayed. Select the target web application by clicking the white space between the Title and the URL.
The Ribbon should now light up. Click on “General Settings”. Do NOT click the gears icon above General Settings as it will take you to the General Settings page instead of the Resource Throttling page.
A dropdown menu will activate. On this menu, click “Resource Throttling”.






On the Resource Throttling page, scroll down to the List View Lookup Threshold section. By default, this value is set to 8.
Change the value to the desired number.
Scroll down and click “OK”.







Now when you navigate back to the list view in question, provided the lookups is less than the number you just set, the view should render normally.







Cheers
C

How do I – Resolve the Powershell New-SPConfigurationDatabase command error – The pipeline has been stopped, This SharePoint farm currently has pending upgrades

I was building a fresh SharePoint farm and issued the “New-SPConfigurationDatabase” Powershell commandlet when I was presented with the following error:

PS C:\Users\blog-spadm> New-SPConfigurationDatabase

cmdlet New-SPConfigurationDatabase at command pipeline position 1
Supply values for the following parameters:
DatabaseName: SharePoint_Config
DatabaseServer: SPSQL.Crayveon.com
FarmCredentials
Passphrase: *********
New-SPConfigurationDatabase : The pipeline has been stopped.
At line:1 char:28
+ New-SPConfigurationDatabase <<<<
    + CategoryInfo          : InvalidData: (Microsoft.Share…urationDatabase:
   SPCmdletNewSPConfigurationDatabase) [New-SPConfigurationDatabase], Pipelin
  eStoppedException
    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletNewSPCon
   figurationDatabase

New-SPConfigurationDatabase : This SharePoint farm currently has pending upgrad
es.  The cmdlet New-SPConfigurationDatabase cannot be executed until the upgrad
e is completed.
At line:1 char:28
+ New-SPConfigurationDatabase <<<<
    + CategoryInfo          : InvalidOperation: (:) [New-SPConfigurationDataba
   se], SPException
    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletNewSPCon
   figurationDatabase

PS C:\Users\blog-spadm> New-SPConfigurationDatabase

cmdlet New-SPConfigurationDatabase at command pipeline position 1
Supply values for the following parameters:
DatabaseName: SharePoint_Config
DatabaseServer: SPSQL.Crayveon.com
FarmCredentials
Passphrase: *****************
PS C:\Users\blog-spadm> 

The really silly outcome of this was not related to any upgrade or stopped pipelines or anything of that sort.  Yet another case of the error message NOT having anything to do with the actual problem.  As it turns out, the solution was in the farm Passphrase I was specifying.  The GUI version of PSCONFIG gives you notification when the specified farm passphrase does not conform to domain password policies, but the Powershell version does not.

I reran the exact same command but this time specifying a more complex passphrase that conforms to domain security policies, and the problem was solved!



Cheers
C

SharePoint Versions Table updated with Oct 2011 CUs for 2007

The versions table has been updated with the latest CU info for SharePoint 2007 versions including SP3 and the October CUs.

• http://crayveon.com/sharepoint-solutions/sharepoint-versions-list/

Cheers
C

How do I – Solve the Access Denied error in SharePoint 2010 under Claims or Classic auth when accessing portal root while content access works fine

Sometimes in a SharePoint 2010 environment, you could encounter a problem where accessing a portal URL directly, e.g. http://portal.company.com. When attempting to access the URL, you could get presented with a simple “Access Denied” error such as this:

In addition, if you’re trying to access a NLB load balanced IP from your App server, you could be challenged for logon three time before getting Access Denied. The problem in this case is that the web application is thinking it has anonymous access enabled, but it actually does not. When a request for the root portal comes into the web app, the app does not challenge the requestor for credentials and when passed onto IIS without credentials and Anonymous Access not turned on, the result is an Access Denied error.
In order to resolve this, we will enable Anonymous Access on the web app and then turn it back off again.
Start by navigating to Central Administration.
Click “Application Management” in the left menu.
Under the Web Applications section, click “Manage web applications”.

A list of all your web apps is displayed.
Select the web app in question.
In the ribbon, click “Authentication Providers”

A popup window with your authentication providers opens. In our case, we see Claims.
Click the link under Zone, in our case “Default”.

Scroll down to the Anonymous Access section.
Check the “Enable anonymous access” check box.
Scroll down and click “OK”.

Now attempt to access your portal e.g. http://portal.company.com, and ensure that you’re able to resolve correctly. Once confirmed, simply follow the above steps again and uncheck the anonymous access check box this time. Once again check your access and all should be good.
Hope that saves someone some time.



Cheers
C

Honored for the 7th time

I am honored today, to have received my 7th Microsoft MVP award for SharePoint. It’s been an incredible journey thus far and we’re only getting started. I look forward to what vNext will bring, but until then I’ll be spreading the word on my new SharePoint Disaster Avoidance Architecture for Large Scale Enterprises endeavor with my friend Jason Himmelstein.



Cheers
C

Speaking at SharePoint Saturday New Hampshire

I’m speaking at the New Hampshire SharePoint Saturday event on 9/24 with my friend Jason Himmelstein. We will be presenting our famous Heavy Metal Power Pivot session. If you’re in the area, we’d love to see you so come on over and say hi!



Cheers
C

Get your Windows 8 Developer Preview now!

Microsoft launched Windows 8 with the Developer Preview at Build/Windows. Watch the Keynote and demos or even better, download the ISOs and get building!



Cheers
C

How do I – Remove all traces of a Workflow from my SharePoint list?

Sometimes when we are working with SharePoint Designer (SPD) workflows on SharePoint lists, things can get a little ugly. SharePoint preserves every version of the workflow unless you manually go and remove it from the list settings. If you have a workflow that happens to keep in a running loop, it could appear as if the newly published workflow didn’t actually publish correctly. In such situations, it’s often better to “reboot” and publish fresh. What we would do is to remove every instance of our workflow from the list settings and then re-publish the workflow again from SPD. Here’s how:

1. Start by navigating to the list on which the workflow was published.
2. In the ribbon, switch to the “Library” tab.
3. 
4. Once on the “Library” tab, you can click the “Workflow Settings” button, but because the ribbon dynamically changes depending on browser size and screen resolution, that button may not always be displayed.  A sure way to get there is to click the “Library Settings” button which is always displayed.
5. 
6. From the Library Settings page, under “Permissions and Management”, click the “Workflow Settings” link.
7. 
8. You’ll now be presented with a long list of historic versions of the workflow.
9. Click the “Remove a workflow” link below the historic list.
10. 
11. On the right, select the “Remove” radio button for every instance of the workflow.
12. Once all the buttons are selected, click the “OK” button to completely remove our workflow from the list.
13. 
14. Now simply switch back to SPD and re-publish the workflow for a fresh start.

Cheers
C

Our Chicago SharePoint Connections Conference slides now available for download!

My good friend Jason Himmelstein and I just completed our Chicago, IL stop of our speaking circuit. We promised we’d publish the slide decks from our three sessions at the SharePoint Connections Coast-to-Coast Tour so here you go.

Battle Scarred but still Standing

Heavy Metal PowerPivot 

Time is money. How SharePoint Logging will save you both 

Cheers
C

Our Chicago SharePoint Connections Conference Boot Camp slide deck

My good friend Jason Himmelstein and I just completed our Chicago, IL stop of our speaking circuit. We promised we’d publish the slide decks from our Pre-Con Boot Camp at the SharePoint Connections Coast-to-Coast Tour so here you go.

Chicago SharePoint Connections Pre-Con Boot Camp

Cheers
C

I’m Speaking at the SharePoint Connections Coast to Coast 2011 Conference in Chicago

My friend, Jason Himmelstein, and I will be presenting at this year’s SharePoint Connections Coast to Coast conference in Chicago held August 8-10 at the Hyatt Regency McCormick Place. We will also be in Las Vegas in November.

We will be bringing back our acclaimed IT-Pro Pre-Conference “SharePoint Collaboration Boot camp” plus presenting three sessions, “Heavy Metal PowerPivot”, “Time Is Money. How SharePoint Logging will Save You Both!”, and “Battle Scarred But Still Standing: A SharePoint Admin’s Tell-All”. We have updated all of these sessions to be fresh, current (Denali anyone?), and exciting so come out and see us if you’re anywhere in the Chicago area during that time.

We are also participating in a Speakers Panel for the Chicago SharePoint Users Group on Tuesday evening August 9th starting at 5:30pm. CSPUG is hosting us and will be providing pizza and lively discussion.

We hope to see you there!

 

Cheers
C

Project Crecent demo

If you’re not excited about Project Crecent and what SQL Server “Denali” brings to the table, you should take 7 minutes to watch this video. It is sure to wet your appetite!

Cheers
C

SharePoint performance debugging

We released a SharePoint 2010 portal to a new client today. Part of the portal design included a report center library that contained security trimmer report folders which are updated asynchronously via WCF services. In addition there was an external report center application that we wanted to link into the SharePoint UI so we simply added a Page Viewer Web part to the page that pointed to the external app.

The problem was that we were seeing major performance lag in rendering the external report app UI. When we pointed to the development server, the UI rendered slick and fast, but when we pointed back to the production server, we experienced lag again. And thus the trace/debug efforts began.

My standard rule when hunting bugs/performance issues is to “Assume NOTHING”. Always compare apples to apples and double, tripper, quadruple check everything.

Our analysis of the differences turned up the following:

1. The architectural difference between the production server and the development server was that the development server had it’s SQL source local to the server while the production server had a separate SQL server it was targeting.  In order to eliminate SQL Server from the equation, we pointed the development server to the production SQL data source and tested against it from the production environment.  The result was fast and responsive which meant that SQL Server wasn’t the problem.
2. This narrowed the problem down to the report server and the client computer.  Next we compared the production and development report servers.  Since both servers were virtualized, we checked the resource settings in the vSphere console.  It turned out that the servers were identical except for the development server having 4 CPU cores while the production server only had 2 CPU cores.  We upped the production CPU cores to 4 and rebooted the VM.  Testing against the new configuration still showed slow, lagging performance in the UI rendering.  That pretty much eliminated the reports servers from the equation unless one of the VMs was actually bad.
3. The next step was to install Fiddler2 on the client laptop and trace the two different calls to see where the traffic was going.  Fiddler did show some minor differences in header and body sizes of the HTTP calls, but not nearly enough to justify the lag we were seeing.  The development server was rendering in under a second while the production server took 5-6 seconds.  What struck me as curious was the fact that Fiddler was reporting that the total execution time of the HTTP call was 0.6 seconds in both cases.  So where exactly did that other 5 seconds go in the case of the production server?  Upon closer inspection, we noticed that the development SharePoint server had it’s properties for the Page Viewer Web Part set to point directly to the IP address of the development report server while the production SharePoint server’s Page Viewer was pointing to the report server using the FQDN instead.  And that’s when it TeaKayGee’d me.  The primary DNS server was flakey and after repointing to the secondary DNS server, performance was back as expected!

I was amazed at how big a difference the DNS request performance could make in this case.  That’s one more item I can add to my checklist when trouble shooting SharePoint performance issues. 

Cheers
C

My interview with @buckleyplanet at #SPConn in Boston

I hung out with Christian Buckley in Boston during the SharePoint Connections Coast to Coast Conference where my friend Jason Himmelstein and I was presenting some of our sessions. During some down time, Christian interviewed me for his “One Thing” series and it just posted on YouTube. Check it out:

 




Cheers
C

Our San Antonio SharePoint Connections Boot Camp and Conference Slides

My good friend Jason Himmelstein and I just completed our San Antonio, TX stop of our speaking circuit. We promised we’d publish the slide decks from our Pre-Con Boot Camp and our three sessions at the SharePoint Connections Coast-to-Coast Tour. Here you go.

 

• SharePoint Connections Pre-Con Boot Camp
• SharePoint Demystified
• Heavy Metal PowerPivot
• Time is money.  How SharePoint Logging will save you both

Cheers
C

Why I think the Firefox 4 designers are retarded

I’ve long since been an Internet Explorer holdout.  Living daily in the SharePoint world as I do, I’ve had to stick with IE through some really bad versions.  Now to be fair, what makes a version bad for ME is personal, biased and based completely on my experience with the product.  Unfortunately, like I always tell my Boot Camp attendees, “Perception IS Reality!”

To an end user, that is the absolute truth and as the consumer of the browser product, I am that user… and I am VERY unforgiving.  So why did I consider IE 6, 7 & 8 to be “bad”.  It all revolved around one thing.  The way I work.  I am always browsing in a dozen different places and then opening tabs that I want to get back to later.  In IE6, we didn’t even HAVE tabs.  That was such an annoyance because we were forced to open multiple IE windows instead.  It annoyed me to the point of writing a special app that would save your open IE windows and reload them after a reboot.

Then came IE7 and it brought with it tabs.  Too bad it lost all the tabs when it crashed.  At that point Firefox 3 was around with tabs and it DID restore tabs after a crash.  It even had a cute little “Oops, we’re sorry” message.  That was the point where many people started moving to Firefox as their main browser.  I started using Firefox but IE remained my main browser.  When I was browsing something I wanted to save for later, I’d do it in Firefox.  When I was doing anything else I ‘d be using IE.

Next came IE8… surely they’d have fixed the tab loss bug by this point, right?  Nope.  To my dismay, IE still lost my tabs after a crash.  At least I had a workaround in Firefox so I kept on doing that.  Now I think it’s a good thing that Firefox always tries to get you to upgrade to the latest version.

But then came Firefox 4.  With great fanfare I went along and upgraded to the latest version.  Imagine my horror when I re-opened Firefox expecting 20 odd tabs to open and there was just the home screen!!!  AAARRRGGGHHH!!!  What happened?  OK, I figured it must now be an optional settings somewhere.  I looked for it, but I couldn’t find it in any obvious location… and I’m a developer!  So if I couldn’t find it, I knew other people were having trouble too.  I ended having to search the Mozilla support site to finally find the answer and here it is:

This is where everyone in their right mind would look, right?  Under the “Tabs” menu… maybe something like “Restore tabs after crash” or something to that effect.

No, no, that would makes sense.  Nope, it’s not under that tab.  The tabs option for restoring your tabs is actually on the “General” tab.  Well of course!  Why didn’t I see that.  If you’re like me, you’re looking at the “General” tab and saying… um… where exactly is that option?  Well you have to look at the hidden dropdown values in the “When Firefox starts” item.

And change that item to be thus:

NOW Firefox is back to NORMAL and will save your tabs again.  Sigh.  So why my soapbox moment?

1. Tabs is what MADE Firefox popular to begin with.
2. I’m sure I’m not the only person on the planet that appreciated the fact it would ALWAYS recover you tabs.
3. So having such great tab features, why for Pete’s sake would you go and make that an OPTIONAL settings???!!!   If you have to make it an optional setting, at least turn it on by default!
4. And if you’re gonna make us hunt and peck for the answer, please just put the thing where we can actually FIND it without having to waste time searching your support forum for answers and then writing long winded tirades blog posts that waste even more time!

Whoever decided to change how tabs works in 4 is… well… nuf said…

And THAT, dear reader, is why this post is titled as it is.

Point in case…

So I want to point out that IE9 finally seems to do the tabs right and correctly restores tabs in a new session… and it does it BY DEFAULT Firefox!

Cheers
C

Speaking at SharePoint Connections Coast to Coast Conference 2011 in San Antonio, TX – May 23-25

My friend, Jason Himmelstein and I will be presenting at this year’s SharePoint Connections Coast to Coast conference in San Antonio held May 23-25 at the Hyatt Regency Hill County Resort & Spa. We will be teaching the ITPro Pre-Conference “SharePoint Collaboration Bootcamp” plus presenting three sessions, “Heavy Metal PowerPivot”, “Time Is Money. How SharePoint Logging will Save You Both!”, and “Wish I’d Have Known That Sooner! SharePoint Insanity Demystified”. All 3 of these sessions promise to be fresh and exciting so come out and see us if you’re anywhere in the San Antonio area during that time. We hope to see you there!

Cheers
C

SharePoint Foundation Logger RTW!!!

We are proud to release SPFlogger to the world wide web as Open Source on Codeplex under the GPL! Ever wondered what those CorrelationIDs in SharePoint error messages are all about?  SPFLogger demystifies the SharePoint logging database and unearths the treasure of information captured therein.  This tool will show you the true power of SharePoint 2010’s new logging capability.

A brief overview

With 3 areas of focus (NT Event Log, ULS Trace Logs, and Timer Job Logs) SPFLogger helps you to identify:

• what errors are occurring in your environment
• a frequency of occurrence of errors
• drill into specific Correlation IDs
• review Timer Job logs and their frequency

This Silverlight RIA Services application can be in an IIS Web Site directly on your SharePoint Server and with minimal configuration be providing you with invaluable data about your SharePoint 2010 farm in a matter of minutes.

Cheers
C

Speaking at SharePoint Connections 2011 in Boston

My friend, Jason Himmelstein, and I will be presenting some exciting sessions at this year’s SharePoint Connections Coast to Coast conference in Boston held April 25-27 at the Marriott Long Wharf. We will be presenting two sessions, “Heavy Metal PowerPivot” and “SharePoint Logging – The Undiscovered Country”. Both sessions promise to be fresh and exciting so come on out and see us if you’re anywhere near the Boston area during that time. We’d love to see ya!

Cheers
C

SharePoint Versions Table updated for the Feb 2011 CU

I have updated the Versions Table with the latest Feb 2011 Cumulative Update information. The Server 2010 download is not available yet, but the location is correct, so just check back regularly.



Cheers
C

Now powered by SharePoint Foundation 2010

The ice storm the other day not only knocked out my servers but also caused one of my raid arrays to die and temporarily take this blog down. I decided to pull forward my migration plans for the blog. I had planned to migrate my blog to SharePoint 2010 in the spring, but Icepocalypse 2011 forced me to bring those plans forward.

My blog used to run on an old server (physical hardware) that was 4 years old. I had actually gone through the process of virtualizing it 2 years ago and I had it running on Virtual PC on top of a Window Server 2008 R2 machine at the time. When the raid array died, I was forced to revert to the previous night’s backup of the VM.

Since the 4 year old server died, I had to temporarily host the blog on the next server in the Datacenter. Unfortunately, that was only a single core (Oh my gosh!!! ) Pentium 4 CPU at 2.8 GHz with a mere 2 GB of RAM that was (gasp!) 9 years old!!! Since the blog was a VM, I was able to simply move the VHD over to the server and install Virtual PC and point it to the VHD and we were in business again. I was only able to give the blog 1 GB of RAM so performance was a little slow.

I’ve since upgraded my datacenter and got a brand spanking new server. The 8 core Intel i7 950 CPU running at 3.7 GHz with 24 GB of RAM and several TB of hard drive space over multiple disks, was humming along nicely. I gave the new blog server 2 dedicated cores and 4 GB of RAM and now the blog is humming along like a top.

Have I mentioned how much I love virtualization lately?

Cheers
C

SharePoint 2007/2010 Version/Download Table Updated

The SharePoint Version Table has been updated with the Dec 2010 CU version numbers and download links. http://crayveon.com/sharepoint-solutions/sharepoint-versions-list/

Cheers
C

How do I – Get A Process ID in C#

Got this question recently… answer is by simply using the System.Diagnostics namespace…

System.Diagnostics.Process.GetProcessesByName("ProcessName")[0].Id;

Cheers
C

Using the YEAR from a Date column in a calculated column in SharePoint

I got this question from someone on the forums and thought it might be worth reposting to my blog for later reference as well.

The question:

Hello – I have an out-of-the box date column titled, “Departure Date” which renders values in the format, ‘DD/MM/YYYY’. I want to be able to filter by Year only so I believe the easiest way to accomplish this is to create a calculated column titled, Year and populate that column with the YYYY values from the Departure Date column. I tried using several formulas but so far have been unsuccessful. I’m currently using this formula, “=TEXT(YEAR([Departure Date]),”YYYY”)” for the calculation value and the value comes up as “1905”…? Help!

Now this one made me scratch my head for a minute and then I fired up my trusty “Calculated Column Debugger”… Excel!!!

USEFUL TIP:  ALMOST ANY FORMULA YOU CAN PUT TOGETHER IN EXCEL, YOU CAN COPY TO A SHAREPOINT CALCULATED COLUMN AND IT SHOULD WORK JUST FINE.

I copied the formula over to an Excel cell and put a date in another cell to reference and sure enough… I got 1905 too!  Then just looking at the formatting options for the TEXT method and the YEAR method, I was able to deduce the answer thus…

The answer:

The problem is that you’re doubling up on methods by passing the result from YEAR to the TEXT method. Either of the following should work just fine:

• =TEXT([Departure Date], “yyyy”)

or

• =YEAR([Departure Date])

Because the TEXT method is expecting a DATE value to be passed, but the YEAR method is returning a TEXT value, it confuses the TEXT method which doesn’t know how to interpret the value and thus produces the weird result you saw.

Cheers
C