An entity relationship in Microsoft CRM defines how two entities interact with each other. A Microsoft CRM entity relationship definition includes three parameters:
Data relationship The nature of the data relationship between two entities (one-to-many, many-to-many, and so on)
Relationship behavior The behavior between two entities, and how Microsoft CRM uses that behavior to manage data when users take actions against one of the entities in the relationship
Entity mapping How Microsoft CRM maps common attributes that two entities share
Microsoft CRM includes hundreds of default entity relationships, and you can modify these default relationships or create entirely new entity relationships. You will almost always create at least one relationship between a custom entity and the Microsoft CRM default system entities. In reality, you will probably create between 5 and 50 custom entity relationships for each custom entity that you create, depending on the complexity of your data model. Consequently, it's critical that you understand entity relationships before you create any custom entities.
Important | You won't need a single line of programming code to create custom entities, but you will need a thorough understanding of the different entity relationship types and the custom relationships that Microsoft CRM supports. |
You can view all of an entity's relationships by using the entity editor in Microsoft CRM. Figure 6-1 shows the entity relationships for the Lead entity.
Figure 6-1: Default entity relationships for the Lead entity
This grid lists all of the Lead entity relationships that Microsoft CRM creates by default. To view the details of any one relationship, double-click a record in the grid. For example, double-click the record with the primary entity of Lead and the related entity of Contact, and you'll see the entity relationship editor shown in Figure 6-2.
Figure 6-2: Relationship editor
You will use the relationship editor to view and configure all of the entity relationship parameters. Let's review each component of an entity relationship definition, starting with the data relationship.
One purpose of entity relationships is to define the data relationship between two entities in the system. Unlike a traditional database, in which you might configure primary and foreign keys to manage data relationships, you will use entity relationships in Microsoft CRM to manage how data interacts within the system metadata. This metadata design gives you the opportunity to easily customize and manage the data relationships without having to touch the underlying system data (and database keys) in Microsoft SQL Server.
Microsoft CRM uses two types of data relationships:
One-to-many
Many-to-many
Let's review both of these concepts in more detail.
One-to-many describes a relationship between two entities in which a single entity possess one or more (many) related entities. For example, consider the relationship between the Account entity and the Contact entity in Microsoft CRM, as illustrated in Figure 6-3.
Figure 6-3: Entity relationship between the Account entity and the Contact entity
Figure 6-3 shows that each Account can have many Contacts, but you can assign only one Account to each Contact. You would describe the relationship between these two entities as follows:
The Account entity has a primary relationship to the Contact entity.
The Contact entity has a related relationship to the Account entity.
In other words, the relationship between the Account and Contact entities is both primary and related, depending on your perspective when you describe the relationship. Microsoft CRM uses mostly one-to-many relationships between the default system entities (Account to Contact, Account to Opportunity, Lead to Activity, and so on). As you browse through the Microsoft CRM customization section, you might notice that the user interface uses different terminology interchangeably to describe the one-to-many data relationship. Table 6-1 shows frequently used Microsoft CRM terminology that describes a one-to-many relationship between entities.
Perspective | Example 1 | Example 2 | Example 3 | Example 4 |
---|---|---|---|---|
Account | Primary relationship to Contact | One-to-many relationship to Contact | Parent relationship to Contact | Primary entity |
Contact | Related relationship to Account | Many-to-one relationship to Account | Child relationship to Account | Related entity |
Although Microsoft CRM uses different terminology to describe the one-to-many relationship, the user interface on an entity's form always displays one-to-many entities in a consistent manner. Figure 6-4 shows an example of the relationship between the Account and Contact entities of the Contact form. On the related entity's form (Contact), a lookup field appears so that users can select the primary entity (Account).
Figure 6-4: The primary entity displayed as a lookup on the related entity's form
Conversely, the related entity (Contact) does not appear on the form of the primary entity (Account). Rather, Microsoft CRM adds a link in the navigation pane of the primary entity to a page that displays all of the related entities in a grid view, as shown in Figure 6-5.
Figure 6-5: Related entities in a grid view
Remembering how Microsoft CRM displays primary and related entities in the user interface might help eliminate some confusion when you try to decide how to set up your custom entity relationships.
Now let's discuss another type of data relationship between entities in Microsoft CRM. Consider the relationship between the Marketing List entity and the Marketing List members, as illustrated in Figure 6-6.
Figure 6-6: Many-to-many relationship between Marketing List and members
Figure 6-6 shows that you can create many marketing lists in Microsoft CRM, and then you can assign multiple members to each list. In addition, you can add members to multiple marketing lists. We would describe this relationship as many-to-many. The Microsoft CRM user interface always uses grids to display many-to-many relationships between two entities. Therefore, any time you see a lookup field on a form, you know that a one-to-many relationship exists between the two entities.
More Info | Because the Account and Contact entities have a one-to-many relationship, you can assign only one account to each contact. (In a many-to-many relationship, you could assign multiple accounts to a contact and multiple contacts per account.) However, Microsoft CRM does allow you to track the relationships between multiple accounts and contacts in a many-to-many environment by using relationship roles as explained in Chapter 2. |
Even though Microsoft CRM uses many-to-many relationships between some of its default system entities, you cannot create custom many-to-many relationships. Although you cannot create a custom many-to-many relationship directly between two entities, you can mimic a many-to-many relationship by using an intermediate entity. The final output in the user interface might not appear exactly as you want it to, but it can meet the needs of most people. We'll explain how to do this later in this chapter.
In addition to understanding how Microsoft CRM structures the data relationship between entities, you must understand the relationship behavior of entity relationships before you can map out your own custom entities. Entity relationships always exhibit one of two behaviors:
Parental
Referential
In the case of parental relationship behavior, actions that you take against the primary entity will also apply to its related entities. With referential relationship behavior, any actions against the primary entity apply only to that entity and to none of its related entities. In Microsoft CRM, only five actions are affected by relationship behaviors:
Delete
Assign
Reparent
Share
Unshare
Consequently, if you took any other action against an entity in Microsoft CRM (such as running a workflow rule), that action would not be affected by the entity's parental or referential relationship behavior.
Tip | What's the difference between the assign action and the reparent action? When you assign an entity, you change the owner of the record from one user to a different user. When you reparent an entity, you change a record's parent entity by using the lookup tool. Changing the parent account of an account is an example of reparenting an entity. |
It's important to understand the differences between parental and referential behavior because you need to specify relationship behavior any time you create a relationship between two entities. We expect that you'll always create at least one entity relationship for every custom entity you create. However, Microsoft CRM also lets you modify the default relationship behavior between the default system entities. Let's review parental and referential behavior in more detail.
If the relationship between entities exhibits parental behavior, actions applied to the parent entity will propagate down to all of its children entities. If you deleted an Account record (the primary entity) like the sample record Coho Vineyard shown in Figure 6-7, Microsoft CRM would delete all of that record's related data, such as its Activity, Note, custom entity, and Opportunity, because of those entity records' parental relationships to the Account entity.
Figure 6-7: Relationship behaviors determine how Microsoft CRM cascades actions
Likewise, when Microsoft CRM deletes the custom entity record and the Opportunity record, it determines whether it should also delete their related entities based on the various relationship behaviors.
In this Coho Vineyard example, Microsoft CRM would delete the Notes and Activities related to the custom entity record and the Opportunity because a parental relationship behavior exists between the those entities. The software refers to this concept of working down the primary and related entity tree as cascading.
More Info | All of the default system entities, such as Leads, Accounts, and Contacts, possess a parental relationship with Activities and Notes by default. Therefore, any action you take against the parent entity will cascade down to all of its Activities and Notes. For example, if five active and two completed Tasks exist for an Account and you reassign that Account to a new user, all of the Tasks (active and completed) will also be assigned to the new user. Many customers want to reconfigure this default relationship behavior between system entities because they do not want to change the owner of completed Activity records. We explain how to change this later in the chapter. |
You can configure custom entities to have a parental data relationship with system entities, but only if the custom entities have a referential relationship behavior to the default system entities. Custom entities cannot possess a parental relationship behavior to one of the default system entities.
In the case of referential relationships, actions taken against the primary entity do not cascade down to its related entities. To demonstrate referential relationship behavior, we modified the previous example by adding a custom entity B with a referential child relationship to the Project custom entity (see Figure 6-8).
Figure 6-8: Referential behavior example
If you deleted the Coho Vineyard Account in Figure 6-8, Microsoft CRM would delete all of the records except custom entity B and its Note. Microsoft CRM would not delete custom entity B because it contains only a referential relationship to the Project custom entity. Microsoft CRM would delete the Project custom entity because of the parental relationship behavior to its primary entity Account.
The referential, restrict delete option is a special kind of referential behavior. When you configure this relationship type between a primary entity and a related entity, Microsoft CRM does not allow the user to delete the parent entity if that entity has any related entities. Rather, Microsoft CRM displays the error message shown in Figure 6-9 to the user.
Figure 6-9: Error message displayed when users try to delete a restricted entity that possesses children entities
Now that you understand the difference between parental and referential relationship behavior, let's examine how you use the relationship editor to configure these relationships in Microsoft CRM. Figure 6-10 shows the relationship editor when you create a new relationship.
Figure 6-10: Entity relationship editor
In the Relationship Behavior section, you can choose from one of four values in the Type of Behavior list:
Parental
Referential
Referential, Restrict Delete
Configurable Cascading
If you choose parental or referential behavior, Microsoft CRM applies that relationship behavior to the actions taken against the primary entity.
If you choose Referential, Restrict Delete behavior, Microsoft CRM applies referential behavior to all actions except the delete action. As we explained earlier, Microsoft CRM will not allow users to delete an entity with related entities if you choose this behavior. Consequently, the software does not need to know how to cascade the delete action to related entities (because it can't possess any related entities when you delete it).
If you choose configurable cascading, Microsoft CRM allows you to specify different cascading behaviors depending on the action that users take against the parent entity. For example, you could set up parental cascading behavior for delete actions against the parent, but then change the cascading behavior of the assign action to referential behavior. For the assign, share, unshare, and reparent actions, you can configure one of four cascading rules:
Cascade All Perform the action on the parent entity and all of its children entities; equivalent to parental.
Cascade Active Perform the action on the parent entity and all of its children entities where the status is active or open. You might select this option if you want to maintain a history of which users owned the previously completed Activities (Tasks, Phone Calls, and so on).
Cascade User Owned Perform the action on the parent entity and only those child entities for which the entity owner matches the parent entity owner.
Cascade None Perform the action on the parent entity only; equivalent to referential.
A simple example might better illustrate how these cascading rules would work in the real world. Figure 6-11 shows an Account with four Tasks (two active, two completed) attached to it.
Figure 6-11: Account with four Tasks
If you take an action against the Account (the parent entity), such as changing the Account owner from Bill to Fred, the cascading behavior of the relationship between the Account and Task entities determines how Microsoft CRM applies the same action (assign) to the children entities. Table 6-2 shows how Microsoft CRM would assign the owners of the four tasks for each of the cascading behavior settings.
Type | Entity | Status | Original Owner | Final Owner | |||
---|---|---|---|---|---|---|---|
Cascade all (parental) | Cascade active | Cascade owned | Cascade none (referential) | ||||
Parent | Account | Active | Bill | Fred | Fred | Fred | Fred |
Child | Task 1 | Active | Jane | Fred | Fred | Jane | Jane |
Child | Task 2 | Active | Bill | Fred | Fred | Fred | Bill |
Child | Task 3 | Closed | Jane | Fred | Jane | Jane | Jane |
Child | Task 4 | Closed | Bill | Fred | Bill | Fred | Bill |
For the delete action, you can configure one of three behaviors:
Cascade All Delete the parent entity and all of its children entities; equivalent to parental.
Remove Link Delete the link between the parent entity and the children entities, but do not delete the children entities; equivalent to referential.
Restrict Prevent the user from deleting an entity that possesses child entities; equivalent to referential, restrict delete.
Although a Merge picklist appears in the Relationship Behavior section, you cannot configure different relationship behaviors for that action. Merge always uses the cascade all (parental) behavior.
Note | The merge functionality applies only to the Lead, Contact, and Account entities. |
Entity mapping is the third component of the relationship definition between two entities. Not every relationship between two entities includes an entity mapping, although every relationship must include a data relationship and relationship behavior. Mapping allows you to specify common attributes that two entities share. Entity mapping provides the benefits of saving your users time and reducing data entry errors by automatically mapping data from the primary entity to its related entity at the time that Microsoft CRM creates a related entity.
For example, if you add a related Contact to an Account, the default entity mapping between these entities automatically populates the address of the Contact with the same address as the Account. Without mappings, the user would have to retype the address information into the Contact, even though it's identical to the address of the Account.
Important | Microsoft CRM maps entity attributes only at the time that it creates a related entity. Mapping does not continually keep data synchronized. Therefore, if the address of the Account (primary) record changes, Microsoft CRM will not automatically map these changes to the Contact (related) records. This type of synchronization would require additional system customization with custom programming. You could also use the bulk edit feature in Microsoft CRM to update the address of multiple Contact records at one time. |
Some of the scenarios in which Microsoft CRM uses entity mappings include:
Adding a related entity to a primary entity by using actions (such as clicking add Related Activity from Action in the entity menu bar)
Adding an Activity to an entity by using actions (clicking add Related Activity from Action in the entity menu bar)
Converting a Lead to an Account, Contact, and Opportunity
To view all of the entity relationships that include a mapping, click the Type list in the Relationships section, and then select Mappable, as shown in Figure 6-12.
Figure 6-12: Selecting mappable relationships
When you open the relationship editor for any mappable relationship, you see a Mappings link in the left-hand navigation pane. Click the Mappings link to display the mapped attributes for the relationship. Figure 6-13 shows the attribute mappings between the Account and Contact entities.
Figure 6-13: Attribute mappings between the Account and Contact entities
Each mapping consists of a source attribute and a target attribute, and you can see that Microsoft CRM already mapped attributes such as the address information between Account and Contact. Therefore, when you create a related Contact for an Account, Microsoft CRM automatically pre-populates the target attributes of the Contact with the values from the source entity (Account). Figure 6-14 shows a graphical representation of the Account and Contact mapping.
Figure 6-14: Mapping attributes between the Account and Contact entities
We did not include all of the Account and Contact attributes in this figure because of space considerations, but you can see that the Account entity includes attributes (such as account-number and creditlimit) that do not map to the Contact entity. Likewise, the Contact entity includes attributes such as birthdate and childrensnames that don't apply to the Account entity. The point we're trying to illustrate is that you don't have to map all of the attributes from one entity to another, just the ones that make sense.
Microsoft CRM includes thousands of attribute mappings by default, but you will probably need to create new attribute mappings or modify the default mappings. Consider an example in which you added a custom picklist attribute with a schema name of new_customerrating to both the Account and Contact entities (see Figure 6-15). Although both entities use the same schema name of customerrating, you must still create a mapping between these two attributes if you want Microsoft CRM to automatically populate the customerrating field when you create a related Contact from an Account.
Figure 6-15: Mapping custom attributes between the Account and Contact entities
To create a custom mapping between two attributes, you must meet the following conditions:
Both attributes must use the same data type.
The length of the target attribute must be equal to or greater than the source attribute.
You can specify an attribute as the target value only one time. However, you can map an attribute from the source entity to multiple target schema names.
Microsoft CRM provides two methods for creating mappings. You can manually map attributes one at a time, or you can use the generate mappings feature to let Microsoft CRM automatically generate mappings for you. When you use the generate mappings feature, Microsoft CRM creates an attribute map if two attributes share a schema name and a data type.
In the Customization section, double-click an entity record and click Relationships in the navigation pane.
Double-click the entity relationship that you want to modify the relationship mapping for, including adding a new mapping.
The relationship editor window will appear; click Mappings in the navigation pane.
To add a new mapping, click New in the grid toolbar. To modify an existing mapping, double-click the mapping you want to modify.
A dialog box appears with the source entity attributes on the left and the target entity attributes on the right.
Select the source and target attributes that you want to map, and then click OK.
An "Attribute Mapping" message appears.
Save the relationship, and then publish the entities that you customized.
Tip | You can map two attributes even if they use different schema names. |
To use the generate mappings feature instead of manual mapping, simply click More Actions in the grid toolbar, and then click Generate Mappings. Please note that when you generate mappings, Microsoft CRM removes all of the existing mappings between the entities and creates new mappings.
Creating mappings for attributes of the picklist data type requires additional steps to ensure that the values map correctly. When you map two picklist attributes together, you must also make sure that the picklist values match up accurately. When a user looks at a drop-down list on a form, Microsoft CRM displays the picklist label to the user. However, when Microsoft CRM maps two picklist fields together, it uses the picklist value, not the picklist label.
To demonstrate this nuance, let's add a new value to the Industry picklist on the Lead entity. Microsoft CRM includes a default mapping between the Industry attribute of Lead to the Industry attribute of Account. When you convert a Lead and create an Account, Microsoft CRM uses this mapping to automatically populate the Account industry with the same value as the Lead industry.
If you wanted to add a new industry to the picklist called Software, you would add this value to both the Lead and Account attributes to keep the values in sync.
When you click Add, you enter the picklist text "Software" in the Label box. The text in the Label box is what the user will see in the drop-down list on the form. However, note that Microsoft CRM also uses a read-only integer picklist value along with the picklist label. When Microsoft CRM maps the Lead industry to the Account industry, it uses the picklist integer value to set the value on the account. Table 6-3 shows how Microsoft CRM would map different picklist values based on sample picklist values and labels.
Source picklist value (Lead) | Source picklist label (Lead) | Target picklist attribute value (Account) | Target pick-list attribute label (Account) | Match? | Resulting picklist value (Account Record) | Resulting picklist label (Account Record) |
---|---|---|---|---|---|---|
1 | Consulting | 1 | Consulting | Yes | 1 | Consulting |
1 | Consulting | 1 | Professional Services | Yes | 1 | Professional Services |
1 | Consulting | 2 | Consulting | No | Blank | Blank |
1 | Consulting | None | None | No | Blank | Blank |
Microsoft CRM always uses the picklist value to determine matches for picklist fields. Consequently, it is critical that you make sure that the integer values of the picklist always match correctly.
The picklist integer value is a read-only field that always increments by one when you add new picklist values. Therefore, if you add multiple picklist values at one time, always enter them in the same sequence on both entities. If you get out of sequence or if you accidentally delete a picklist value, you will need some creative reworking to get the picklist values mapping correctly again because you can't edit the picklist integer.
Consider an example in which you added three new Industry picklist values, but you mistakenly entered them in a different order on the Lead and Account entities.
If you entered the three new industry values in the order shown, Microsoft CRM would give them the picklist values listed. However, if you converted a Lead with the Real Estate industry value selected, Microsoft CRM would map that to the Software industry of the Account record because it matched the integer values of 35 on both attributes. Obviously, you would want to modify the system so that the industry values mapped correctly. However, you cannot simply edit the picklists integer values because they are read-only values.
To fix this problem, you could delete the existing Real Estate and Software picklist values from both the Lead and Account entities. Then you would create two new picklist values on both entities, making sure to enter them in the same order. Microsoft CRM would assign two new picklist values, which would then map correctly when users converted a lead. If you don't have a lot of records assigned to these picklist values yet, it might be easier to just change the labels and update your lead records accordingly
This same concept of matching picklist values also applies to entities with status reasons and state attributes such as Account, Lead, and Opportunity. Please make sure that you match up the values for all of the status reasons for each of the different states between two entities.