Salesforce (Data) Relationships Matter


The ability to maintain data quality is dependent on establishing the right relationships between objects and configuring appropriate controls over data entry.  Too often this only gets the focus it deserves a year or two after Salesforce is implemented when data quality is poor and causing problems with reporting, integration or workflows.

Salesforce Data RelationshipsThere are two types of relationships available to connect objects together (master-detail and lookup) each of which has further options.

Chose the relationship type which best represents the real-world relationship between the business concepts the objects mirror then apply the tightest options possible to control over data quality.

Don’t add relationships to objects until you fully understand the choices available!

Master-detail (parent-child) relationships

This is always a required relationship as detail records don’t have an owning user and access control is managed at the master level.  When a master record is all related detail records are deleted as well.  There can be no more than two master-detail relationship in an object and only one if the object is the master of another relationship.  Standard objects cannot be detail records and Leads/Users cannot be master records.

There is an option to allow users to change the parent (master record) of a detail record.

A master record can have no more than 10,000 detail records.

Lookup (cousin) relationships

This relationship is not automatically required and has no effect on record access.  If the relationship is set to be required a record can’t be deleted if other records are related to it.

If the relationship is not required you can choose whether to prevent a record being deleted if other records are related to it, or to allow deletion by automatically clearing any lookup relationships. 

Setting a lookup to be required has a significant impact when creating a partial copy sandbox which contains objects with more than 10,000 records.  The partial copy sampling process will retain relationships which are required hence the related record will be copied as well.  If the lookup is not required then values can be lost from lookup relationships during the sampling process.

Filtering relationships

Applying an active filter to a relationship means when the user searches for a related record only those which match the criteria will be presented.  An active filter which is required cannot be bypassed.  An active filter which is optional can be bypassed.

An object can have no more than 5 active filtered relationships.

Validating relationships

Applying validation rules to a relationship can be used to check (when the record is saved) that a related record meets specific criteria.  This can be helpful if you hit the limit of 5 active filters as the limits on validation rules are higher (20 per object in Professional Edition and below, and 100 in Enterprise Edition and above).  Validation rules are also more powerful and more flexible that lookup filter criteria.

Active filters provide a better user experience than validation rules as they limit the records able to be selected, rather than allowing records to be selected then blocking the save.

Hints to guide selecting the right relationship type

  • If a record cannot exist by itself and access is controlled by the parent use master-detail.
  • If a record cannot exist by itself but has its own access controls use a required lookup.
  •  Use active filters if it does not make sense to allow connection to any record.
  •  Use validation rules to limit which records be related if there are already 5 active filters.

References

Limits – http://resources.docs.salesforce.com/198/17/en-us/sfdc/pdf/salesforce_app_limits_cheatsheet.pdf

Cheat sheet – https://resources.docs.salesforce.com/200/latest/en-us/sfdc/pdf/salesforce_filtered_lookups_cheatsheet.pdf

Lookup Filters – https://help.salesforce.com/HTViewHelpDoc?id=fields_lookup_filters_notes.htm

 

Comments are closed.