Part 2: Exploring Salesforce Relationship Workhorses

Let’s dive into the Salesforce workhorse relationships: lookup, master-detail, and the less familiar, hierarchical (ok, so the latter isn’t really a ‘workhorse’).

Before we jump in let’s standardize on some language and lay down some concepts. In the previous post we briefly discussed that relationships have a direction: the relationship field (foreign key) on a Salesforce object references another Salesforce object (implicitly using the other object’s Id field as the primary key). Salesforce does not normally reference primary or foreign keys in the UI and the language can get confusing when discussing relating two objects. To simplify, we are going to use the standard relationship concept of child and parent. And this concept translates well since multiple child records can be related to the same parent record. The child object contains the foreign key field and the parent object the primary key.

In this simple ERD example, the relationship between the two is established by the reference field (foreign key) called Employer that contains the values of the related organization’s primary key allowing us to identify all the employees for each organization. Since we have Employee referring to Organization we consider Employee as the child in the relationship.

A key benefit of relationships is that it allows us to traverse between objects (e.g. a formula field on a child object can reference a field on the parent through the relationship field). Traversals are one-way — we can only go from the child to the parent. In fact, we can traverse multiple parent relationships—up to five (i.e. parent object, then grandparent object and then grand-grandparent object and so forth). We just can’t go the other way—have a field on a parent reference a field on a child object. As a side note: the platform does allow us to reference children–though in those instances it’s usually for all the children records and not via formulas (another post to discuss, maybe).

How do you know which object in the relationship is the child? It’s the object where you created the relationship field.

Salesforce generally refers to all the different relationship fields as being of type reference. Now let’s roll up our sleeves and get cracking.

Lookup relationship

Lookups are the most common of all the relationship fields. If we need to loosely relate two objects this is it. In fact, as soon as a new object is created we automatically get a couple of lookup fields: Created By, Last Modified By and Owner (okay so Owner isn’t a ‘typical’ lookup field—it can be polymorphic and we’ll talk about that in the next post, put that aside for now). These three lookup fields are all relationships to the User object. The implication is that a newly created object is automatically a child to the User object—in three different ways. Let’s take a closer peek at lookups.

Lookup field characteristics:

• Custom objects allow up to 40 custom lookup fields (Salesforce can increase the limit to 50): Since we are allowed to create so many of these they are ‘everywhere’. Salesforce initially limits to 40 for performance reasons; adding more lookups requires more computational work for Salesforce.
• Can be optional: We can elect to have the lookup be either required or optional (keep in mind changing an optional lookup to be required will cause users who are attempting to update existing records to potentially experience errors for those that aren’t populated).
• Allows for formula traversals and reporting: As noted a key benefit of relationships—referencing parent fields. Besides formulas we can also traverse relationships using custom report types which we’ll explore later.
• Related list of child records for most parent objects: Powerful benefit of the Salesforce platform: automatically providing a mechanism to display child records when viewing the parent.
• Loosely couples the child object to parent object:
  • If the parent record is deleted, the child record is not impacted.
  • Sharing/access to the child record is independent of parent record access
    While obvious, these are important to call out in that this is one of the major differences between lookups and master-detail (which we’ll cover next).

• Lookup filters: Sometimes we want to limit what kind of parent record is selected for the child; to help with that the platform offers a way to filter what parent records can be selected. Interestingly, the filter can be set as required or optional—which allows the user to choose to ignore the filter criteria.
• Self relationship: In some use cases we need to have a hierarchy of records within an object—lookups allow this by allowing the same object to be both a child and parent through the relationship (great examples on standard objects are the parent Account and parent Case relationships).
• Custom lookup fields are indexed (we’ll get to indexing)
• Record lock contention potential if the lookup field is configured to not be cleared (we’ll cover record locks in the next post)

Master-Detail relationship

This is an important relationship that provides some very interesting benefits that can be brought to bear to address certain use cases. The master in master-detail represents the parent object and the detail, the child object.

Master-detail field characteristics:

• Allowed to have up to two per object—that’s it; can’t ask for more.
• Objects on the master side of the relationship are able to use roll up fields to summarize detail records: Rollup fields are a major benefit of the master-detail relationship (lookup fields are super envious). We can use rollup fields to calculate values across the child records, with or without the use of filters, allowing child records to be included or omitted from the rollup calculation. Note that some fields on the child records cannot be used in rollup filters (usually certain kinds of formula fields)—a simple work around is to create a custom field that through an automation copies the value from the formula field into a static field which then can be used in the rollup (there are third party apps and code solutions to mimic rollup functionality for lookup relationships).
• Standard objects cannot be children (detail part of the master-detail relationship).
• Tightly couples the parent to the children:
  • Object on the detail side (child) has no visible owner field.
  • Sharing/access for the parent is applied to the child.
  • If the parent is deleted, so is the child.

• Always required to be populated: There is no way to set this field as optional. And if we pause to think about it, this makes sense—detail records don’t have an owner field so they need to be tightly coupled to a master (parent).
• Optionally can allow the child record to be reparented: This allows a detail record to be associated to a different parent. Defaulted to not allowing reparenting, it is usually based on the use case if this should be enabled.
• Lookup filters: Just like with lookups we want to limit what kind of parent record is selected for the child; to help with that the platform offers a way to filter what parent records can be selected. Interestingly, the filter can be set as required or optional—which allows the user to choose to ignore the filter criteria.
• Master-detail fields are indexed (haven’t forgotten, we’ll get to indexing).
• Record lock contention: This is more of a potential issue for master-detail relationships then lookups (in the next post we’ll go into more detail regarding record locks).
• Use on a junction object for many-to-many relationship (see previous post): In order for an object to work as a junction object it needs to be a child to two different parent objects. While lookups can be used to implement many-to-many relationships, master-detail provides more benefits (and a few considerations):
  • No ownership concerns for the junction object.
  • Deleting either parent record automatically removes the child.
  • The order in which the master-detail fields are created matter; the first relationship is the primary relationship and will cause the junction object to take on the look and feel of the parent.
  • Access to the join object is determined by examining the user’s access to both parents—if the user lacks access to one parent, the user will not be able to see the related junction records; additionally if the user has read/write access on one parent and read-only on the other parent, then the user will have the lowest common access which would be read-only for the junction records.

Hierarchical relationship

The hierarchical relationship type is a little-understood relationship type that can only be associated to the standard Salesforce User object. Its purpose is to relate users to each other in a different hierarchy as an alternative to the standard manager user field.

Hierarchical field characteristics:

• Only allowed on User object (can’t create standard lookup or master-detail relationships on User object).
• It’s a specialized version of the lookup relationship:
  • Automatically self-references back to the User object (no way to change it).
  • Similar to standard lookup: we can only create up to 40.

• In the UI, a hierarchical field—depending on what is enabled in the org—includes a drop-down of user types (e.g. User, Partner User, Customer Portal User); which will limit the lookup field to those users.
• Similar to lookup, hierarchical fields support filters (in addition to the user type drop-down described above).
• Users selected in the hierarchy cannot be the same user on the current record OR any users below that user in that hierarchy.
• Supported in approval processes: When configuring an approval process and determining the next approver Salesforce allows us to select from the standard user manager field or any hierarchical fields. To make things even easier, the platform allows us to create—on the fly while configuring the approval process–a new hierarchical field without having to build it in advance.

Here are some general Salesforce relationship considerations definitely worth noting:

• Some objects cannot have any relationships point to them (i.e. they can’t be a parent in a relationship). These are typically special purpose objects (e.g. Sharing or History) or the Activity (Task/Event) object. Salesforce won’t display them as an option as a parent when creating the relationship field.
• Some standard objects can be part of a lookup relationship while not being allowed to be part of a master-detail relationship (e.g. Product2).
• The user object does not allow for related lists (this would be unmanageable—consider how many objects have lookups to User to identify who created the record, who last modified it and who currently owns it).
• There are other considerations that are captured nicely here: (https://help.salesforce.com/articleView?id=relationships_considerations.htm&type=5).

Custom Report types

Salesforce’s reporting is powerful and flexible. One reason for that is the report type which act as a template defining what objects, how they can be related and what fields are available for a report. There is a limit to how many objects and how they can be related in a report type (without some kind of limit it would be possible to create almost impossible to run reports). A really cool—and sometimes overlooked—feature is that we can leverage to get around the limit is traversing to other objects from those defined on the report type via relationship fields.

Let’s take a look on how we can add these relationships to a custom report type:

1. Open an existing custom report type or create a new one (confirm or configure the object relationships for that report type making sure to save); return back to the main custom report type page and locate and click on ‘Edit Layout’.

2. Report type layouts control what fields are available and how they are grouped for building/managing reports. We can add fields to the main layout by dragging them from the list of all available fields on the right side of the page. To switch between the different objects in order to select other fields, select the name from picklist. What we are interested in is the link ‘Add fields related via lookup’ that is tucked under the picklist.

3. Clicking on that link will display a new modal—based on the selected object in the picklist–for all the available relationship fields and their associated objects.

4. Clicking on a relationship refreshes the modal and—here’s the best part—displays fields from that object to include in the report type. In addition, we can click on the ‘view related fields’ link for the second level relationships to traverse to another object (and so on).

Indexing

Indexing is a common database technique designed to speed up searches. When a field is marked as indexed that means Salesforce has added a—hidden from us—data structure that is a copy of that field. This data structure is much faster to search and is associated back to the object it is indexing allowing for a quicker reference. Unless there’s a ton of records, and we mean a ton (100,000s and more on a single object), indexes usually don’t matter greatly. Once we reach a large data volume threshold they become incredibly important in returning successfully targeted sets of records in a time-efficient manner. Here’s some more detail on SFDC’s indexing: https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm.

And this short article may be worth a peek: https://developer.salesforce.com/blogs/engineering/2015/06/know-thy-salesforce-field-indexes-fast-reports-list-views-soql.html

In part 3 of the Exploring Salesforce Data Relationship Series, “Special Relationship Tools“, we’re going to take a look at some special platform relationships that–while we can’t create–we can certainly leverage (we’re looking at you, polymorphic relationship).

 View the entire 5-part Salesforce Data Relationships series here

Series Navigation

Part 1: What’s a Salesforce Data Relationship?Part 3: Special Relationship Tools