Anaplan Use-Case 8: Dependent Dropdown / Filtered Picklist (Many-to-Many)

Have you ever been asked to show a limited set of selectable values (as dropdown) in a list-formatted line-item Y based (dependent) on another line-item X? Was the relationship between X and Y Many-to-Many? If yes, then feel free to dive into the context and problem statement mentioned in this article.

If the relationship between X and Y is 1-to-Many, then feel free to hop on to the article Dependent Dropdown (1-to-Many).

NOTE: This article is a segment of the Anaplan Use-Cases Series. If you haven’t already, I encourage you to go through the introductory blog Why Anaplan Use Cases Series? to understand the background behind this endeavor.

Context

Solve IT is a (hypothetical) publicly traded consultancy firm that has analyzed and solved hundreds of IT (Information Technology) problems for various Fortune 500 companies over the last two decades. The firm credits its success to its workforce — thousands of talented individuals it employs at various levels/titles.

The firm has a standard list of Annual Performance Plans to incentivize its employees at different levels. Each plan clearly states the maximum number of firm’s shares and bonus (as % of annual salary) that can be doled out to an employee based on his/her annual performance.

HR department has a carefully formulated mapping of job titles and annual performance plans, indicating the list of performance plans each job title is entitled to. For example, if an individual is at the level of Manager, he/she is entitled to one of the A1 and A2 performance plans only.

User Story

As HR Manager, I need to be able to assign employees to an annual performance plan, so that the Finance Manager can later consume this information for further financial planning and forecasting.

For each employee, I should see only relevant plans in the dropdown — ones that are mapped to the employee’s job title.

Analysis & Design

1) During Employee-to-Plan assignment, user wants to see only relevant plans for each employee — plans that are mapped to the title of the given employee. Hence, Plan (Y) will need to be a dropdown dependent on Title (X), implying that we need to use the Dependent Dropdown (aka Dependent Filter or Filtered Picklist) concept.

2) We addressed similar issue about Dependent Dropdown in another article, wherein we declared X (Hub) as the property of Y (Zone) for the solution. However, that problem involved 1-to-Many relationship between X and Y.

3) As seen in the Title-to-Plan mapping in the context, a single title can be linked to multiple plans, and conversely, multiple titles can be associated with the same plan. Since the relationship between Title and Plan is Many-to-Many, we cannot simply go ahead with declaring Title as a property of Plan dimension. Instead, this problem requires a different approach — using the Combination List concept.

Implementation

1) We already have Employee and Title-Plan mapping data in two different modules.

2) Create a flat combination list Combo with two list-formatted properties Title and Plan. The properties would hold the (Many-to-Many) relationship between the two dimensions, a pre-requisite for Dependent Filter implementation.

3) Setup a process to update (load valid combinations in and delete invalid combinations from) Combo list using the same instructions as mentioned in the Combinations List article. (While loading Combo list from ‘DAT02 Title-Plan Mapping’ module, don’t forget to load the Title and Plan properties of Combo dimension as well.)

4) Create a new list-formatted line-item Plan in ‘DAT01 Employee’ module to store plan assignments for each employee. To ensure that the new line-item displays only those plans that are associated with the title of the given employee, we apply Filter on the given line-item as Dependent on the data in the Title line-item (of the same module).

5) Create a UX board page to display data off the module ‘DAT01 Employee’, letting the user manage employee-to-plan assignments. Now, user should see only relevant items in the dropdown.

Conclusion

With the Dependent Dropdown problem (where Y is supposed to show picklist filtered on X), we must first determine the relationship between the two dimensions X and Y, and then establish that relationship as follows.

• For 1-to-Many, make X as a property of Y.

• For Many-to-Many, make both X and Y as properties of a Flat Combination List (of X and Y).

While we resorted to list and its properties to establish a relationship between X and Y, we could, very well, use a hierarchical list structure (parent-child relationship) as an alternate solution.