Tuesday, November 30, 2010

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
    • 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

5 comments:

  1. i am searching sharepoint 2010 cascading lookup column tools that can support content type hub. I have tried all the tools above, but there is not support. Could you give me reference about that?

    ReplyDelete
    Replies
    1. This would likely largely map to feature 11. I will add content type hub support as a desired feature. Please let me know what you find that works with it.

      Note: 1 option is to duplicate the lookup lists in each site where they are used, but that would largely defeat the purpose of the CTH.

      Delete
  2. Can you add Sparqube Lookup Column Suite to your lookup columns comparison?
    Features: 1,2,3,4,6,8,9,10,11,12,13,14,15,16,17,18,19
    Please read more here: http://www.sparqube.com/SharePoint-Lookup-Column/Comparison.aspx

    Artem

    ReplyDelete
  3. Lookup Plus for SharePoint 2013, It is more than Sharepoint Lookup. Cascaded Lookup, Filtered lookup, Cross-site Lookup /drop down/ and
    some controls are free. ("Create new item" link) Visit, http://www.azu.mn
    Or watch the channel http://www.youtube.com/watch?v=70-hXWY6ARY&list=PL-5v3e65O1cFMddgCpzIURE_UAsL-oDdW

    ReplyDelete