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

Accessing Checkbox in SharePoint using JQuery

I couldn't find anything obvious for accessing SharePoint Check Box so chucked this together, hopefully useful for someone else 😉😎

Read this article to know about Accessing Radio Buttons using J Query in SharePoint

Note: The below code/solution is applicable to accessing check box in a custom SharePoint List forms using SharePoint Designer. To know how to create a custom form in SharePoint Online, read this article Get Current Logged in User & Manager using REST API in SharePoint OnlineAccessing Check Box control in SharePoint is little tricky than accessing the check box in HTML. The reason being, Check Box control is rendered as a table instead of a single control as shown below (Check Box and its preview in developer tools - Chrome):

So, each check box is rendered inside SPAN tag in a table. To access the normal SharePoint controls, we will use either ID or title property but for Check Box it is little complicated.

The actual check box control is in "input" tag 2. The values (Example here: A, B) are inside "label" tag 3. But each control (Yes & No) are represented by ID which is generated by SharePoint 4. If you look at the ID, there is "ff5" which is the ID of the control generated randomly by SharePoint

Now, open the designer, add an ID to the TD of the Check Box so that we can query it easily using JSOM as shown below:

//Get the checked values of Check Box
$("input[id ^= 'tdLanguage']").is(function(){
    var checked = $(this).is(":checked"); //is checkbox checked (true/false)?
    var theVal = $(this).next().text();  //get the label for this checkbox
    alert(checked + " : " + theVal);
});

//Check if checkbox is selected or not
($("#tdLanguage").children().find('input:checkbox').is(':checked'))

//Check box checked or not - single check box type in SharePoint
$("td[id='tdLanguage'] input:checkbox[name*='ff5']").is(":checked")

//Check all the values of checkbox
$("td[id='tdLanguage'] input:checkbox[name*='ff5']").attr('checked',true )

//Uncheck /Clear all checkbox values
$("td[id='tdLanguage'] input:checkbox[name*='ff5']").attr('checked',false )

*** "'tdLanguage'" is the ID of the SharePoint Radio Button Control, "ff5" is the unique ID of the SharePoint Control in your List form. Change this ID according to your form to get the exact result.
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

SharePoint Online Software Boundaries and Limits

Size limit and Number of items that can be synced

1.     You can sync up to 20,000 items in your One Drive for Business library. This includes folders and files.

2.   You can sync up to 5,000 items in a SharePoint library. This includes folders and files. These are the libraries that you find on various SharePoint sites, such as team sites and community sites. This also includes syncing other people's One Drive personal sites that you may have access to. You can sync multiple SharePoint libraries.

3.     In any SharePoint library, you can sync files of up to 2 GB

Character limit for files and folders

1. In SharePoint Online, file names can have up to 256 characters
2. Folder names can have up to 250 characters
3. When you sync One Drive for Business with SharePoint Online, a folder named "forms" isn't supported at the root level for a list or library. This occurs because "forms" is a hidden default folder that's used to store templates and forms for the library.
4. Any file that’s currently open by an application (for example, an Excel .xlsx file) can't be synced by One Drive for Business.
5. To sync the file, close any application where the file is currently being used, and then sync the file.
6. The following characters in file names aren't supported when you sync One Drive for Business with SharePoint Online: (\, /, :, *, ?, “, <, >, |, #, %, ~)

SharePoint Online Site Collection

1. SharePoint Online Site collection package details below:

• Small Business: a single Team Site Collection
• Midsize Business plans: limits 20 Team Site Collections
• Enterprise, Education, and Government: limits to 10,000 Team Site Collections

2. In all options, just single Public Web Site Collection can be created, 1TB for My sites, 2000 site/sub site per site collection

Developer Limits

Only Sandbox Solutions are designed to allow SharePoint developer to customize/develop on Office365/SharePoint Online

• No access to file/folder. It means you cannot use IO API commands
• Only be deployed at a site collection level scope (not farm scope)
• No access to web.config
• PDF Documents cannot be opened in the browser
• Restrictions to access security
• Cannot overuse system resources

Storage

The following table describes the limits for SharePoint Online in Office 365 Business Essentials and Office 365 Business Premium:

Feature	Description
Storage per user (contributes to total storage base of tenant)	500 megabytes (MB) per subscribed user
Storage base per tenant	10 GB + 500 MB per subscribed user + additional storage purchased For example, if you have 10,000 users, the base storage allocation is approximately 5 TB (10 GB + 500 MB * 10,000 users) You can purchase an unlimited amount of additional storage
Site collection storage limit	Up to 1 TB per site collection. (25 GB for trial). SharePoint admins can set storage limits for site collections and sites. The minimum storage allocation per site collection is 100 MB
List view threshold limit in site libraries, including files and folders	You can sync up to 5,000 items in site libraries, including folders and files
Site collections (#) per tenant	500,000 site collections (other than personal sites)
Sub sites	Up to 2,000 sub sites per site collection
File upload limit	2 GB per file
Number of external user’s invitees	There is no limit to number of external users you can invite to your SharePoint Online Site Collections

Read More

Site Mailbox is removed from SharePoint Online

I tried to create a Site Mailbox this morning, in SharePoint Online and came to know this feature is no longer supported in SharePoint Online.

 

Tried to understand it in detail and following are the updates:

1. Microsoft is notifying Office 365 customers that access to Site Mailboxes is being removed from SharePoint Online, and no more Site Mailboxes can be created from March 2017 onward.
2. Site Mailboxes originally appeared as a feature of Exchange Server 2013. Site Mailboxes appended together an Exchange mailbox with a SharePoint site to allow users to collect email conversations in a single location, as well as access shared documents from both Outlook and SharePoint.
3. If you are using Site Mailboxes, then you have got some migration work to look forward to. Otherwise, if you are looking for “email-centric collaboration”, Office 365 Groups are the way to go (Reference: Microsoft Community Groups).

Read More

Slide Library in SharePoint Online

When you try to migrate from SharePoint 2010 to SharePoint Online (Office 365), Slide Library template is not listed as app in the Create App section.
You can still get it with a direct URL. I would not depend on it being there forever as the Slide Library is officially not in SharePoint 2013, but for now you can create one.
The direct URL to create the old Slide Library:
http://yourserver/sites/yoursite/_layouts/15/slnew.aspx?FeatureId={0be49fe9-9bc9-409d-abf9-702753bd878d}&ListTemplate=2100

Update 22/05/2017:
Even though above solution works as expected in SharePoint 2013 & SP Online it is advisable not to use since Microsoft may remove this feature in future.

Read More

Challenges to be considered before migrating from SharePoint 2010 to 2016

1. Identifying the business owners for each site / site collection
2. Data Cleansing - identifying the old contents/obsolete data for archiving
3. Identifying the intranet links embedded inside the documents (while migrating to SharePoint Online) 
4. Optimize and define databases - Perform re-organization of content. For example, as you determine what will be brought over to the new environment, you may end up archiving some ‘less-used’ content to a ‘read-only’ or ‘slower’ server.Consider moving read only data to a separate SQL instance or even separate SQL farm where the data is read-only. You may also want to consider moving less frequently used content to SQL servers that have less resources and keep the higher performing SQL servers for the frequently used/collaborative content, perhaps your ‘old’ SQL server.
5. Size of data to be migrated – this will affect the migration time
6. Any good migration should involve a content audit. Data security (legal & audit implications should be considered) –confidential documents which are restricted for internal use cannot be migrated to SharePoint Online. Key ‘things’ like ‘Highly Visible or Critical Areas’, ‘Executive Users Permissions’, ‘Large AD Group Users’ assignments, ‘Highly Used’ Features, ‘Heavy Customizations’, Integration with other systems, etc. must be identified, and documented in order to identify where the critical data and functionality exists. Depending on the access to the content, an appropriate ‘services’ and ‘security’ model can be included in the SharePoint migration planning (and later validation).
7. SharePoint 2010 cannot be directly migrated to 2016 and it needs to be migrated to 2013 and then to 2016 version (when Database upgrade approach is opted as an option for migration)
8. If tool based migration (like Sharegate, Metalogix) approach is used, then direct migration from 2010 to 2016 is possible
9. 10 GB is the new soft limit for file sizes in SharePoint 2016
10. If custom master page is used, then the existing master page needs to be upgraded /recreated from 2010 -2013 –then 2016 version for using it in SharePoint 2016
11. While using database upgrade (2010 to 2013 and then to 2016) approach, SharePoint 2010 sites will be made read only during migration process
12. Any custom farm solutions should be upgraded to SharePoint 2016 before use
13. The same connectivity/integrations to external systems in SharePoint on-premises environment may not be achieved in SharePoint Online
14. New user ID’s needs to be created in SharePoint Online. New/ existing permission levels needs to be created in SharePoint Online
15. End users needs to be trained for new UI/navigation, etc.

Read More

Enable "Open with Explorer" Option In SharePoint Online Document Libraries In Microsoft Edge Browser

Happy New Year 2017 to all readers and wish you all success in this year 😃😃😃

I was asked by my colleague who is from different Technology stream about “Open with Explorer” option disabled in SharePoint Online document libraries in Microsoft Edge browser.
Microsoft Edge browser has a new look and feel than other IE versions and obviously it is going to take a while for all end users to get used to / to know the various options in it.I tried checking the permissions of the user and he has admin permissions but still the user couldn’t open the library with explorer option as shown below:

As usual searched for various articles and understood about the features of Microsoft Edge browser.Read this KB article about Microsoft Edge and SharePoint behavior:

https://support.office.com/en-us/article/Using-SharePoint-in-Windows-10-a16bdaa4-73d6-45f8-897f-0a3cb8131763

Solution:
Navigate to the edge browser -> click on Open with Internet Explorer as shown below:

Now, the SharePoint site should open in the IE 11 (latest version before Microsoft Edge is released).As expected, in IE 11, Open with Explorer option is enabled and everything works perfectly as shown below:

If you want to sync, click on Sync and it will open the One Drive -> Sign in -> Sync the data.

Hope this helps you!
Please share your valuable comments which will help me write more and share this post using the social buttons below.

Read More

Character Limit using JQuery in SharePoint

In this post, we will learn how to implement Character Count (here in this example, maximum allowed 2000 characters) using JQuery in SharePoint.

Prerequisite:
1. Create a SharePoint List with a column named "Comments" - data type Multi line text with plain text as the option selected
2. SharePoint Designer 2013 for editing the SharePoint list with span and to identify the ID of the field created

Solution:
1. Open the SP site in SharePoint designer, navigate to the list -> Create a custom new form -> Edit it.
To know how to create a custom new form in SharePoint Designer, read this article.
2. Below is the HTML code for my "Comments field in SharePoint Designer:

1 2 3 4 5 6 7 8 9 10 11 12

<tr> <td width="190px" valign="top" class="ms-formlabel"> <H3 class="ms-standardheader"> <nobr>Enter your comment:</nobr> </H3> </td> <td valign="top" class="ms-formbody" style="background-color:#ffffff; width:400px;"> <SharePoint:FormField runat="server" id="ff6{$Pos}" ControlMode="New" FieldName="Comments" __designer:bind="{ddwrt:DataBind('i',concat('ff6',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@Comments')}" /> <SharePoint:FieldDescription runat="server" id="ff6description{$Pos}" FieldName="Comments" ControlMode="New" /> <span title="2000" id="commentsCount">2000</span><span> Characters Left</span> </td> </tr>

Make a note that I have added a Span tag below the SharePoint control to display the Characters. Also, we have declared it as 2000 characters for this example.

Now, add a script tag or a separate file (then refer it inline) -> add the below code in it save it:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

$(document).ready(function() { //Character count for comments text $("textarea[name*='ff6']").keyup(function () { characterCount('ff6', '#commentsCount'); }); }); //Character count for multiline text function characterCount(controlID,spanId) { var controlVal = $("textarea[name*='" + controlID + "']"); var cmax = $(spanId).attr("title"); if(controlVal.val().length >= cmax) { controlVal.val(controlVal.val().substr(0, cmax)); } $(spanId).text(cmax - controlVal.val().length); }

In this above JS, "ff6" is the ID of the SharePoint control.

Save the form and JS, try typing inside the control you can see the characters been automatically counted and decreases as you type. Cool Isn't it? 😉
Please share this post by clicking the below social buttons if this helps you😎
Happy coding! 

Read More

Accessing Radio Buttons using JQuery in SharePoint Online

I couldn't find anything obvious for accessing SharePoint Radio Buttons so chucked this together, hopefully useful for someone else :)

Note: The below code/solution is applicable to accessing radio button in a custom SharePoint List forms. To know how to create a custom form in SharePoint Online, read this article 

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

Accessing Radio Button control in SharePoint is little tricky than accessing the radio buttons in HTML. The reason being, radio buttons are rendered as a table instead of a single control as shown below (radio button and it's preview in developer tools - Chrome):

So, each radio button is rendered inside SPAN tag in a table. To access the normal SharePoint controls, we will use either ID or title property but for Radio Buttons it is little complicated.

1. The actual radio button control is in "input" tag 

2. The values (Example here: Yes, No) are inside "label" tag 

3. But each control (Yes & No) are represented by ID which is generated by SharePoint 

4. If you keenly look at the ID, there is "ff41" which is the ID of the control generated randomly by SharePoint.

Now, open the designer, add an ID to the TD of the Radio button so that we can query it easily using JSOM as shown below:

1 2 3 4

//Radio button change event $("td[id='tdApproved'] input:radio[name*='ff4']").change(function(){ // do something });

1 2 3

//Clear the values of radio button - reset the values of radio button $("td[id='tdApproved'] input[name*='ff4']")[0].checked = false; $("td[id='tdApproved'] input[name*='ff4']")[1].checked = false;

1 2 3 4

//Checking the value of radio button if($("td[id='tdApproved'] input:radio[name*='ff4']:checked + label").text() != 'Yes'){ // do something }

1 2	//Focus Radio button 1st element in control $("td[id='tdApproved'] input:radio[name*='ff4']:eq(0)").focus();

1 2 3 4 5 6 7 8 9

//Align all Radio Buttons in the page horizontally function HorizontalAlignChoices() { var objSpans = $(".ms-RadioText"); objSpans.each(function () { if ($(this).is("span")) { $(this).closest("tr").css({ "float": "left" }); } }); }

*** "tdApproved" is the ID of the SharePoint Radio Button Control, "ff4" is the unique ID of the SharePoint Control in your List form. Change this ID according to your form to get the exact result.

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!   

Update: 21/06/2017

Read this article, to know how to access Checkbox in SharePoint using JQuery 😎

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