- 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
- 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
- Cascading Updates (and possibly deletes)
- Maintain Referential Integrity
- Updates made in one list are reflected in the lookups.
- Keyboard filtering from the dropdown and multiselect interfaces
- Autocomplete, find as you type
- Cascading drop-down filters
- One or more dropdowns to filter the final dropdown
- Ex: State, County, City
- Lookup filters (likely based on a List View)
- Ex: Only show my Cities, or only show Active Items in the lookup
- 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.
- AJAX
- We don't want to postback when applying realtime filtering
- We may not want to preload all the values for very large lists
- May be used in a Calculated Column
- Ideally the text field and ID field would both be accessible
- 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
- Link to add an item to the lookup list from the lookup column interface
- Cross site support
- By default the list must be in the same subsite as the lookup column
- 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.
- 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.
- Matches the SharePoint look and feel
- Ex: Inherits themes/styles for drop down
- Can be created as a site column
- Sometimes only implemented as a list column.
- Conversion tool
- Converts lookup columns to custom lookup
- Converts custom lookup back to a regular lookup
- Allow filtering from a multiselect
- Ex: Filter lookup of Cities when multiple States are selected
- Set the lookup column's default value
- Referential Integrity (2010 new feature)
- Cascade or disable updates/deletes when the item is used in lookups elsewhere
- Not available on a multi-select
- 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
- 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
Posts
