Working with Salesforce relationships is a little different from traditional relational database structures, but Salesforce has great tools for building custom data relationships, and most people can adapt quickly to the SOQL model. As with any other system, though, there are a few “gotchas” to watch for when designing Salesforce relationships. I ran into one of these “gotchas” just recently. I needed a lookup relationship from our custom object to the standard Product table, Product2. No problem, right? Create the lookup field in the custom table, and there it is.
Not so fast!
It Threw an Error. But Why?
I created the relationship using a lookup field and selected “Don’t allow deletion of the lookup record that’s part of a lookup relationship.” (i.e., keep the product if it has any child records). Unfortunately, Salesforce could not save the field. When I tried to save the data, I received this error message:
“Cannot Create Master-Detail Relationship
You cannot create a new Master-Detail relationship on an existing custom object if records already exist. You must first create a Lookup relationship, populate the lookup field with data in all records, and then change the relationship type to Master-Detail.”
The error message didn’t make any sense. This was a lookup, not a master-detail, and since the custom object was brand-new, it didn’t hold any records. I tried again and made absolutely sure it was a lookup. Same result.
What was going on here?
To review the basics, Salesforce includes standard “objects,” analogous to database tables, right out of the box. These objects are extremely useful because they come integrated as part of a full-fledged web application. Salesforce administrators can also add custom fields to the standard objects, create entire custom objects, and build relationships between standard and custom objects. Every addition to the database structure automatically integrates into the web application alongside the standard data.
Remind Me How Salesforce Relationships Work.
Salesforce relationships fall into two categories: “lookup” and “master-detail.” In a master-detail relationship, every detail record must have a related master record.
Think of a sales order. It has some header data, such as an order number and the customer name. It also has one or more line items for individual products ordered. The master order record holds the header data. The line item detail records hold individual products. Every line item has to connect with a master order record. An order table has a master-detail relationship with an order line item table.
On the other hand, a lookup relationship is just a simple reference. It’s much more flexible and can even be blank. In an SQL database, this relationship type would use an optional foreign key.
The Salesforce Object Reference includes basic documentation about these data relationships. Unfortunately, it doesn’t say anything about this error. So why can’t I save the new relationship field? Surely the documentation covers this use case somewhere.
I Don’t Have Time for Rocket Science. I Just Need to Know How to Build What I Want.
The solution was relatively straightforward: Always select “Clear the value of this field” when creating a lookup relationship to the Product table. But why isn’t this in the documentation?
As it turns out, this “gotcha” is mentioned in the Trailblazer documentation’s Object Relationships Overview.
Remember the SQL foreign key analogy for lookup-type Salesforce relationships? The same analogy applies when defining referential integrity in relationships. Just as we can choose cascade updates and deletes in SQL, we can choose what happens when a parent record is deleted. Either “Clear the value of this field” or “Don’t allow the deletion of the lookup record that’s part of a lookup relationship.”
Okay, I Understand the Basics. Why Is This Important, and Why Didn’t They Warn Me About It?
Salesforce ensures data integrity and makes sure there are no dead references. The system provides two ways of ensuring referential integrity. If you clear the key field when the parent is deleted, there won’t be any bad links. This is pretty flexible and loose. If you want a tighter relationship, use the don’t-allow-deletion option to get a little closer to the master-detail model.
Aha! That’s the key. The product object can’t have cascade-delete relationships. An explanation can be found in a warning panel on Salesforce.com, Inc.’s previously mentioned Object Relationships Overview page:
“Warning
Choosing ‘Delete this record’ also can result in a cascade-delete. A cascade-delete bypasses security and sharing settings, which means users can delete records when the target lookup record is deleted even if they don’t have access to the records. To prevent records from being accidentally deleted, cascade-delete is disabled by default. Contact Salesforce to get the cascade-delete option enabled for your organization.
Cascade-delete and its related options are not available for lookup relationships to business hours, community, lead, price book, product, or user objects.”
This warning block doesn’t explicitly call out the “Don’t allow deletion” option, but it does shed light on how Salesforce manages related data. Locking a record based on dependencies seems to fall into the same category as performing a cascade-deletion.
So the limitation is documented, sort of, but this detail may take a little digging and inference to figure out.
Disclaimer: References to documentation and error messages are accurate as of the Winter ’18 release. If the documentation is updated at some future date, that would be great, but then this post could be outdated.
What Happens If I Try to Change the Option Later?
Interestingly, when I attempted to edit the lookup field and change to “Don’t allow deletion,” the error message on save was much more useful:
“Cannot add a lookup relationship child with cascade or restrict options to Product2 (Related field: Domain)”
This confirms it. Any option that restricts a parent object based on child data falls into the same category as cascade-deletion. Gotcha, gotcha!
TL;DR
When creating a lookup to the Product table in Salesforce, always select “Clear the value of this field” instead of “Don’t allow deletion.”
Susco offers Salesforce development solutions for businesses and can help you understand how to use its capabilities. We offer guidance and insight to demonstrate how Salesforce can help maximize your business processes. Contact us at (504) 264-9343 to learn more about our products and services.