I couldn't find anything obvious for accessing SharePoint Multi-value lookup columns and filtering the values so chucked this together, hopefully useful for someone😃
In continuation to my previous article on
Lookup Columns, we are going to learn about Multi-value lookup columns in SharePoint.
At the end of this article, I am going to give a bonus to the readers of this article 😊
Let's go ahead and create two source lists for this demo:
1. States
2. Cities (State is a lookup column from States list)
3. The third list will be used to implement the
Multi-value lookup column and also we are going to implement
filtering multi-value lookup columns in this article. I have created a list named "Multi value lookup demo" which has State and City as lookup value from the first two lists created. Make sure, you select
Allow multiple values options as shown below:
The new item form without filtering looks as shown below:
Following are the
functionalities to be implemented & issues/challenges needs to be considered while implementing filtering multi-value lookup columns in SharePoint:
1. An option can be double clicked to select or clicked Add button to select a value
2. An option can be double clicked to deselect or clicked Remove button to deselect a value
3. More than one option can be selected and can be bulk added to the selected values
4. The JQuery implemented should satisfy the above conditions while filtering
5. When State is selected, the values of Cities should get filtered. In the same way, when a value from the deselected then the values from Cities should be removed
I have added a Script Editor web part in
New item form and added JQuery as reference. Also, added the JS code which will implement the filtering functionality.
If you want to implement the same functionality in
Edit item form, following the same steps just mentioned above.
I have used
CAML query to do the filtering functionality which is shown below:
$(document).ready(function () {
//Call multi-value lookup function on selecting State
$("select[title='State possible values']").dblclick(function () {
multiValueLookup();
});
//Call multi-value lookup function on removing State
$("select[title='State selected values']").dblclick(function () {
multiValueLookup();
});
//Call multi-value lookup function on clicking Add - State
$("input[value='Add >'][id^='State_']").click(function (){
multiValueLookup();
});
//Call multi-value lookup function on clicking Remove - State
$("input[value='< Remove'][id^='State_']").click(function () {
multiValueLookup();
});
});
function multiValueLookup() {
var items = "";
var citiesListName = "Cities";
$("select[title='City possible values'] option").remove();
$("select[title='State selected values'] option").each(function (i) {
var clientContext = new SP.ClientContext.get_current();
var oList = clientContext.get_web().get_lists().getByTitle(citiesListName);
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml("<View><Query><OrderBy><FieldRef Name='Title' /></OrderBy><Where><Eq><FieldRef Name='State' LookupId='TRUE'/><Value Type='Lookup'>" + $(this).val() + "</Value></Eq></Where></Query></View>");
var items = oList.getItems(camlQuery);
clientContext.load(items);
clientContext.executeQueryAsync(success, failure);
function success() {
var pn2 = "";
var pn1 = "";
var ListEnumerator = items.getEnumerator();
while (ListEnumerator.moveNext()) {
var currentItem = ListEnumerator.get_current();
if (currentItem.get_item('Title') != null) {
var pn1 = currentItem.get_item('Title');
if (pn2 != pn1) {
items = "<option value='" + currentItem.get_item('ID') + "' title='" + pn1 + "'>" + pn1 + "</option>";
$("select[title='City possible values']").append(items);
pn2 = pn1;
}
}
}
}
function failure(sender, args) {
// alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}
});
}
The above code is self explanatory and I have added few screenshots below for reference:
When an value is selected
When an value is deselected
The added value in the multi-value lookup demo list
Cool isn't it? 😊 Very simple as well.
If you have read my previous article on Cascading Lookup Columns in SharePoint, you would have noticed that I have implemented the functionality using REST API.
You may think why I didn't implement multi-value lookup column using REST API? so the
bonus is here. I have implemented the above functionality using REST API as well. I will add the code in download link and you can follow either of the way to implement this functionality 😎
This is tested in SharePoint Online and I am sure it should work for SharePoint 2013, 2016 & 2010 as well.
To download, lists templates, JS code using CAML, JS code using REST API, click this link. 🔗
Please free to comment. Always, your comments help me to write more.😃 Share this post to others if this helps you!😇
Topics include
