Abin Jaik Antony

its all about my work & life….

Cascading dropdowns in InfoPath from SharePoint.

Posted by Abin Jaik Antony on May 26, 2010

It is very common for every developer to have a situation to  develop cascading dropdowns during their development life. It can be Category –Product scenario, Country-State scenario etc.  Here the case is something different; cascading is not on regular ASP.NET Page or not a simple web part. But it’s on a InfoPath form…..!  You will ask, what makes the difference?  I say, if the InfoPath is browser enabled, the filter option of the InfoPath won’t work or it won’t support.  Any how we developers have to accomplish this cascading task. Below, i have a scenario and its solution step by step.

Scenario:  I have two SharePoint lists “Categories “and “Products”. “Categories” SharePoint list have a column named “Category” and “Product” SharePoint list have 2 columns – Product and Category, out of which Category is a look up column from “Categories” SharePoint List. Now we have to develop a InfoPath form with 2 dropdowns, Categories and Products and we need to make it work in a cascading style. Means when the user selects a particular category on the Categories dropdown, related set of products should be populated on the Products dropdown. This is the scenario.

Solution: Follow the simple steps to accomplish this task.

  1. Open Microsoft InfoPath 2007 from Start à All Programs à Microsoft Office.
  2. Create a blank InfoPath form template using the option “Design a Form Template”. Don’t forget to check the option “Enable Browser Compatible Features” while creating the blank template.
  3. Now we have a blank InfoPath form in front of us. Drag and Drop two dropdowns from the toolbox and name them as Categories and Products.
  4. After step 3 , we need to populate the dropdowns with data. For that we need to create data connections with in the InfoPath form. “Categories” dropdown can be populated directly from the “Categories” SharePoint list. But Products dropdown can’t be populated from products SharePoint list, because its need to populated according to the selected category. In the next 2 steps we will create data connections for the dropdowns.
  5. In this step we will create data connection for the Categories dropdown. Select “Dataconnections… ” option from the “Tools” menu [menu available on the top portion] of the InfoPath form. From the “Dataconnections” window click add and on the DataConnection wizard select “Create new dataconnection” option with “Receive Data” Selection. On the “select the source of your data” step ,  go with the option “Sharepoint Library or List”. The coming steps of the wizard are self informative and proceed with selection of our Categories SharePoint List. Once we finish with the DataConnection wizard steps, Bind the created Categories dataconnection  with “Categories” Dropdown by right clicking on the control and from “Dropdown  list properties”  , select “Look up values from the external datasources” option. Now there will be a selection  with available dataconnections. Select our categories dataconnection and select category field  for  “Entries” option. Check on the InfoPath preview, whether data is populated correctly or not. So population of the  Categories dropdown is over.
  1. In this step we will create dataconnection for the Products dropdown. Here  we have to do some more work to create Products dataconnection. This is the portion we have to look on. Before going to create dataconnection for Products, we have to create a webservice that have function which return set  of  products according to the category passed. So, Create a WSPBuilder project and  add a Webservice item to the project  from WSPBuilder project templates or you can go with other webservice creation techniques and deploy it to the sharepoint site. Usually I create SharePoint components with the WSPBuilder . If it is with WSPBuilder, packaging and deployment is quite easy.

Give a user-friendly name to the webservice and in the code behind of the Webservice file add the following piece of code.

[WebMethod]
        public DataSet GetProducts(string Category)
        {

            DataSet dsProducts = new DataSet();
            DataTable dtProducts = new DataTable();
            DataColumn dcCategory = new DataColumn("Category");
            DataColumn dcProduct = new DataColumn("Product");
            dtProducts.Columns.Add(dcCategory);
            dtProducts.Columns.Add(dcProduct);

            SPSite site = SPContext.Current.Site;
            SPList list = site.OpenWeb().Lists["Products"];
            SPQuery query = new SPQuery();
            query.Query = @"<Where>
      <Eq>
         <FieldRef Name='Category' />
         <Value Type='Lookup'>"+ Category  +@"</Value>
      </Eq>
   </Where>";
            SPListItemCollection itemCol = list.GetItems(query);

            DataRow drCategory;
            foreach (SPListItem item in itemCol)
            {
                drCategory = dtProducts.NewRow();
                drCategory[dcCategory] = Convert.ToString(item["Category"]);
                drCategory[dcProduct] = Convert.ToString(item["Product"]);
                dtProducts.Rows.Add(drCategory);
            }

            dsProducts.Tables.Add(dtProducts);
            return dsProducts;
  }

This webservice method will return set of products for the given category. Build this project, build the WSP and deploy to the sharepoint site. After the deployment of the WSP , we can find the webservice in the _layouts virtual directory of the sharepoint site on the IIS. It will look like this http://localhost:5050/_layouts/CascadingDDLService.asmx .   Browse that .asmx  file and check whether  the “GetProducts” webservice method  is working on invoking. If it is working, we can proceed with it during the creation of “Products” dataconnection.

Now we can start creating dataconnection for Products. Follow the procedures mentioned on the step 5,till you reach “select the source of your data” step on the dataconnection wizard. On this step you have to select “Web service” option rather than “Sharepoint Library or List” option that we have taken on the step 5. After selecting  “Web Service” option , on this next step it will ask us to provide the webservice URL. Provide our webservice url that we have deployed before, it will give set of web- methods in the web service, in our case it will be only method ,“GetProducts”. Click next , it will ask for the  default parameter values and go on with the steps with clicking Next. But on the last step we have to consider one important thing , uncheck the option “Automatically retreive data when form is opened.” and click Finish to end dataconnection wizard.Now the Products dataconnection is created and we can bind this dataconnection to the Product dropdown list. Follow the same procedures mentioned on the step 5 to bind the dataconnection on the dropdown.

  1. Next step is to create the “Rules” for the Categories Dropdown. This step is very important for the cascading to work.
    • Right click on the Categories dropdown, select “Rules” option. From the rules window, click “Add” button to add a rule for the categories dropdown.
    • On the click of the “Add” button, you will see a window like this

Name the rule with a user-friendly name. Click on the “Set Condition” button and set a condition as per the below mentioned image.


We need to set the condition that Rule need to shoot when Categories is not blank.

    • After setting the Condition we can set the “Actions” for that rule.Here we need to set 3 actions. Click on the “Add Action” button to set an action.
    • In first action we will set the Products Field previously set value to Null. Select “Set a field’s value” from the action’s dropdown.For that select Products Field from main datasource and click OK without setting the value.

    • On the second action we need to set the Category parameter for the “GetProducts”  dataconnection, which is a secondary datasource. Select “Set a field’s value” from the action’s dropdown. In the Field section select Category parameter

and set its value to the Main datasource Category Field.

    • In the third action we will populate the “GetProducts” datasource . For that Select “Query using a dataconnection” from action’s dropdown and select “GetProducts” dataconnection from Data connection’s dropdown.

Finally the Rule window will be like this

Now save InfoPath Form and publish to any SharePoint site. Browse the InfoPath form from any of your browser, cascading of dropdown will work with charm…. :)

About these ads

One Response to “Cascading dropdowns in InfoPath from SharePoint.”

  1. Narsinh said

    Hi
    thanks for above artical .

    I has same requirement but little differnce . it has only one dropdown control it should show values concern to login user

    Like if my manager is login or open forms the dropdown list should shows its team members not all employees

    it was work fine with infopath 2007 filter option but notworking in infopath form services.

    if you help will be great

    Thanks
    ND

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: