Displaying the Render Date and/or Time in SharePoint Pages

This is a simple enough task with a little JavaScript.  Inserting the script below into your master page will output the date and time in the simplest way possible when the page is rendered:

   var dt = new Date();

If your language is American English, you will see the following: Fri Oct 24 18:19:06 EDT 2008.  Of course, if you’d like things formatted a little more nicely, you can always expand the JavaScript.

Another nice trick is to use similar JavaScript to display the year information for the copyright notice that you are likely to have in your footer or elsewhere on your page.  The copyrightYear function below will return startYear if it is that year or earlier, and startYear-currentYear (e.g., 2007-2008) if the currentYear is greater than startYear.

// copyrightYear: Display a copyright year range.
// Arguments:
//		fieldName:	startYear
<!-- This Javascript function is used to display the copyright year range.
function copyrightYear(startYear) {
 // Get the current date
 var dt = new Date();
 var y  = dt.getYear();
 var yEndYear = "";
 // Y2K compliant
 if (y < 1000) y +=1900;
 // If the current year is greater the startYear, then display the range, otherwise just the current year
 if (y > startYear) yEndYear = "-" + y;
 document.write(startYear + yEndYear);

If you called the function like so today:


you would see 2007-2008.

You can see a working example of this on our demo page.

“SharePoint Is Not a Database”

This is something that I’ve told people more times than they really want to hear.  Sure, SharePoint sits on top of SQL Server, which is an excellent database, but SharePoint itself is not a database!  So is this a dire “abandon all hope, ye who enter here” message?  Absolutely not.  With planning and forethought, you can build quite nice applications in SharePoint that act in a database-like way.  What it does mean is that all of the old rules still apply.

When I think about this, I’m always reminded of the Rules of Data Normalization poster from Database Programming & Design magazine by Marc Rettig which was hanging in one of my colleague’s cubes in the late 1980s.  (Through the miracle of the Web, I was actually able to find this picture of it!)  Though I can’t recite the rules from memory, it has informed my database and now custom list designs to this day. 

  1. Eliminate repeating groups
  2. Eliminate redundant data
  3. Eliminate columns not dependent on key
  4. Isolate independent multiple relationships
  5. Isolate semantically related multiple relationships

I’m not going to go into the details of what each of these rules means (a quick search on the Web or an introductory Computer Science course will explain them to you).  These rules and the nice 80s colors are cool, but what does this mean when you develop SharePoint applications today?  Well, quite a few things.

  1. First and foremost, make sure that you build your lists following these rules.  One key flaw in SharePoint’s architecture (it’s there for good reasons having to do with Office compatibility) that forces violation of the rules is the Lookup Field Type.  Rather than storing the ID of the referenced item, the text value is stored.  Try to avoid using the Lookup Field Type directly and…
  2. Build custom forms to replace Newform.aspx and EditForm.aspx that enforce these rules.  The default forms contain a single List View Web Part, which works very well in creating and editing items in a standalone list.  When you want lists to interrelate, you’ll want to build custom forms.  These custom forms can contain multiple Data View Web Parts (DVWPs).  A DVWP used as an input form can only interact with a single list, but you can have the DVWPs interact using hidden columns and JavaScript.  (Yes, you can write custom Web Parts here, and I’m not discouraging that, but in many cases managed code overcomplicates things.)Rather than using Lookup Field Types, you can display the available values from the lookup list(s) and capture the IDs in selects or with radio buttons, etc. which you build yourself in DVWPs.  With JavaScript on the events, you can then “stuff” the chosen values into the appropriate columns in the target list.Several notes on custom forms:
    * Don’t edit the default forms directly — take a copy and change the copy.  You never know when you might want to revert to the original form.
    * Change the forms associated with the list by right clicking on the list container in SharePoint Designer, Properties, Supporting Files tab.  Making the change here will cause a redirect to your custom form even in places that the original form is requested.
  3. Handle deletes elegantly.  There is no hook for workflows in SharePoint Designer to trap on a delete.  However, if you build your forms well, you can provide delete capabilities that you can trap on and handle.  There’s no such thing as cascading deletes in SharePoint lists (it’s not a database, remember?), so you’ll need to do the work yourself.  If you’ve followed the data normalization rules well, this should not be too onerous a set of tasks.  One simple way to protect yourself is to just disallow deletes in lookup lists.  This means that you will need to provide some administrative function to handle them, but you will avoid simple breaks to the application.  Be sure to…
  4. Teach your users the value of all of this.  If you build your forms well, the amount of education ought to be minimal, but you may want to explain your methods so that they understand how it all holds together.  Why?  Well, if they don’t understand, they may try to do things to get around your good planning, which will make it all fall apart.

I’ve written many posts in the past that have snippets of how to do each of these things, so if you have questions about any of the details, take a troll back through my old posts or shoot me your questions.


Internal Microphone with Vista

This one might fall into the "Duh, Marc" category, but when I get stuck, I figure maybe someone else out there might get stuck, too.  (And it makes me feel better to think that way.)

I was trying to get Skype set up on my Dell Latitude D820 laptop this morning (which is running Vista), and I couldn’t get the microphone to work.  When I went into the Control Panel, Sound, and looked at the Recording tab, I saw this:


So the internal microphone was there, but it was "Currently unavailable".  After a little poking around on the Web, I found the answer: I needed to set the Internal Mic as the default:

New Picture

Once I did that, all was right in the world again.

Technorati tags: , ,

SharePoint Workflow History “Disappears”

By default, workflow history will "disappear" after 60 days.  Microsoft has built this into MOSS intentionally to ensure good performance.  By deleting old workflow history, it’s more likely (but hardly guaranteed) that the Workflow History list (/Lists/Workflow History by default) will stay below the magic 2000 items per list limit.  (See the "Working with Large Lists in Office SharePoint Server 2007" white paper for details on this limit.)

The official Microsoft answer to this in Technet is to disable the Workflow Auto Cleanup Timer Job.  Unfortunately, this is a farm-level setting and will affect every Site Collection in your farm, which may be more far-reaching than you intend.  However, it might be the right solution for you.

Another option is to run some code to change the time length for the history retention.  There is some good code and discussion here (I’m reproducing the code below in case the link changes.  Thanks to Shola Salako!  I haven’t tested this code, so use it carefully.) to do this by changing the value of SPWorkflowTemplate.AutoCleanupDays on specific lists.  NOTE: Don’t use the SQL method!  Running the C# example which utilizes the object model is perfectly legitimate, but poking around in the database will leave you in what Microsoft calls an "unsupported state" and is just a bad idea.

For companies that rely on workflow history for auditing and regulatory compliance, taking one of these actions is going to be critical.

   1: /*
   2: * Date: September 17, 2007
   3: * 
   4: * Program Description:
   5: * ====================
   6: * This program is a workaround for Microsoft Office SharePoint Server 2007
   7: * bug #19849, where the AutoCleanupDays is set to 60 by default and by design
   8: * in MOSS installations. This program gives the customer the opportunity to
   9: * change this number.
  10: * Workflow histories would not show after 60 days by default.
  11: */
  12: using System;
  13: using System.Collections.Generic;
  14: using System.Text;
  15: using Microsoft.SharePoint;
  16: using Microsoft.SharePoint.Workflow;
  18: namespace ShowWFs
  19: {
  20: class Program
  21:     {
  22: static string siteName;
  23: static int newCleanupDays, assoCounter;
  24: static string libraryName, wfAssoName;
  25: static SPSite wfSite;
  26: static SPWeb wfWeb;
  27: static SPList wfList;
  28: static void Main(string[] args)
  29:         {
  30: try
  31:             {
  32: switch (args.Length)
  33:                 {
  34: case 0: //no parameters entered by user
  35:                         {
  36:                             System.Console.WriteLine("Error: No arguments entered (site, library, workflow and days)");
  37:                             showHelpUsage();
  38: break;
  39:                         }
  40: case 4: //correct number of parameters
  41:                         {
  42:                             siteName = args[0];
  43:                             libraryName = args[1];
  44:                             wfAssoName = args[2];
  45:                             newCleanupDays = Convert.ToInt32(args[3]);
  46:                             assoCounter = 0;
  47:                             wfSite = new SPSite(siteName);
  48:                             wfWeb = wfSite.OpenWeb();
  49:                             wfList = wfWeb.Lists[libraryName];
  50:                             SPWorkflowAssociation _wfAssociation = null;
  51: foreach (SPWorkflowAssociation a in wfList.WorkflowAssociations)
  52:                             {
  53: if (a.Name == wfAssoName)
  54:                                 {
  55:                                     a.AutoCleanupDays = newCleanupDays;
  56:                                     _wfAssociation = a;
  57:                                     assoCounter++;
  58:                                 }
  59: else
  60:                                 {
  61:                                     _wfAssociation = a;
  62:                                 }
  63:                             }
  64:                             wfList.UpdateWorkflowAssociation(_wfAssociation);
  65:                             System.Console.WriteLine("n" + wfAssoName + ": " + assoCounter.ToString() + " workflow association(s) changed successfuly!n");
  66: break;
  67:                         }
  68: default: //default number of parameters
  69:                         {
  70:                             System.Console.WriteLine("Incorrect number of arguments entered (" + args.Length.ToString() + " arguments)");
  71:                             showHelpUsage();
  72: break;
  73:                         }
  74:                 }
  76:             }
  77: catch (Exception e)
  78:             {
  79:                 System.Console.WriteLine("An error has occurred. Details:n" + e.ToString());
  80:             }
  81: finally
  82:             {
  83: if (wfSite != null)
  84:                     wfSite.Dispose();
  85: if (wfWeb != null)
  86:                     wfWeb.Dispose();
  87:                 System.Console.WriteLine("nFinished setting AutoCleanupDays!");
  88:             }
  89:         }
  90: static void showHelpUsage() //help screen
  91:         {
  92:             System.Console.WriteLine("nnMOSS Workflow Set AutoCleanup Usage:");
  93:             System.Console.WriteLine("====================================");
  94:             System.Console.WriteLine("ShowWFs siteURL library workflow days");
  95:             System.Console.WriteLine(" - siteURL (e.g. http://serverURL/site)");
  96:             System.Console.WriteLine(" - library (e.g. "Shared Documents")");
  97:             System.Console.WriteLine(" - workflow (e.g. "Approval")");
  98:             System.Console.WriteLine(" - days for auto clean up (e.g. 120)");
  99:         }
 100:     }
 101: }

SharePoint Workflows: Failed on Start (retrying)

At one of my clients, I’m using a pretty simple SharePoint Designer workflow to manage their Purchase Order approval process.  About half the time, the workflow runs fine.  The other half, the workflow instance gets stuck with the status ‘Failed on Start (retrying)’.  If I terminate the “stuck” instance and fire it off again manually, it runs fine, so I don’t think that it’s a bug in my code or bad data.

The environment is WSS and seems to be up to rev on all of the patches and hotfixes.  There aren’t any other workflows running, though folks at the client have done some experimenting, so workflows have been running in the past.

Looking around the Web, this doesn’t seem to be a unusual occurrence, but I haven’t found anything authoritative enough to explain it.  One suggested fix is to reset the WWF counters by running this command:

Lodctr /R c:WindowsMicrosoft.NetFrameworkv3.0Windows Workflow Foundationperfcounters.ini

We ran it, and everything seems to be working fine now.  I’ll update this post if we learn anything more…


Alas, the attempt to fix things above did not seem to work. The documents are all being submitted by one person, and there still doesn’t seem to be any pattern to the successes vs. failures.  Whenever the workflows are getting stuck, I’m seeing a permission error in the logs like this:

10/13/2008 08:52:33.66     w3wp.exe (0x178C)                          0x047C     Windows SharePoint Services     Workflow Infrastructure         98d8     Unexpected System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))     at Microsoft.SharePoint.SPGlobal.HandleUnauthorizedAccessException(UnauthorizedAccessException ex)     at Microsoft.SharePoint.Library.SPRequest.AddWorkItem(String bstrUrl, Guid& pWorkItemId, DateTime& pDeliveryDate, Guid workItemType, Guid workItemSubType, Guid parentId, Int32& pItemId, Boolean bRememberWebId, Guid& pItemGuid, Guid& pBatchId, Int32 userId, Object varBinaryPayload, String pwzTextPayload, Guid processingId, Boolean bAutoDelete)     at Microsoft.SharePoint.SPWorkItemCollection.Add(DateTime deliveryDate, Guid workItemId, Guid workItemSubType, Int32 itemId, Guid batchId, Guid itemGuid, Boolean rememberWebId, Int32 userId, Byte[] binaryPayload, String textPayload, Boo… 

10/13/2008 08:52:33.66*    w3wp.exe (0x178C)                          0x047C     Windows SharePoint Services     Workflow Infrastructure         98d8     Unexpected …lean inProgress, Boolean autoDeleteOldUnprocessedEvents)     at Microsoft.SharePoint.Workflow.SPWorkflowPendingEventCollection.Enqueue(SPWorkflowEvent workflowEvent, DateTime deliveryTime, SPRunWorkflowOptions runOptions)     at Microsoft.SharePoint.Workflow.SPWorkflowManager.RunWorkflowElev(SPWorkflow originalWorkflow, SPWorkflow workflow, Collection`1 events, SPRunWorkflowOptions runOptions)

I think that the error being a permissions one may be misleading, but I’ve tried bumping up the permissions for the person who is submitting the documents to the library to Full Control anyway.