Cascading dropdowns in InfoPath from SharePoint

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 a regular ASP.NET Page or not a simple web part. But it’s on an 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. Anyhow, 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 has a column named “Category” and “Product” SharePoint list has 2 columns – Product and Category, out of which Category is a look up column from “Categories” SharePoint List. Now we have to develop an InfoPath form with 2 dropdowns, Categories and Products and we need to make it work in a cascading style. This means that 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 within 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 it needs to be 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.
6. 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 at. Before going to create dataconnection for Products, we have to create a webservice that has a function which returns a 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 a 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 in the step 5, till you reach “select the source of your data” step on the dataconnection wizard. In this step, you have to select “Web service” option rather than “Sharepoint Library or List” option that we have taken in step 5. After selecting “Web Service” option, in this next step, it will ask us to provide the webservice URL. Provide our webservice URL that we have deployed before, it will give a 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 in the last step, we have to consider one important thing, uncheck the option “Automatically retrieve 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 in step 5 to bind the dataconnection on the dropdown.

• The 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 needs 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 the 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 browsers, cascading of dropdown will work with charm….