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.
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.
- 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 and higher to update keys / values bi-directional between SharePoint and external data sources (full CRUD supported).
Key Value Lookup Column for SharePoint 2010 has not been reviewed by any customers.
Submit A Review
The views and opinions submitted and expressed here are not those of Microsoft.