Introduction

This article describes how to filter dropdown values in InfoPath form based on the current user. There is a parent list "Master" with two columns - Agency(Choice) and UserId(person). There is also a child list "Child" with two columns - Title and Agency(Choice).

Master List


Child List

Form


Requirement

As shown in above screenshot, "Agency" dropdown shows all values but the requirement is, it should display values based on the current user. For example, if the current user is "Danish Islam", as per Master list it should show only "Agency1" and "Agency3" in the dropdown.


Solution

To meet this requirement, we need two data connections and a rule. 

Data Connection 1: Follow steps to create the first connection to receive dropdown values from Master list.
  1. On the form ribbon, click on "Data Connections" under "Data" section.
  2. Click on Add -> Select "Receive Data" -> Click Next -> Select "SharePoint Library or List".
  3. Provide Site URL -> Click Next -> Select "Master" list -> Select fields - Agency and UserId -> Click Next.
  4. Provide connection name (say Master) and check "Automatically retrieve data when form is opened" (If not checked).
Data Connection 2: Follow steps to create a second connection to receive user profile information of the current user.
  1. On the form ribbon, click on "Data Connections" under "Data" section.
  2. Click on Add -> Select "Receive Data" -> Click Next -> Select "Soap Web Service".
  3. Provide Soap URL (http://siteUrl/_vti_bin/UserProfileService.asmx)-> Click Next -> Select Operation "GetUserProfileByName" ->Click next -> Leave parameters field default (This will take current user by default).
  4. Provide connection name (say GetUserProfileByName) and check "Automatically retrieve data when the form is opened" (If not checked).
Populate Agency Dropdown: On form right click "Agency" field and select "Dropdown List box Properties" and follow below steps-
  1. Under List Box Choices, select "Get choices from External Data Source".
  2. Select "Master" as Data Source.
  3. For "Entries", click picker button and select "Agency" field. Click OK.
  4. Click OK again to close properties dialog box.

Rule:
Follow below steps to create rule to filter dropdown values-
  1. Select Data tab from ribbon and click Form Load
  2. Create a new rule. 
  3. Add a new action to set the field value. Click on picker button for "Field". In the dialog box, click on "Show Advanced View". From the dropdown select "Master" connection.
  4. Expand queryFields and select "UserId". Click OK. 

  5. Click on function button for "Value". Select "Insert Field or Group". Click on "Show Advanced View". From dropdown select, "GetUserProfileByName" connection.
  6. Expand dataFields -> propertyData -> Values to select value. Click "FilterData". Click Add.
  7. In the first dropdown select "Insert Field or Group", select "Name". In the third dropdown, select "Type Text" and type "PreferredName". Click OK.
  8. Click OK to close all dialog boxes.

  9. Add a new action to "Query for Data". In data connection dropdown, select "Master". Click OK.

  10. Preview the form, it should show filtered data.


Hope it helps.


See Also