SharePoint InfoPath 2010 Cascading Dropdown Lists With No Lookup Columns

Products: SharePoint InfoPath 2010

A recent client needed an intuitive form for capturing user data.  There were many fields on the form and three (3) that required special attention.

  • the 1st field provided a drop-down list of teams
  • the 2nd field provided a drop-down list of categories
  • the 3rd field provided a drop-down list of reasons

The requirement was that the values in the 2nd and 3rd fields were filtered based on the selection of the 1st field.

I attempted to arrive at a solution that used lists with ‘lookup’ columns as I had seen demonstrated on a few websites.  In this case, the list content was too lengthy as they included 300+ entries; response time suffered.  The form load response time exceeded 15-20 seconds regardless of cacheing.

I threw out the list lookups and instead, defined 3 basic lists with the Primary Team key values being represented within the Category and Reason lists; similar in form to that of relational tables.

Big Idea

Rather than provide filtering based on list lookup columns, each form field control (Primary Team, Category and Reasons) would contain a condition on the external data connection to filter the list of values.

Setup Details

  • SharePoint Source Lists
    • Primary Teams – define as a simple 1 column list – Title column contains the Primary Team values
    • Primary Categories – define as a simple 2 column list – Title column contains the Category values – Teams column contains the Primary Team [Title] values
    • Primary Reasons – define as a simple 2 column list – Title column contains the Reason values – Teams column contains the Primary Team [Title] values
  • Change Control List
    • Create the list called ‘ChangeControl’ and configure the following columns
      • Title
      • PrimaryTeam         [single line of text / required]
      • PrimaryCategory   [single line of text / required]
      • PrimaryReason      [single line of text / required]

NOTES:  We need the above fields in the list to be defined as a ‘single line of text’ as the field will simply contain the selected value from each dropdown box within the InfoPath form.

  • Open the List in InfoPath
    • View the list in SharePoint
    • Select the ribbon option of Customize Form
      * the 4 above fields will show in the body of the form
    • Create a ‘Read’ connection to each of the three (3) lists
      • Primary Team
        • show columns:  Title
      • Primary Categories
        • show columns:  Title, Teams
      • Primary Reasons
        • show columns:  Title, Teams
  • Change each field to a Drop Down List Box
    • right click on each field
    • mouse-over the Change Control option and select the Drop Down Box option

NOTES:  So, even though the original field was a ‘single line of text’, the form fields may actually be defined as another type of field.

  • Configure the Control related to:  PrimaryTeam
    • right-click on the field: PrimaryTeam and select Drop-Down List Box Properties
    • select the radio button: Get choices from an external data source
    • select the Data Source of Primary Teams
    • select the dialog to the right of the Entries section
      • select the column: Title
      • no filtering required
      • select OK
    • the Value field will resolve to a period
    • the Display Name field will resolve to a period
    • check the box to show only unique entries
    • select OK

Summary:  We want to show a list of available teams from the Primary Teams list connection in our control.

  • Configure the Control related to:  PrimaryCategory
    • right-click on the field: PrimaryCategory and select Drop-Down List Box Properties
    • select the radio button: Get choices from an external data source
    • select the Data Source of Primary Categories
    • select the dialog to the right of the Entries section
    • select the column: Title
    • select Filter Data
      • select Add
      • select the Teams column (left comparison field)
      • retain “is equal to”
      • select the drop down and select “select a field or group …”
      • select “Show Advanced View”
      • select the ‘Main’ data source (expand the dataFields section)
      • select the field of PrimaryTeam
      • select OK;OK;OK;OK
    • the Value field will resolve to a period
    • the Display Name field will resolve to a period
    • check the box to show only unique entries
    • select OK
  • Configure the Control related to: PrimaryReason
    • * follow identical steps of PrimaryCategory

Complete

As always, feel free to leave feedback and let me know how SharePoint has affected you!

 

Advertisements
This entry was posted in InfoPath. Bookmark the permalink.

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