Multi value Lookup Columns in SharePoint

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 😊

Previous articles references
CAML Query tutorial for SharePoint
Cascading dropdown (or) Filtered Values in Lookup Columns in SharePoint
Cascading drop down in SharePoint using REST API

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!😇

Read More

Cascading drop down in SharePoint using REST API

Cascading drop down or filtered Lookup Columns in SharePoint is one of the most used functionality in most of the projects for various business needs.

In one of my old articles, I have explained about creating cascading or filtered lookup columns using JQuery & SPServices in MOSS 2007 version of SharePoint. 

Cascading drop down (or) Filtered Values in Lookup Columns in SharePoint

This solution has limitations such as this will work only in List Forms and it may not work after certain limit of values in the drop down.

Previous articles reference 
CAML Query tutorial for SharePoint
Multi value Lookup Columns in SharePoint

In this article, we will learn to implement Cascading or Filtered Lookup Columns in latest versions of SharePoint using REST API (applicable to SharePoint 2013, Office 365 – SharePoint Online, SharePoint 2016). REST API uses OData (Open Data Protocol) services to read/write/update data in SharePoint.

I have created 2 lists with the following information as shown below:

1. Drinks 
2. Drinks Type (Drink column is a lookup column to display Title from Drinks List)             

             

Now create a list to test the cascading or filtered lookup functionality. I have created a list with “Drinks Menu” as the name. 

             

Please note, the Drinks and Drink Type columns are created as Choice type and all the values from Drinks and Drink Type are added as choices in these fields. This is implemented in this way so that we can avoid the issue surfaces when we have large number of options in Lookup Column. 

Also, if you add a new value to parent lists (Drink or Drink Type lists in this scenario), add the values as choice in the cascading lookup value implementation list ( Drink Menu list in this scenario).

Now, click on the new item which will open the “NewForm.aspx” -> Edit the page -> Add a Web Part -> Insert -> Categories -> Media and Content -> Script Editor -> Click Add to add it.

In the Script Editor, click Edit Snippet and add the CascadingDropdown.js (download from the below attachment). I have referred “JQuery.1.12.0.min.js” in the code which is also available for download. Make sure JQuery is referenced properly in your code else, the script won’t work.

Following are the scenarios, I have covered in this example:

When “Drinks” drop down is not selected or changed to empty, then “Drink Type” & “Price” fields should be disabled and empty

When “Drinks” is selected, “Drink Type” should cascade (filter the values) and show the types based upon selection

When “Drink Type” is selected, the “Price” should automatically populate in the Price field

The data saved to the list will look as shown below:

JS code for cascading the lookup values (Drinks -> Drink Type) is shown below:

//Function to filter the values of Drink Types
function loadDrinkTypes(selectedDrink) {
    var drinkTypeListName = "Drink Type";
    var drinkTypeListURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + drinkTypeListName + "')/items?$select=Title,Drink/Title,Drink/Id&$expand=Drink&$filter=Drink/Title eq '" + selectedDrink + "'";
    getReqData(drinkTypeListURL, function (data) {
        var items = data.d.results;
        if (items.length > 0) {
            var optionsAsString = '<option value=""></option>';
            for (var i = 0; i < items.length; i++) {
                optionsAsString += "<option value='" + items[i].Title + "'>" + items[i].Title + "</option>";
            }
            $('select[title="Drink Type"]').html(optionsAsString);
        }
    },
        function (data) {
            //alert("Some error occurred in getting Drink Types");
        });
}

//JQuery AJAX to access REST API JSON data
function getReqData(reqUrl, success, failure) {
    $.ajax({
        url: reqUrl,
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (data) {
            success(data);
        },
        error: function (data) {
            failure(data);
        }
    });
}

JS code for setting the Price value automatically on Drink Type selection is shown below:

//Function set the Drink Price 
function setDrinkPrice(drinks, drinkType) {
    var drinkTypeListName = "Drink Type";
    var drinkTypeListURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + drinkTypeListName + "')/items?$select=Price&$filter=(Drink/Title eq '" + drinks + "') and (Title eq '" + drinkType + "')";
    getReqData(drinkTypeListURL, function (data) {
        var items = data.d.results;
        if (items.length > 0) {
            var price = "";
            for (var i = 0; i < items.length; i++) {
               $("input[title='Price']").val(items[i].Price);
            }
        }
    },
        function (data) {
            //alert("Some error occurred in getting price");
        });
}

Please download the full code to view how each method are called in document.ready()

Download the JS files, List Templates from this link for the complete solution.

Note:

1. The above code is tested in SharePoint Online (all the lists are in Classic Mode and not Modern List views in SP Online)

2. It should work in SharePoint 2013 and 2016 versions as well

Please free to comment. Always, your comments help me to write more.😃 Share this post to others if this helps you!😇

You’re still here? I don’t have anything else for you sorry. It’s straightforward. Go… go make some users happy. 😎😀
Update: 06/07/2017
To know about implementing multi-value lookup column and filtering values, read this article: Multi value Lookup Columns in SharePoint

Read More

Get Current Logged in User & Manager using REST API in SharePoint Online

Scenario: 
Environment: SharePoint Online 
Approach:      Populating Current logged in user in People Picker. Also populating user's Manager automatically using REST API and SP Services.

Solution:

1. Create an SharePoint custom list "Learning" (in my case).

2. Create two columns with the following names and data types shown below:

         Internal Name                      Title                                                   Type

CurrentLoggedinUser           Current Logged in User Person or Group (People Only)

Manager                              Manager Person or Group (People Only

3. When you try adding a new item in the list, it should look like:

Here the two people pickers are SP 2013 people picker type where it will show the matching users while typing few words automatically.

4. Now open the list in SharePoint Designer, Create a New Custom Form and make it as default as shown below in the screenshots:

Open the new custom form in Advanced Mode, where you can edit the List Form. Try viewing the page by clicking F5 or Preview in Browser icon in top left corner in the designer.

5. You can see the difference in People Picker. When you create a custom list form, the default forms are automatically changed to SharePoint old type. (for more details read this blog).

6. Now add the ID for both fields as shown below. Also, add the reference the JQuery in the form 

7. Now add the ID's for the two people picker values so that it will be useful for querying the SharePoint controls as shown below. 

 8. Add a JS file ("Learning.js") in the Site Assets Library and refer it in the new form. Add the below code in the newly created "Learning.js" file.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

$(document).ready(function() { var userAccountName= $().SPServices.SPGetCurrentUser(); //Set current logged in user and manager name in people picker LoadPeoplePickerDetails(); //Show the form fields on document.ready() $("#onetIDListForm").show(); }); /****************** All function defintions starts here********************************/ //Function to set people picker values function LoadPeoplePickerDetails() { var url=_spPageContextInfo.webAbsoluteUrl + "/_api/SP.UserProfiles.PeopleManager/GetMyProperties" getReqData(url,function(data){ try { //Get properties from user profile Json response var userDisplayName = data.d.DisplayName; var AccountName = data.d.AccountName; $("#tdCurrentUser [id$='upLevelDiv']").html(AccountName); $("#tdCurrentUser [id$='checkNames']").click(); var Manager = data.d.ExtendedManagers.results; $("#tdManager [id$='upLevelDiv']").html(Manager[Manager.length-1]); $("#tdManager [id$='checkNames']").click(); } catch(err){ } }, function(data){ //alert("some error occured in getting current User info"); }); } function getReqData(reqUrl,success, failure) { $.ajax({ url: reqUrl, method: "GET", headers: { "Accept": "application/json; odata=verbose" }, success: function (data) { success(data); }, error: function (data) { failure(data); } }); }

9. Save the JS and the custom new form as well.
10. Now, try adding a new item. The current logged in user and the current logged in user's manager name will be automatically set in the people picker as shown below.

11. It is basically, on document.ready(), REST API call is made and it populates the data on the form load.

Note: 
1. The people picker for Manager field is loaded after getting the value from current logged in user field. Hence if you have to change the user, the manager value is set during document.ready(). If you want to change the user and set the manager field, then write a method for setting value of manger and call it on value set in first people picker
2. I have implemented only in  Custom New Form only.

For ease of use, I have shared the js and the List Template. Please use this link to download.

In the above tutorial, you have learned how to create a Custom Form in SharePoint Online, make REST API calls and also set the values to the People Picker in the list form.

Please share your valuable comments which will make me write more and also share this post using the below social buttons to others. Happy Share(ing)Point!    

Read More