MS Dynamics CRM Look-up vs Connections

Out-of-the-box Connections are handy in situations where one entity record looks up multiple records from another entity or when there is a need to represent a many-to-many relationship between records. When designing our CRM solution, sometimes it’s necessary to think about how NOT to create new lookups to fulfill the business need while still representing the same data.

For example, say we need relate four contacts (AuthorizedRep1, AuthorizedRep2, AuthorizedRep3, and AuthorizedRep4) to a custom “project” record. Instead of creating four lookups to the contact entity on the project form, it is a better idea to create one or more connection roles and then connect each of the four contacts to the project record using appropriate connection roles.

What are the Benefits of using Connections over Lookups?

1. Connections help keep our forms clean. For example, instead of putting four lookups to a contact on a form, we can instead create four connections and use a sub grid to display the contacts on the form.

2. Multiple records can be connected using the same role. Lookups do not allow more than one record to associate with one lookup, nor do they allow us to define how the records are related.

3. Connections eliminate the need to establish custom relationships between entities. Connections are an out-of-the-box feature and can connect almost any entity using a connection role. Lookup fields do create entity relationships behind the scenes.

4. Representing many-to-many relationships using lookups creates intersecting tables in the background. Using connections avoids intersecting tables, still enabling many-to-many connections.

5. When there are multiple departments or business units using the same entity for unique purposes, a business-specific custom lookup might mean nothing (or may be ambiguous) to other businesses units using that entity. Using connections eliminates the confusion caused by creating department specific lookups for an entity.

6. Connection roles can be categorized by specific categories and business. Doing this helps with data retrieval and reporting.

7. With connections, no custom SQL index is required since it is an out-of-the-box feature and the fields are already indexed.

As we can see, there are a lot more advantages to using connections over custom lookups in terms of data representation and the data model. However, one disadvantage of using connections over lookups is that with connections, data retrieval is a little less intuitive. Regardless, the advantages still make connections a CRM designer’s choice over lookups.

Connections provide the following capabilities:

• An easy and flexible way to make a connection between two records of most Microsoft Dynamics CRM entity types. All customizable business and custom entities can be enabled for connections.

• An option to add useful information, such as a description of the connection and the duration.

• The ability to create connection roles that describe the relationship between two records, such as a relationship between a doctor and a patient, or a manager and an employee.

• A quick way to create multiple connections and roles for a particular record. For example, a contact may have many relationships with other contacts, accounts, or contracts. In each relationship a contact may play a different role.

• Information for building queries and creating graphs. You can search for all connections and connection roles for a particular record and create graphs and charts for visual representation of the connections.

• Support for workflows and auditing for automating and improving business processes.