#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.

SharePoint 2007, 2010, and 2013 Cascading Lookup Column Comparison

Scenario

• You have multiple SharePoint lists that are related
• Parent/Child or other relationship
• You want to allow the user to define the relationship via a Lookup Column Dropdown (Combobox) or Multiselect Interface
• Or you need filtered lookups or some other additional lookup functionality

Issue

• The out of the box SharePoint Lookup Field columns are severely limited
• They really only work for relatively small lists of uniquely named items that don't change often
• SharePoint 2010 natively supports related lists, but do not have all the features below
• We need one or more of these advanced features
1. Cascading Updates (and possibly deletes)
• Maintain Referential Integrity
• Updates made in one list are reflected in the lookups.
2. Keyboard filtering from the dropdown and multiselect interfaces
• Autocomplete, find as you type
3. Cascading drop-down filters
• One or more dropdowns to filter the final dropdown
• Ex: State, County, City
4. Lookup filters (likely based on a List View)
• Ex: Only show my Cities, or only show Active Items in the lookup
5. Datasheet view editing (Excel, Access), InfoPath, and Office DIP support
• In most cases this is unavailable.  The workaround is to create a view without the lookup fields for use with Office integration and ensure the lookup fields are not required or have a default value.
6. AJAX
• We don't want to postback when applying realtime filtering
• We may not want to preload all the values for very large lists
7. May be used in a Calculated Column
• Ideally the text field and ID field would both be accessible
8. Supports multiple display fields from the lookup list
• ex: First and Last Name concatenation in the dropdown
• Workaround would be to use a calculated field within the lookup list
9. Link to add an item to the lookup list from the lookup column interface
10. Cross site support
• By default the list must be in the same subsite as the lookup column
11. Cross site-collection support and content type hub support
• Workaround would be to synchronize lookup lists accross site collections using a workflow, content deployment job, or a custom timer job.
12. Two-way relationships
• Items within a lookup list will keep track of where they have been referenced.
• Ex: Orders are linked to a customer via the order form.  On the customer form, you can see the linked orders.
• SharePointBoost's LookupBoost or Sparqube's Lookup Tracker can be installed to provide this information via a separate relationships page.
13. Matches the SharePoint look and feel
• Ex: Inherits themes/styles for drop down
14. Can be created as a site column
• Sometimes only implemented as a list column.
15. Conversion tool 
1. Converts lookup columns to custom lookup
2. Converts custom lookup back to a regular lookup
16. Allow filtering from a multiselect
• Ex: Filter lookup of Cities when multiple States are selected
17. Set the lookup column's default value
18. Referential Integrity (2010 new feature)
• Cascade or disable updates/deletes when the item is used in lookups elsewhere
• Not available on a multi-select
19. Projected Columns (2010 new feature)
• Lists with the lookup column will expose other columns from the lookup list.
• Ex: Order list with a Customer lookup column could also include Customer Phone and Customer Email in the Order list view based on the selected Customer

Resolution

• The following third-party products are available to meet your needs.  This analysis was done based on the marketing material available on their websites and any personal experience I have with them.   ? - denotes unknown feature
• SharePoint 2010 Lookup Column (Out of the box - not third party)
• Features: 1 (includes recycle bin), 2, 5?, 10, 13, 14, (17 via code), 18, 19
• Sparqube SharePoint Lookup Column (SharePoint 2010+)
• Features (provided by vendor): 1, 2, 3, 4, 6, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19
• Note: There are 2 column types to choose from.  Each has advantages and disadvantages.  All features above may not be available depending upon which control you use.
• (12) Sparqube Lookup Tracker provides this as a separate (included) feature
• KWizCom SharePoint Cascading Lookup Plus
• Features: 1?, 2, 3, 4, 6, 7, 8, 9, 10, 12*, 14, 15.1, 17?, 18?, 19?
• Review Notes
• Runtime issues will occur if you are still referencing AJAX 1.x
• Single-select, single-value items appear in a grid format with a header
• Display form grid uses a hyperlink icon rather than text
• Dropdown skins do not match SharePoint but are customizable
• *(12) Same list 2-way relationships are not supported
• For this scenario, use SharePointBoost's LookupBoost or Sparqube's Lookup Tracker
• SharePointBoost Cascaded Lookup
• Features: 1, 2, 3, 4, 5 (except multiselect), 8*, 9, 10, 12*, 13, 14, 15, 17*, 18, 19
• *(12) Lookup Boost: Provides feature 12 via a separate relationship page
• Review Notes
• Installed properly
• Worked as advertised
• Issue with saving a sub-site as a template after implementing a root level site column and content type.  Workaround provided by SharePointBoost
• *(8) - Doesn't support hyphenated values in the drop-down for SP 2010.
•  *(17) - Only supports defaulting to first item in a view and does not support the standard Default property, so this is difficult to set from code.  Does not support defaults on a multi-select.
• Azu Lookup Plus 2013
• I haven't evaluated this yet.  Please refer to the website.
• Infowise
• Features: 3, 4, 9+, 10, 12 (with bi-directional bundle), 13, 14, 15, 17?, 18?, 19?
• (9+) Enhanced support for inline simple text additions and for task creation
• Bamboo Lookup Selector
• Features: 2, 3, 6, 10, 11, 17?, 18?, 19?
• SharePointBoost Cross-Site Lookup
• Features: 2, 10, 15, 17?, 18?, 19?
• Lookup Boost: Provides feature 12 via a separate relationship page
• SharePoint Tool Basket
• Features: 2, 6, 17?, 18?, 19?
• Open Source
• SharePoint Cascaded Lookup Dropdowns
• Features: 3, 6 (client-side only), 17?, 18?, 19?
• Not a column.  Must be applied to each form.
• Open Source
• CodePlex Filtered Lookup
• Features: 4, 10, 17?, 18?, 19?
• Open Source