Restrict lookup results

[jorge.ramos]

I have two separate lists as follows:
Departments - Contains a list of all business departments
Employees - Contains a list of all employees and has a lookup filed that references the Department filed in the Departments list.

I need to create a new list that has a lookup column with a restricted dropdown list that includes only the names of those employees whose Department field is equal to "Recruiters".

I found some code at http://forum.spform.com/forms-designer- ... -id-35457/ and edited as shown below, but I receive an error about "fd" being undefined when I click the lookup filed in the new list that I created.

function (term, page) {
var id_department = fd.field('Department').value();
if (!term || term.length == 0) {
if (id_department == "Recruiters") {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=(Department eq " + id_department + ")&$orderby=Title";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Title&$filter=(Department eq " + id_department + ") and startswith({LookupField}, '" + encodeURIComponent(term) + "')";
}
}

[rostislav]

fd is Forms Designer - a separate from CSL product. It seems that all you need is to hardcode "Recruiters" into there, so:

var id_department = "Recruiters";

Should do it for you.

[jorge.ramos]

I added that variable that you suggested; but I get a "Loading failed error" when I click on the lookup filed. The code that I am using now is:

function (term, page) {
var id_department = "Recruiters";
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=(Department eq " + id_department + ")&$orderby=Title";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Title&$filter=(Department eq " + id_department + ") and startswith(Title, '" + encodeURIComponent(term) + "')";
}

[rostislav]

1. Make sure you're using the correct value type. I.e. what is the type of Department? If it's a lookup field, what field (and field type) it points to?

2. Open up console (F12) -> reproduce the error -> see the error message in the console, it will include a URL. Go to that URL and see what the document at the address says - it will include an error. Paste this document here together with the full URL

[jorge.ramos]

Below is the code that I am using now; which no longer returns an error, but instead now it returns "No matches found" when I click on the lookup filed. Department is a PlumSail lookup field that exists on the list that I referenced when I created the lookup field. The value of that field is a lookup of a field on another list called Departments.

function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=(Department eq 'Recruiters')&$orderby=Title";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Title&$filter=(Department eq 'Recruiters') and startswith(Title, '" + encodeURIComponent(term) + "')";
}

[rostislav]

"The value of that field is a lookup of a field on another list called Departments." Does that mean that the value of the CSL field is another lookup field or are you simply saying that the lookup points to a field in another list? In case it's the latter, then you still haven't answered what field and field type it points to.

Anyway, try this. You need to use the expand attribute:

Code: Select all

return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Department/DepartmentField&$filter=(Department/DepartmentField eq 'Recruiters')&$orderby=Title";

Where DeprtmentField is the field that Department points to (go to the target list's settings -> Department field -> see the value of the dropdown under "In this column:").

[jorge.ramos]

Thank you very much; you're assumption was correct. The working code is as follows:

Code: Select all

function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=(Department/DeptName eq 'Recruiters')&$orderby=Title";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Department/DeptName&$filter=(Department/DeptName eq 'Recruiters')&$orderby=Title";
}