The Key Value Lookup Column uses foreign key relationships to implement content lookup from other SharePoint lists as usual with databases instead of item IDs, as SharePoint out-of-the-box lookup does.

SharePoint 2010 out-of-the-box comes with some new database-like features, e.g. unique field values and elements of referential integrity. Unfortunately native foreign keys are still not supported for list lookups. That makes it difficult to migrate or seamlessly integrate normalized external LOB data with foreign keys and referential integrity, e.g. from SQL databases, using integration tools like the Business Data List Connector.

The Key Value Lookup Column add-on now uses native keys (e.g. a Customer ID) for lookup values to another SharePoint list. To ensure integrity not the SharePoint Item ID is used (like with SharePoint out-of-the-box lookup) but the foreign key itself. That solves many issues with SharePoint standard lookups, e.g. with deployment and updates.

Now it's possible: All your corporate master data, replicated to native SharePoint lists including foreign key relationships between...

 
Fig.: Sample usage scenario of a Key Value Lookup Column in SharePoint 2010. You can look up the CompanyName in the Orders list from the Customers list using the CustomerID only as a key - not the SharePoint Item ID.

The Key Value Lookup Column has the following features and benefits:

• Lookup for a value in another SharePoint list using a foreign key, e.g. a Customer ID.
• Works just like foreign keys in databases - but with SharePoint lists.
• Integrity is forced with the key only, not with the SharePoint Item-ID.
• Only the key gets saved - not the de-normalized lookup token (ID;#value) as usual with SharePoint standard lookup.
• If the key is present in the lookup list, the corresponding value is displayed.
• If the key does not reside in the lookup list for any reason, the key is displayed instead of a blank field as in case of the SharePoint standard lookup.
• In case of changes of key or value (e.g. by BDLC background updates), the current, updated content is automatically shown during runtime.
• Lookups to calculated values are supported, e.g. to lookup a formatted address information that consists of values from several columns.
• If used for database integration or migration, all referential integrity between tables is kept for the SharePoint lists. If SharePoint default lookups were used instead, this integrity would be lost after import.
• Very easy data deployment, because you have full control of the keys. That is not the case with SharePoint generated Item-IDs by default.
• High performance with optional cache settings, if required.
• Key Value Lookups can be used with the Business Data List Connector for SharePoint V3.1 to update keys / values bi-directional between SharePoint and external data sources (full CRUD supported).

With the complete FREE shareware version you can download and use a full featured solution. See below for limitations. To buy a license for this product, please click the shopping card symbols on this page.

The product is licensed on a per server base (one time fee). No additional clients licenses (CALs) are required. If you have several servers (e.g. in a farm scenario) all servers have to be licensed. You need a license for every web frontend server. Application server, infrastructure server, e.g. for indexing or search don't need a license. You can optionally buy Software Assurance (SA) for free updates / upgrades to protect your investment.

To activate the full featured product, please place the license file into the feature directory on the licensed servers, for example C:\Program Files\Common Files\Microsoft Shared\ Web Server Extensions\14\TEMPLATE\FEATURES\ Layer2.SharePoint.KeyValueLookup_KeyValueLookupField\ and IISReset.

For more information about licensing please check our FAQs or contact sales@layer2.de directly.

Please enter the product folder in the distribution zip-file, run the installer (*.exe) and install or safely upgrade / remove the product following the steps. Use the "Run as Admin" option of the context menu (right click). After sucessful installation you have an additional column / field type available named Key Value Lookup. Just add the new column to your list and configure the lookup.


Fig.: Sample Key Value Lookup Column configuration: You can select the web, where the lookup list is located, the list to lookup, the key field to use for lookup and the value to display in list or detail view.

• The Key Value Lookup is for looking up in SharePoint lists only - not for external data sources. But you can sync SharePoint lists and external data sources using the Business Data List Connector bi-directional with full CRUD.
• Multi-value lookup is not supported yet.
• You can't look up for several values at once. But, as a workaround, you can have more than one Key Value Lookup Columns per list.
• Only drop-downs are currently supported in edit view (no radio buttons etc.).
• Please note, that Microsoft InfoPath 2010 does not support custom fields that contain code - as the Key Value Lookup Column do. That is a known limitation of InfoPath, no product issue.
• It is planned to support expressions in later versions, e.g. [Name] + ', ' + [Firstname].
• It is planned to support more flexible linking in later versions, e.g. link to lookup detail view.
• Shareware limitations: A shareware note is rendered after the display value, e.g. MyValue (Shareware Version). Please request a time limited full featured license for better evaluation, if required.
• SharePoint versions supported: Microsoft SharePoint Server 2010 or Microsoft SharePoint Foundation 2010 is required. WSS 3.0 or MOSS 2007 is not supported. 

Did you found any additional issues? Please give feedback to sales@layer2.de.