Display information from another list based on a lookup column connection: Updated version

Change log
November 01. 2013
v1.45: Changed the DispForm code to fix an issue with picking up the ID of the selected item. Thanks to Dmitry.

October 29. 2013
v1.44: Fixed a bug where the FieldInternalName and not the DisplayName was used as formlabel.

October 26. 2013
v1.43: Fixed a bug (data is not pulled in) in EditForm. This bug is affecting all browsers, but for IE it kicks in only when the lookup columns contains less than 20 items.

October 17. 2013
v1.42: Changed how the id of the value fields pulled back from the lookup column is generated. This to ensure unique IDs when using this feature on multiple fields. Now the ID is constructed like this:

[FieldInternalName of the Lookup column]_[FieldInternalName of the field pulled in]

Look at the bottom of the article for details.

October 17. 2013
v1.41: Added compatibility with DFFS. To achieve this, I have appended the table to the formbody of the lookup column. This to ensure the information is hidden with the field itself when changing tabs or hiding fields by rules.

This is an update of a previously posted article.

This solution lets you pull in additional information from another list based on a lookup column. It works much in the same way as the SP2010 / 2013 lookup column setting “Add a column to show each of these additional fields” found in the list settings > Change column. The difference is that this one works in NewForm and EditForm as well, where the built-in SharePoint feature only works in DispForm.
This image shows NewForm
IMG
All the highlighted fields have been pulled in based on the lookup column. Please note that these fields are not stored in the current item, but are shown when viewing the form in NewForm, DispForm or EditForm.

I have updated the code to use spjs-utility.js and thus support newer versions of jQuery and browsers other than IE. The only new feature I have added, is support for displaying the information in DispForm.

I have NOT changed the function call to ensure backwards compatibility with the older solution.

This solution is in fact only tested in SP 2007, but should work for SP2010/2013 as well. Please post any findings below.

Use this code in a CEWP below NewForm, DispForm and EditForm:

<script type="text/javascript" src="/test/English/Lists/PullInfoFromLookup/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="/test/English/Lists/PullInfoFromLookup/spjs-utility.js"></script>
<script type="text/javascript" src="/test/English/Lists/PullInfoFromLookup/PullInformationFromConnectedList.js"></script>
<script type="text/javascript">
var fields = init_fields_v2();

/* Object containing all arguments for the function
 * "arrFinAndDispName" is an array on format "FieldInternalName|Display name" from the list the lookup is pulling information from
 * "ListGuid" is the list Guid or the displayName of the list the lookup is pulling information from
 * "LookupFIN" is the FieldInternalName of the lookup column.
*/
var argumentObj = {'arrFinAndDispName':['TextField1|My text field 1',
						   'TextField2|My text field 2',
						   'TextField3|My text field 3',
						   'Responsible|Responsible',
						   'Hyperlink|Hyperlink',
						   'RichText|Rich text multi line'],
						   'ListGuid':'405EC50E-FAF7-4473-8D50-F9E725BEAA9B',
						   'LookupFIN':'MyLookupColumn'};

init_displayInfo(argumentObj);
</script>

Download the code
The code for the file “PullInformationFromConnectedList.js” can be found here, spjs-utility.js is found here, and jQuery here.

How to access the values after they have been pulled in to the table
Each of the TDs with the values have been assigned an ID like this:

MyLookupColumn_TextField1

The green part is the FieldInternalName of the lookup column, and the red part is the FieldInternalName of the field that the info is pulled from.

To get the value from this field, you use a standard jQuery selector like this

var myFieldVal = $("#MyLookupColumn_TextField1").html();
alert(myFieldVal);

Ask if anything is unclear,
Alexander

77 Comments on “Display information from another list based on a lookup column connection: Updated version”

  2. Hi Alex,

    another great solution!

    Am I right in thinking that once you Select a lookup on the newform.aspx in “List A” which looks up say a title from “List B”
    Your script populates the associated fields from “List B” in the form.
    Clicking OK it then saves it as say “Item X”

    BUT if you update one of the associated fields in “List B”
    Will the fields in Item X be updated automatically?

    (I am guessing not)

    1. Hi,
      The default behavior is not to write the values to a filed in the current form, but to dynamically show it when the form is loaded. If you prefer to add the code for storing the valued in this form, then your assumption is correct.

      Alexander

  3. Hey Alexander,

    Hard as I may try I cannot get the data to show up on my edit form.

    On my edit form I am using a simple list look-up to select a value that is stored in the Title field in my look-up list. My look-up list also has a multiple line rich text box field. Into the this list I load test case titles and text.

    What I am trying to do is select the test case titles using the drop down and then show the corresponding test case text using your solution. Just cannot get the text to show up.

    Did a little debugging and found that when I reach

    }else if(location.href.toLowerCase().match(“editform”) !== null){
    inpHidID = $(fields[argObj[‘LookupFIN’]]).find(‘input’).attr(‘optHid’);
    selID = $(“*[id='”+inpHidID+”‘]”).val();
    }

    Both inpHidID and selID remains “undefined” after executing the two lines of code. Both the fields and argObj objects are populated. Looking through the fields object and the html source I cannot find any input element that that has an attribute of ‘optHid’

    I am running this on SharePoint 2010 privately hosted by Microsoft (Not Office 365) with your latest versions of SPJS-Utility and jquery-1.10.2.min.js.

    Without being able to look at what the optHid attribute contains normally it is beyond me to interpret your code.

    Do you have any ideas what I can look at next? Happy to send you page source.

    K

      1. Hi Alexander,

        It works great in ie 10 and Chrome 31. Thanks so much for spending all the time to help.

        Kobus

  4. Hi again Alexander,

    One enhancement recommendation:

    I use custom names for my edit forms as I build different edit forms for different data views. You hard coded the DispForm.aspx and EditForm.aspx names in your logic on lines 19 and 28. Not a big deal as I updated them in your source, but it would be very nice if you can include an override in “var argumentObj” to pass names if they are different than the defaults.

    K

  5. Hello, Alexander.
    When I am try to get information from a list, I have no results.
    Debugging I found folowing:

    In the display form link from lookup field is like
    http://anysite/_layouts/15/listform.aspx?PageType=4&ListId=4e3e0229-0067-4daa-a97b-807d2a451f9c&ID=1&RootFolder=*

    The split function from the row
    selID = ts.split(“&id=”)[1].substring(0).split(“&”)[0];
    returns 4e3e0229-0067-4daa-a97b-807d2a451f9c instead ID=1
    I suggest to replace this row by
    selID = ts.split(/[&?]id=/)[1].substring(0).split(“&”)[0];

  6. Hi Alexander,

    I have a requirement to Display approvers names in the Editform.aspx depending on a selected category. To achieve this I have set-up a second list with 2 columns, 1 column is for the Category and the second Column displays the approvers for that category.

    On the 1st list I have a lookup column for the Category column within this list but Out of the box sharepoint won’t let me display the approvers in the EditForm.

    In the editform.aspx i have added a Content editor and added the below script. I do not get any results. Any thoughts?

    var argumentObj = {‘arrFinAndDispName’:[‘Title|Title’, —–This is the Category column in the Category / Approvers list
    ‘Approvers|Approvers’], —-This is the approvers column in the Category / Approvers list
    ‘ListGuid’:’C554A5F5-1413-451C-98E2-BE26CCAC775B’, GUID of the Categoery / Approvers List
    ‘LookupFIN’:’Approvers’}; Name of the lookup in the original list

    ​​

    ​​​​
    // Init all fields
    fields = init_fields();
    /* Object containing all arguments for the function
    * “arrFinAndDispName” is an array on format “FieldInternalName|Display name” from the list the lookup is pulling information from
    * “ListGuid” is the list Guid or the displayName of the list the lookup is pulling information from
    * “LookupFIN” id the FieldInternalName of the lookup column.
    */
    var argumentObj = {‘arrFinAndDispName’:[‘Title|Title’,
    ‘Approvers|Approvers’],
    ‘ListGuid’:’C554A5F5-1413-451C-98E2-BE26CCAC775B’,
    ‘LookupFIN’:’Approvers’};

    init_displayInfo(argumentObj);

  7. I see that there is a new version 1.50 that states in the release notes file that you need use this with DFFS you need to place the code in the custom JS section of the DFFS config.

    Which part of the code needs to be added in there? Is it all of the CEWP content from above except for calling the .js and .css files that are already referenced on the page?

    Thanks!
    Jim

    1. Hi,
      You must add a reference to the “PullInformationFromConnectedList.js” file in the CEWP where you have the DFFS script, and then add the function call to the Custom JS section:

      var argumentObj = {'arrFinAndDispName':['TextField1|My text field 1',
						   'TextField2|My text field 2',
						   'TextField3|My text field 3',
						   'Responsible|Responsible',
						   'Hyperlink|Hyperlink',
						   'RichText|Rich text multi line'],
						   'ListGuid':'405EC50E-FAF7-4473-8D50-F9E725BEAA9B',
						   'LookupFIN':'MyLookupColumn'};

init_displayInfo(argumentObj);

      Alexander

  8. thank you for the great post.

    The only issue I am running into is the “Number” column displays like this:
    Unit Price 20.6400000000000
    the “Unit Price” column is a “Number of 2 decimal places”.
    How can I get this fixed PLEASE.

    1. Hi,
      The numbers are stored in the DB withe all these decimals, and the query used to get the values does not “know” how many decimals to show. Either you can add a calculated column to the list you are pulling the information from that presents this number as a string, or you can change the function “getListItemFromLookup” something like this:

      //Change this:
val = item[fin];

//to this:

val = item[fin];
if(!isNaN(parseFloat(val))){
	val = parseFloat(val);
}

      Alexander

  9. I have a slightly different use case but I think I might be able to get this to work.

    I have 2 lists.
    – The 1st list is a Request Form that is filled about by users
    – The 2nd list contains over 50,000 items that I would like to be able to display on the 1st list in some cases.

    I can use DFFS to show/hide the regular fields that I want to see in the 1st list. For that new type of request I would like to have a textbox instead of a lookup to pull in information to display from that 2nd list.

    Example: User enters in ABC123 it checks list 2 for that value. If that value appears in the Title column then 10 or 15 fields of data are displayed for the user.

    Do you think that is possible without having to add a Lookup against that 50,000 rows because it takes quite a while to load the form if I do add as a lookup?

  10. Hi,
    10x, great job. I was really missing associated lookup columns in SP2007.
    Any chance to display the info also in views and not only forms?

  11. I am getting _vti_bin/lists.asmx 500 (Internal Server Error) in browser console. But I can access the lists.asmx directly on the browser?

    I am using this in SP2013 foundation. Do I need to change anything?

  12. Hi Alex,

    This solution in a SharePoint 2013 site doesnt work with the update user experience setting applied. But when the site was at the SP 2010 look & feel works smooth.

    Any idea???

  13. Hi I am trying to use your code in SP2013 lists, nothing shows up. in f12 I get error
    Exception in window.onload:Error:An Error has occurredJSPlugin.3005

    Any Idea?

    Thanks

  14. Hi Alexander

    I tried the code but I always get a error above the new form web part: “Exception from HRESULT: 0x80131904”.
    Here is my full code:

    var fields = init_fields_v2();
    var argumentObj = {‘arrFinAndDispName’:[‘Mitarbeiter_x0020_Nr_x002e_|Mitarbeiter Nr.’,
    ‘Lohn|Lohn’,
    ‘Lohnlauf|Lohnlauf’],
    ‘ListGuid’:’F20A1CE6-F9FE-4205-8175-30C8649E7D98′,
    ‘LookupFIN’:’Name_x0020_des_x0020_Mitarbeiters’};
    setTimeout(function(){
    init_displayInfo(argumentObj);
    },100);

    These are the names of all columns in the source list.
    Kind regards

  15. Hi,

    I Added the delay function, but nothing happens. the error I was getting previously was due to a windows pack. On the edit/new screen the lookup values are not displayed.

  16. Hi Alex,

    Here is my script in me editForm…What do I seem to be missing?

    var fields = init_fields_v2();
    var argumentObj = {‘arrFinAndDispName’:[‘Address2|Address2’], ‘ListGuid’:’28FAB255-E855-4389-90AE-81EA93E52712′, ‘LookupFIN’:’MailFrom’};
    setTimeout(function(){
    init_displayInfo(argumentObj);},100);
    var myFieldVal = $(“#MailFrom_Address2”).html();
    alert(myFieldVal);

  18. Hi Alex,

    The function is working properly in edit form. In newform the attributes are displayed but am getting Exception from HRESULT: 0x80131904. Any idea?

  20. Hi Alex,

    Thank you for this very efficient solution. I am trying to use this in SP 2013.I had a question – can this be made to work for a default value of the Lookup Field? I have implemented it in the Edit form and it works fine when I click and select a value from the Dropdown.However,if i were to assign a value to the dropdown programmatically – it doesnt work.Seems like the code works only on the Change of the Dropdown list.

    Would it be possible for you to help me out?

    Thanks
    Ujwala

  21. Hi Alex – I’ve been looking for a solution like yours for awhile, but unfortunately I cannot get it to work. Everything loads fine and I get no errors in debug mode. I’m using…
    spjs-utility.js-1.183, I was using the latest version initially
    jquery-1.11.2

    My CEWP below the AddForm and DispForm is…
    var argumentObj = {‘arrFinAndDispName’:[‘TSADescription|TSA Description’,
    ‘OriginalEndDate|Original End Date’],
    ‘ListGuid’:’6B2FC3A7-3BBF-41FA-AEF0-72D2DAD75A22′,
    ‘LookupFIN’:’Title’};
    setTimeout(function(){
    init_displayInfo(argumentObj);
    },100);
    var myFieldVal = $(“Title_TSADescription”).html();
    alert(myFieldVal);

    I don’t see where the issue is with AddForm, but while debugging DispForm, it appears that variable ts in init_displayInfo is always undefined.

    I’m using SP13 in the cloud.

      1. Hi Alex – thanks for the quick response.

        PullInformationFromConnectedList.js loads fine. I can step through the code and everything seems run. In PullInformationFromConnectedList your comments say it “Must include reference to JQuery and spjs-utility”. Do you mean inside PullInformation…js or someplace else?

      2. Alex –
        After a little further digging this morning, it appears that when displayInfo(selID,argObj) is called, selID is undefined. argObj is populated as expected. I’ve looked and looked, but cannot figure it out.
        Thanks!

      3. Alex – I got it working for DispForm and EditForm, unfortunately I cannot get it working for AddForm. Since there is not an option in displayinfo() for AddForm that is where I think the problem is, but since you’ve said it works for AddForm I figure I must be missing something.

        Thanks for the help.

  22. Alex – latest and final post…
    I got all three forms (DispForm, EditForm and NewForm) working. I changed one line of code in PullInformationFromConnectedList.js.
    I changed:
    }else if(location.href.toLowerCase().match(“editform.aspx”) !== null){
    to:
    } else {
    so that both edit and add run through the same piece of code. I’m hoping this will continue to work fine. If there is any “gotchas” I need to be aware of I’d greatly appreciate letting me know.
    Anyways, this is GREAT! Thank you for providing a great solution.

  23. Hello! I love this but can’t get it to work. Would there be any reason for the code in the script to produce an Failed to Load Resource error in regards to jquery?

    I can’t seem to get it tow ork and I’ve tried it from a library hosted on our site and from google.

    1. I got this issue fixed but now nothing loads at all. No errors and nothing shows up. My code looks like:

      var fields = init_fields_v2();

      /* Object containing all arguments for the function
      * “arrFinAndDispName” is an array on format “FieldInternalName|Display name” from the list the lookup is pulling information from
      * “ListGuid” is the list Guid or the displayName of the list the lookup is pulling information from
      * “LookupFIN” is the FieldInternalName of the lookup column.
      */
      var argumentObj = {‘arrFinAndDispName’:[ ‘Sec_x002e_|Section’,
      ‘Title|No.’,
      ‘Title0|Title’,
      ‘Description|Description’],
      ‘ListGuid’:’DB10E8A2-67C3-404C-9D4A-ACC92BB4146′,
      ‘LookupFIN’:’Lookup’};

      init_displayInfo(argumentObj);

      Where Sec_x002e_, Title, Title0, Description all are the internal field names of the list where the data is being pulled from and the other side of | is the name of the fields in the list where the lookup is. Am I missing something Alex?

      1. Hi,
        I have not looked at this one in a while, but look at what “Gol4Man” posted above your question and see if this resolves your problems as well.

        Alexander

  24. Alex,

    When you pull date fields through, they are displayed in US format (yyyy-mm-dd). Is there anyway to change this to UK format?

    Cheers

    1. I guess the quickest solution is to create a calculated columns in the list you are pulling the values from that has the correct format – use something like this:

      =TEXT([Your date column name], "MM/dd/yyyy")

      Alexander

  25. Finally I implemented this feature on my site. I modifed in PullInformationFromConnectedList.js the function displayInfo(selectedID,argObj,fields) and init_displayInfo(argObj,fields) to pass fields as parameter
    Other change was this:
    Delete this line:
    }else if(location.href.toLowerCase().match(“editform.aspx”) !== null){
    And replace by this line:
    }else{
    (Thanks @Gol4Man)

    Thank you!

  26. Trying to implement this in multiple lookup coulmns within a list but no success so far. Does this solution work with multiple columns or is it designed for just one lookup column in a list?

    1. Hi,
      I have not tested this myself, but I guess it should work. The method you should use it to create two different argumentObj like this:

      var argumentObj1 = {...the arguments...};
init_displayInfo(argumentObj1);
var argumentObj2 = {...the arguments...};
init_displayInfo(argumentObj2);

      Alexander

  27. Does your solution work when throttling is enabled? We have a couple of large lists that are impacted by throttling where we are using lookups. Thanks! P.S. Using SharePoint 2013

  28. Hi Alex,

    It appears as if Microsoft got rid of the “optHid” attribute on their dropdown in the 2013 Cloud version of sharepoint. I had this working perfectly up to the day the upgrade was done.

    So, because optHid is not there, your code sets your variables (in your code) inpHidID and selID to “undefined”. Beyond this point your code executes without errors, but nothing happens.

    Sorry had not had time to further debug, but thought this may be useful info.

    1. An update. Turns out it was a timing issue. Wrapped the call in a _spBodyOnLoadFunctionNames call and all works as it should. Thanks Alex

  29. Hello

    I have a small problem with the browser. The solution works on chrome and firefox but for some reason, on internet explorer (version 11.0.23) the solution doesn’t work anymore. Strange is, that it worked last week.

    Best regards

  30. It seems that the problem was that I was using some old version of the scripts. I replaced it with the newest one and that solved the issue. Sorry for taking your time.

    Best regards

  31. Hi,

    This is a great tool – thank you for developing it.

    The only issue i am having is with the NewForm.aspx. When the form loads the addition information does not appear under the dropdown box. However, as soon as i change the selection within the dropdown the additional information is displayed.

    I have tried this in both IE and Chrome.

    Has anyone else experienced this?

    1. Hi,
      The easy fix would be to have the field start up as empty in NewForm – forcing the user to make a manual choice in the lookup column.

      Alternatively you can run this line of code when the form has finished loading:

      $(fields["TheFieldInternalNameOfYourColumn]).find('select').trigger("change");

      Alexander

      1. Hi Alexander,

        Thank you for your reply.

        What was the line of code you were suggesting?

        Kind Regards

        Kev

    1. Hi,
      I don’t have this solution installed so I cannot give you the ready code, but basically you need to add something like this to your custom js:

      function dffs_PreSaveAction(){
    var v1 = jQuery("#LookupColumnName_NameOfField").text();
    setFieldValue("NameOfFieldToWriteTo",v1);
    // Repeat for other fields
    return true;
}

      To find the ID of the placeholder (to replace LookupColumnName_NameOfField) for the values you must right click and select “inspect” and look at the html code.

      Alexander

      1. Alexander,

        Using the above solution in another DFFS form with slightly different requirements gives me erratic results when using DFFS rules to concatenate user entries (e.g, lookup fields) and the pulled forward fields from the script above when the form is saved. Sometimes the form saves and it missed concatenating a user entry (some timing issue no doubt).

        Is there a JS mod that would accomplish the needed concatenation as the user fills out the form rather than when it is saved?

        The concatenated results must be in a specific order, e.g., LookupField1,LookupField2,PullForwardField3. If one of the fields is empty the concatenated result should not show a blank space in place of that field.

        Thanks for your help.

        Mike

      2. You can try to edit the file PullInformationFromConnectedList.js and add an onkeyup=’yourCustomFunction(this);’ parameter to the input fields to do this when the user types in the field. Then add a function like this to your custom js:

        function yourCustomFunction(inp){
    var v1 = jQuery("#LookupColumnName_NameOfField1").text();
    var v2 = jQuery("#LookupColumnName_NameOfField2").text();
    var v3 = jQuery("#LookupColumnName_NameOfField3").text();
    var strArr = [];
    if(v1 !== ""){
		strArr.push(v1);
    }
	if(v2 !== ""){
		strArr.push(v2);
    }
	if(v3 !== ""){
		strArr.push(v3);
    }
    setFieldValue("NameOfFieldToWriteTo",strArr.join(",")); // Change the comma to whatever character you want to join the array by
}

        Alexander

  35. Alexander,

    I’m using the scripts from this update and find that the pulled forward fields do not always appear under the lookup field when the Edit form loads. They do appear if the user re-selects the lookup field.

    I am using

    $(fields[“TheFieldInternalNameOfYourColumn]).find(‘select’).trigger(“change”);

    and

    setTimeout(function(){
    init_displayInfo(argumentObj1);
    },500);

    from your earlier comments.

    How can I ensure the JavaScript fires every time the Edit form loads?

    Thanks
    Mike

    1. I haven’t looked at this since 2013 so I’m not 100% sure, but it doesn’t seem right to use the code like that- try changing it like this:

      setTimeout(function(){
init_displayInfo(argumentObj1);
$(fields["TheFieldInternalNameOfYourColumn"]).find("select").trigger("change");
},500);

      Alexander

  37. Alexander,

    I wish to display, on hover, Related Fields in the Display form (or List View) that correlate to a multiple lookup column choice.

    Users cannot easily determine which of the Related Fields pulled forward with the Lookup field are associated with the Lookup choice when the related fields are missing for one or more Lookup choices. SharePoint simply shows them all in a sequence with no indication which of the Related Fields matches with the Lookup link to the second list.

    I have three screen shots that demonstrate the issue but am unable to attach here for some reason-will email to you.

    Thanks
    Mike

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.