Thursday, 17 January 2013

Working With 2/3 level Cascading Dropdown In Sharepoint List

Sometimes we face such limitations or issues, requirements where we need to do some custom coding in sharepoint. obviously this hampers performance and consumes time in developing forms and testing it. 

Simply, i wanted to create a list where i need to implement cascading Dropdowns. most of us are familiar with such scenarios like Country, State, District dropdown are filled up one by one depending on selection of previous dropdownlist. For such simple requirement we may need to invest time in custom code and testing.

Below image can give a better idea about given scenario.


Here i wanted to create a list of category, sub-categories and Store. my store list includes look up for category and sub-category which i need to fill-up on the basis of selection of fist dropdown value. lets see how it works. i will try to guide through images as we all know that we hate reading and require faster output.

List Structure:

1. Created a list Category.(List Name:Category)
Image:1
2.Created Sub-Category List (List Name:SubCategory)
Image:2
In this list, I have look-up column 'RequestCategory' for 'Title' in 'Category' list.

3.Created a list where we expect to select cascading dropdown while adding or editing a item into list(List Name:Store).
Image:3

In this case if you observe add, edit form. it will bring all items from 'SubCategory' (2nd list) and not only in which we are interested in by selecting    Item from 'Category' (1st List).

Solution:
To resolve this problem we need following (.js) files.

2.jquery.SPServices click here for latest version

add content editor webpart where you want to make cascading dropdown work.

put below script into content editor webpart:

<script language="javascript" src="/sites/Home/Requisition/Shared%20Documents/jquery-1.9.0.js" type="text/javascript"></script>
<script language="javascript" src="/sites/Home/Requisition/Shared%20Documents/jquery.SPServices-0.7.2.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
      ExecuteOrDelayUntilScriptLoaded(loadCascadingDDL, "sp.js");
      function loadCascadingDDL() 
                {
                          $().SPServices.SPCascadeDropdowns({
                                       relationshipList: "SubCategory",
                                       relationshipListParentColumn: "RequestCategory",
                                       relationshipListChildColumn: "Title",
                                       parentColumn: "ItemCategory",
                                       childColumn: "ItemSubCategory",
                             });

                }
</script>

Here in above example parameters are below:

relationshipList: is the list name of 2nd image.(SubCategory list)
relationshipListParentColumn:Parent column from 2nd image(parent look-up column in SubCategory List)
relationshipListChildColumn:The child field name which you want to display on second dropdown(from SubCategory List/ 2nd image)
parentColumn:The field name which you want to treat as a parent column(from list where you want to input data/3rd image)
childColumn:The field name which you want to treat as a child column(from list where you want to input data/3rd image)

all this works for add/Edit item as well and we don't require any custom coding.

Output:


Note: it can fail or will not respond, if you do have space,-,(,) characters in your field name.

Going ahead question may raise in your mind that is it possible to create  three level cascading dropdowns? i'll say, why not?
In this case we will repeat same function for next cascading dropdwon.

Three level cascading dropdownlist:

I created a new list 'Request' where i need to refer all above lookups with this list. below is screeenshot for list.


and my output should be:


And my code would be:

<script language="javascript" src="/sites/Home/Requisition/Shared%20Documents/jquery-1.9.0.js" type="text/javascript"></script>
<script language="javascript" src="/sites/Home/Requisition/Shared%20Documents/jquery.SPServices-0.7.2.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
      ExecuteOrDelayUntilScriptLoaded(loadCascadingDDL, "sp.js");
      function loadCascadingDDL() 
                {
                          $().SPServices.SPCascadeDropdowns({
                                       relationshipList: "SubCategory",
                                       relationshipListParentColumn: "RequestCategory",
                                       relationshipListChildColumn: "Title",
                                       parentColumn: "ItemCategory",
                                       childColumn: "ItemSubCategory",
                             });
                          $().SPServices.SPCascadeDropdowns({
                                       relationshipList: "Store",
                                       relationshipListParentColumn: "ItemSubCategory",
                                       relationshipListChildColumn: "Title",
                                       parentColumn: "ItemSubCategory",
                                       childColumn: "Request",
                             });

                }
</script>



Thanks!!!

14 comments:

  1. Source list getting from other sites?

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. we tried this using Task list. but is not working... if use custom list it is working fine.

    Task list is not supported?

    ReplyDelete
    Replies
    1. Every list is supported, please check with ur internal field name as well.

      Delete
  4. Hi,
    I dont have much idea about SPSERVICES.
    I need to implement single dynamic drop down list to SP page. Just say, i have three column Name , Created , Modified.
    Now, i need to created dynamic drop down using one column Name. So all the files present in document library should appearin in drop down.
    How can i do this. Could you please provide me code and where to paste this.
    Thanks,

    ReplyDelete
  5. Would it work for external columns? My all three (Country, State & City) columns are coming from SQL Database via External Column.

    ReplyDelete
  6. When I am doing same steps I am not able to filter out ItemSubCategory on basis of ItemCategory.
    I think my java script is not working on sharepoint.
    Please help me out regarding this.

    ReplyDelete
  7. I used your approach and was able to populate the dropdownlists, but when saving the item of the list i get the "invalid postback or callback argument error". please help !!

    ReplyDelete
  8. For the 3 level list you have 2 screen shots, are there 2 new lists one name "Request" and the last one would be the new list where everything is cascaded?

    ReplyDelete
  9. 3 Level Cascading drop down SharePoint step by step using jquery. https://youtu.be/yBytlB1yTtM

    ReplyDelete
  10. 3 Level Cascading drop down SharePoint step by step using jquery. https://youtu.be/yBytlB1yTtM

    ReplyDelete
  11. Hi I need to filter with 3 columns based on data...
    kindly help me out for this

    1. Columnn name : Country, india,us,uk,singapore 4 names
    2. Columnn name : places, We have 2 places top places,normal places
    3. Columnn name : city, we need filter places depends on cities

    For Example If i select india, need to show 2 dropdowns, top places or normal places, in third column need to display only india places not for all top places.

    Kindly help me out of this isse

    ReplyDelete