vLookup for SharePoint 2010

16.02.2013 Updated to v1.3 with these changes:

  • Added more options to process the values pulled back from the query. In addition to the previous substring, you can now have prefix and suffix and additional date formatting options. Hover over the help icon to learn more. Please note that you must reconfigure any existing “substring setup” after upgrading to v1.3.
  • Added support for hyperlink columns.
  • Added count to the “Action menu”.
  • Fixed bug with “hide field” when using a text string enclosed in curly braces in the “In URL” configuration.

25.01.2013 Updated to v1.26 Added option to split the string by a specified separator, returning a selected array index.

30.11.2012 I have updated to v1.23 to fix a compatibility issue with Office 365 for SP2013.

18.11.2012 Updated to v1.22 and added some video tutorials:

Please note that this requires v1.22 of vLookup_sp2010.js

08.11.2012 v1.2 fixes some bugs and adds some new features.
Bugs fixed:

  • Removed an extra “/NewForm.aspx” from the url generated in the “addChild” function.
  • When creating a child in a subsite or a parent site, the setting of values from the “parent item” failed due to a wrong baseUrl parameter in the URL.

Features added:

  • As suggested by “advacomp”, added an option to use a text string to set a field in the child list. See instructions on the help icon in the “In URL” section of the setup.
  • Added an option in the GUI to hide the form label in DispForm and EditForm.

16.10.2012 v1.1 fixes a bug with wrong list ID appearing when you edit the configuration from DispForm or EditForm.

I have previously posted a solution for SharePoint 2007, and by request I have rewritten the solution for SharePoint 2010. Please note that this solution will NOT work for any SharePoint version prior to SharePoint 2010.

This solution, as the previous one, supports these “methods”:

  • Show all records: Returns a list view of all the records.
  • Sum: Sums the values for all the records.
  • Average: Sums the values for all the records, and devides the sum on the number of non-empty records.

In the 2007 version, the configuration was setup in a calculated column trough the list settings > edit field.

This new solution is much easier to set up as it has a GUI:
IMG

In this solution, the configuration is stored in a dedicated list – shared by all vLookup columns in the site. This eliminates the fiddly work of hand editing the configuration.

When you first setup this solution in a site, the configuration list is created automatically. This list is not to be hand edited, and you might optionally hide it from browsers trough SharePoint Designer. You must however ensure all users have read access to this list.

You can use this solution in plain list views, grouped views and in DispForm / EditForm (see note bout field type below).

When this solution is setup in a list view, it looks like this:
IMG

DispForm and EditForm:
IMG

IMG
Adding new “Tasks” in EditForm does not refresh the form, only the “vLookup items” – thus you does not lose unsaved data in your EditForm.

How to set up the solution
  1. Download the code for the file “vLookup_sp2010.js” from here.
  2. Download the file “spjs-utility.js” from here. Ensure you get the latest version (by date of the folder).
  3. If you prefer a local copy of jQuery, get it here.
  4. Upload this files to a library in your SharePoint site collection, or to a folder in the root site – created using SharePoint Designer.
  5. Add a column to your list / library where the FieldInternalName starts with “vLookup”. This is what triggers the solution. Note that it’s the FieldInternalName and not the Display name that must match.

    You can use both a calculated column or a single line of text column as “placeholder” for the “vLookup items”. If you use a calculated column, use a formula like this:

    =""

    The contents of the field will be overwritten anyway.

    To be able to have the solution work in EditForm, you must use a single line of text column as “vLookup field”. Note that you cannot use this field to store anything as the contents will be overwritten by the vLookup solution.
  6. Add a HTML Form Web Part to the bottom of your list view, DispForm or EditForm, and add this code trough the “Source editor”:

    <script type="text/javascript" src="/test/vLookupv2/Script/jquery-1.7.2.min.js"></script>
<script type="text/javascript" src="/test/vLookupv2/Script/spjs-utility.js"></script>
<script type="text/javascript" src="/test/vLookupv2/Script/vLookup/vLookup_sp2010.js"></script>

    IMG

    You use the exact same code for list views and for list forms.

Creating the configuration list

The first time you set up the solution, you will get this prompt:
IMG

Hit “OK” to create the list:
IMG

You will now get this prompt (provided your field is in the view):
IMG

You will now be presented with the configuration GUI:
IMG

This GUI is automatically presented while the configuration has not been created, or it contains errors that are detected by this solution. To reenter the configuration at a later time, add “vLookupSetup=1” to the URL like this:

// List view
.../Lists/MyList/AllItems.aspx?vLookupSetup=1
// DispForm
.../Lists/MyList/DispForm.aspx?ID=12&vLookupSetup=1

All the configuration option are explained by hovering over the question mark.

Creating new items in the “child list”

When you add items to the child list, and want to prefill values in your NewForm, you use the In URL section of the configuration to set the “from” and “to” fields.

You must add the same code to a HTML form web part below the form web part in NewForm as you have used in DispForm and list views. This code will pull in the values from the “parent list” and fill in the “child list”.

If you are “connecting” the two lists by a lookup column, ensure you prefill the lookup column like in the example in the top of this article.

MUI support

This solution supports MUI and you can set labels and column headers in multiple languages. Use “default” as “LCID” to have a default value and/or set up the correct LCID (matching your installed language packs) as alternate values.

Do you use this solution?
I spend a lot of time developing these free solutions. If you use it, please hit the PayPal button to send me a few bucks as motivation for releasing new solutions.

Tags:

195 Comments on “vLookup for SharePoint 2010”

  1. First, you are so awesome, thank you!

    I am trying this out on a test site and I can’t figure out how to trigger the initial configuration list creation?

    1. Hi, If you have referred the scripts correctly, the setup should trigger automatically. Are you absolutely confident you have the script “src” correct? Alexander

    2. Ok, it works perfect on pure SP2010, but not on SP2010 in v3 mode. Thanks again!

      1. Hi, Yes, this solution is 100% dependant on 2010 mode to work.

        Alexander

  2. So far everything is awesome; great enhancements too!

    The only thing I can’t seem to get working is the “In URL” function. The config form saves fine, but when I try to create a new item from the ListView, the value from the parent column isn’t picked up by the target column in the NewForm dialog.

  3. Hi,

    I successfully create the Configuration List, then I go through the configuration screen to setup my vLookup, which after clicking save doesnt appear to do anything, Then everytime I view an item in my list I get the pop up saying:

    “vLookup for SP2010 – No configuration found

    Field: vLookupWitness

    Setup this field now?”

    Any ideas ? (there is a record in my config list so my config is getting saved)

    Cheers,

    1. Hi,
      I’ll need a few screenshots of your configuration GUI, the list URL where you use the solution, and the configuration stored in the configuration list.

      You find my email here

      Alexander

    1. What was your problem? – it might help others.
      Sorry, missed your follow-up below.

      Alexander

  8. Hi, i cant make it working … config list created ok. All config seems ok, but every time it asks to config my lookup field.
    Config is :
    {“listName”:”{2ADF35A6-B23C-42F5-AF46-36FA66E81C51}”,”listBaseUrl”:”/DictLib”,”query”:{“fin1″:”_x041e__x0440__x0433__x0430__x04″,”type1″:”LookupId”,”value1″:”[currentItem:ID]”,”fin2″:””,”type2″:”Text”,”value2″:””,”AndOr”:””,”operator1″:”Eq”,”operator2″:”Eq”,”orderBy”:”ID”,”orderAscending”:false},”viewFields”:[{“fin”:”Title”,”disp”:{“default”:”SecondName”}}],”action”:”concat”,”viewItemLink”:true,”editItemLink”:true,”label”:{},”noItemsLabel”:{“default”:”No contacts”},”addNew”:true,”addNewLabel”:{“default”:”Add contact”},”inUrl”:[{“from”:”ID”,”to”:”_x041e__x0440__x0433__x0430__x04″,”hide”:true}]}

    1. Hi,
      Post some screenshots of the configuration list and your setup.

      Alexander

    3. What reasons can be why the control cant find its configuration?
      Maybe because im using russian language?

    4. Hi,
      I think this must have to do with the configuration not being identified. This identification is done based on the url “name” of the list. Please check that the Title field from the list “SPJS-vLookupSettings” represents the url “name” of the list you want to address in this format:
      ListUrlName:FieldInternalName

      Alexander

    5. In second inspection I suspect the ListBaseUrl is wrong. It looks like you have entered the actual list url and not the site url.

      The parameter you are looking for is the variable “L_Menu_BaseUrl” – right click the list view – view source – search for the variable name and you’ll find the value. If you are on the root site, this is an empty string.

      Alexander

    1. Sorry for the late reply. My guess is that the FieldInternalName name for the column “ParentBug” is wrong.

      Alexander

  10. Hello I am getting the same error Pete was having. I made the Query Field Lookup(id) = [currentItem:ID].

    When I click on the Save button the page does anything.

    I am working on SharePoint 2010 Version 14.0.6029.1000

    This solution looks really cool. Any help you can provide would be awesome.

    Thanks,

    -David

    3. Hi,
      This error indicated that you have jQuery below v1.6. You must upgrade to 1.6.4 or above,

      Alexander

  12. Hello Alexander,

    I am continue to have issues getting this script to work. I created a list called DavidResto and a child list called PhoneNumbers. In the DavidResto list, I created the vLookup field. To set up the application, I placed HTML WebPart (https://docs.google.com/open?id=0B7b4EY8WkMz8M0x1Mlpjb0V1eWc) with the code specified on the Display, Edit and New Forms. On the New From the vLookup disappears, on the Display and Edit forms I am prompted to configure the vLookup. Here are the configuration settings specified: https://docs.google.com/a/nyu.edu/file/d/0B7b4EY8WkMz8R19maEZaazAxdGM/edit

    Once I get prompted that the list has been configured successfully I reload the page. When I click on the item again, I am prompted with to configure vLookup again. Here is a screenshot of the SPJS-vLookupSettings list: https://docs.google.com/a/nyu.edu/file/d/0B7b4EY8WkMz8bzljR1dTelZ6bjQ/edit

    Thanks for your help.

    -David

    1. Hi,
      Sorry for the late reply. The ListBaseUrl is wrong and should read “/test”, not “/test/lists”

      Alexander

  13. Hi ,
    I am new to SharePoint so this a beginners question –
    How can I tell where I should put the js files on the SharePoint-2010 file system?
    It is currently located in the following directory –
    C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14

    And the WebPart code looks like this (in all the 3 forms option – New, Edit and View)

    But I see no configuration screen although I have a field with internal name of vLookupTest1.

    Any thoughts?

    Regards,
    Avi.

    1. HI,
      Upload the files to a document library in your site, no need to put them on the server.

      Alexander

  14. Awesome post, once I got it working.

    Was getting the same error as Pete and Ians; where the configuration couldn’t be found and the Configuration pop-up appeared every time DispForm was loaded.
    “vLookup for SP2010 – No configuration found
    Field: vLookupField
    Setup this field now?”

    I am using a custom DispForm and found the Configuraion list was saving the item with a title using the name of the custom Disp form: e.g. “vLookupBug_DispForm.aspx:vLookupTaskTitle”.

    Replacing the Configuration title “vLookupBug_DispForm.aspx” with the name of the `parent’ list resolved the issue (e.g. “vLookupBugTracker:vLookupTaskTitle” resolved the issue !

    Keep up the good work !!

    Thanks

    Rorster75

    1. Hi,
      Look at my answer to Ians – I suspect the ListBaseUrl to be wrong.

      Sorry – I did not read your post good enough… I have fixed this bug in v1.1!

      Alexander

  15. Bug in new Child List — if you pass the ID to a lookup field in the child new item it will set the lookup field properly as long as there are less than 20 items in the lookup (Sharepoint uses HTML Select for select lists shorter than 20 but a differenct technique when 20 or more. Your code does not handle the 20 or more case — tested and verified with two simple lists.

    1. Hi,
      Thank you for pointing out this bug. It is related to spjs-utility.js, and you must update this to v1.12 to fix it.

      Get it here

      Alexander

  16. Great tool, it took a while to configure but works great. We needed to add the script to the child list form, not to the master list form, that confused us. But now it works, thanks!!!

  18. Hi Alexander,
    I have made some progress, but still have some issues, hopefully you can help.
    I was able to configure the View,Edit and New form by browsing to –
    DispForm.aspx?ID=12&vLookupSetup=1
    However, when I try to edit or view form I get the following error –
    [spjs_QueryItems]
    Missing Parameters!
    You must provide a minimum of “listName,”query” or “visitName” and “viewFields”

    Any ideas?

    Thanks,
    Avi.

    2. Same here… I have the List View working, but the DispForm throws this error for me. Were you able to solve the problem?

    3. The error is also preventing me from accessing the Dynamic Forms with Tabs configuration screen.

    4. The error text says:

      [spjs_QueryItems]

      Missing parameters!

      You must provide a minimum of “listName”, “query” or “viewName” and “viewFields”.

    6. Hi,
      Have you placed the CEWP below the form web part?
      Is the DispForm a standard, unmodified form?

      Alexander

    7. Yes to both. But I am calling these scripts in the following order if it matters:

      /jquery-1.7.2.min.js
      /spjs-utility.js
      /DynamicFormsForSharePoint_v2.js
      /vLookup_sp2010.js
      /Text2HTML.js

    8. What version of the vLookup solution are you using? – and have you tried to strip away all other scripts to ensure there are no interference from another script?

      Alexander

    9. I removed everything except the three required ones and I still got the error.

      /jquery-1.7.2.min.js
      /spjs-utility.js (ver 1.12)
      /vLookup_sp2010.js (ver 1.22)

      1. Send me some screenshots of your setup – including the CEWP code and the configuration screen + the corresponding list item item from the list “SPJS-vLookupSettings”.

        You find my email in the top of the file “vLookup_sp2010.js”.

        Alexander

    10. The issue appears to be with the spjs_utility.js (ver 1.12). I will try using an earlier version or two next.

    11. Ok, someone helped me do real troubleshooting. The listName variable in not getting passed to the dispform.aspx by the listform.aspx. When we went through the stack it shows listName as undefined, but you can see it in the URL. Same behavior whether the dispform.aspx is opened in a dialog or the page.

    12. Hi,
      I think I finally found the error. Try with v1.24 and let me know if this fixed the issue.

      Alexander

  19. The add child routine adds an extra /newform.aspx to the URL which doesn’t seem to cause any errors except when using vLookup with Dynamic Forms for Sharepoint.

    One way to fix this is to change the addChild routine replacing this statement
    customOpenInDialog(url+”/NewForm.aspx?”+urlKey+source);
    wtih this one
    customOpenInDialog(url+”?”+urlKey+source);
    since addChild is only referenced once in vLookup_sp3010.js that fix works in my testing.

  20. Hi Alexander,

    ok, I have done with three vLookup items. Works fine.
    But now I would like to add a some entries from a library into my list. Any idee to transfer the parent-ID over the upload-form to the client form? It would be nice to add this functionality to your vLooup- tool.
    Best regards
    Rudolf

    1. Hi,
      Could you describe this in more detail – I’m not sure I follow.

      Alexander

  21. Alexander,
    I would like to change the date formating from day of week, mm/dd/yy Time to simply mm/dd/yy — can you tell me where in the vloookup_sp2010 or spjs-utility I can find that code to change it.

    Thanks,
    Earl

    1. A bit late maybe, but here it is:

      fieldVal = fieldVal.toLocaleString().split('GMT')[0].replace(' 00:00:00','');

      Alexander

  22. Would it be possible to set a static value in the From Field for In URL? I would like to just enter the value to pass using the GUI instead of having to create another column that defaults to the value.

    1. I ended up modifying some code to make it work:

      function init_vLookupPresetFromURL(){
      var qs, arr, s, f, t;
      qs = getUrlKeyValueProper(‘getFromParent’);
      if(qs!==”){
      vLookupPresetFromUrl.active = true;
      arr = qs.split(‘~’);
      if(arr.length>0){
      $.each(arr,function(i,raw){
      s = raw.split(‘|’);
      if(s.length===3){
      if(s[0].match(/{[^]]*}/g))
      {
      setFieldValue(s[1],s[0].replace(“{“,””).replace(“}”,””),true);
      }
      else
      {
      f = s[0];
      setFromUrlObj.fromFields.push(f);
      t = s[1];
      setFromUrlObj.toFields.push(t);
      if(s[2]===’true’){
      $(vLookupFields[t]).hide();
      }
      }
      }
      });
      ExecuteOrDelayUntilScriptLoaded(do_vLookupPresetFromURL, “sp.js”);
      }
      }
      }

    2. With the above code, if a value is put inside of curly braces it will preset the field using the text inside the curly braces instead of doing a lookup from another field. I threw this together quickly so I’m sure there’s some clean-up that can be done.

    3. Hi,
      Nice enhancement. I’ll post an updated version with this fix shortly. I’ll include some other bug fixes as well:
      1. Error when setting a value from a field in the “mother list” in a subsite as the baseurl is wrong.
      2. Adding new items form the link in a list view adds an additional /NewForm.aspx” to the URL.

      Alexander

  23. I’m having exactly the same issue as Avi, I’m a newbie so I’m sure it’s something straight forward, is there an answer for this? Or does anyone have a video tutorial of how to set it up?

    Thanks,

    Phil

    1. This error states the the item with ID 12 in the list “Other” is deleted. This should not have anything to do with the vLookup solution directly.

      Alexander

    2. ohhh, I was just copying the code in your example, is that not correct? Do I have to alter the ID? what should the code be to get the config page back up?

      Thanks again!

    3. This id must reflect one item in your list. 12 is only an example.

      It may be difficult to understand while using modal dialogs, so maybe you should turn this off while setting up the solution.

      You can do that in the list settings. When the form loads in a standard page, you have access to the URL and can see the ID.

      I do not have more time to look at it right now, but if you do not get it right I can look at it tonight (GMT+1).

      Alexander

    4. Did you manage to find it out?

      If you have other scripts in the same page, there may be interference. Try stripping away all other scripts.

      Alexander

    5. I managed to work out how to view the config file again, but I’m still not able to get it working as I keep getting the error message.

      I don’t have any other scripts on the page, I’ve got it in test and just using this script and a simple list.

      I really hope i can get it working as it seems like it will be a great tool and script.

      If I can get it working, I’ll be making a contribution for sure!

      Thanks!

      1. Hi, Which version of spjs-utility.js are you using?

        If you activate the console in IE (hit F12 and select Console) – do you see any errors?

        Alexander

      2. I’m using the latest, 1.2 built in October.

        Don’t I get any errors, I’ll try tomorrow. I did try to ru through it but I just had the same error as when I run it normally.

  25. Thanks Alexander, here’s some screen shots to show how I’m setting it up.

    This is the MainList that I’m looking up against:
    http://dl.dropbox.com/u/24443247/sp2010_Vlookup_MainList.PNG

    Here is the Other list that I’m trying to pull the data into from the Main List (note the column name starts vLookup)
    http://dl.dropbox.com/u/24443247/sp2010_Vlookup_Other_List.PNG

    Here is the setting of the column in the Other list showing it’s a text list as required:
    http://dl.dropbox.com/u/24443247/sp2010_Vlookup_setup_Column.PNG

    And here is the config page:
    http://dl.dropbox.com/u/24443247/sp2010_Vlookup_Config_screen.PNG

    Does this help at all??

    I’m using jQurey 1.8.2

    1. Sorry, I was replying on my phone whilst in bed, hence the awful english.

      I tried to check for bugs but, unfortunately I’m not an experienced developer like yourself and didn’t get any errors that were obvious.

      I did get another chap to look at it who does have dev experience and he confirmed it looked I’d done it correct as per instructions. Would love to get this working.

      Thanks again,

      Phil

    2. Hi,
      I have forgotten to ask whether you get this error in a list view or in DispForm / EditForm?

      Do you get the same error if you set up this code in DispForm as you do in a list view?

      Which version of IE are you using? – and can you try with Chrome or Firefox?

      Is this an Office 365 site or a “normal” site?

      Alexander

    4. It’s in the DispForm.aspx and I’ve tried in both IE9 and Chrome and it’s the same result.

      The environment is an on premise sharepoint 2010, so standard really.I’m not sure what you mean by the list view though sorry.

      What would you like me to try next?

      Thanks Alexander!

      Phil

    5. Hi,
      Try v1.21 and see if this has fixed it.

      Regarding list view: You can add a CEWP with the same code as in DispForm below the list view web part to have the “vLookup magic” in the list view – provided you add the vLookup column to the view that is.

      Alexander

    6. Sorry Alexander, I’ve tried version 1.21 and unfortunately it’s still the same…. Is there anything else I can try???

      1. I believe the error is in line 1245. try alerting listID below that line – what do you get?

        Alexander

    8. Do you mean this line? listID = location.pathname.toLowerCase().replace(L_Menu_BaseUrl.toLowerCase(),””).replace(“/lists/”,””).split(“/”)[0];

    9. It tells me “other” which is the name of the target list not the source list. The source list is called MainList. The List I’m calling the DispList.aspx is on the other list, does that make sense?

      Thanks!

      1. Thank you for the beer – I have to pick this up tomorrow – its getting late.

        Alexander

  26. Alex,
    When you have more than one lookup column in the same list there is no way to use the GUI to edit each configuration– original creation is no problem but need something like vLookupSetup=fieldInternalName instead of =1 to be able to edit each vLookup column configuration.

    Workaround is to directly edit the configuration list but that is highly prone to human error and GUI is much easier.

    THanks

    1. Hi,
      The GUI is supposed to display all vLookup column configurations below each other. Are you sure you do not see them if you scroll down?

      Alexander

  27. Hi Alexander, I can’t get the field configuration screen to show. I’ve tried on the listview and dispform. The script successfully creates the list and then prompts to configure a field, but then the yellow configuation form doesn’t show.

    I also tried to access it manually by appending the URL with ?vLookupSetup=1 but it still won’t show.

    Any ideas why that might be happening?

  28. Hi,
    Have you looked at the video tutorial to ensure you have the correct setup?

    If so, hit F12 in IE and look for errors in the console.

    Alexander

    1. The only error showing in the console is:

      “[Your site] is running in Compatibility View because ‘Display intranet sites in Compatibility View’ is checked.”

      Could that cause a problem?

    2. I just tested this on two different SP2010 site collections, with IE8 and IE9, on a ListView and DispForm, and get the same result.

      The configuration list is successfully created, then I am prompted to set up the vLookup field, I click OK but nothing happens.

    3. Same result from Firefox 10.0.7, and no errors in the Firebug console. I’m stumped (which isn’t hard to do).

    4. Ok, the problem is a conflict with our internal branding. When I deactive the branding site feature it works fine. I’ll report back if I figure out the specific issue.

    5. Hi,
      I’m glad you figured it out. I guess you must look at the page source to see if the tags addressed by the script (like “MSO_ContentTable”) actually exists.

      Alexander

  29. Hello, has anyone had any success getting this to work within Office 365? I’ve gone through everything as you’ve laid out and I never seem to get the configuration prompt to appear. Not certain if I am doing something wrong or if it’s possibly something environmental.

  31. Apparently I am a horrible little spammer. I did some further digging and discovered that this will not execute on Office 365 sites that have been upgraded to SharePoint 2013 bits. The error thrown in the dev tools of IE is:

    SCRIPT5022: The property or field has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.
    sp.runtime.js?rev=9sKdsC9N6p2BiRk3313M7Q%3D%3D, line 2 character 19964

    I have not had the opportunity to test this in a 2013 on-premise installation, but I’m guessing that I may be able to reproduce it there.

    1. Hi,
      I’m glad you figured it out. Regarding SP2013, I have not had the chance to test it as I do not have access to a test site.

      Alexander

    2. Hi,
      Earl Libby had the same problem and we found out that the 2013 version of Office 365 has some bits missing from the list schema that breaks the compatibility with the 2010 code.

      I will update the code soon, but in the meantime, you may edit it on your own like this (code example provided by Earl Libby):

      try{
	dispFormURL = list.get_defaultDisplayFormUrl();
	newFormURL = list.get_defaultNewFormUrl();
	editFormURL = list.get_defaultEditFormUrl();
}catch(err){
	var baseType = list.get_baseType();
	if(baseType == 0){
		dispFormURL = dObj.listBaseUrl + '/Lists/' + dObj.listName + '/DispForm.aspx';
		newFormURL = dObj.listBaseUrl + '/Lists/' + dObj.listName + '/NewForm.aspx';
		editFormURL = dObj.listBaseUrl + '/Lists/' + dObj.listName + '/EditForm.aspx';
	}else{
		dispFormURL = dObj.listBaseUrl + '/' + dObj.listName + '/Forms/DispForm.aspx';
		newFormURL = dObj.listBaseUrl + '/' + dObj.listName + '/Forms/Upload.aspx';
		editFormURL = dObj.listBaseUrl + '/' + dObj.listName + '/Forms/EditForm.aspx';
	}
}

      Insert this code in line 1065 – replacing line 1065-1067.

      Alexander

  32. I have updated to v1.23 to fix a compatibility issue with Office 365 for SP2013.

    Alexander

  33. Alexander, just curious, how hard would it be to make the “Tab Color Override” option dynamic? Either by putting logic into the script, or by having it read from a column. The use case is that I would like the “Status” tab color to reflect the current status of the project.

    1. Oops, I put this comment on the wrong post. I meant this for the dffs script. I’ll move it over there.

  34. Hi Alexander,

    Hoping I can finally get a answer from you on one of my questions…

    I followed your second video exactly and set up a list to be able to add documents. It worked great until my testers filled the list with content (about 100 entries). Now it will not pre-fill the lookup in the document library with the list item ID. When I change the add new item setting from “ID” (like depicted in you video) to “Title” it works again.

    What is going on? Why did it sudden stop working when using ID? I need it to use the ID like your video shows to determine the lookup, not the Title.

    1. Hi Matt,
      I have tested this vLookup connection like you describe – with more than 100 items in the list, but I cannot reproduce this bug.

      Could there be other changes (scripts or solutions) interfering with your setup?

      Regarding your other questions, please remember that this “free lunch” I have been serving here since late 2008, is something I do on my spare time. I try to keep up with the questions, but they tend to pile up now and then.

      Alexander

    2. I will double check my settings, but from my knowledge nothing was changed on my part. I had this same issue in the past with another list. I think it might have been caused by having multiple items with the same Title in the lookup list? I ended up building a different solution to make it work the way I needed.

      I greatly aprpeciate your help, and love all your solutions! I completely understand what you are saying about limited spare time. I’ll be sure to donate again to buy you a beer.

    3. This is still an issue for me. 🙁 I double checked all settings, and they mirror exactly what you have in the video. Could it be because I have a 2nd list uses this same solution to fill another field in the same document library? Both will only populate their connected lookup field if I use Title, and not ID. Thanks for any insight you can provide.

  35. Hi and big thanks for the great solution! It has been extremely helpful.

    Here is one bug I came across (SharePoint 2010):
    1. Create Parent and Child lists and add the vLookup column;
    2. Create a Document Library for WebPart pages;
    3. Create a new WebPart page;
    4. Add a List View Webpart with the Parent list to the Web Part Page;
    5. Configure vLookup to display the “Add new item” link.

    The bug:
    The “Add new item” link in the vLookup column is wrong. It does not contain the the Parent List GUID, but the GUID of the WebPart page library where the current page is located. As a result, the lookup ID in the new item does not get prefilled when the NewForm is opened.

    I worked around this bug by creating another copy of the vLookup_sp2010.js that I use on the WebPart pages. In that file on line 1087 I replaced “_spPageContextInfo.pageListId” with the correct list GUID and the solution now works. This is ofcourse a dirty ad hoc solution and new copies of the script need to be created for every List that is used on a Web Part page.

    I hope I explained the solution clearly enough.

    Thanks again for the solution!

    1. Hi,
      Very good explanation. The issue should be fixed in v1.25 – could you test it out?

      Alexander

  36. jQuery 1.90 is not compatible with this solution. Thanks so much for sharing (more beers coming if this works!)

  37. The latest version v1.2.6 seems to have lost the ability to hide the fields when using the {} notation. Around line 837 the following lines were removed (and thereby removing the hide capability)
    if (s[2] === ‘true’) {
    $(vLookupFields[t]).hide();
    }

  38. Hi Alexander, couple questions for you:

    – The 2007 solution had a “count” action. Can you add that to the 2010 solution?
    – I am using your vLookup and Tabs together, and it works great. But when a user does an “Add New” from a tab in the parent list, and they save the new item in the child list, the page returns to the first tab. Is it possible to redirect them to the tab the Add New came from?

    Thanks for all you do!

    1. Your first request is added addressed in v1.3, but on your second I need some more details. Is it Dynamic Forms for SharePoint you are using, or one of the older tabs solutions?

      Alexander

  39. Ah, I thought of one more question right after submitting the others.

    – Would it be possible to add another condition on the query?
    – Or, better yet, can the script go against a specific ListView? That way I can pre-filter the list before doing the vLookup.

    Thanks again!

    1. Not sure what you are asking here, some more details or an example please.

      Alexander

    2. The query section on the configuration page allows you to specify two conditions. Can you add the ability to specify a third condition?

      Alternatively, if the vLookup was performed on a specific ListView instead of the whole List itself, I could use the View Settings to pre-filter items I don’t want to include in the vLookup results.

  40. Alexander, (or any informed user…) Wonderful, wonderful — but one question: Can you make additional examples of ViewFields SubString use? My specific issue is that I am retrieving a Date-only field, but it is displaying as Date and Time (Monday, January 14, 2013 12:00:00 AM). My preference would be to display Date only (January 14, 2013).

    Again, many, many thanks!

    2. An additional question: The use of “FileLeafRef” for a document is very effective — I’ve looked for a way to represent a Hyperlink field, and am using the “URLwMenu” value. URLwMenu displays as: “http://server/site/library/file.txt, HyperlinkText” (that is, the hyperlink URL value, a comma, and the hyperlink text). Is there a way to display the assembled hyperlink?

      Just can’t thank you enough (uh, I guess this is where I buy you a beer!)

    3. Hi,
      This has been addressed in v1.3. Read the change log in the top of this article for details.

      Have a nice weekend,
      Alexander

    4. Alexander, Tricky! You’ve solved both my problems! I’m loving the results, though just to hold up my end of the bargain, I would like to report back my observation on the parsing for Date fields – the format is indeed changing, but without the JavaScript Date Methods having any actual affect. so I started with “Monday, January 14, 2013 12:00:00 AM”, entering “getMonth”,”/”,”getDate”,”/”,”getFullYear” produces “Mon Jan 14 2013” (instead of the desired “1/14/2013”). Further, entering “toLocaleDateString” produces “toLocaleDateString” as the display text.

      As far as I’m concerned, I like the “Mon Jan 14 2013” result, and am fully satisfied with your modification (… to be perfectly honest, I’m actually blown-away by your recent approach, creating configuration lists on-the-fly, etc.). Really great, great work!

      R’grds – Ben.

    5. I haven’t tried it yet, but did you put the arguments inside square brackets? Like [“getMonth”,”/”,”getDate”,”/”,”getFullYear”]

    6. DCH – DOH! Quite right! The square brackets are required. Thank you for your intuition, and another thank you to Alexander!

      R’grds – Ben.

  41. Hi There,

    I’m having problem to add JS scripts on Html Form Web Part in new Sharepoint 2013 of Office 365!

  43. Alex,
    First thanks for the solution. I have watched the video and come pretty close to having it all work except the follwing. Once I have everything running and I have the config page and the Parent list reloads I get an error on the vLookupChild field. I have attached the url to a screen cap of the error which I put directly below the column name just to show that I do have that column. Any help would be greatly appreciated.

  44. And you will see some different names because I scratched it all and did it again with a different list name etc…

  45. Alex,
    In order to prepare our customers for Office/365 conversion to 2013, I have replaced their earlie1.3r version of vLookup (probably 1.3) with 1.4. Ran into a problem where I had to open and save each vLookup config document in order to have the filtering work. Without this change the children did not filter — all children items show up in each parent document.

  46. Alex,
    I have a critical issue with a customer whose Office/365 site has been upgraded to SharePoint 2013 with the 2013 look and feel.
    I am using the latest version of vlookup 1.3 and vlookups work but ?vLookupSetup=1 does not bring up the configuration dialog — nothing renders and there are no errors on console and no breaks running the debugger

  47. Hi Alex,

    Would it be possible to have the input(s) “id=vLookup_query_value1_*” allow JavaScript? I ask because I would to have the ability to filter the items based on a value in the query-string.

    Thanks,
    Colin

    1. Got what I was after:

      
	/* CCB - CHANGE OLD
	vArr = str.match(/\[currentItem:[^\]]*\]/g);
	*/
	/* CCB - CHANGE NEW - START */
	vArr = str.match(/\[currentItem:[^\]]*\]|\[queryString:[^\]]*\]/g);
	/* CCB - CHANGE NEW - END */




/* CCB - Change Old
			if(fin!==null){
				if(fin==='ID'){
					str = str.split(v).join(id);
				}else{
					if(split.length===3){
						finID[fin]=split[2];
					}
					viewFields.push(fin);
					str = str.split(v).join("~"+fin+"~");
				}				
			}
			*/
			/* CCB - CHANGE NEW - START */
			if(split[0]=='currentItem' && fin!==null){
				if(fin==='ID'){
					str = str.split(v).join(id);
				}else{
					if(split.length===3){
						finID[fin]=split[2];
					}
					viewFields.push(fin);
					str = str.split(v).join("~"+fin+"~");
				}				
			}
			else if(split[0]=='queryString' && fin!==null){
					fin=getUrlKeyValueProper('Assigned');
					str = str.split(v).join(fin);		
			}
			/* CCB - CHANGE NEW - END */





/* CCB - CHANGE OLD
		if(mBlobObj[fin].blob.match(/\[currentItem:[^\]]*\]/g)!==null){
			mBlobObj[fin].hasVariables = true;
		}
		/* CCB - CHANGE NEW - START */
		if(mBlobObj[fin].blob.match(/\[currentItem:[^\]]*\]|\[queryString:[^\]]*\]/g)!==null){
			mBlobObj[fin].hasVariables = true;
		}
		/* CCB - CHANGE NEW - END*/

      1. Code blocks didn’t appear to work, please ignore those.

        -Colin

      2. In all your free time, lol…If you end up updating this solution again it would be nice to see this feature added to the setup menu with the option to pick the queryString item name and set a default value if blank/null. Comes in handy for filtering children items in views of the parent.

      3. Made a modification that will change the build query’s item to “ID is not null” when the query string value is blank. This allows for all items (minus the other filter) to be returned when no query string item matching the settings is found. This modification will work with both row 1 and 2 of the build query.

        else if(split[0]==’queryString’ && fin!==null){
        fin=getUrlKeyValueProper(fin);
        if(fin)
        {
        str = str.split(v).join(fin);
        }
        else
        {
        curRow = str.split(v)[0].charAt(str.split(v)[0].length – 4);
        function allItems(itemName, curRow, newValue)
        {
        str = str.replace(‘”‘+itemName+curRow+'”:”‘+str.split(‘”‘+itemName+curRow+'”:’)[1].split(‘”‘)[1]+'”‘,'”‘+itemName+curRow+'”:”‘+newValue+'”‘);
        }

        var buildSetting = [[‘fin’,’ID’],[‘type’,’Text’],[‘value’,”],[‘operator’,’IsNotNull’]];
        for (var i in buildSetting)
        {
        allItems(buildSetting[i][0],curRow,buildSetting[i][1]);
        }
        }
        }

      4. Got some sleep in me and cleaned this up a bit:

        else if(split[0]==’queryString’ && fin!==null){
        fin=getUrlKeyValueProper(fin);
        if(fin)
        {
        str = str.split(v).join(fin);
        }
        else
        {
        dObj = $.parseJSON(str);
        curRow = v==dObj.query.value1?1:2;
        var optChange = [[‘fin’,’ID’],[‘type’,’Text’],[‘value’,”],[‘operator’,’IsNotNull’]];
        for (var i in optChange)
        {
        str = str.split(‘”‘+optChange[i][0]+curRow+'”:”‘+dObj.query[optChange[i][0]+curRow]+'”‘).join(‘”‘+optChange[i][0]+curRow+'”:”‘+optChange[i][1]+'”‘);
        }
        }
        }

    2. Hi,
      I think I understand, but could you email me some screenshots that explains how your modifications are user in the for?

      Alexander

  48. Alex,
    Rendering a Lookup field in vLookup is throwing an error (Value does not fall within the expected Range) using V1.4.5 in the SharePoint 2013 native environment. Yet this same vLookup with the same data works fine in the SharePoint 2010 environment. I can send you the configuration information but the only difference is that one of display fields is a lookup field — if I remove it from the rendered fields then it works.

    1. Doing a little more debugging works for other single an multi-valued lookup fields just not this particular field — very strange

  49. Alex

    First of all I like the idea of vLookup.
    But…the implementation is just straight forward and implemented in a way that it isn’t extendable.
    First thing you should change is the implementation of vLookup as a plugin. So there is no “magic” on loading the page, but a defined interface like:
    $(“#vLookup_myTag”).vLookup(options);

    Then you should separate data-retrieval from output. In my case I just wanted the collected listitem to be returned and passed to a treeview control. No chance!

    Also I needed to intercept the call to addItem. No chance to inject a delegate here…
    This could be done with the above mentioned options
    options = {
    beforeAddCildDelegate : myDelagate,
    output: “Raw” // “template”
    template: templateString // to bind listitems to
    }

    Well, I somehow worked around it. If you like I can send you my changes to your code. Maybe you find some time to adopt then.
    Falko

  50. Hello Alex,

    Yet another great solution!!

    I was able to get this setup and working quickly based on your instructions and videos. Very nicely done!!!

    I did run into a minor issue related to the >20 items lookup list issue. Similar to Matt’s post above, if I use the “Title” in the URL instead of the ID it works to auto-populate the parent lookup column on the new child item, but if I use ID it only works up to 20 items in the Parent list. This is in no way a deal breaker for me, but I thought I would provide the feedback.

    I am using SP2010, jquery 1.8.0 / vlookup 1.47 / spjs-utility 1.15.

    I’ve tried backing down the jquery and spjs-utility a version each, but I wasnt able to resolve the >20 items issue.

    Again, great work!!!

    -Brent

  51. Pingback: vLookup for SharePoint 2013 v1.5 « SharePoint JavaScripts

  52. Hi Alexander,

    Thank you for vLookup it’s great!!

    While implementing the solution, the script wouldn’t load the the setup screen when it was first configured. I had to access the setup screen by going to “…/Lists/MyList/AllItems.aspx?vLookupSetup=1”.

    I doubled and triple checked the script order.

    After accessing the setup screenI, I was able to get it to work. Thank you for the YouTube video as well 🙂

    Cheers,

    -David

  53. Hi Alex,
    I tried it. The solution looks great, however I was not successful – I don’t see the option to add new items and the child items are not displayed.
    I followed your video tutorial on this page.
    I realized that in the query you use parent = currentitem:ID, even though the lookup settings targets the “Title” field.
    Nevertheless, I tried both options, neither of them worked.
    Is there anything I am missing?
    Thank you,
    Marek

      1. Hi Alex,
        thank you for quick reply.

        Using F12, Script, Debug, there is an error:
        SCRIPT5022: Sys.ArgumentException: Value does not fall within the expected range.
        Parameter name: serverRelativeUrl
        sp.runtime.js, row 2 char 38258
        I also see that the ribbon icons are not highlighted although they should be. I think this may not be an issue connected to vLookup, so if it’s not, don’t spend any time with it. It’s a clean SharePoint web app anyway 😮

        I am enclosing the config screenshot.
        http://s21.postimg.org/715oori5j/List_Settings.png

        The script source is as follows (“Sestavy” is a library in root site, vLookup is a folder in it):

        I have the lists connected via single line of text as I wanted to make it work in editform as well.

        Thank you!

  54. Hi Alex,

    Great solution and love the work you do. I have been using vLookup successfully for some time now, but after upgrading to the latest versions of jQuery, spsjs-Utility and vLookup any new vLookup forms I add does not auto populate the child “MyParent” field. The dropdown on the Child New item form shows “(None)” and not the originating parent Title. My old setups work until I recreate them and then they never work again. Any ideas?

    Runs on a Microsoft Cloud Hosted instance of SP 2010.

    Includes are:
    jquery-1.10.2.min.js
    jquery.SPServices-2013.01.min.js
    spjs-utility.js (version 1.173)
    vLookup_sp2010-2013.js (Version 1.56)

    No errors showing in Debug.

    Config Blob:
    Title parent:vLookupChild
    blob {“listName”:”Child”,”listBaseUrl”:”/sites/GITPMO”,”query”:{“fin1″:”MyParent”,”type1″:”LookupId”,”value1″:”[currentItem:ID]”,”fin2″:””,”type2″:”Text”,”value2″:””,”fullCAML”:””,”AndOr”:””,”operator1″:”Eq”,”operator2″:”Eq”,”orderBy”:”ID”,”orderAscending”:false},”viewFields”:[{“fin”:”Title”,”settings”:””,”disp”:{“default”:”Title”}}],”action”:”concat”,”viewItemLink”:true,”editItemLink”:true,”hideLabelInForm”:true,”label”:{},”noItemsLabel”:{“default”:”No items found”},”addNew”:true,”addNewLabel”:{“default”:”Add new child”},”inUrl”:[{“from”:”ID”,”to”:”MyParent”,”hide”:false}]}

    1. Hi,
      Could it be that you have forgotten to refer the scripts in NewForm in the child list? If not, could it be interference with SPServices? – try to remove it temporarily to check.

      Alexander

    2. Hi Alex,

      My own stupidity amazes me some times. I missed the HTML form references on the child default new form… Doh! Now it works 100%. Sorry to bother and thanks again for the inspiration you provide.

  55. Hey Alex,

    I have a second field in the “In URL” from to section that I want to transfer to the new child item. The from field in the parent is list look up and the to field in the child list is a single line of text field. vLookup inserts the value “[object Object]” into the to field when a new child is created.

    I realize that the list lookup result is an object, but cannot figure out how to extract just the value and port that over the new child record. Any guidance?

    As always, your SharePoint stuff rocks……

      1. Hi Alexander,

        I upgraded both SPJS-Utility (version:1.175) and vLookup_sp2010-2013 (version:1.62) to your latest versions and still get the same “[object Object]” value inserted into the child field. Just for clarity, I have other choice and text fields on the parent that is successfully written to the matching child fields.

        Thanks for your help.

        K

      3. Hello,

        Sorry I could have explained better 🙁

        The field on my parent list that is not properly passing on to the child list is a regular lookup field. It points to my projects list and I use it to add a project name to my parent record. This field is not used in the vLookup query, I just want it’s value to be added to the child record. FYI: The project list does have 4 custom content types defined, so not sure if this may be an issue.

        So creating a new child record from the parent edit form works great. The only problem is that the value in the parent “Project” lookup field is not transferred to the child record. After the record is created, the project field in the child list (which is a “Single line of text” field) contains the “[object Object]” value”.

        All the other fields transfer just fine from the parent list to the child list.

        K

      5. Hi,
        I’m sorry, but I cannot reproduce this error. Can you provide some more details, and if possible a few screenshots?

        Maybe you can test using a lookup source without the custom content types to see if this has something to do with this behavior.

        Alexander

      6. Hi Alexander,

        I have a number of source lists that do not have custom content types and they all work perfectly.

        How do I get the screenshots and trace data to you? Do you have a public email address?

        Kobus

  56. Hi Alexander,

    Thanks for another great bit of functionality! I’m trying to think of a way to designate the content type in the parameters passed to the new form or a way of designating ctype in the URL somehow. Any suggestions? All my attempts to date have failed. I don’t want to use the default the content type for items added via the vlookup field.

    Thanks,
    Stephanie

  57. Hello I am using vlookup for SP 2010 v. 1.4. and my users experienced something strange. Some times when they backed from the browser, a message popped and just by pressing ok, they were transfered to the vlookup configuration, thus able to change things. Can I somehow prevent this? I was not able to reproduce this sadly, but that user called me and saw him in the config part myself.

    1. Hi,
      You should update to the latest version to fix this issue, and to benefit from all the latest enhancements. The current release is now 1.631.

      Alexander

  58. Hi,
    I am attempting to use the vLookup within the DFFS. I am on 2010 and have IE8. I do not have a customized Master Form and I have the most up to date code for all of the various parts.
    My CEWP for under the forms or lists it setup like this:
    DynamicFormsCSS
    jquery-1.10.2
    spjs-utility
    CascadingDropdowns
    vLookup
    DynamicFormsforSharepoint
    I followed the video instructions for installation using the exact same Parent Child lists. Installation on the list view of the Parent does not perform as expected. After installing the CEWP code under the list and the overlay over the list,the background of the list changes color as expected, but the link into the setup of the form is unavailable(the little box and Title link that should be in the bottom left corner). When I go to the next step of add new item to the Parent, the configuration setup for vlookup does not trigger. Manually entering ?vLookupSetup=1 does not open anything.
    Any ideas on what I am doing wrong?
    Thanks!
    Lana

    1. Are you sure you have all the script src links correct? – You can send me screenshots of your setup and I’ll take a look.

      You find my email in the “About me” tab.

      Alexander

  61. Hello Alexander,

    Thank you for this solution, it has made my life easier.

    I am having an issue opening attached files on the browser from a record. When I click on a pdf file, it loads but it opens behind the preview Window.

    Here is more detail about my setup.

    1. I am using DynamicFormsforSharePoint.js in combination with the vLookup_sp2010_2013.
    2. I have updated all the scripts to the latest available on your site, including spjs-utility.js
    3. I have SharePoint 2010 SP1
    4. Here is a screenshot:https://drive.google.com/file/d/0B7b4EY8WkMz8NG1DUl9OVWhNOW8/edit?usp=sharing

      1. The file opens normally.

        If I may also add, I have one form with DFFS solution and vLookup. Once I click on the preview for the vlookup, that form also has the DFFS solution with the attachment giving me trouble. It appears like the file is opening in preview mode.

        -David

      1. Thank you Alexander. I was able to fix the problem by including this line on the page: “$(“a[href$=’.pdf’]”).removeAttr(‘onclick’).attr(“target”,”_blank”);”

        Now it opens the PDF files on a new tab, getting rid of the problem.

        Here is a reference article: “http://social.msdn.microsoft.com/Forums/sharepoint/en-US/2ac5221a-868f-470a-8939-3c15e7978b1d/open-pdfs-documents-in-new-browser-window-sharepoint-2010?forum=sharepointcustomizationprevious”

        Cheers!!

        -David

  62. Hi Alex,

    This is a wonderful solution, thank you for the hard work you’ve put in. I have a question about populating child field that I didn’t see addressed in the article or comments (though I may just be missing it). Is it possible to populate a multi-lookup column in a child list? I’ve tried populating by ID and Title from the parent item without success.

    Thanks,
    Isaac

    1. Hi,
      Sorry, but I have not added support for this in the code. I’ll put this on the wish list for the next release, but you can add a new “case”:

      case 'SPFieldLookupMulti':
// code
break;

      to the function “setlookupValue” if you want to give it a go by writing your own code.

      Alexander

  63. Alexander, wondering if there is a way to create a rule in DFFS based on a vLookup result.

    I’m using vLookup in my dynamic form to return a multi-choice field. Based on what options were selected in the multi-choice field, I’d like to show or hide fields in my dynamic form. Is this possible?

    1. This is not possible without adding custom code like this to the Custom JS section of the Misc tab:

      function vLookupIsLoadedCallback(fin){
  if(fin==="YOUR_FIELDNAME"){
    var vLookupTable = $("#vLookup_"+fin+"_"+GetUrlKeyValue("ID"));
    // Do something
  }
}

      Alexander

Comments are closed.