Use Many-to-Many Relationships to Build a Better Multi-Select Picklist
I have undertaken to find a better way to implement Multi-Select Picklist functionality. The Multi-Select Picklist is desirable in many instances:
- Contractor (Account) Trades
- General Contractor
- Storage and Transportation
- Contact Hobbies and Interests
- Horseback Riding
- Ice Hockey
- Mountain Climbing
- Customer Products Purchased
- SonicWall TZ200
- SonicWall TZ210
- Dell PowerEdge T100
- PGP Full Disk Encryption
- Office 365 – E1 Plan
- CRM Online
- Prospect Nurture Marketing Areas
- Can be similar to Products Purchased, as shown above.
More appropriately termed Multi-Select Lookups; two major factors in why a Many-to-Many Lookup is better than a Multi-Select Picklist, is that the data is relational. We all love relational databases. It’s what makes CRM so powerful. We can maintain the list of hobbies or products in one place, and reference it from Accounts, Contacts, Opportunities, etc. I mean, the data is structured, so it can be referenced with lookups, and searched with lookups. You don’t want to have to be typing in the text values of all the various hobbies, or manually adding a bunch of products to a big Advanced Find OR Statement to find the Contacts that might be interested in tickets you have for the big game.
The many-to-many lookup does all this and a little more. Imagine, you can also put the Contractor’s license number in the Trade relationship field, or the expiration date of a service contract — or if they’re a football fan, who there favorite team is…
You can start by visualizing a manual many-to-many picklist as it’s own custom “intersection” entity with one-to-many relationships with the two main entities.
We’ve also delivered a solution that will bring multi-selections into CRM from a web form, https://go-nmc.com/vendors.aspx, and populate the many-to-many in a CRM Account.
Another nice feature is that the many-to-many attributes or characteristics can be (conditionally) shown in the grid of the form of the Account or Contact. For example, we can show the Contractor Trades directly on the form in a sub-grid, if (and only if) the Account is a Vendor.
The list can be varied, but the aim is the same — provide an easy way to flag a customer, vendor, or contact with a sub-set of characteristics from a commonly used set.
This solution will work with Microsoft Dynamics CRM 2011 Online as well as on-premise.