1. Modeling
  2. Dimensions
  3. Picklists
  4. Create many-to-many filtered picklists

A picklist is a dropdown list that provides users with a list of valid values to select from. Filtered picklists contain more complexity and reference other lists in a model: the driver and filter lists. Selections made in a driver list determine what users can select in a filter list.

Many-to-many filtered picklists map multiple list items in a driver list with multiple list items in a filter list. For example, Executive, Senior Director, and Senior Manager in the Role list qualify for Plan A in the Compensation Plan list. The Senior Director and Senior Manager roles also qualify for Plan B.

Create your driver and filter lists in General Lists  in the model settings bar. 

Unlike one-to-many filtered picklists, many-to-many filtered picklists also require a valid combinations list to map the relationship between list items in the driver and filter lists. An example of the valid combinations list could be Compensation Plan Lookup. Ensure all three lists contain list items.

For example, the Compensation Plan Lookup list contains the Role Compensation 1, Role Compensation 2, and Role Compensation 3 list items.

Create list properties in the valid combinations list

  1. Select General Lists  in the model settings bar, select your valid combinations list, then select Open.
  2. Select Properties > Insert, then create two list-formatted properties. For example, Role (driver) and Compensation Plan (filter). 
  3. Select OK
  4. Select the driver list property, then select the ellipsis (...) in the Format column.
  5. Select List in the Type dropdown, then select the driver list (Role) in the List dropdown.
  6. Select OK.
  7. Select the filter list property, then select the ellipsis (...) in the Format column.
  8. Select List in the Type dropdown, then select the filter list (Compensation Plan) in the List dropdown. 
  9. Select OK.

Map list item relationships between driver and filter lists

  1. Select Grid View in the valid combinations list.
  2. Map the relationships between list items in the driver and filter lists.

ParentCodeRole (driver)Compensation Plan (filter)
Role Compensation 1

ExecutivePlan A
Role Compensation 2

Senior DirectorPlan A
Role Compensation 3

Senior DirectorPlan B
Role Compensation 4

Senior ManagerPlan A
Role Compensation 5

Senior ManagerPlan B

Create a module for the filtered picklist

  1. Select Modules  in the model settings bar, then select Insert Module.
  2. Insert two line items that represent the driver and filter lists on columns. For example, Role and Compensation Plan. In this example, you can also add the Employees list on rows, and remove Time. 
  3. Select OK.
  4. In Blueprint select the driver line item, then select the ellipsis (...) in the Format column. 
  5. Select List in the Type dropdown, then select the driver list in the List dropdown.
  6. Select OK.
  7. Select the filter line item, then select the ellipsis (...) in the Format column.
  8. Select List in the Type dropdown, then select the filter list in the List dropdown.
  9. Select Dependent in the Filter options, then select the driver list in the Filter based on data in dropdown to view combination examples. If you want users to access all list items in the filter list, select Allow access to unfiltered items in the Format dialog to enable Show All at the bottom of the filtered picklist.
The Format dialog shows Dependent formatting options for the Compensation Plan line item.
  1. Select OK.

In your module, you can map list items in the driver line item to valid values in the filter line item.


RoleCompensation Plan
Employee AExecutivePlan A
Employee BSenior DirectorPlan A
Employee CSenior DirectorPlan A
Employee DSenior ManagerPlan B
Employee ESenior ManagerPlan B