02.03.2011 Updated the code for the file “vLookupForSharePoint.js” to v1.7.0

• Add an “orderBy” option to sort the returned items by a FieldInternalName – See parameter descriptions below.
• The dependencies upon interaction.js and stringBuffer.js has been eliminated.

10.06.2010 Updated the code for the file “vLookupForSharePoint.js” to fix some bugs.
This update includes:

06.05.2010 Updated the code for the file “vLookupForSharePoint.js”.

This update includes:

01.04.2010 Small update to the file “vLookupForSharePoint.js”. I have modified the argument “hoverToView” to have separate settings for DispForm and for ListView. You must change the argument “hoverToView” to “hoverToViewDispForm” and “hoverToViewListView” in your calculated columns.

I have long thought of making a generic solution for pulling information from another list, kind of like you would do in Excel with the function vLookup. Here is my attempt to create just this. I’m fairly confident this solution will fill an empty space in many SharePointers hearts…

This solution features

• Easy, generic interface to pull information from any list in current site or cross site
• Concatenation of values from multiple items
• Sum, average or count values from multiple items
• Link back to filtered view of all matched items in “sum”, “avg” or “count” -mode, or to individual items in “concat” -mode
• Direct link to documents
• “Reverse lookup” on lookup columns – the “mother” item now has link back to the “children”.
• And more…

The way this solution works is by using a calculated column to build a string with all the parameters like this:

This code matches the ID of the current item against a lookup column (the “child-list” has a lookup column named “ParentBug” targeting the “Mother-list” – what field the lookup is connected to is irrelevant as it is the ID we are looking for). This approach is a bit different than it will be for other columns as the ID is not accessible to a normal calculated column.

="listName:vLookupBugTrackerTasks|find:ID|findInCol:ParentBug|action:concat|linkBack:true|viewFields:Title#Title;TaskDescription#Task description;Deadline#Deadline"

This example matches the text in the “Title” column in one list against the “Title” column in another list.

="listName:vLookupBugTrackerTasks|find:"&Title&"|findInCol:Title|action:concat|linkBack:true|viewFields:Title#Title"

These are the available arguments:

These are the base arguments that are required:
‘listName’,’find’,’findInCol’,’action’,’viewFields’

Note: If your formula errors out, it most likely has to do with the string being to long. To overcome this problem, just concatenate the string like this:
…first part of the string her”&”and the rest here…

Example images:

List view with tasks pulled from tasklist

Hover over each item for “link back menu”. Click on a “link back” will take you to the item

hoverToViewListView:true – before hover:

hoverToViewListView:true – after hover:

“sum”, “avg” or “count” looks like this:

A click on a “link back” on items in “sum”,”avg” or “count” -mode will take you to a filtered list of all matched items.

The target list view is set in the parameter “linkBackView”.

DispForm with “newItemLink:true”:

The same item in EditForm:

When setting the parameter inQueryString like this “inQueryString:Title;ID”, the value from these fields are included in the querystring of the URL (click to enlarge the image):

You need another script in the target list to pull the parameters from the query string and write them to a field in NewForm. Look here for an example.

When the code is added to the listView and to DispForm/EditForm, you create a new “enhanced lookup column” by creating a new field of type “Calculated (calculation based on other columns)” with a FieldInternalName starting with “vLookup” (you can rename the column afterwards, it’s only the FieldInternalName that is important). All fields with a FieldInternalName starting with this text will be included. No need to specify the columns to include! (this is not true for EditForm though, see separate instructions). Note: the ID column must be in the view (but can be hidden in the script).

Here is the code

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”).

The jQuery-library is referred from Google, but if you prefer a local copy, it is found here. The pictures and the sourcecode refers to jquery-1.5.1.min. If you download another version, be sure to update the script reference in the sourcecode.

The sourcecode for the file “vLookupForSharePoint.js” is found below.

Read here how to add a CEWP to the DispForm or EditForm.

Add this code in a CEWP below the list form in DispForm:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
  init_vLookupForSharePointDispForm();
</script>

Add this code in a CEWP below the list form in EditForm:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
  // This is an array of objects. Add more fields by adding a new object to the array
  // The "insertHere" value can be "top", "bottom" or a FieldInternalName to append the returned data to
  init_vLookupForSharePointEditForm([{'FieldInternalName':'vLookupTasks','FieldDisplayName':'Tasks','insertHere':'BugDescription'}]);
</script>

The reason the EditForm code is different is that the calculated column is not available unless we query for it using a CAML query. The “insertHere” parameter is used to place the “new field”.

Add this code in a CEWP below the list view:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
// The argument "true" sets the ID column to hidden
  init_vLookupForSharePointListView(true);
</script>

The code for the file “vLookupForSharePoint.js”:
download code

Upload the file to the scriptlibrary as described above.

If you find any bugs, please let me know!

Regards
Alexander