#SharePoint Lookup Column DefaultValue Datasheet View Bug

Scenario

• You have set a Lookup Column's default value property via code
• There is no way to set this via the SharePoint UI with the exception of the workaround to default the first item in the lookup by making the column required
• You receive the following errors
• "The text entered for ColumnName isn't an item from the list." dialog box
• The spreadsheet will not save the row and an invalid entry window will pop up

Side Note
I have developed a SharePoint solution that will allow a user to specify column defaults from a root site list for all instances of a Site Column within a specified site. For example, a company has several offices that each have a category that may change.  Each office has it's own team site. Every document and list item in the company must also have the office category populated for filtering and searching.  The user would set the office category from an office list at the root site which includes the office team site url, at that time, my tool updates the default value in each list/library where that site column is used within that team site.  If you would like more info, please comment to this post or contact Kiefer Consulting.

Issue

•  Datasheet view does not work properly
• You will run into errors if any of the following are the case
• The client is running Office 2010 64bit with the 2007 Office System Driver: Data Connectivity Components workaround
• The name and id are swapped in this case.  If you set the defaultvalue to the backwards combo the default will work for this client configuration but will not work with the standard new item form or other client configurations.
• Ex: "Name1;#1"
• The client is running an older version of Office
• A defaulted lookup is multi-select
• You may run into errors if the following is the case
• A defaulted lookup is not visible within the Datasheet view

Resolution

• There is only one unique circumstance where Datasheet view will work with standard formatted defaults
• Office 2010 32bit is installed on the client
• The lookup columns are single-select
• All defaulted columns are visible within the Datasheet view
• To code for maximum datasheet support, you must use a repeated id format workaround
• Ex: spfieldlookup.defaultvalue = "1;#1;#2;#2"
• This will solve the "The text entered for ColumnName" issue for multi-selects and non-visible fields in Office 2010 32bit
• This will also work for Office 2007 for single-select columns, and non-visible multi-select fields.  You will still get the error for visible multi-select fields
• The side effect to this format is that the id value will be visible, instead of the display value, until the user moves off the row.