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

Monday, November 8, 2010

From SmartPart To Visual Web Part Demo and Slides

Here is the solution and slides for my presentation on SharePoint SmartPart and SharePoint 2010 Visual Web Part development.

Slides and Project
NOTE:
I updated the slides to add a section on uploading images to an image library, as in my prior post.  I also updated the custom property section of the slides to reflect the need to implement the custom property in the Web Part class rather than the User Control class.

I also updated the slides to include a quick intro to SharePoint for presenting to a .Net usergroup.