200 thoughts on “vLookup for SharePoint 2013 v1.5”

1. Another awesome upgrade Alexander, you rock!

   I can’t seem to get any results returned when I include a Choice (Checkboxes allow multiple selections) type column in the ViewFields box. Am I doing something wrong or is that working as designed?

   (my question is not related to the upgrade… I never tried to do this in previous versions)

   1. Hi,
      Check out v1.53 and let me know if it fixes the problem.

      Alexander

2. Your solutions are great! I am having trouble getting the vLookup tab to integrate into DFFS. I found the SP 2007 version as that is the environment I am working in; but no luck. I was able to get the cascading dropdowns integrated into DFFS though.

   1. Hi,
      I’m sorry I have not mentioned that it is the SP2010/13 version of vLookup that can be integrated and not the SP2007 version. For SP2007 you must use the “old” setup.

      Alexander

      1. Bummer! Is there a link to the “old” version setup instructions? Will there even be any functionality of vLookup with DFFS with SP2007?

3. This isn’t specifically related to the update, but I am having issues getting the ‘In URL’ values to work correctly with people picker/user fields. I have the problem in both 2010 and 2013, though it presents differently. In 2010, I am working with a multi-select people picker field in both the parent and child list. When adding an item to the child, the new item form populates the people picker with broken [object#] references. In 2013, I have tried with multi-select and single value fields (and mixed between parent and child) and in all cases I get nothing at all populated in the people picker field on the form. Am I missing something special about these fields or are they not supported?

   Thanks again for the awesome solution!

   1. Okay, I have managed to partially resolve this.

      First, people picker fields are handled differently in 2010 versus 2013, so I’m not sure that one solution can work for both.

      Second, the value that is passed here is wrong – it is always ‘[object]’ and not the user name or ID. Passed from vLookup_sp2010-2013.js line 1028 (stored as ‘val’) to spjs-utility.js line 370 (stored as ‘newVal’).

      To get the 2013 people picker working, I made the following code changes in spjs-utility.js (starting line 369; original code retained as comments at bottom):

      case ‘SPFieldUser’:
      case ‘SPFieldUserMulti’:
      $(document).ready(function(){
      var FieldName, Picker, PickerID, PickerEditor, PickerObject, testVal;
      FieldName = “Field Name”
      testVal = “DOMAIN\\USERNAME”
      Picker = $(“div[title='” + FieldName + “‘]”);
      PickerID = Picker.attr(‘id’);
      PickerEditor = $(“input[title='” + FieldName + “‘]”);

      PickerEditor.val(testVal);
      PickerObject = SPClientPeoplePicker.SPClientPeoplePickerDict[PickerID];
      PickerObject.AddUnresolvedUserFromEditor(true);

      // if(browseris.ie5up && browseris.win32 && !IsAccessibilityFeatureEnabled()){
      // thisField.find(‘div.ms-inputuserfield’).html(newVal);
      // thisField.find(‘img:first’).click();
      // }else{
      // thisField.find(‘textarea:first’).val(newVal);
      // }
      });

      This works, but still has some issues.

      First, I am hard coding the name of the field rather than pulling it dynamically. I suspect this information can be gotten to using some method on the ‘thisField’ value, but I am not familiar enough with it to know.

      Second, I am hard coding the user name to get populated. Without this, I get the same broken [object] references as with SP2010.

      I will keep looking into the issue with the broken [object] references, but I would appreciate any insight you would have into how to best grab the name of the field from within the context of the setFieldValue function (rather than needing to pass the value in).

      Thanks again!

      1. Okay, I have resolved the issue with the broken [object] references. It has to do with multiple value people picker fields as the data source (note: all people picker fields seem to be treated as multiple value fields in SP2013 – even when the option is turned off). I have extended vLookup_sp2010-2013.js in order to handle multiple value people picker fields. Here is the new code block, starting at line 1019:

         if(typeof(val)===’object’){
         if(val.get_lookupValue!==undefined){ // User
         if($(spjs.vLookup.fields[spjs.vLookup.data.setFromUrlObj.toFields[i]]).attr(‘FieldType’).match(‘SPFieldUser’)!==null){
         val = getUserInfo_v2(val.get_lookupId()).Name; // Find loginName
         }else{
         val = val.get_lookupValue(); // DisplayName
         }
         setFieldValue(spjs.vLookup.data.setFromUrlObj.toFields[i],unescape(val),true);
         }else{
         var valTemp;
         for (var j = 0; j < val.length; j++){
         if($(spjs.vLookup.fields[spjs.vLookup.data.setFromUrlObj.toFields[i]]).attr('FieldType').match('SPFieldUser')!==null){
         valTemp = getUserInfo_v2(val[j].get_lookupId()).Name; // Find loginName
         }else{
         valTemp = val[j].get_lookupValue(); // DisplayName
         }
         setFieldValue(spjs.vLookup.data.setFromUrlObj.toFields[i],unescape(valTemp),true);
         }
         }
         }else{
         setFieldValue(spjs.vLookup.data.setFromUrlObj.toFields[i],unescape(val),true);
         }

         I could still use some help on getting the field name into the setFieldValue function in spjs-utility.js and, potentially, adding some code to dynamically select my edits (from 2nd post here) for 2013 or the original code for 2010.

         Thanks.

4. Sorry to keep reposting about this. I finally got it all working properly, including a dynamic version selection for SP version. The full, modified solution can be had here: https://www.dropbox.com/s/30kxcxfda5qk74r/vLookup-tsi.zip. Please feel free to reuse.

   Thanks for the excellent solution to start with!

5. Hi,
   Sorry for the late reply. I have updated to v1.55 and think this problem should be fixed. I have not changed anything in spjs-utility, only in the vLookup script.

   Could you test it and see if it works? – I have testes it in SP2010 only…

   Alexander

   1. Alexander,

      I was able to test this in 2013 this morning. It generates the proper string (i.e. i:0#.|DOMAIN\username), but fails to resolve the names. I tested both with your and my modified versions of spjp-utility. I think the issue is that the 2013 people picker needs to make the call to resolve the names one-at-a-time rather than on the concatenated string of usernames.

      I wasn’t able to test in 2010, but will do so shortly.

      1. In 2010 (both your version and mine), only the last user out of the multiples actually resolves and gets stored.

6. Sounds strange – it is working in my SP2010 setup. Are you sure your people picker is set up with multiple selection allowed?

   I have tried to address the problem in SP2013 in spjs-utility v1.172.

   Can you test it?

   Alexander

   1. I will test this out and report back for you.

      I also have an unrelated question: Is there any way to make this work with custom forms (i.e. ones using Data Form Web Part rather than List Form Web Part)?

      1. Sorry, but I cannot help you with custom forms. You can make it work, but it is impossible for me to make a generic solution for custom forms.
         Alexander

7. Sorry it took so long for me to test. This is properly resolving the names now, but it is still only populating the last of the multiple names. I only tested it in SP 2013 this time.

   I tested using the following:
   jQuery version 1.10.1 and 2.0.3
   Internet Explorer 10 and Firefox v22
   Your vLookup_sp2010-2013.js and my spjs-utility.js
   My vLookup_sp2010-2013.js and your spjs-utility.js

   Same results with all configurations.

   1. Hi,
      Maybe I misunderstood, but did you test with my version of both scripts?

      Have you tested that you can select the two persons manually in the list you try to prefill? I tested in SP2013 and IE 10 and it populated the people picker with 3 persons (actually 1 person and 2 groups, but still 3 values).

      Alexander

8. Sorry, I should have been more specific. I first tested with both of your scripts (vLookup v1.55 and utility v1.172) and then tested in the alternate configurations I listed. It’s only working correctly for me when using my versions of both scripts. The fields on both the parent and child allow multiple values and I can add the names in both places (after the script resolves only the last name, I can still manually add the other(s)).

   Not sure why we are getting different results.

   I did manage to get my custom form working by pasting the code from a default form (without vLookup active) for my desired field into a Content Editor Web Part. Worked quite well actually.

   I also have another unrelated question: when populating people fields from a child list onto the parent’s forms or view pages, is it possible to format the names with the links to profile pages intact (and, even better, with presence indicators)?

   1. Hi,
      I looked at the code you provided, and the only difference is that you iterate over the multiple values in the vLookup script and set one by one, and I pass a semicolonseparated string that is split into an array again in setFieldValue and iterated over to set the field value (for SP2013).

      I do not think your approach will work for SP2010 / 2007 where the value is inserted as a string and resolved by a simulated click on the resolve user button. If you set the values one by one the last one will be the only one left at the end.

      If you use your version of vLookup and my spjs-utility you will trigger this line:

      p.DeleteProcessedUser();

      which will remove already resolved users. Try wrapping it like this to prevent removing previous values if the picker supports multiple values:

      if(!p.AllowMultipleUsers){
      p.DeleteProcessedUser();
      }

      Any luck?

      Alexander

9. Alexander, crazy question for you. I have three lists: A, B, and C. List A has a lookup column to List B. And List B has a lookup column to List C. Using vLookup for SharePoint, can List C get data from List A based on the these lookup relationships?

   1. If you create all these connections using vLookup (creating children), you can bring A’s ID into a textfield in B (InURL), and when creating C from B you can bring this value to another text field in C. Using this ID you can pull in A-list values.

      Alexander

      1. Awesome idea, thanks!

      2. Your welcome, did you test it and make it work?

         Alexander

      3. No, I haven’t had time to try this yet, but its on my list 🙂

10. Hi again, I am having an issue displaying dates that I can’t figure out how to fix. I have a Calculated Column in one list that returns values as Date and Time. When I pull them into another list with vLookup, they are all one day earlier than the date in the other list. I don’t know if this was happening in prior versions or not.

    1. Does this occur on this calculated date only, or for normal date columns as well?

       What time is set in the date? – if it is near midnight, it might be due to a daylight saving time issue?

       Alexander

       1. Looks like it is happening for both calulated dates and normal date columns. I am only using the date, and not setting a time. Is there a default time used when you don’t set the time?

       2. Military time: 00:00:00 – which can result in the wrong day if it looses 1 hour due to the daylight saving time. Not sure how this should be fixed unless you can include the time and set it to 08:00 or something like that.

          Alexander

       3. I solved this by creating another column that adds one day to the date, and then using vLookup to pull that column instead, which subtracts a day and then its correct.

11. Great solution.

    Wondering if there is a way to do multiple vlookups to unique child lists from one parent?

    1. No problem.
       Alexander

       1. Cool – can you tell me how or point me to the instructions?

       2. Just add another field with a name starting with “vLookup” and you are good to go.

          Alexander

12. Alex,
    I am running into a strange problem with a Lookup field in the ViewFields — I have 3 other lookup fields that work just fine but when I add this particular lookup field to the ViewFields I get the following error when retrieving children

    Value does not fall within the expected Range.

    I am using Vlookup 1.5.6 and SPutilities 1.173.

    Thinking maybe the lookup list was corrupted I recreated it — same error.

    Tried to debug — put a breakpoint on every alert in vlookup in IE debugger — no break.

    My temporary workaround is to create a hidden field created by workflow with just the text from the lookup field — this of course works but has the distasteful side effect that since workflows run asynchronously (and whenever the feel like running) there is a delay with this “workflow created field” showing up in the vlookup on the parent (ugh).

    1. I am wondering if this issue is because there are two many lookup fields in the child list.. there are 14 lookup fields in the list and the one that does not work is the last one on the list — will do some more investigating on that issue

       1. Well that does seem to be the root cause, the first 12 lookups work, the 13th and 14th throw the above error. And those are the exact sequences of when the Lookup columns were defined in the view. SharePoint 2013 — I know there is a limit to how many lookup columns can be in a view but not sure how this relates to vlookup client code access to the list.

       2. So this is related to the 12 lookup column max allowed in a view

       3. So I fixed my problem by deleting some of the lookup columns and then adding them back in a different order so the lookup column I need in vLookup now works since it was created in the list before the 12th lookup column

13. Hi Earl,
    I have been away for the weekend and have not been able to answer before. I’m glad you figured it out!

    If you have access to the central administration you can increase this limit under Manage web applications – [name] – general settings – resource throttling.

    Alexander

14. Hi there, couple questions for you. Can you add an option to put a count or sum above or below the data returned on a “Show all records” action?

    Also, I have a scenario where I am displaying multiple columns from items in the child list, and the length of the content varies in each row in the parent, so the columns don’t line up in the view. Would you recommend using CSS to restyle the data? Can I reference the individual columns that are returned by the vLookup script?

    1. Hi,
       Use the red “special configuration” for the ViewFields to add CSS like this:

       {"css":"width:200px"}

       This will set the column width.

       Alexander

       1. I can’t seem to get the special configuration to work to resize columns. I’d like to make some wider and some narrower (with word wrap). It definitely works for text formatting like color, etc. but when I try to resize the column nothing happens.

          I tried several css properties including width, max-width, column-width but none had any effect on the column. I also applied them to several different column types including single line text and rich text.

          Am I doing something wrong?

       2. Is there a way to apply css to the whole table and not just individual columns? I’d also like to be able to prevent the table from go off screen to the right.

       3. Hi,
          I will fix this issue later tonight. There was a bug preventing the css from applying to other than single line of text columns.

          Alexander

15. I’ve added a second vLookup to my parent list and its working good except I cannot get the fields to set automatically from the create new item on the second vLookup. Has anyone has trouble with this or is there something different I need to do to get the fields to auto populate from the parent on the second vlookup?

    Thanks,
    Jay

16. Have you remembered to add the vLookup script to newform in the child list?
    Alexander

17. Alexander, I have a list with nine vLookup columns in the default view, and a growing number of rows. Currently it takes over 30 seconds for the page to give control back to the user. I have a couple questions regarding performance improvement that I would like your expert opinion on.

    1) Does the CAML query affect performance? Does a more complex query take more time? What if there are a number of ANDs making the query results more restricted?

    2) Would indexing columns in the target list improve the performance of this script?

    Just for the record, in most places where I use vLookup there are no issues, but this list has a lot of lookups to complete.

    Thanks in advance!

    1. Hi,
       Sorry for the late reply. The delay most likely have to do with processing all the queries, but I cannot see how changing the CAML will help. You can however set an itemlimit for the query to pull back less items. How many items does the query return combined?

       Alexander

       1. I don’t really want to limit the items. I’m basically recreating a pivot table with your script. I have about ten columns that are counting all the items in one list related to the value in the first column. Something like this, where the PM name field is the one I’m matching on, and the others are all vLookup count type columns.

          PM Green Red Yellow On Hold Complete Cancelled Total
          John 2 1 1 0 2 4 10
          Mary 1 0 0 1 1 0 3
          Peter
          Etc.

       2. Sorry, I tried to make it look like a table but its all compressed.

       3. I see,
          It all comes down to the share number of items the script has to download and process. It depends on the line capacity and the “processor speed” of the computer.

          How many items are “counted” all together to create this table?

          Alexander

       4. Today its only 175 items, but it will only increase over time. So if I have 10 vLookup columns, and 10 rows to match on, does that mean its doing 10*10*175 “transactions”?

       5. If I understand you correctly, you are returning the 175 items 10 times = 1750 items.

          As this is client side code, I do not see any way of reducing the load other than reducing the number of vLookup columns / transactions.

          Alexander

18. One more question… in an older version of the script, the results of the count action were presented as a hyperlink to a filtered view containing all the items in the count. Is that still possible? Thanks!

    1. I will see what I can do about this one.

       Alexander

19. Alexander, Thanks for your awesome solutions. I just bought you a beer! I was wondering if there is a trick or pre-req to get the vLookup and Cascading dropdowns tabs to show up in Dynamic Forms. I have downloaded all of the latest files and the below is what my content link file looks like. When I add it to the Edit form on a very small list I dont see either of those two tabs. On a larger list I see Cascading dropdowns but not vLookup. Wasn’t sure if there are any pre-reqs for both tabs to show up. Cheers!

    -Kasey

    1. Last try.. these are the files and the order but the syntax has been changed obviously…

       href=”/SiteAssets/DynamicForms/default.css”
       src=”/SiteAssets/DynamicForms/jquery-1.10.2.min.js”
       src=”/SiteAssets/DynamicForms/spjs-utility.js”
       src=”/SiteAssets/DynamicForms/vLookup.js”
       src=”/SiteAssets/DynamicForms/CascadingDropdowns.js”
       src=”/SiteAssets/DynamicForms/DynamicFormsForSharePoint_v3.js”

    2. Thank you for the beer!
       The only pre-req is for vLookup: the tab will not show until you have at least one field with a FieldInternalName starting with “vLookup”.

       For the cascading dropdowns tab to show you must have DFFS v3.11 or above.

       Hope this helps,
       Alexander

20. Hi Alexander, couple observations when doing vLookups on currency type columns. Earlier this year you added the prefix formatting, and the right alignment for number columns. Thanks for all that!

    I noticed today that the currency values always have a decimal in the vLookup results, even when I choose zero decimals in the source list. Also, negative values in the source list show as (-10,000) but in the vLookup results its -10,000.00.

    I don’t think I can adjust these using the custom css options, but please let me know if there is a way. Thanks!

    1. Hi,
       Click the question mark to the right of “ViewFields” to see the formatting options. You will find this example for number formatting:

       {"decimals":2,"decimalSeparator":","}
       

       Alexander

       1. Wow am I stupid, of course its there, thanks!

       2. No problem,
          Alexander

       3. Ok, I was able to use the formatting option you posted to successfully remove the decimal, but then I lost the comma. For example, it went from “$12,000.00” to “$12000”

       4. I see, I’ll fix it later tonight and publish an updated version.
          Alexander

    2. Hi,
       Take a look at v1.6. I think this should have fixed the problem.

       Alexander

       1. Hi, thanks so much… I didn’t see the new file posted on spjsfiles though?

       2. You are right – I actually forgot to upload it… Now you should find it there.

          Sorry about that,
          Alexander

       3. No problem! I just tried v1.60 and when I used the following my numbers disappear completely:

          {“prefix”:”$ “,”decimals”:0,”thousandsSeparator”:”,”}

       4. I should also mention that the prefix and decimals work fine on their own or in combination, but when I add the thousandsSeparator it breaks.

       5. Acutally, scratch what I just said. Prefix and thousandsSeparator work fine. Its the decimals part that doesn’t work now.

       6. Looks like I was a bit to fast there… I’ll do another pass and get back to you.

          Alexander

21. DCH,
    Please try v1.61 and see if this helps.

    Alexander

    1. Nailed it! Thanks so much!

22. Alexander,
    I have a customer who has a very strange problem. His vLookups all stopped working — actually the only part that is not working is the setting of the lookup value in the CHild document. I checked the URL being passed and it looked okay with all the parms being passed and if I check the hide box on the psssed value the lookup field is indeed hidden so that tells me the vLookup code in the child new item must be running.
    I ran the IE debugger and no console issues and no breakpoints with debug on. I even created another separate site with a simple vlookup and it works just fine. The site where none of the vlookups are working does have a custom master page and my best guess at this time is that its related. Our next step is to change back to OOTB master page to see if that resolves the issues but I thought I would see if you ever have run into an issue like this — or can point me to some way to debug it.

    Here is the backend of the URL that invokes the newform for task
    /Tasks/NewForm.aspx?parentListGuid={dd1f5660-1a3d-4464-bd80-2d91c2f561ad}&parentItemId=103&baseUrl=/iact&getFromParent=ID|Related_x0020_Case|false&Source=/iact/Lists/Customer%20Service1/DispForm.aspx?ID=103&IsDlg=1

    I don’t see any issues with the URL — do you?

    1. Well reverting back to the OOTB master page did not seem to resolve the issue either so I don’t know where to go from here.

       Here is the source snippet of the field that should be set by the above URL — and you can see SPMP – id 103 in the list but it is not being selected.

    2. Hi,
       Could this be related to the “over 20 items i a lookup column issue” – did it work when the lookup list contained < 20 items? Look at the function "setlookupValue" in the vLookup script - which version of the script are you using? Alexander

       1. I am using 1.47 — there never used to be a problem with earlier releases with more than 20 items in a lookup column. Is this a regression issue with your new restructure. I need to understand if I need to update are other customers that are using various levels of the code base. I have sent this customer v1.6 to test with.

       2. Well finally I have properly upgraded the customer site to 1.6.2 and it solved the issue — so 1.4.7 apparently does not correctly handle the over 20 case

23. Hi,
    In v1.55 I changed the function “setLookupValue” to support setting the lookup by text value and not only the ID as before.

    Other than that, I cannot recall fixing a “20 items issue”, but obviously 1.62 fixed something.

    Alexander

24. Hi Alexander,

    One of the sites I’m working with uses a custom masterpage which seems to conflict with this part of the script:

    if($(“#MSO_ContentTable”).length>0){ // 2007 and 2010
    $(“#MSO_ContentTable”).prepend(b.join(”));
    }else{ // 2013
    $(“#contentBox”).prepend(b.join(”));
    }

    The site is 2010 but there is no MSO_ContentTable in the source. I am able to resolve this issue by removing the if statement and forcing:

    $(“#MSO_ContentTable”).prepend(b.join(”));

    Any possibility of of updating the script to check for something other than MSO_ContentTable?

    1. **EDIT**

       The site is 2010 but there is no MSO_ContentTable in the source. I am able to resolve this issue by removing the if statement and forcing:

       $(“#onetIDListForm”).parent().prepend(b.join(”));

       1. I will include a check for this situation in the next release.

          Alexander

25. Hi Alexander,

    In the latest version, why is the resizeDlg commented out?

    //spjs.vLookup.resizeDlg();

    Thanks!
    Colin

    1. Cant remember… Will uncomment it in the next release.

       Alexander

26. Alexander, can you make the “Enable Add New Item” configuration form specific? In one of my lists I want the contributors to see it on the EditForm, but I don’t want visitors to see it on the DispForm.

    Also, one of the latest releases introduced a horizontal line separator between each row. Can that be made optional? I upgraded from 1.61 to 1.631 and saw the new line.

    Thanks!

    1. Actually, can you add custom CSS to vLookup like you have for DFFS?

    2. Forget that comment. You already added that in the ViewFields “Special Configuration” section.

27. Sorry, one other item I wanted to ask about. I noticed that if I use a tooltip from your DFFS solution on a vLookup column, and then I select the “Hide Form Label” option, the tooltip goes away too. Can that be changed so that the tooltip will still display? Thanks!

    1. Thanks for adding this to the update to DFFS!

28. The “In URL” option works great for auto-selecting lookup columns, but if I change my lookup to allow multiple values (multi-lookup) it doesn’t appear to set the value automatically anymore. Is this expected, or a configuration error on my part?

    1. This is unfortunately expected behavior as setting multi-choice lookup columns is not currently supported.
       Alexander

       1. Thanks for the quick reply Alexander! I see that in the code now, I was only trying the multi-lookup with a single value passed so the isArray check/multi-value not supported alert never popped up. I only need to select/set a single value (after that users will select additional values). This update to the setlookupValue switch statement did the trick for me:

          case ‘SPFieldLookupMulti’:
          thisField.find(‘select’).val(lId);
          thisField.find(‘select option[value=”‘+lId+'”]’).trigger(“dblclick”);
          break;

       2. I see, but unfortunately using a multi lookup column in that end of the query is not supported in this version. I might look into that for the next release.

          Alexander

    2. AdvaComp – were you able to keep using this solution?

       I have a unique situation where I have two lists (Projects and Documents) and the vLookup is working prefectly to see which “Documents” are associated to a “Project” (the user goes to the “Document” and selects the “Project(s)” associated in a Multiple Item Lookup – then when you look at the “Project” the vLookup shows the items).

       What I’m trying to do is show the related “Project(s)” on each “Document” without having to add a Multiple Item Lookup on the “Project” list. This way, my users simply need to do the Multiple Item Lookup on just the “Document” list and then both places will leverage the vLookup code to show the relations.

       When ever I try the second scenario above by using the query of “ID” Lookup (id) equals to [currentItem:NAMEOFLOOKUP], I get an error stating “Exception from HRESULT: )x80131904”. Since I’ve got SharePoint 2010, I also pull in the “ID” and “Title” of the “Project” into separate columns and when i try doing a “text” lookup for “Contains” or begins with, I do get a match on the first item in the Multiple Lookup, but the second, third, etc aren’t returned.

       Thanks in advance for any help you can give – I’d love to be able to leverage this on multiple items without needing to add the Lookup Columns to both lists.

       1. Hi,
          I’m not sure I get the problem here… You already have a multi lookup column in the documents list with link to all the associated projects don’t you?

          Alexander

       2. Right. My users to go the “Documents” list and use a Multi Lookup Column titled “Impacted Project” (Impacted_x0020_Project) to select the projects using the “Title” of the Project. On my “Project” list, I have a vLookup set up and it’s working perfectly (Build query shows: Impacted_x0020_Project, Lookup (id), is equal to, [currentItem:ID]).

          What I’m trying to do is show a vLookup on “Document” that lets me display the contents of the “Project” list using just the “Impacted_x0020_Project” field. I don’t have lookup field on the “Project” list to connect it to related document – I only wanted to have one “Lookup” column on the “Document” list , but show the vLookup on both lists.

          I hope that makes sense! Just trying to prevent having a Multi Lookup field on both lists.

       3. Hi,
          Why cant you just display the lookup column yo already have (“Impacted_x0020_Project”) in DispForm or list view of the Documents list? This will have links to all the projects this documents is attached to?

          Alexander

       4. That’s what I’m doing now, but I really liked the layout of your lookup to show the information in rows/columns rather than a semicolon separated list :). I totally understand if it’s not doable – just something extra I was trying to do.

          Thanks!

29. Alexander, question for you, I have columns that I need to apply date formats and custom styles to. Is that possible?

    1. I’m not sure, have you tested?
       Alexander

    2. Did a quick test and it should be no problem – here is an example of the configuration in the red input field under the ViewFields section:

       {"dateFormat":"MM/dd/yyyy","css":"font-size:20px;color:green;"}
       

       Alexander

       1. Thanks for checking into it. I see now what is happening that made me think it wasn’t working. I used the custom css option to set the width of the column, but it only applies if there is content in at least one row. Is there a way to set the column width for empty columns?

       2. This is fixed in the updated code (v1.644).

          Alexander

       3. You’re awesome

30. Hey Alexander,

    Would it be possible to get an option on whether to open the vLookup form in a dialog box or the page? with DFFS sometimes the page is just better looking than jamming the form into the dialog box. =)

    1. I’ll see what I can do in the next release.

       Alexander

31. Regression bug with discussion lists. From releases 1.63 to the most current when you add a vlookup to a discussion list the discussion list item get created correctly with the correct related column but no items show up on the parent item vlookup.

    So far I have identified that it works correctly in Release 1.3.1 and will keep different releases until I find the one that is breaks in.

    1. Okay I have narrowed down when the regression bug was introduced — it was in Release 1.56 — 1.55 works and 1.56 does not.

       1. Hi Earl,
          I’m sorry for the inconvenience. I will look into this during the weekend and let you know when it is fixed.
          Alexander

       2. And the only difference is the Scope=’Recursive’)
          qb.unshift(“”);

          And if I revert line 1393 in r166 to qb.unshift(“”); — it fixes the discussion list render in parent vlookup problem

    2. Hi,
       Could you send me some screenshots and more details on your setup so that I can recreate it?

       Alexander

       1. I emailed you screen shots of the config and the discussion lit

32. Alexander, I hope this makes sense, it was hard to describe in words. Let me know if you want a screen shot.

    When I use the Show All Records action, and I display multiple fields in a ListView, but I don’t specify any Display Names in vLookup config form, the table of my lookup items includes an empty header row. Also, when I do the same thing using the Shaded view style, every other result set shows the empty header row and a separator line. Can you change the script to only include the header row and separator line when there are Display Names to show?

    Thanks!

    1. Another formatting question for the vLookup solution: How difficult would it be to implement grouping in the vLookup “Show all records” results? Or maybe some other way to visually separate subsets of the results?

       1. I’ll look at it and see what I can do in the next release.

          Alexander

       2. Alexander, the grouping functionality in the new version (1.70) is awesome! I haven’t tried to break it yet, but my first attempt was perfect. Big thank you!

       3. I’m glad you liked it!

          Alexander

       4. I might have found one thing wrong with the new version. I will keep trying, but so far it seems like the css applied to individual columns is broken. The other special configurations seem to work fine, like percentage and date objects, but css is not.

          Also, just a note for the help icon next to the new GroupBy option, the css for this section is not formatted like the css in the ViewFields special configuration. The GroupBy css is plain old css like color:red; but the ViewFields css requires the {“css”:”color:red;”} format.

       5. This one isn’t working in either Firefox or IE10.

       6. Hi,
          I cannot see that there are any errors in the “Special configurations CSS”. Are you sure you do not have a typo somewhere in your CSS string?

          The reason for having different format for the “Special configurations” and the “GroupBy” is because the “Special configurations” allows for multiple different configurations besides CSS, and therefore must wrap it differently.

          Alexander

33. Hi Alex,
    Is it possible to add listing, when you fill Rowlimit in LookupSetup?

    1. I’ll see what I can do in the next release.

       Alexander

34. Hey Alex,

    I am trying to use the feature “Open links in new dialog box” I have unchecked the box to have it open in the current page but it seems to be ignoring the setting and continues to open in the dialog window. I am on the latest version of vslookup using it with DFFS (latest) and spjs-utilities (latest) on SP 2010 server.

    1. Forgot to mention Action = “Show all records” as per the instructions.

    2. Hey Alex,

       Ok, after some more testing. The view/edit forms do open in the same window as expected. But the new form still opens in a dialog. Can this be changed so it is consistant with the fuctionality of the display/edit forms?

       Thanks =)

       1. Hi,
          I will look into it in the next release.

          Alexander

       2. Hey Alex, any chance this is in the works? Having all the page work in the same consistant manner would greatly help with some formatting issue we are experiencing.

35. Alex,

    I have used vLookup a few times in conjunction with Dynamic Forms. I attempted the same today after downloading all your newest files. I cant seem to get the vLookup configuation list to trigger first time setup. Dynamic Forms and Cascading Dropdowns works fine. I have dffs added to my display form. I have a field with internal name of vLookup on my main list. I tried to go to dispform.aspx?id=1&vLookupSetup=1. I have an item with ID of 1. It just takes me to the dispform in edit mode but does not attempt to create he config list. Any ideas?

    jQuery Version: jQuery 1.11.0
    vLookup Version: 1.650
    DFFS Version: 3.334
    spjs-utilities Version: 1.181

    1. I figured out my own dumb mistake. In the URL I had id=1 instead of ID=1. As always, Alex, I love your solutions!!

36. Alex,
    I am having the L_Menu_BaseURL undefined issue with IE 11 on an On-Premise SharePoint 2013 site with the latest release R1.65. Works with Firefox and the same site deployed on Office/365 also works just fine.

    1. Hi,
       Which line (and script) do you get the error in? Is this a “native” SP 2013 list or is it upgraded from SP2010?

       Alexander

       1. Well this morning I discovered the problem is even more serious than I realized. Now none of my Office/365 sites work and they all throw the L_Menu_BaseURL undefined error. This appears to be an issue with the latest IE 11 update as this was all working a few days ago. The Update level is 11.0.5 KB2936068. When doing view source L_Menu_BaseURL can not be found and this is the line that throws the error.
          /* SP2013 */
          if(typeof _spPageContextInfo !== “undefined” && _spPageContextInfo.webUIVersion === 15){
          var L_Menu_BaseUrl = L_Menu_BaseUrl || _spPageContextInfo.webServerRelativeUrl !== “/” ? _spPageContextInfo.webServerRelativeUrl : “”;
          var L_Menu_LCID = L_Menu_LCID || _spPageContextInfo.currentLanguage.toString();
          }

          Good old Microsoft keeps making our job more difficult.

       2. I have found that if I replace your logic with the following it works for SP 2013 with IE 11 at latest patch level
          if(typeof _L_Menu_BaseUrl ==”undefined”){
          L_Menu_BaseUrl = _spPageContextInfo.webServerRelativeUrl !== “/” ? _spPageContextInfo.webServerRelativeUrl : “”;
          L_Menu_LCID = _spPageContextInfo.currentLanguage.toString();
          }
          /*

       3. So upon further investigation I have found that when Microsoft applied the KB2936068 patch it reset the compatibility mode settings on your IE browser. So the simple fix to this issue is to change the IE 11 browser settings back to compatibility mode.

          But also if you add some logic similar to mine to check for L_Menu_BaseURL being undefined which it seems to be in non compatibility mode the issue goes away. However I think there are some other base SharePoint issues unrelated to your tools running IE 11 in native mode.

       4. Hi,
          I’m glad you figured out a workaround. There is an easy fix for this by changing

          if(typeof _spPageContextInfo !== “undefined” && _spPageContextInfo.webUIVersion === 15){
          var L_Menu_BaseUrl = L_Menu_BaseUrl || _spPageContextInfo.webServerRelativeUrl !== “/” ? _spPageContextInfo.webServerRelativeUrl : “”;
          var L_Menu_LCID = L_Menu_LCID || _spPageContextInfo.currentLanguage.toString();
          }

          to

          if(typeof _spPageContextInfo !== “undefined” && _spPageContextInfo.webUIVersion === 15){
          var L_Menu_BaseUrl =  _spPageContextInfo.webServerRelativeUrl !== “/” ? _spPageContextInfo.webServerRelativeUrl : “”;
          var L_Menu_LCID =  _spPageContextInfo.currentLanguage.toString();
          }

          I’ll post an updated version during the weekend.

          Alexander

37. I love the new version! The ability to group is great! Only one thing “broke” for me, and it was actually a configuraion that AdvaComp made to the spjs-utility.js file for Multi Lookup fields and using the “create new item” option. For some reason, that modification no longer works when I installed the new vLookup file.

    Otherwise, great work! Thanks as always!

38. Alexander, is there any way to hide the empty cell and border when no Display Name is entered in the ViewFields section?

    1. This is fixed in v1.710 – can you confirm?

       Alexander

       1. Huge thank you for this one! It looks excellent!

          The only issue I found was on some ListView pages where there aren’t enough list columns to push the table content together. In this case the vlookup table columns spread out and didn’t line up on the page. I think the width property is in the header row, which is hidden, so I added some simple jQuery on the page and it looks perfect:

          $(“tr.vLookupTableRow td.childTable:first-child”).prop(“width”,”20px”);

       2. Alexander, I wrote too soon. My code worked in Firebug, but not on the page. I think it needs to be integrated into the vLookup script, but since its minified now I couldn’t figure out how to get it in there.

       3. I’ll look into it during the weekend. If you want to fix it now you can run the code through this tool to make it readable: http://jsbeautifier.org
          Alexander

       4. Awesome, thanks!

       5. My little line of jQuery added to the dffs Custom JS section works in Firefox but not IE 10

       6. v1.172 is awesome! Thanks you for the formatting fixes. The width issue on single column vlookups is resolved for me. Also, the icon on grouped results is awesome.

39. bump…
    This would be a nice add allowing me to show summed up lookups stacked to look like they are adding things up.

    exp :
    income Q1 Q2…
    $25 $50
    Expense $20 $40
    Net $5 $10

    1. Sorry, but the only way you can have this layout is if you have three list items and use the title field to store “Income”, “Expence” and “Net” and them have a column for each “Q” to store the values.

       Alexander

40. Hi Alexander!
    1. I think that it will be greate if you add filter for lookup items. For example: then user open list item, he see only whose lookup elements which Assigned to him. Admin group will see all lookup elements.
    2. Is it possible to publish 2 versions of script? min and for developers? like jquery..

    1. Hi,
       1: I’ll see if I can think of a way to add such filters.
       2: Run the minified version trough this page: http://jsbeautifier.org to get the readable version.

       Alexander

41. Little improvement for rendering the correct DocIcons for PDF-Files. vLookup expects the DocIcons in the /_layouts/Images/-Folder as GIF-Format.
    —————-
    src='”+L_Menu_BaseUrl+”/_layouts/images/ic”+fieldVal+”.gif’>”;
    —————
    But there is nothing for PDF-Format (no icpdf.gif is available). Instead SP2013 uses the new 15-hive in /_layouts/15/Images and here are only PNG-Formats. Perhaps it is nicer for SP2013 to use the new Icons in PNG-Format:
    ———————-
    src='”+L_Menu_BaseUrl+”/_layouts/15/images/ic”+fieldVal+”.png’>”;
    ———————–
    There is also an Icon icpdf.png für PDF-Formats.

    1. This was added in v1.720.

       Alexander

42. Hi, another awesome improvement if you would consider it: when using the “Show All Records” action, and the “View Item Link” or “Edit Item Link” options, if the items come from a list using dffs can you make the link go to the tab the field is on?

    1. Hi,
       Which field is it you want to go to in the DFFS form – is it the one you find the item by in the CAML query?

       Alexander

       1. Not necessarily… many times I use fields for the lookup that I don’t display in the vlookup table. Could it be specified in the configuration? Maybe in the ViewFields section? If you don’t want to store the tab location of all the fields, I wouldn’t mind manually entering a tab index next to the field.

43. FYI – if you configure vlookup on list view – it makes the SP List ribbon disappear

    1. The ribbon is not visible until the table is selected. For some reason with the CEWP or HTML code the table is not selected by default. Is there a way to change this?

    2. This is a “feature” of SP 2010 – look here for a way to fix it: https://spjsblog.com/2012/01/12/bring-back-the-missing-list-tools-menu-in-list-view-with-multiple-webparts/

       Alexander

44. Hello, vLookup-Columns not yet rendered in SP2013 ListViews with Paging and Search-Filter, because there gets an REST-call by inplview.aspx without Page refresh. How can i achieve an Page Refresh in order to get the vLookup-Rendering also in the inplview.aspx for Paging and Search-this-List-Filter

    1. It seems, that in the inplview of Listview-Paging and Listview-Searchbox, like this
       http://server/site/Lists/Activity/AllItems.aspx#InplviewHash6afd55c5-52af-44db-a156-de2e14f59d4e=CascDelWarnMessage%3D1-WebPartID%3D%7B6AFD55C5–52AF–44DB–A156–DE2E14F59D4E%7D
       the vLookup-Columns are not rendered. Is there somebody else with this Issue? In short: Paging and Listbox-Search with vLookup not working yet? Has anybody the same issue?

       1. Hi,
          I will look into it as soon as I can manage.

          Alexander

       2. Hi,
          Add this to the vLookup list view code (your CEWP or HTML Form Web Part):

          <script type="text/javascript">
          var vLookupListViewTracker = "";
          setInterval(function(){
          	if(location.hash.indexOf("InplviewHash") > -1 && location.hash !== vLookupListViewTracker){
          		vLookupListViewTracker = location.hash;
          		spjs.vLookup.init();
          	}
          },1000);
          </script>

          Please let me know if it helps,

          Alexander

       3. Hello,
          with this fix the behavior of the inplaceview is much better now. it works with a single listview-paging Action or a single list-box-search Action. But the fix gets broken, when i use the inplaceview twice without pagerefresh
          best regards SchneidAIR

45. Hi Alexander, I’m not sure what the problem was, but today I noticed that some vlookup fields were no longer working. I tried to figure out what was wrong, but ultimately had to revert back to v1.71 to get the queries to work. There are definitely matches, but the script versions since 1.720 display my “no items found label”.

    1. It’s weird… I have some vlookup fields on the page that work, and some that don’t, and they appear to be set up the same way. They all work in v1.71 but some break in v1.72 and forward. I am using the “show all records” action, I am using the query builder and have two conditions. Is there any troubleshooting information I can provide?

    2. Anyone else notice this? Perhaps its just something with my configuration.

    3. When I revert back to the older version I lose all the new formatting fixes that were added to the latest couple.

       1. Sorry for the late reply. Are there any errors in the developer console? Is there any “logic” as to which one is failing? – does for example the position in the form do any difference?

          Alexander

46. No problem at all, you are super busy!

    It works fine in the display form. It only breaks in a list view, and only some break. I don’t see any connection between the ones that break and the ones that don’t.

    I do get this error in the developer console:

    SCRIPT5007: Unable to set property ‘0’ of undefined or null reference

    1. Which script and which line does the error refer to?

47. If I am reading it correctly, its line 115 right about here:

    break;case”number”:d[i+2].html[0]=””;

    1. Hi,
       Please run the vLookup script trough this tool: http://jsbeautifier.org to “unpack” it before testing again as troubleshooting the minified code is hard to do.

       Send me the error and the line reference when testing with the unpacked code.

       Alexadnder

       1. Ok, done. Here’s what came up:

          Line 1838
          Unable to set property ’0′ of undefined or null reference

       2. Can you email me the vLookup setup screen?

          Alexander

       3. Yes, just sent. Thanks again for all your help!

48. Alex,
    We have an Office/365 customer whose vlookups were working and now all of sudden they stopped working. When opening the item you get the No configuration found message and there is a configuration item (cases:vlookupDoc) if you proceed and create the config document it creates an item with title of :vlookupDoc (no list name). And when item is reopened you get the same error. It appears that however you are getting the list name stopped working.

    I am suspecting an Microsoft update of Office/365 is causing this error. Any insight to what may be causing this problem???

    1. Some additional information and I have a temporary patch that fixes the problem. Apparently I was wrong and this particular site has never worked and its because the top level site was generated with a space in the name as in ..sites/Top Site/cs and so this line of code does not work
       uSplit = location.pathname.toLowerCase().replace(L_Menu_BaseUrl.toLowerCase(),””).replace(/\/lists|\/forms/,””,””).split(“/”);
       because location has the %20 and L_Menu_BaseUrl has a space, but with this rough quick fix it does:
       var tempecl = L_Menu_BaseUrl.replace(” “, “%20″);
       uSplit = location.pathname.toLowerCase().replace(tempecl.toLowerCase(),””).replace(/\/lists|\/forms/,””,””).split(“/”);

       Most people try to avoid spaces in URLs particularly for sites and subsites so it probably a problem not encountered often.

       1. Thanks for the clarification. I sat down to look at this right now.
          I will add a fix in the next release. Have a nice vacation.

          Alexander

       2. Hi,
          You can try changing this

          location.pathname.toLowerCase()....

          to

          unescape(location.pathname).toLowerCase()...

          Alexander

       3. In debugging it further on entrance to vlookup, L_Menu_BaseUrl has the %20 but when it is changed to _spPageContextInfo.webServerRelativeUrl it reverts back to spaces.

          Right unescape should work.

49. Great work.
    One question. I may have missed it. I am using the latest version of vLookup with the latest DFFS. I have everything working except vLoopup in the edit form. Lookups work fine in the dispform. when I try to access the vLookup menu from DFFS in the editform it tells me that it needs vLookup which I have since dispform and listview works fine. My question – does vLookup work in editform or supported? I have tried it with 2 forms, just need conformation or a step in the right direction.
    Thank you.

50. More info.
    The VLookup message in vLookup tab is as follows “To start using vLookup you must add at least one columns with a FieldInternalName starting with “vLookup”.”

    From what I can tell it is correct in both instances.

    1. Hi,
       I suppose you have used a calculated column, and this does not render in EditForm. To be able to use vLookup in EditForm you must use a single line of text column as “vLookup” column.

       Alexander

       1. Thanks. Yes it is setup as a calculated column using =””.
          The name of it is vLookupTherapy.
          It works fine from the listview and the dispform.
          I can see it in the dispform setup screen under tabs in the add field list.
          I do not see it in the editform setup screen anywhere except for in the “misc” tab in the Initially hidden fields area. It is unchecked.
          If I remove the script references it does not show either and I do not know if this is how it is supposed to work in SP2013.
          In the listview and viewform it works as it should. If I click new child it open the screen as expected.

          
          My column settings:
          vLookupTherapy Calculated (calculation based on other columns)
          The type of information in this column is: Calculated (calculation based on other columns)
          Formula: =””
          The data type returned from this formula is: Single line of text
          

       2. Hi,
          There is obviously a bug causing the calculated columns to show in the “initially hidden fields” for NewForm and EditForm as calculated columns only show in DispForm (and list views). I will fix this in the next release.

          To make vLookup work in EditForm you must delete the existing calculated column “vLookupTherapy” and recreate it with the same name as a single line of text column.

          No existing “connections” will get lost as the column is used as a placeholder only.

          Alexander

51. No luck. I dropped and replaced the field. The results are the same.
    I can wait for the next release as the users have the other two options.

    Thank you.

    1. Hi,
       The bug I mentioned has nothing to do with vLookup directly – it’s a bug in dffs. You should be able to use vLookup in EditForm if you have the vLookup column as single line of text.

       When you have this set up and open the dffs edit gui – do you not see the vLookup section, or do you get the message “To start using vLookup you must add at least one columns with a FieldInternalName starting with vLookup”?

       Alexander

       1. This is what I see.
          Edit form:
          Dynamic Forms for SharePoint v3.352 / 1.183
          – by Alexander Bautz / SharePoint JavaScripts
          Rules Tabs Field tooltip Misc vLookup Cascading dropdowns Resource Management
          [vLookup plugin v1.722]
          To start using vLookup you must add at least one columns with a FieldInternalName starting with “vLookup”.

          DispForm:
          Click to enter vLookup setup

          NewForm:
          The vLookup plugin is not loaded – get it here

          IE -> F12 -> Field lookup
          

          Script used:

          Happens in Fox and IE10/11.

       2. It looks like you are still using a calculated column. To use the solution in editform you must use a single line of text column as “vLookup” column.
          Alexander

       3. Ahh. I get it. Works now. Thank you. Now for the live version…

52. Hi , how can i update the Look up field in sharepoint list . i m trying to update by using set_item(fieldname,FieldValue). But it is not working. Can you help me on this ….

    1. You can use the function setFieldValue from spjs-utility.js.

       Alexander

53. Hi Alexander,

    I am using your vLookup to look up documents in a document library. I have a question, is there a way to have the LinkName field (link to open the document) display the same in the DispForm so that the user does not have to open the document display form and click on the name field to open the document?

    Basically i would like for them to open the document by clicking on the Name field in the table view that the vLookup displays.

    Thank you.

    1. Hi,
       Sorry for the delay. Use the field name “FileLeafRef” to get this link. You can also use “DocIcon” in a separate column to have the icon in front of the file.

       Alexander

54. Alex,
    When changes are made to the parent document in edit mode and then a new vlookup document is added the changes to the parent document are lost. I expect this is working as designed and it may be impossible to force a save when the clicking on any vlookup actions and we just need to document it better for our customers.
    Thoughts?

    1. Hi Earl,
       I’m not sure I understand exactly what you mean, can you email me some more details?

       Alexander

       1. Sure – if you open a parent item in edit mode, make some changes to field values, then press Add new item in a vlookup without first saving the parent. Then after creating and saving the child item, the parent item is rerendered but the changes made prior to adding new child item are not retained … the Add new item does not force a save of the parent item.

       2. This is the drawback by having the vLookup column in EditForm… I try using them in DispForm whenever possible.

          Unfortunately I do not have a built in “force save” function, but you can use this function in EditForm to tell the user what is going to happen:

          function vlookupAddChildPreCheck(fin){
          	if(fin==="vLookup_Your_FieldInternalName"){
          		if(!confirm("Any unsaved changed to this form will be lost. Are you sure you want to proceed?")){
          			return false;
          		}
          	}
          	return true;
          }

          Alexander

55. Hi Alexander,

    I am using your very cool vLookup tool in my SharePoint 2013 environment. It is looking up a date field, but we are finding that if someone in USA adds a date over there it calculates the date back to UK time (where the servers are based). Is there a way to make it time zone aware at all please?

    Thanks,
    Adam

    1. Hi,
       I’ll keep this in mind for the next release and see if I can fix it.

       Alexander

56. So this is working great. Because the SPID is not created until the record of the parent is saved, I assume I cannot create child records when making a new parent item. So I have this case wher I need to enter multiple child items related to a parent, is there a way to have something that would basically be a button or something that would “Save and add new child item” vs hitting the save button, finding the parent record in a long list and clicking create new child each time? Basically, what is the easiest way to create multiple child records?

    Thanks

    1. Hi,
       Sorry for the late reply.

       You find a possible solution here: https://spjsblog.com/forums/topic/save-and-add-new-child-directly/

       Alexander

Comments are closed.