vLookup from New on separate list

Forums vLooup for SharePoint vLookup from New on separate list

Tagged: ,

Viewing 5 reply threads
  • Author
    Posts
    • #12829
      Michael Collins
      Participant

      I’m trying to use vLookup (or another technique) to populate a (“child”?) list item with information from another (“parent”?) list without having to create the child from the parent. I have an Employee List that our Human Resources department wants to keep tight control over, and I’m creating an Employee Self-Evaluation form that I want to automatically populate with confidential information that’s in the Employee List (e.g., Salary, Step, etc.). I know how to use vLookup to create a child list item when I start from the Parent list, and we’re doing that for a half-dozen other child lists that HR manages. What I can’t figure out is how to start from New on my child list, the Evaluation, and have the Evaluation auto-populate with information from the Employee List that applies to the person who created the Evaluation list item. I expect I’m defining the Query incorrectly, but I don’t know where I’m going wrong. Any tips? Thanks!

    • #12854
      Alexander Bautz
      Keymaster

      Hi,
      Add this to the Custom JS in your DFFS setup on the child NewForm:

      (function(){
          var res, qb = [], item;
          qb.push("<Where>");
          qb.push("<Eq>");
          qb.push("<FieldRef Name='Employee' LookupId='TRUE' /><Value Type='Integer'><UserID/></Value>");
          qb.push("</Eq>");
          qb.push("</Where>");
          res = spjs_QueryItems({"listName":"EmployeeList","query":qb.join(""),"viewFields":["Salary","Step"]});
          if(res.count > 0){
              item = res.items[0];
              setFieldValue("Salary",item.Salary !== null ? item.Salary : "not set");
              setFieldValue("Step",item.Step !== null ? item.Step : "not set"); 
          }
      })();

      Change the following:

      1. Line 5: “Employee” must match the FieldInternalName of the people picker in the Employee list.
      2. Line 8: “EmployeeList” must be changed to match the display name or the list guid of the “Employee list”.
      3. Line 8: “viewFields” must change to match the fields you want to pull back from the Employee list
      4. The lines with “setFieldValue” must be changed to match the FieldInternalName of the “child list” in the first parameter, and the FieldInternalName from the “Employee list” in the second part. It uses a “shorthand if” to ensure it will not pull back a null-value.

      Let me know if you need more information.

      Best regards,
      Alexander

      • #12874
        Thomas Wolstenholme
        Participant

        Hi Alexander,

        Massive fan of what you have built and cannot thank you enough!

        I am trying to implement this but I am not getting any values to return.

        I think it is because of this: <Value Type=’Integer’><UserID/></Value>”

        I am trying to match the Employee field (which is a dropdown with lookup values) in the Evaluation List to the same value in the Employee column in the Employee List. How would I get this user selected value to compare to the Employee column?

        Also, I would like one of the returned values to be a hyperlink to edit the line item in the Employee List, is this possible?

        Something like:
        setFieldValue(“Hyperlink”,item.Hyperlink!== null ? item.Hyperlink: “not set”);

        Thank you in advance!

        • This reply was modified 4 years, 3 months ago by Thomas Wolstenholme. Reason: additional query
      • #12884
        Thomas Wolstenholme
        Participant

        I don’t know if it is the most elegant solution but I managed to get it to work by creating a var for the value I was wanting to grab and then use to lookup the information in the external list (external list refers to the Evaluation list if you are comparing this to the original query)

        var val =  $(fields['BPUID']).find('input').val();

        Full solution is below for reference:

        
        
            $("#BPUID_spjs_lookup").change(function(){
        	var val =  $(fields['BPUID']).find('input').val();
        	
        	var res, qb = [], item;
        	
        	    qb.push("<Where>");
                qb.push("<Eq>");
                qb.push("<FieldRef Name='BP_x0020_UID' LookupId='TRUE' /><Value Type='Text'>"+val+"</Value>");
                qb.push("</Eq>");
                qb.push("</Where>");
        
            	res = spjs_QueryItems({"listName":"Building Demo","query":qb.join(""),"viewFields":["Name1","Site_x002f_Location","Description","OP_x0020_Number"]});
            	console.log(res);
                if(res.count > 0){
                    item = res.items[0];
                    setFieldValue("Name1",item.Name1 !== null ? item.Name1 : "not set");
                    setFieldValue("OP_x0020_Number",item.OP_x0020_Number !== null ? item.OP_x0020_Number : "not set");
                    setFieldValue("Site_x002f_Location",item.Site_x002f_Location !== null ? item.Site_x002f_Location : "not set");
                    setFieldValue("Description",item.Description !== null ? item.Description : "not set");
                }
            });

        For the hyperlink query I added the below, basically using the same as above but this time creating a variable to store the ID number found in the external list item

        var idNumber = $(fields['ID0']).find('input').val();

        and using this to bolt onto the end of my hyperlink to send the user to the right item to edit.

        document.getElementById("changeControlLink").innerHTML = "<a href='https://demo/Lists/Building%20Demo/EditForm.aspx?ID="+idNumber+"' target='_blank'>Submit a Change</a>";

        However, one thing which is stumping me is that when is DispForm:

        var val =  $(fields['BPUID']).find('input').val();

        returns ‘undefined’ because it becomes read only, so my link doesn’t work as the ID number is not added at the end of the hyperlink.

        Is there any work around for this problem?

        Full code below:

        
        
        	$(function(){
        	var val =  $(fields['BPUID']).find('input').val();
        	
        	var res, qb = [], item;
        	
        	qb.push("<Where>");
                qb.push("<Eq>");
                qb.push("<FieldRef Name='BP_x0020_UID' LookupId='TRUE' /><Value Type='Text'>"+val+"</Value>");
                qb.push("</Eq>");
                qb.push("</Where>");
        
            	res = spjs_QueryItems({"listName":"Building Demo","query":qb.join(""),"viewFields":["ID"]});
        
            	if(res.count > 0){
                    item = res.items[0];
                    setFieldValue("ID0",item.ID !== null ? item.ID : "not set");
                }
                
                var idNumber = $(fields['ID0']).find('input').val();
            	
            	document.getElementById("changeControlLink").innerHTML = "<a href='https://demo/Lists/Building%20Demo/EditForm.aspx?ID="+idNumber+"' target='_blank'>Submit a Change</a>";
        
            });
      • #14101
        Michael Collins
        Participant

        Hi, Alexander. Thank you so much for the help you provided for my original question. I’ve been able to modify the javascript in order to retrieve info from the Employee List on creation of a New list item, even when the child list is in a different site, which is awesome. I’m struggling with another variation, though, to which there’s likely a simple answer that I’m just not seeing.

        The task now is to retrieve Employee List information for an employee other than the logged-in user; rather, the logged-in user will select another SharePoint user from either a People-Picker field (my preference) or a lookup list populated from the Employee List — I’ve tried both. Here’s the code I’m using now. I suspect I need to pass different information in place of the <UserID /> parameter, but I can’t find the right syntax.

        Employee_PP is the name of the People Picker field in the child list, and a DFFS rule calls the function GetEEListData when the value of Employee_PP is changed.

        function GetEEListData(){
        alert(“Running function GetEEListData”);

        var res, qb = [], item;
        qb.push(“<Where>”);
        qb.push(“<Eq>”);

        // THE FOLLOWING RETRIEVES ZERO RECORDS
        qb.push(“<FieldRef Name=’Sharepoint_x0020_ID’ LookupId=’TRUE’ /><Value Type=’Integer’><FieldRef Name=’Employee_PP’ /></Value>”);

        // THE FOLLOWING RETURNS INFO ABOUT THE LOGGED-IN USER:
        // qb.push(“<FieldRef Name=’Sharepoint_x0020_ID’ LookupId=’TRUE’ /><Value Type=’Integer’><UserID/></Value>”);

        // THE FOLLOWING RETRIEVES ZERO RECORDS
        // qb.push(“<FieldRef Name=’Employee_PP’ LookupId=’TRUE’ /><Value Type=’Integer’><UserID/></Value>”);
        qb.push(“</Eq>”);
        qb.push(“</Where>”);
        res = spjs_QueryItems({“listName”:”Employee List”,”query”:qb.join(“”),”viewFields”:[
        “Sharepoint_x0020_ID”,
        “Title”,
        “DivisionAsText”,
        “SectionAsText”,
        “ClassificationAsText”,
        “Employee_x0020_ID”,
        “Representation”,
        “Supervisor”,
        ]});

        if(res.count > 0){
        item = res.items[0];

        setFieldValue(“Title”,item.Title !== null ? item.Title : “not set”);
        setFieldValue(“Employee_x0020_ID”,item.Title !== null ? item.Title : “not set”);
        setFieldValue(“Division”,item.DivisionAsText !== null ? item.DivisionAsText : “not set”);
        setFieldValue(“Section”,item.SectionAsText !== null ? item.SectionAsText : “not set”);
        setFieldValue(“Classification”,item.ClassificationAsText !== null ? item.ClassificationAsText : “not set”);
        setFieldValue(“Supervisor”,item.Supervisor !== null ? item.Supervisor : “not set”);
        }
        };

      • #14139
        Alexander Bautz
        Keymaster

        Hi,
        You cannot use the field this way in the tag. You must either user the “display name” directly like this:

        qb.push("<FieldRef Name='Sharepoint_x0020_ID' /><Value Type='Text'>"+getFieldValue("Employee_PP").join("")+"</Value>");

        Or maybe better, use the id like this:

        var loginName = spjs.utility.getFieldValue({"fin":"Employee_PP","key":"loginName"});
        var ppInfo = spjs.utility.userInfo(loginName);
        qb.push("<FieldRef Name='Sharepoint_x0020_ID' LookupId='TRUE' /><Value Type='Integer'>"+ppInfo.ID+"</Value>");

        Hope this helps,
        Alexander

    • #12889
      Alexander Bautz
      Keymaster

      Hi,
      When viewing this in DispForm, the field will not have an “input” element. You should use this code to access the field value:

      var val = getFieldValue("BPUID");

      If you have an older version of spjs-utility.js you must use one more argument in the DispForm code:

      var val = getFieldValue("BPUID",true);

      Let me know how this works out.

      Alexander

      • #12928
        Thomas Wolstenholme
        Participant

        Worked perfectly!

        So simple, thanks Alex – sending you some beers for the great tools you have developed for the world to use and share! Honestly it is brilliant!

    • #12942
      Alexander Bautz
      Keymaster

      I’m glad it worked out!

      Best regards,
      Alexander

    • #14162
      Michael Collins
      Participant

      Thank you again, Alexander! Your advice in Reply #14139 was exactly what I needed! Now I have four hours before the demo to clean up the rest of the page. ;~)

    • #14197
      Alexander Bautz
      Keymaster

      I’m glad it worked out.

      Alexander

Viewing 5 reply threads
  • You must be logged in to reply to this topic.