Lookup Relationship Status: It's complicated

Lookup relationship fields

Salesforce allows the linking of different object data using lookup fields. But how do you determine the right lookup type to use? Let's review the different options.

What kind of lookup relationships can I use?

Relationships between objects in Salesforce are created though custom field types called Lookups. These field types can be on standard and custom objects and enable linking of record data from one Salesforce object to another object. For example, linking one or more Opportunities to an Account.

There there are two (2) lookup field types to choose from:

1. Lookup
2. Master-Detail

Both lookup types can can be used to connect Salesforce objects and relate data together to represent 1:Many / hierarchical relationships. Without getting too serious, let’s put some labels on our relationship setup:

1. Related (child / many) record = Many (e.g. opportunity)
2. Lookup (parent / 1) record = 1 (e.g. account)

So now that we know the types of lookup relationships we can have, which one do we want to use?

Feature comparison

Feature	Lookup	Master-Detail
Always Required	No	Yes
Default Sharing rules	Independent	Controlled by Parent
Cascade deletion	No 1	Yes
Record Access	Independent between related and lookup records	Same as Parent record
Report on Track History	Yes	No 2
Create Rollup Summary Fields	No	Yes
Rollup Summary Filter	N/A	Yes 3

Notes

1. Can be requested from SFDC - https://help.salesforce.com/articleView?id=000199350&type=1
2. Workarounds exist
   1. Process Builder / Flow - https://www.adminhero.com/building-better-field-history-tracking/
   2. Rollup helper app - https://appexchange.salesforce.com/appxListingDetail?listingId=a0N30000009i3UpEAI
   3. Declarative lookup rollup summary app - https://github.com/afawcett/declarative-lookup-rollup-summaries/blob/master/README.markdown#packaged-release-history
3. Only supports AND criteria for multiple conditions

So which lookup type should I use?

So you are ready to create a Salesforce object lookup field but still don’t know which is the right one for you? Ask yourself these questions before deciding:

1. Can a lookup field ever be blank (null) when a new related record is created or edited?
2. Does ownership of a related record need to be different to the lookup record?
3. Would cascade deletion of related child records cause data issues?
4. Will record access to related records be different to lookup record access?

If you answered Yes to any (or all) of these questions then a Lookup relationship is the way forward. Otherwise, you can use a Master-Detail lookup relationship field.

But what if I want to change an existing lookup relationship?

If you want to take your Salesforce lookup relationship to the next level, you can convert a Lookup to a Master-Detail type. Before taking the plunge, take a moment to reflect on the following:

1. Is the lookup field populated on all related records?
2. Do you want to report on track history field changes?
3. Are there a lot of reports based on the existing field relationship type?

Additional considerations before proceeding

• The Master-Detail field lookup type requires that all lookup fields are populated on related records prior to conversion.
  • If you change a relationship field type on an object from lookup to master-detail with blank lookup field values, you’ll get an error reminding you to update any missing data first then convert the field type.
• Track history object reporting is not supported for master-detail fields as of the Winter 19 release.
  • This is due to the way sharing rules work on the platform.
  • You could replicate the field history object functionality by creating a custom object and a process builder that creates a record when field change events occur. See https://www.adminhero.com/building-better-field-history-tracking/ as an example.
• Existing reports based on the previous field relationship will need to be recreated.
  • The previous reporting object relationships are no longer valid after changing the field type to a Master Detail type. This could create a lot of work to recreate existing reports. A silver lining to this is the opportunity for some system maintenance to see if reports are actually being used and to delete reports that are outdated or no longer being run.  See the "Impact of Relationships on Reports" section of the Considerations for Object Relationships help article for further details. 

Recap

Data relationships in Salesforce can be daunting. But asking yourself a few questions before jumping in can help find the right relationship solution for long-term related data happiness.