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:
- Support for multiple webparts in one page
- Bugfix: get listName in EditForm mode
- Added option “DispFormRelURL” under “inQueryString” to provide current items relative DispForm URL
- Handling multichoice values by replacing “;#” with “<br />”
- Fixed bug with “newItemLink” if “linkBack” is not true
- Added id tag for the “newItemLinkContainer” – to make hiding it possible.
- Fixed bug when item has no “children” (newItemLink and inQueryString)
- Fixed bug when matching on a calculated column
06.05.2010 Updated the code for the file “vLookupForSharePoint.js”.
This update includes:
- Some small bugfixes
- Added support for getting items connected by a multiLookup
- Added createNewItem option by setting the parameter “newItemLink:true”
- Added inQueryString option to include values from DispForm when creating new items using the “createNewItem” option
- Added option to display vLookup connected items in EditForm
- Localized for Norwegian, Swedish and English
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:
- listName: Name or GUID of the target list.
- listBaseUrl: The base URL of the site the target list is located in. Defaults to current site is the argument is omitted.
- find: The string to search for in the column specified as “findInCol” below. To use the current items ID (in case of a lookup connection), specify like this: “find:ID”.
- findInCol: The FieldInternalName of the column to query against.
- viewFields: “Array” of the fields of which to return a value for. Format: FieldInternalName1#DisplayName1. Separate multiple fields with semicolon.
- action: “sum”, “avg”, “count” or “concat”.
- sumOrAvgPrefix: A prefix in “sum” or “avg” -mode, like “$” for dollar.
- sumOrAvgPostfix: As above, but postfix.
- linkBack: true=Link back to item(s), false=no linkback. Default value: false
- linkBackView: If in “sum”, “avg” or “count” -mode, set the target view name for the filtered result on “linkBack”. Defaults to “AllItems.aspx” if omitted.
- hoverToViewListView: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in list view. Default value: false
- hoverToViewDispForm: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in DispForm. Default value: false
- hoverToViewEditForm: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in DispForm. Default value: false
- newItemLink: (In “concat” -mode) true=Displays a “Create new item” link. Default value: false
- inQueryString: (In “concat” -mode) semicolon separated array of FieldInternalNames to include the value from in the queryString that is passed to the NewForm. This only applies if the above parameter is set to true. Default value: “”. You need another script in the target list to pull the parameters from the query string and write them to a field in NewForm
- orderBy: New! A FieldInternalName to sort the returned items by. Set the parameter “orderBy” in the calculated column like this: orderBy:Title. To have the result ordered descending, append a hash behind the FieldInternalName like this: orderBy:Title#
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
