Overview

Sparqube Lookup Column is powerful component that significantly enhances standard lookup functionality. It allows referencing list data from any site within site collection and provides set of additional controls for easy and intuitive data look-up:

Picker Dialog and Autocomplete Textbox will allow you to work with large list items. You can also apply list view filter or specify custom filter to reduce number of possible items for lookup column and prevent incorrect data selection. Moreover, Sparqube Lookup column provides ability to build parent-child relationships between columns in current list (cascaded lookup), so data entered in one field filters out options present in another:

Features

• Supports reference lists located outside of the current site (cross-site lookup support)
• Cross-site collection lookup support
• Additional controls for columns value editing including Picker Dialog with search function, Autocomplete textbox, Drop-down checkboxes and others useful controls
• Allows building parent-child relationships between columns in the same list (cascaded lookup, multi-levels supported)
• Multiple filtering in cascading scenarios
• Advanced filter options (filtering items by existing list view and ability to specify custom filters)
• Ability to add new items to source list using "add new item" link
• Single and multiple values supported
• Automatic removal of duplicates
• Ability to show additional fields from source list
• Enforce relationship behavior
• Datasheet view supported
• Ability to display value in list view as text or as link to parent item
• Supports default values
• Default values can be passed to new item form via query string using sqc* prefix, where * is lookup column internal name (eg. sqcMyLookupCol1=Value1&sqcMyLookupCol2=Value2)
• Default values fetched from source list item can be passed to new item form via query string using sqlookup* prefix, where * is lookup column internal name (eg. sqlookupMyLookupCol1=SourceListItemID1&sqlookupMyLookupCol2=SourceListItemID1)
• Document lookup
• Track related items directly from within their parent item (reverse lookup)
• Conversion of standard Lookup to Sparqube Lookup Classic column
• Multi-lingual (specific language support can be added per request)

Setup

Requirements

Server:

• SharePoint Foundation 2010/2013 or SharePoint Server 2010/2013/2016
• Windows Server 2008/Windows Server 2008 R2/Windows Server 2012/Windows 7
• .NET Framework 3.5/4.5

Installation

Sparqube Lookup Column requires the following permissions for an account running installation:

• Must be a member of the local server Administrators group to install application files.
• Must be a member of the SharePoint Farm Administrators group to deploy solutions to the farm.

To install Sparqube Lookup Column:

1. Run as Administrator self-extracting package Sparqube.LokupColumn.exe
2. In confirmation dialog, click Yes to proceed with files unpacking
3. In Welcome screen, click Next to begin the installation
4. Verify that the system checks pass successfully. If all requirements are met, click Next to proceed
5. Read and accept the End-User License Agreement
6. Select one or more web applications to which you want to deploy Sparqube Lookup Column
7. Please wait for the setup to deploy the solution. Depending on your farm configuration it can take from 30 seconds up to 15 minutes. Click Next to continue
8. Please check the details to see if solution was properly installed. Click Close to finish setup

Licensing

You can use Sparqube Lookup Column for 3 days after installation without license. After that if you want to continue using it, you should request free 30-days trial or buy a license.

Note: After evaluation period is expired, you cannot use the product. This will be indicated by the following warning message: "Evaluation period has expired. Please activate the product". You can purchase Sparqube Lookup license at any time or you can request extended trial license by contacting Sparqube sales department.

To activate Sparqube Lookup Column on your SharePoint farm you need to:

1. Install Sparqube License Manager
2. Activate the license

Installing Sparqube License Manager

Download Sparqube License Manager from Sparqube Web site and install it to Central Administration Web application.

Licensing and Activation

You can get Full or Development license key via purchase or request Trial license key via product Download web page. License key is sent via e-mail and looks similar to the following: A1B2C3D-E4F5G6H-I7J8K9L-M1N2O3P.

License is activated using License Manager:

1. Open SharePoint Central Administration
2. Under Sparqube Configuration section, click Sparqube License Manager:

   

3. In this page, you can see all installed Sparqube components and its license type and status. Find Sparqube Lookup Column and click it.

   

4. In product detail page, you can see product information and licenses details. To activate license click Enter new license

   

   

5. If SharePoint server has internet connectivity you can activate online:
   1. In Product Activation dialog, click Activate online
   2. Enter license key and click Activate button

      

6. You can also activate license via email.
   1. Click Activate via email.
   2. In Product Activation dialog, you can see SharePoint Farm ID. Clicking on email address link in this dialog box will open new message window of your installed email client in which you should enter your license key which you'd like to activate. In case you do not have email client installed on your box you can simply copy Farm ID and License Key and send an email from any other suitable machine to this address sales[at]sparqube.com.

      

   3. After that, you will receive en email with activation code within 48 hours.
   4. To enter received activation code please repeat steps 1-4 and click Enter email activation.
   5. Enter email activation

      

7. After successful activation, you can refer to License Manager page and check license details.

   

Configuration

Lookup column types

Sparqube Lookup is the suite of three components consisting of the following SharePoint columns: Sparqube Lookup Classic, Sparqube Lookup Extra and Sparqube Lookup Tracker. Each column serves its own purpose and has unique set of features useful in certain scenario.

Sparqube Lookup Classic extends capabilities of standard SharePoint lookup field allowing to lookup and reference data from the same site or other site from the same site collection (cross-site lookup). The limitation to one site collection is specific to native Lookup field architecture on which Lookup Classic is built. Lookup Classic is backward-compatible with native Lookup field and can be converted to and from native Lookup preserving column data. Lookup Classic has wide variety of features and is fitting the majority of lookup use cases.

Sparqube Lookup Extra column is built from scratch and uses its own newly-implemented lookup engine. This approach allows to overcome limitations of native lookup and derived from it Lookup Classic. Lookup Extra can lookup data across site collections and additionally supports column data-types not supported by Lookup Classic: File, Choice, User or Group, Boolean and Lookup. Lookup Extra can even lookup complex types like documents. However, there are drawbacks of not using native lookup architecture:

• Sorting and filtering in list views are not supported.
• Source item additional fields cannot be displayed.
• Datasheet view usage is limited to read-only.
• Conversion from/to standard lookup is not supported. Same is true for conversion from/to Classic Lookup, thus Classic and Extra are mutually incompatible.

In other words, Lookup Extra is a valuable alternative to Lookup Classic in scenarios where classic lookup approach will not work, but lookup in general will be possible with certain limitations acceptable as a compromise.

Sparqube Lookup Tracker has logic reverse to lookup columns. It allows showing items which are referencing current item by lookup. Basic sample of Tracker scenario is the customer and his orders. If orders are located in the list and reference customer by lookup, these orders can be tracked from the customer list item.

Lookup column comparison chart

Feature	OOTB Lookup	Sparqube Lookup Classic	Sparqube Lookup Extra	Sparqube Lookup Tracker
Base lookup features
Items lookup (forward lookup)
Items tracking (reverse lookup)
Cascading lookup
Cross-site support
Cross-site collection support
Column types supported: Text, Number, Calculated
Column types supported: File, Lookup, User, Choice
Default values support
Backward-compatible with OOB lookup
Column conversion from OOTB Lookup
Advanced features
Show additional fields from source list
List view support
"Add new item" link
AJAX-enabled
In-site relationship enforcement
Cross-site relationship enforcement
Full datasheet view support (read/write)
Limited datasheet view support (read-only)
Value display
Display as link to source item
Display as text
Grid display

Adding Sparqube Lookup Classic to SharePoint list

Sparqube Lookup Classic column can be added to a lists, sites and content types like any other column types included with SharePoint.

To add Sparqube Lookup Classic column to SharePoint list:

1. Open existing list and add new column (In the ribbon, select List Tools tab > List > Create Column in Manage Views section)
2. In the Columns Name box, type a name for the column.
3. Under The type of information in this column is, click Sparqube Lookup Classic:

   

4. In Lookup Source section:
   1. Select SharePoint Site and list to get list data from
   2. Under Display Column in this list box, select column from the source list that will be displayed in the lookup

      

5. In Parent-Child Relationships between Columns, you can create a dependency between this column and another one in the same list. Select Make this a dependent column check box and choose Parent column from this list, which triggers changes in this column, then choose Filter column from lookup source list. Filter column will match with the value of Parent column to filter the results.

   

   

   Sparqube Lookup column supports different column types for Parent and Filter column. It can be Sparqube Lookup Classic, Sparqube Lookup Extra, Choice, Lookup, Person or Group, Single line of text, Yes/No, Content Type.
   
   Show all values when nothing is selected in parent column check box allows tuning up how filtering is performed. With the check box unchecked (default behavior) values of the column are filtered even if parent column has no values selected. If check box is checked then column available values are not filtered when parent column has no selected value.
   
   There are cases when multiple dependencies are necessary. For example you have Company Representatives list where there is a Company lookup column, which references data from Companies list, but, since there are lots of company records in the source list, it is required to filter data not only by country, but also by company size, for example. Suppose that Company Representatives list have Location lookup column referencing data from Country column, Company Size lookup column references data from Employees column and Company lookup column references data from Name column of Companies list.

   

   To achieve such behavior, Company lookup column will be configured the following way:
   1. Lookup Source section:
      1. Set Source List set to Companies
      2. Set Display Column set to Name
   2. Parent-Child Relationships section:
      1. Check Make this a dependent column
      2. Set Parent Column to Location
      3. Set Filter Column to Country
      4. Add new filtering sub-section by clicking Add more filters…
      5. Set Parent Column to Company Size
      6. Set Filter Column to Employees
      7. Checking Show all values… will allow to show values unfiltered by Company Size when no value will be selected for Company Size

   

   Similar behavior to the following will be seen during adding items to Company Representatives list:

   

   Four companies are shown since they meet the following conditions:
   • Country is US
   • Company size is greater than 1000 employees
6. To allow column to have more than one value, check the Allow multiple values option in the Multiple Value field section. Note: allowing multiple values will prevent sorting in list views.
7. Sparqube Lookup Classic column provides a set of instruments for lookup column value editing. In Column Value Edit section under Edit column value using box, you can choose control for column value editing in New Item and Edit Item forms from following list:

   

   1. Drop-Down List – displays possible options in simple drop-down menu.

      

   2. AutoComplete Textbox – enables users to quickly find and select from a pre-populated list of values as they type, leveraging searching and filtering.

      

   3. Radio Buttons

      

   4. Multiple Items Picker – similar to standard lookup control for multiple items selection.

      

   5. Check Boxes – displays the source list items as check boxes, multiple values can be selected.

      

   6. Drop-Down Check Boxes – displays values selectable by check boxes in drop-down menu.

      

   7. Picker Dialog – represents a dialog box in which user can find and select items from source list. This control is useful if you need to choose lookup data from large lists. It supports single and multiple value selection. To use this component, it is required to specify columns shown in its dialog form.

      

      

   In this section you can also enable items adding to lookup source list by checking Allow items adding to source list during column value edit box. With this option set "Add new item" link will be rendered in item add or edit form, by clicking which you will be able to add new items to the source list.

   

8. Column displays value as link to parent item in list view by default. In Column Value Display Format section, you can change this behavior by set Show column value as flag to Text, so item’s text will be displayed instead of link to it.

   

9. In Default Value section, you can enter the default value for the lookup column. You can type texts separated by semicolon for multiple default values selection.
10. In the Filter section, choose whether and how you want to filter source list items. If you want to turn filtering off and allow user to select any items from source list, then select Show all items. To filter items by existing list view, you can check Show items from the following view and choose list view from drop-down. Also you can specify custom filter selecting Show items only when the following is true and provided advanced filter options similar to item filter in list view:

    

11. In the Additional fields section, select secondary columns from Source list that you want to add to this list. These additional columns are automatically populated when you select a value in current column. Only columns with supported data types are listed:

    

12. Relationship section allows adding additional level of control to lists bound by parent-child relationship. By default there are no action constraints to list items of such lists, thus if any item in parent list is referenced by items in child list, such item can be deleted, for example. By enabling Enforce relationship behavior such actions can be prohibited and list items referenced in other lists cannot be deleted if Restrict delete is selected. In case Cascade delete is selected, item in parent list and all items referencing it in child lists will also be deleted.

    

    Note: please ensure that Sparqube Lookup Column site collection feature is activated for proper functioning of Enforce relationship behavior.
13. In the Datasheet View section, you can provide column behavior in Datasheet views. Possible options: Hidden, Read only and Allow edit.
    Note: If you choose Allow edit, then column will be rendered as standard SharePoint Lookup column in Datasheet view, special column settings such as filtering, parent-child relationships will be disabled in this view.

    

14. In the Convert section, you can convert existing standard SharePoint Lookup column to Sparqube Lookup Classic. Simply choose existing Lookup column and click Convert button to convert it to Sparqube Lookup Classic. All existing data will be preserved. This functionality is available only when creating new Sparqube Lookup Classic column.

Adding Sparqube Lookup Extra to SharePoint list

Sparqube Lookup Extra column is added and configured the same way as Lookup Classic. The only difference is that source list can be selected from any site collection of current web application:

Note: Sparqube Lookup Column site collection feature should be activated for automatic values synchronization with source list items.

Adding Sparqube Lookup Tracker to SharePoint list

Sparqube Lookup Tracker allows tracking and displaying related items from another list, which are referenced by lookup column. Assume there is an Orders list with lookup column referencing data from Customers list. Orders of certain customer can be shown in customer Item Display form with Lookup Tracker. In such scenario Sparqube Lookup Tracker column should be added to Customers list:

1. Open existing Customers list settings and add new column
2. Select Sparqube Lookup Tracker under The type of information in this column is

   

3. In Lookup Source section select site collection, site and list containing items referencing data from current list by lookup (in our case select Orders list
4. Under Lookup Column box select lookup column by which tracking relation is established. This can be either standard SharePoint lookup or Sparqube Lookup Classic or Sparqube Lookup Extra.
5. In Display Format section select referenced items display mode:
   1. Inline – items are displayed as inline links to the list items. Display Column setting specifies what data will be shown as link text;
   2. Table – items are displayed as table. List View setting specifies which existing list view will be used as rendering template. This approach allows using all the significant view features and properties: sorting, styles, filters and column set.

How to convert standard Lookup column to Sparqube Lookup and back

Convert to Sparqube Lookup Classic

To convert standard Lookup column to Sparqube Lookup Classic:

1. In List Settings page, click Create column.
2. In Name and Type section, choose column type as Sparqube Lookup Classic.
3. Expand Convert section, choose existing Lookup column and click Convert button. In confirm dialog, click OK button.
4. After that, lookup column is converted to Sparqube Lookup column, and column settings page is opened, where you can specify additional settings for this column.

Convert from Sparqube Lookup Classic to standard Lookup

To convert Sparqube Lookup Classic column to standard Lookup:

1. In List Settings page, click on target Sparqube Lookup Classic column to open Sparqube Lookup column settings page.
2. Expand Convert section and click Convert button.
3. In confirm dialog, click OK button.
4. Column is converted to standard Lookup column.

Use cases

Filter lookup values by other fields in the same list (simple cascading behavior)

One of the most common questions in SharePoint is how to setup lookup column in SharePoint list with ability to filter out values by other field in the same list. This can be easily achieved with Sparqube Lookup. Let’s assume you have two lists:

• Product Categories - list of product categories with Category Name column (Title column)
• Products - list of products referencing Product Categories list. Its columns are Product Name (Title), Product Category (Lookup column with Product Categories list as source list) and others

We need to add reference to product to third list (let’s call it Orders) which will be nicely filtered by product category. In order to implement it, follow next steps:

1. Add Category column to Orders list:
   1. Create new Category column and select Sparqube Lookup for column type.
   2. In Source Data, select SharePoint site that contains Product Categories list, and select Product Categories list under Source List box.
   3. Under Display Column in this list box, select Category Name column of Product Categories list.
   4. (Optional) In Column Value Display Format section under Show column value as box, we can choose rendering product category in Orders list as Text, not as link to its item.

   

2. Add Product column to Orders list as dependent lookup column:
   1. Create new column, name it Product and select Sparqube Lookup for column type
   2. In Source Data, select SharePoint site containing Products list and choose Products list under Source List
   3. Under Display Column in this list box, select Product Name column of Products list
   4. In Parent-Child Relationships between Columns section, check Make this a dependent column option and select Category column of Orders list under Parent Column in this list and Product Category of Products list as Filter Column

   

Now, when adding new item to Orders list, when Category is selected or changed, drop-down list of products will be automatically re-filled with filtered items which correspond selected product category:

Matrix-type geographical data with cascading behavior

The process of entering geographical data is one example where a dependent field is immediately useful. In this example, let’s assume you have Map SharePoint list that contains geographical data stored in matrix structure. This list has Country, Region and City columns:

There is another Companies list, in which this geographic data can be referenced in cascading manner: user could choose country, then its region, then city from the list of the cities for selected region.

This can be done in the following steps:

1. Add Country column to Companies list:
   1. Create new Country column and choose column type as Sparqube Lookup
   2. In Source Data, select SharePoint site that contains Map list, and select Map list under Source List box
   3. Under Display Column in this list box, select Country column

   

2. Add new Region column to Companies list:
   1. Create new Region column and choose column type as Sparqube Lookup
   2. In Source Data, select SharePoint site that contains Map list, and select Map list under Source List box
   3. Under Display Column in this list box, select Region column
   4. In Parent-Child Relationships between Columns section, check Make this a dependent column option
   5. Under Parent Column in this list box, select Country column of Companies list
   6. Under Filter Column in source list box, select Country column of Maps list

   

3. Add new City column to Companies list:
   1. Create new City column and choose column type as Sparqube Lookup
   2. In Source Data, select SharePoint site that contains Map list, and select Map list under Source List box
   3. Under Display Column in this list box, select City column
   4. In Parent-Child Relationships between Columns section, check Make this a dependent column option
   5. Under Parent Column in this list box, select Region column of Companies list
   6. Under Filter Column in source list box, select Region column of Maps list

   

After that, when user adds new customer or edits existing one, she can choose country, after that regions drop-down list will automatically refresh and display only regions from selected country. After region is selected, list of cities will automatically populate with values corresponding to selected region.

There is no limitation on number of cascading levels in Sparqube Lookup. You can also check Allow multiple values to allow Lookup columns containing more than one value.