vLookup from New on separate list

[Michael Collins]

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!

[Alexander Bautz]

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

[Thomas Wolstenholme]

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 7 years, 8 months ago by Thomas Wolstenholme. Reason: additional query

[Thomas Wolstenholme]

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>";

    });

[Michael Collins]

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”);
}
};

[Alexander Bautz]

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

[Alexander Bautz]

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

[Thomas Wolstenholme]

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!

[Alexander Bautz]

I’m glad it worked out!

Best regards,
Alexander

[Michael Collins]

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. ;~)

[Alexander Bautz]

I’m glad it worked out.

Alexander

[Author]

Posts