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
Consider me teased!
Looking forward to this-
Charlie
very impressive, i’m anxious to test it out.
Hi Alexander:
When you say, ” When the code is added to the listView and to DispForm, you create a new “enhanced lookup column…”, do I add this to the main list or the lookup list?
Thanks-
Charlie Epes
In the main list.
Alexander
I tried to mirror what you have in your lists (list names and field names) added ID to my view and addede code to both list view and dispfom, at the bottom. No error, no text. the only difference is my source list is not a task list, but if I understand correctly this should not matter, right? is there something I missed?
Please post the calculated column content. Charlie also has trouble getting this working. I will update the post with more examples, but want to try to understand what is going wrong for you guys first.
Alexander
I also noticed a similar issue with the ID field when I tested the Rating In SharePoint script. What position should the ID column be in?
that update nailed it, perfect!
You got it? – please tell me what was the problem, and i will make a better description in the text.
Alexander
Hi alexander,
First I want to thank you for the great work you are publishing. It’s a real life safer.
I have very little knowledge of Javascript, yet I can understand some of it. Perhaps this is a noob question, but I was wondering why do I have to put seperate javascripts in a documenent library or folder in root of sharepoint if it is also possible to put that code in the content editor webpart so I don’t need to call it.
For instance this:
Put the code in the Content Editor Webpart instead of calling for it.
I don’t know why you do this. I assume there is a specific reason for it?
Thanks Ahead!
the main reason i use a library is it allows you to have the ability to single source, Share script across multiple list and libraries. Also from a maintenance perspective, this way you only update one file to update all places it is used.
Wow impressive and speedy reply. Thanks Larry. That makes sense. I assume then it is possible to put all of it in one content editor webppart since there are not other I will use or need to adjust.
Also anohter question, where I have been looking for a while and eventuelly I ended up adjusting, combining writing some code yet it is not really what I would like it to be. It works but I would not call it perfect.
I’m am trying to filter a list based on the current user that is logged in in SharePoint wss 3.0. Don’t mistake my question with filter you can apply on user fields but with a normal single line textfield.
I managed to find a code and adjust it so that can display all the current user fields (like department etc). And use this to filter a list with a search box. On the basis of what is in for instance field Department it will filter. So if in department says FR J.K Jenkins it will look in all the rows for FR J.K. Jenkins
I am going to import several type of people like for instance (example) Name of Policemen, Name of Firemen etc etc. All of them have a specific code before their names and there are no double names. Even if that would be the case it would not be a real issue.
SharePoint filters with AND operator not OR operator. If you would have JUDGE1 JUDGE2 and JUDGE 3 and a person can be in all there of them but not all at once you can’t show all columns related to a judge. Thats why I created my own stuff.
Shortly said I want to search (what would filter automatically) in specific rows and not ALL the rows. So lets say search in Column FR Firemen and others (not all). I search with a OR operator you would say for each column.
The question is, if there is a way to filter columns on the basis of specic columns with OR operator instead of AND. I manged to do this already but not on specific columns like search only in JUDGE1 and JUDGE2, so DONT search in comments etc.
Hi Carel,
Take a look at this one Filter list view based on membership in SharePoint group. It’s not spot on, but you may adapt it to your need.
Alexander
Thx for the tip alexander, I will see what I can do with it. Yet another question (sorry i have so many). I assume this filter tip you gave me filters the list AFTER the list is loaded? If so is it possible to filter the list before it is loaded or in the background where users can’t see it with slow internet. Users with slow internet get to see the whole list (items what are not for their eyes) and then it gets filtered.
Thank you!
Oh forgot to ask if that filter works on calendars 2
Yes, it filters after (or during) page load. In IE the page does not render before the filter is finished, but in FireFox it does. To filter before the page loads, you must have a “filter webpart”.
Javascript cannot do this unless you query the list and build the full HTML with the script.
The filter does not work in calendars.
Please post additional questions in the Filter list view based on membership in SharePoint group post.
Alexander
I am trying to implement this solution but can’t seem to get it working. I don’t get any errors, but my calculated column just displays the formula text instead of the lookup value.
I am using WSS 3.0. I’ve included all the js files and referenced them correctly in the CEWPs. I created a calculated column called vLookupDescription. My formula looks like this:
=”listName:{0B65ED1A-5850-408C-A716-1574E784D709}|find:”&CR&”|findInCol:CR|action:concat|viewFields:Description#Description”
I was expecting this formula to lookup the CR value in the list identified by the GUID and pull back the content of the Description field. Any thoughts about what I am doing wrong?
Thanks so much for publishing all your great solutions!
It looks right to me. Do you get any errors or is nothing happening?
If your “formula” is displayed in the calculated field, there is something wrong with the script reference.
Alexander
Sorry, I must be missing something really simple. Can you let me know if these statements are true?
– The CEWPs are only needed on the list view and DispForm from the vlookup originating lists, not the target lists
– The ID field only needs to be exposed on the originating list, not the target list, and only if you want to do a vlookup like your first example above
– Nothing needs to be changed in the vLookupForSharePoint.js file
Hi,
CEWP in DispForm and listview are placed in the list where you want the information displayed. No changes are made to the list holding the information you want to pull in and display.
Even though the ID is only necessary if you use it in the “find” argument, it is required to be in the view.
No changes are made to the file “vLookupForSharePoint.js”.
Alexander
Hi, I don’t know what I did differently, but I tried again and finally got this working! Awesome solution! 5 stars!
Small update to the code. See comment at the top of the article.
Alexander
Hi Alexander:
This code is the Holy Grail of SharePoint…
In my experiment to NOT have “vLookup…” appear in my display name of the lookup column, I changed the two references to “vLookup” in the “vLookupForSharePoint.js” script to an underscore – “_”.
Now, my two (!) lookup fields have names like [_Manager Last Name] and [_Manager First Name].
I have noticed that, perhaps because my 2nd list has 3,000 items to search through, that the rendering takes a few more seconds than a normal calculation. Is this nomal?
Thanks again!!
Charlie Epes
Hi Charlie,
Glad you figured it out. I would very much appreciate to hear what caused the trouble making this work.
About the FieldInternalName vs DisplayName:
It is the FieldInternalName that is used to identify the “vLookup fields”. As long as the FieldInternalName is starting with “vLookup”, you can use whatever you want as DisplayName.
Regards
Alexander
Hi Alexander:
Regarding your statement:
“It is the FieldInternalName that is used to identify the “vLookup fields”. As long as the FieldInternalName is starting with “vLookup”, you can use whatever you want as DisplayName.”,
I used “_Mgr_x0020_Last” as my internal field name and it still works. Perhaps this is because I changed the JQuery script to look for an underscrore in the field name, instead of looking for “vLookup”… I’m not sure.
I will send you a screenshot showing another type of vLookup that shows who the manager is for various employees.
Charlie Epes
The code is updated and new features has been added.
Alexander
The code is updated to fix some bugs. See top of article for details.
Alexander
Could you help me out I have two lists, the main one is called SDS_SR_P2 and the child list is called SDS_ServiceTask.
In SDS_SR_P2 EditForm I have the following code in a CEWP:
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/jquery-1.4.2.min.js”][/script]
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/interaction.js”][/script]
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/stringBuffer.js”>[/script]
[script type=”text/javascript” src=”http://epwvp2020:48368/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/vLookupForSharePoint.js”][/script]
// 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’:’Short%5Fx0020%5FDescription’}]);
[/script]
[/script]
In the DispForm CEWP I have this code:
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/jquery-1.4.2.min.js”>[/script]
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/interaction.js”][/script]
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/stringBuffer.js”][/script]
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/vLookupForSharePoint.js”][/script]
[script type=”text/javascript”]
init_vLookupForSharePointDispForm();
[/script]
In the list view CEWP code I have:
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/jquery-1.4.2.min.js”>[/script]
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/interaction.js”][/script]
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/stringBuffer.js”][/script]
[script type=”text/javascript” src=”http://epwvp2020:16119/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/vLookupForSharePoint.js”][/script]
[script type=”text/javascript”]
// The argument “true” sets the ID column to hidden
init_vLookupForSharePointListView(true);
[/script>
The columns I want to show in the main list (SDS_SR_P2) from the child list (SDS_ServiceTask) are Description, Task Details, and Task Status.
The calculated column in the main list (SDS_SR_P2) is called vLookup and has this is the formula:
=”listName:{f5599905-d13a-4ddd-af0e-8413049f8bfe}|find:”&Title&”|findInCol:Title|action:concat|linkBack:true|viewFields:Title#Title”
I have nothing displayed in the vLookup column in the view.
What have I done wrong or am missing?
Thank you.
Hi,
Take a look here to learn how to post code in comments
If you receive no alerts, and the calculated column is empty, i would think the query return 0 items. Double check that your “Title” fields have the exact same value.
To display the number of items returned, insert this alert betveen line 351 and 352:
Alexander
Ok I have started from scratch and have created list just like your examples. The tasks are now showing in the vLookupBugTracker view 🙂 .
But in the DispForm they do not show I just have the formula displayed (listName:vLookupBugTrackerTasks|find:ID|findInCol:ParentBug|action:concat|linkBack:true|viewFields:Title#Title;TaskDescription#TaskDescription;Deadline#Deadline )
any ideas?
Thx
please ignore my previous note I found the problem I was reference the wrong javascript library
One thing that is not working for me now is the “Create new item” link in the EditForm.
here is my formula: =”listName:vLookupBugTrackerTasks|find:ID|findInCol:ParentBug|action:concat|linkBack:true|newitemLink:true|hoverToViewListView:true|viewFields:Title#Title;TaskDescription#TaskDescription;Deadline#Deadline”
what am I doing wrong?
thx
Never mind I see the problem, small i instead of cap I.
I added inQueryString:Title;ID to my vLookupTask column formula and now it included the title and ID in the URL.
I put the code from https://spjsblog.com/2010/02/17/prefill-fields-in-newform-from-url-query-string/ in a CEWP below the VLookupBugTrackerTask NewForm.aspx to get the value in the URL.
But nothing is pre-filled in the NewForm. I’m wondering in reading the js about ClientVal, do I need ClientVal somewhere?
can anybody help with this? Please.
Hi,
ClientVal is the parameter in the querystring in the example. You would replace ClientVal with “vLookupTitle” to read the Title.
Then duplicate the steps with “vLookupID” to prefill another field with the ID.
In the article you link to, the field is “addressed” directly, but you could use the function “setFieldValue” found in this article
Alexander
like this?
var queryStr= getQueryParameters();
// Is the parameter “vLookupTitle” defined?
if(queryStr[‘vLookupTitle’]!=undefined){
var properVal = decodeURI(queryStr[‘vLookupTitle’]);
$(fields[‘Title’]).find(‘input’).val(properVal);
}
I dont know what you mean by the field being ‘addressed’ directly, sorry could you please explain? Thank you.
That code looks good. If it still does not work, assure that the CEWP is placed below the form webpart.
By “addressed directly” i mean the line “$(fields[‘Title’]).find(‘input’).val(properVal);”.
If it is a single line of text, this is just fine, but if you want to set another field type, you might want to use the “setFieldValue-function” from the article mentioned above.
Then the line would be something like this:
setFieldValue(‘Title’,properVal);
Alexander
Ok now I understand the field value, thx.
My CEWP is below the NewForm webpart, but still does not pre-fill the Title field.
Maybe it’s in my URL string, is it correct: http://epwvp2020:48368/eacoteam/knowledgeportal/emts/Lists/vLookupBugTrackerTasks/NewForm.aspx?source=/eacoteam/knowledgeportal/emts/Lists/vLookupBugTracker/DispForm.aspx?ID=1&vLookupTitle=Bug%20number%201
Thanks
Check that the scripts are working How to troubleshoot when the scripts does not work
Alexander
I put alert(typeof($)); and got function, so its reaching the qQuery.
not sure what to put in to test the function as Im not sure what the function name is. please help, thx
Hi,
Is the NewForm modified in SharePoint Designer? – the function init_fields() works only in unmodified forms.
If this is not the issue try to alert the variables like “properVal”, and to check that the fields = init_fields() is working – to to alert:
alert($(fields[‘Title’]).html());
This would give you the full HTML for the <TR> containing the Title field in the form.
Alexander
The form has not been modified in SPD.
I inserted alert($(fields[‘Title’]).html()); and am getting no alert.
I am wondering, I don’t need the Text to Html script because I’m using your link to create a new item, right?
Hello and thanks.
The form has not been modified in SPD.
I inserted alert($(fields[‘Title’]).html()); and am getting no alert.
I am wondering, I don’t need the Text to Html script because I’m using your link to create a new item, right?
is this script correct?
// <script type="text/javascript" src="http://epwvp2020:48368/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/jquery-1.4.2.min.js"></script> <script type="text/javascript"> alert($(fields['Title']).html()); </script> <script type="text/javascript"> fields = init_fields(); // Get all querystring parameters var queryStr= getQueryParameters(); // Is the parameter "vLookupTitle" defined? if(queryStr['vLookupTitle']!=undefined){ var properVal = decodeURI(queryStr['vLookupTitle']); setFieldValue(‘Title’,properVal); } // Function to separate each url search string parameters function getQueryParameters(){ qObj = {}; var urlSearch = window.location.search; if(urlSearch.length>0){ var qpart = urlSearch.substring(1).split('&'); $.each(qpart,function(i,item){ var splitAgain = item.split('='); qObj[splitAgain[0]] = splitAgain[1]; }); } return qObj; } function init_fields(){ var res = {}; $("td.ms-formbody").each(function(){ if($(this).html().indexOf('FieldInternalName="')<0) return; var start = $(this).html().indexOf('FieldInternalName="')+19; var stopp = $(this).html().indexOf('FieldType="')-7; var nm = $(this).html().substring(start,stopp); res[nm] = this.parentNode; }); return res; } </script>Hi,
You cannot use the fields object in line 6 when it is not defined until line 10…
No need for text to HTML to use vLookup.
Alexander
Would I put it after line 10 then?
And do I need the text to html script?
Does the rest of the code look ok?
thanks.
In line 17 you must fix the apostrophes.
I tested the code, and you must refer the “spjs-utility.js” to use the setFieldValue function. You must also switch the “init_fields” for the “init_fields_v2” from the “spjs-utility.js”.
Alexander
Thank you.
I think I fixed line 17.
Could you look at the rest of my code, its still not working.
<script type="text/javascript" src="http://epwvp2020:48368/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/jquery-1.4.2.min.js"></script> <script type="text/javascript" src="http://epwvp2020:48368/eacoteam/knowledgeportal/emts/EMTS%20Documentation/Javascript/spjs-utility.js"></script> <script type="text/javascript"> fields = init_fields_v2(); // Get all querystring parameters var queryStr= getQueryParameters(); // Is the parameter "vLookupTitle" defined? if(queryStr['vLookupTitle']!=undefined){ var properVal = decodeURI(queryStr['vLookupTitle']); setFieldValue('Title',properVal); } // Function to separate each url search string parameters function getQueryParameters(){ qObj = {}; var urlSearch = window.location.search; if(urlSearch.length>0){ var qpart = urlSearch.substring(1).split('&'); $.each(qpart,function(i,item){ var splitAgain = item.split('='); qObj[splitAgain[0]] = splitAgain[1]; }); } return qObj; } function init_fields_v2(){ var res = {}; $("td.ms-formbody").each(function(){ if($(this).html().indexOf('FieldInternalName="')<0) return; var start = $(this).html().indexOf('FieldInternalName="')+19; var stopp = $(this).html().indexOf('FieldType="')-7; var nm = $(this).html().substring(start,stopp); res[nm] = this.parentNode; }); return res; } </script>And my URL looks like this:
http://epwvp2020:48368/eacoteam/knowledgeportal/emts/Lists/vLookupBugTrackerTasks/NewForm.aspx?source=/eacoteam/knowledgeportal/emts/Lists/vLookupBugTracker/DispForm.aspx?ID=1&vLookupTitle=Bug%20number%201&vLookupID=1
Hi,
You have to read the code and look for errors… The code you provided has parts of the function “getQueryParameters” commented out and therefore does not work.
The function init_fields_v2() is found in the spjs-utility.js. You should not rename the init_fields() as you did.
Here is a working sample – change the script “src”:
<script type="text/javascript" src="/test/English/Javascript/jquery-1.4.2.min.js"></script> <script type="text/javascript" src="/test/English/Javascript/spjs-utility.js"></script> <script type="text/javascript"> fields = init_fields_v2(); // Get all querystring parameters var queryStr = getQueryParameters(); // Is the parameter "vLookupTitle" defined? if(queryStr['vLookupTitle']!=undefined){ var properVal = decodeURI(queryStr['vLookupTitle']); setFieldValue('Title',properVal); } // Function to separate each url search string parameters function getQueryParameters(){ qObj = {}; var urlSearch = window.location.search; if(urlSearch.length>0){ var qpart = urlSearch.substring(1).split('&'); $.each(qpart,function(i,item){ var splitAgain = item.split('='); qObj[splitAgain[0]] = splitAgain[1]; }); } return qObj; } </script>Alexander
Thank you, I made those changes, but it is still not working for me.
does it matter that I have the code for cascading menus and Heading jasscript on the form also?
I would recommend you to test it in a “clean” list. If you get it working there, it is easier to troubleshoot the script in your “production list”.
Alexander
Hi, awesome solution! I have two questions for you:
Q1) Would it be possible to display an image for the “hoverToView” options rather than the “Hover to view entries…” text?
Q2) Is it possible to combine this solution with another calculated column? I have a column that compares various fields and then displays a Green, Yellow, Red KPI-type icon. I would like to use my code to figure out the right icon to display, but then use yours to link the vLookup to the image. For example, can I stick the vLookup code
in my code somewhere:
Q3) I plan to test this myself, but just wondering if you can use the same List as both the query source and target?
Q4) Is it possible to change any of the formatting on the pop-up that displays? Things like the width of the window, background color, etc.?
Thanks again for all you do!
Hi
Q1: Change the parameter “hoverToViewText” to hold an image address.
Q2:Not sure what you mean. Is it the target list that has the KPI – and you want to “find” the items based on the image displayed?
Q3: Yes
Q4: For now you would have to dig into the code and change it there. I have a plan to separate it into style tags or “style objects” to pass to the function.
Alexander
I ran into an interesting bug – when I try to apply this to a grouped view in a ListView Web Part on a web part page it doesn’t quite work. The ID column disappears and the sort/filter is removed from the vLookup column header as normal so it looks like the script is working, at least until you expand the groups. Once the groups expand the full calculated string with the function parameters is shown instead of the replacement text (I have hoverToView set as true). What is interesting is that if you test the grouped view on the normal list page it works fine, it is only when you try to recreate the view on a web part page that the issue is evident. Otherwise this solution is quite nice, and if it could be made to work on the web part page it would be nearly perfect.
The only other suggestion I have would be including the “Create new item” link on the list view as well as the display/edit forms. There is probably a good reason why it isn’t set up that way (I didn’t see it mentioned in the article, I was thinking it might be an issue of getting the parent item’s ID), but it would be quite handy if it were possible.
Hi,
I have tested the grouped view in a web part page and had no trouble getting it to work as expected. Did you put the CEWP below the list view web part?
Your other question should be possible to achieve, but i cannot promise anything regarding when it might be fixed.
Alexander
Alex,
Thanks for this solution, it’s great.
I’ve added the inQueryString to my task calculated column, but Title isn’t appearing at the end of my URL in DispForm. Calculation:
=”listName:{898E757B-CFD9-434E-9AC1-AE1015542F65}|find:ID|findInCol:ParentBug|action:concat|hoverToViewListView:true|inQueryString:Title|viewFields:Title#Title;TaskDescription#Task description;Deadline#Deadline;Responsible#Responsible;Status#Status”
Also, I can’t seem to add inQueryString and newItemLink to the calculation at the same time without getting the “The formula contains a syntax error or is not supported.” error. I can only add one at a time, otherwise the error. Any ideas what I’m doing wrong?
There is no “syntax” in the formula – it is only a string. The problem is that the string is getting too long. Break it up by adding “&” in the middle somewhere.
The parameter inQueryString does only apply when combined with newItemLink.
Alexander
Thanks, I added the “&” and was able to add both. The “Title” still isn’t showing up in the URL though.
The parameters are case sensitive – please double check the spelling.
Alexander
Does this look right? inQueryString:Title (I want to pull the title field from the parent list)
It’s the only part I can’t get to work. Anything else I should check?
Nevermind, I got it to work. I just retyped it in to the string, instead of copying/pasting and that worked.
Another awesome solution Alexander! Is it possible to sort the results when using the concat action?
Hi,
I have added a sort option, see updated code.
Alexander
Very cool, many thanks! I put in the new script and received the following error:
I do have items in the target list where the lookup column is empty. I could probably default the field to a space character as a work around. Any thoughts?
Oops, forgot to use the sourcecode tags. Here is the error message:
[sourcecode language="”javascript”"]
<Where><IsNotNull><FieldRef Name=’_x0031_PID’ /></IsNotNull></Where>
I had forgotten an alert in the code – see v1.7.1 or search your v1.7.0 for “alert(query)” and remove the line.
Is your other comment related to this error?
Alexander
This works perfectly, thank you again! My comment below regarding html calculated columns is resolved too (my error).
Hi, another question, I can’t seem to make this work with html calculated columns.
One field in my target list displays basic html wrapped in a but its not coming through in the concat results from the vlookup script. Actually, the whole item is skipped, not just that field.
Should it be working or is that a known issue?
Thanks again for all your contributions to the SP community!
On the contrary, this solution reads html code from calculated columns with ease. Could you have a wrong FieldInternalName?
Alexander
I’m starting to feel bad about asking too many questions. But I have one more on this post. Would it be difficult to check for multiple conditions?
So in addition to doing the Find in the target list, the script would also check a value in another field in the same list before returning any results.
For example, I have Project list and CR List. I am using your solution to concatenate CRs related to a Project. I don’t want CRs that are in a Cancelled status to be returned so I would need to check another field for that.
Actually, while I was typing I thought of creating a view that filtered out what I don’t want and changing the GUID reference to that view in the calculated column. I’ll see if that works…
Just an update, this didn’t work, which I should have known since the list still has the same GUID in every view.
Could you make this happen using a calculated column? Querying by view is not supported in this solution – it is possible, but not implemented here.
Alexander
Yes, that worked perfectly! Thanks for the idea.
Hi, has anyone been able to access an AssignedTo field using this script?
Thanks!
This should not be a problem – verify that your FieldInternalName is correct.
Alexander
Hi Alexander, I have another request, and I suppose I could never out-post Larry 🙂 so I’ll give it go.
Would it be possible to add a summary row at the top of a concatenated list where you could display a count of the number of items returned?
And this could also be used to display a “no items found” type message or something when nothing is returned.
I will make a note of this and see what i can do – it is not difficult, but my time is limited as i have posted far to many solutions to manage to update them all…
Alexander
Hi, I can’t get the vLookup fields to display on my NewForm or EditForm when using the Tabs v2.0 solution. It works fine on the DispForm. Are these compatible?
I have not tested it. Try calling it in the reverse order and see what you can make out of it.
I cannot see anything that would prevent it from working.
Alexander
Hi, Whenever I click on the link for the javascript file (“vLookupForSharePoint.js”) my IE is unable to open the page and I can’t save a copy directly. Did this file move by any chance? I’m very interested in trying to implement this on my comapany’s Sharepoint portal.
Hi,
Right click and “Save target as”. Did that help?
Alexander
That’s what I meant when I said I couldn’t save the target directly either, sorry for the confusion and thanks for the quick reply too.
Save target as gives the following error: “Internet Explorer cannot download vLookupForSharepoint/ from bautz.homeserver.com”
I thought maybe the file was moved, I will try from my home computer in case it is just settings at my work preventing it.
Hi Alex,
I’ve used your excellent vLookup solution on a few SP 2007 sites, but am having trouble implementing on a SP 2010 site. I’ve gone over the steps enough to be seeing double and as far as I can tell I have my calculated column set up correctly. I am linking my text file with the script (from a central library on the same site) to a CEWP below the list view web part. Do you know whether this solution is compatible with 2010?
Hi,
I’m not sure to be honest. I have remade this solution for “internal use” on SP2010 due to some new requirements. I will see if i can get the time to wrap it up in a new post.
Alexander
Alexander, do you have the 2010 version of this code available?
Its interesting… this script works selectively on SP2010 pages. My site was converted from 2007 to 2010 today and the vLookup works some of the time. I am using the v3 mode fo now.
Ok, it looks like it breaks in a grouped view, but works when the list view is not grouped.
Sorry, correction, breaks in a view that is grouped and collapsed by default, otherwise works in an ungrouped view or grouped view that is expanded by default.
This version of the script will not work in SP2010 if you do a visual upgrade. I’ll see what I can do about the 2010 version – stay tuned.
Alexander
Awesome, thank you so much for looking at it!
I also wanted to report that the script works on the DispForm in v3 mode.
Alex-
First of all thanks for developing this code. It is by far the most used tool I use.
I noticed that in the newer versions (I’m using 1.7.0) that the returned rows are alternating between a white and gray background. I don’t believe it did that in earlier code. Is there any way to turn the alternating off?
Thanks –
Hi,
Sorry for the late reply. Search the script for “highligthStyle” and you find what you are looking for.
Alexander
Hello Alex, I have just emailed you from my office ID. I hope you would be able to help me get the errors with the data types corrected as I have described in my email. I am a business manager and have zero experience with code. Please help. Anand
Hi, I can’t get this script to display columns where Type=”Computed”.
For example, I’d like to display the file type icon before the document name for items in a Library. Should that work and I am doing something wrong?
Sorry for the late reply,
This should work – ensure you have the correct FieldInternalName – icon (the file extension) would be “DocIcon”.
Alexander
Hi there, I use this script extensively, thank you! Would it be possible to add a math function that is the % of the total?
Hi,
If you could provide a bit more details I might be able to help.
Alexander
I would like to be able to get the sum of a number field across rows (which your script already does) and then divide the sum by the value of that field in each row.
For example, three projects with 10, 15 and 25 hours respectively. Your script can already find the sum which would be 50 hours. I would like to use the script to say Project A is 20% of the total (50/10), Project B is 30% (50/30), etc.
In this case the same List is both the source and the target and I would display the percentage on each row.
Sorry, but this is not possible without custom building it.
Alexander
Hi there! I am using the 1.7.0 version of this script and when I remove the references to the stringBuffer and interactions scripts on a ListView page the vLookup script fails. It justs displays the line that begins with listName: instead. Can you think of any reason why that would happen? I am also referencing jQuery 1.6.1 on the page.
I have not tested it, but it might be that this solution does not like jQuery 1.6.
Alexander
I have to agree with DCH, without the stringBuffer and interactions scripts, any use of this script fails. The 1.7.0-1.7.1 versions still need those scripts to work properly.
Great job on this library. Keep up the good work.
Any way to have the returned values just concatenated, like in a comma separated list?
Not without modifying the solution.
Alexander
Alexander, I have attempted to implement this solution but I couldn’t quite get to do what I needed. I was wondering if you had any plans to update this version to use the GUI and some of the newer features from the 2010 version.
Sorry, I do not have such plans.
Alexander
Hi Alexander, this solution looks great and absolutely like the answer to my current problem – however i just can’t get it running:
– i always get just the string displayed in the calculated column
– scripts are referenced correctly and loaded (verified in ie debugger)
– when I debug the page debugger breaks at line 365 -> res = queryItems(listName,query,viewFields); and error “The value of the property ‘queryItems’ is null or undefined, not a Function object”
I searched for the queryItems function but cannot find it – do I need to reference another library or am I missing something quite basic here?
Any tipp is highly appreciated!
Best Regards,
Markus
Hi,
Sorry for the late reply.
There was a bug in v1.7.1 – a 2 year(!) old bug regarding a missing function. I have updated this in v1.7.2 by adding a few functions to fix it.
If you are using SP 2010 you should however use the 2010 version found here
Alexander
Hi Alex,
I am using SP2007 and looking through your vLookup solution, but what I was looking for was the ability use a LookUp field to select a field from another list (on the same site) and have other fields on the list populate the respective fields based on the field I chose from the other list.
Is that what the solution above does?
I have read through the comments and one guy seems to have had the same issue. I am getting this in the lookup field.
listName:{62372C56-CFAF-4D66-A6E6-C6082CD64B60}|find:Arizona|findInCol:State|action:concat|viewFields:Description#Description
A reply you had stated I wasnt referencing the script correctly.
I have a clean test list. i created three columns. a “Name” column and a “State” column and a “vlookupState” column.
what am I doing wrong?
just to make sure i understand, i am trying to pull all other records that have the same state value as the current item.
Hi,
The name of the column is case sensitive: vLookupState
Alexander
I apologize for the typo. I actually am using a capitol “L” in vLookup
I named that particular column vLookupState” but that should be an issue if I understand the directions correctly. (which I may not as I am not the brightest bulb in the shed).
Any other ideas as to why it is returning the formula in the vlookup column. Everything else I seem to have working, the formula is even working.
As always, you time is appreciated.
I also should point out that the only place the formula is showing is in the listform. the disp and edit work perfectly.
OK I figured that out. I just needed to work on the stinking view. Thank you for your crazy fast support!
Is the CEWP placed below the list view web part, and is the list view a standard SharePoint list view?
Alexander
I am using your TabsforSharepoint solution as well. Does the code need to go anyplace special? Can I just append it to the end of the “tabs” code?
Thanks again
Please disregard all previous issues as I have figured them out with a little tinkering. My final question is how and if I can get this to work with a lookup field.
I want to clarify. I am using this to grab all related data in the current list. I can get it to work with every type of field but a lookup field in my list. what is the code for getting it to work on the lookup field if ther eis any?
here is my ciurrent string. =”listName:{5C3A3A8D-3BEA-4AA1-9992-04F91F4A5900}|find:”&Critical Topic&”|findInCol:Critical Topic|action:concat|linkBack:true|viewFields:Status#Status;Title#Title”
The critical topic is a lookup to another lists “Title” field
Sorry for the rambling just dont see an answer anywhere on this site for someone trying to do the same thing.
Sorry for the delay. If you havent already figured this out, you cannot use a lookup column in a calculated column. If you can get the value you want to use as the “search string” could come from a text field, you can use it, but you will have to use square brackets around field names with spaces like this:
[Critical Topic]
Alexander
Just checking: You are using SharePoint 2007?
Alexander
I am using 2007…. uhg
A few notes. I still am unable to donate to you following the instructions you gave me, it seems that the county code or something is not going away on my side. I have used a few of your products and you do great work and deserve to at least let me buy you a drink! Lets figure somthing else out.
Second. If people are trying to do what I was doing I have found a solution. While it is not the most elegant it gets the job done.
I was trying to make this work with a lookup column. While this will not work I found a work around. I am using SP 2007. I went into SP Designer and created a simple text column. I then created a workflow to transfer any items from the lookup column to the text column. I then used the vlookup solution on the text column and wala.
Hi,
It seems Paypal has some trouble changing language in the form. You can try this link where I have tried to force lc=US
If you want a non-workflow solution, insert this code in the CEWP:
<script type="text/javascript" src="/Scripts/spjs-utility.js"></script> <script type="text/javascript"> var fields = init_fields_v2(); var nameOfLookupColumn = "Lookup"; var nameOfTextColumn = "LookupValuePlaceholder"; // Hide the text field $(fields[nameOfTextColumn]).hide(); var oPreSaveAction = typeof PreSaveAction === "function" ? PreSaveAction : ""; PreSaveAction = function(){ var myLookupVal = getFieldValue(nameOfLookupColumn); setFieldValue(nameOfTextColumn,myLookupVal); if(typeof oPreSaveAction === "function"){ return oPreSaveAction(); }else{ return true; } } </script>You must change “nameOfLookupColumn” and “nameOfTextColumn” to the FieldInternalName of your fields.
You find spjs-utility.js here
Alexander
Hi Alex,
I have followed the instruction and mirrored your example. However I am still getting the following message:-
The ID Column must be in the view.
You may hide it in the script….
I have included the ID column in the view.. Any help would be appreciated.
Hi Alex,
I have followed the instructions and mirrored your examples. But still keep getting this message:-
The ID Column must be in the view.
Note: I have added the ID column to the view.