SharePoint 2010 Example: Update Total in List Based off Values in Another List

I have heard a lot of request on the steps required to create a filtered dropdown within SharePoint 2010. I wanted to just create a quick post that will walk you through the steps. The example is pretty simple, but should be enough to get you started.

In our example we are entering data into a timesheet. We want to be able to select a client and a project. Once the client is selected then we would want to filter the list of projects to show only projects related to that client.

To get started we need 3 lists:

  • Clients
  • Projects
  • Timesheet

At a high level, here are the steps we are taking:

  1. Create the Client Lists in SharePoint.
  2. Create the Projects List in SharePoint and add a lookup column to link with the Client.
  3. In the Timesheet list add two lookup columns, one for client and one for project.
  4. Edit the Timesheet list form in InfoPath 2010 :
    1. Apply and desired style changes
    2. Add a Data Connection that we can use for the Filtering
    3. Add a Rule to Reset the Projects Field whenever the Client Field is updated

Since I am assuming you are familiar with creating lists and adding lookups, I will spend the rest of the blog focused on step 4, editing the form in InfoPath.

From the library Ribbon, select the Customize Form option.

The form will open in InfoPath and you can then make any desired design changes to the form. In my example I have changed the background style, inserted an image, added some header text and removed the attachments row. My changes to the form are shown below:

Next up, we will want to create a data source that we can use for the filtering. We already have 2 data sources that have been added from our lookup columns, but those only retrieve the title and we are looking for additional information from the list. To add a new data source select the Manage Data Connections link on the toolbar.

From this menu, select to add a new data source connection to receive data from a SharePoint list. As you work through the menu, you will be prompted to enter the particular list data that you want to reference in your lookup. We will first enter the URL for the site we are referencing, then the specific list and then finally the fields that we want to use in our data source. As a final step you will need to give this connection a unique name, in my example we are using Project_Details.

Now that we have the data source in place we can create the filter on our projects field. We will need to right click on the field we want to filter and select the option for the Drop-Down List Box Properties.

We first need to update the Data Source to point to the data source we added earlier. Once we do this, we can filter the entries (to enter in the option to see the entries, select the icon next to the entries field).

The filter we want to set up can be explained like this: Only show the Project Options when the Client field on the form is equal to the Client field in the Projects list. Below are the screenshots for the configuration.

For the filter we will first select the client field on the form:

Our operator will be “is equal to” and our final selection will be the client field in the projects list:

Now that we have the filter configured, the final step is to add a rule that resets the project field whenever the client field is modified. This way we can be sure that if they change the client, they will also need to update the project. The ribbon makes creating rules in InfoPath very easy. Simply select the field the rule will apply to and then select the rule you want to apply. In our case we want to set the rule to occur whenever the field changes and we want to reset another field’s value.

The menu screen is displayed and we can quickly use the icons to pick the field we want to set and then enter the value. In our case we leave value blank because we want the value to reset to a blank entry.

Now, we simply need to publish the form back to SharePoint using the Quick Publish option.

Now whenever we select to create a new item, our custom form is displayed and the options for project will filter based on the client selected.

Here is a screenshot of our list, which allows you to see that the filters about are working correctly.

Leave a Reply

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

You are commenting using your 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