Master – Detail / Lookup Alternative for Opportunity Products (with Process Builder!)

Have you ever encountered a request where creating a lookup or master-detail relationship to the Opportunity Product object is required or preferred? Unfortunately, this is currently not possible as that bit of functionality is not supported. There are a few Ideas (links below) to request this feature so please up-vote them if this is an item you would like to have added to Salesforce.

https://success.salesforce.com/ideaView?id=08730000000BroaAAC

https://success.salesforce.com/ideaView?id=08730000000BrfaAAC

I have been able to replicate a lookup relationship by storing the Opportunity Product Id value into a custom text field in a custom object that would act as the child object. Using Apex triggers I would sync fields and record count totals that mimic cross object formula fields and roll-up summary fields. With the introduction of Process Builder, however, I wanted to see if there could be a way that did not involve Apex to maintain the simulated lookup relationship.

Scenario: Create a custom object to track sub items and delivery date of the product. The quantity  of each sub item related to an Opportunity Product should be subtracted from the product quantity and reflected in a field to store the remaining items not delivered.

Components used in this post

• Custom objects / fields
• Visualforce
• Custom JavaScript buttons
• Process Builder

Reviewing the Data Model

Since creating a lookup relationship to the Opportunity Product object is out of the question I will be creating a custom object to act as a mirror to Opportunity Product and inherit field values and any changes to the field values. I will call this object Opportunity Product Mirror or OPM (I’m sure you can think of a better name 🙂 ). I will then create the Sub Item object and configure it to be the child object the OPM object using a Master-Detail relationship.

The OPM object is connected to the Opportunity Product (OP) object via a look up field to the OPM object. This will be used to facilitate syncing field values from the OP record to the OPM record. There should always be a 1:1 relationship between these objects.

Roll-up summary fields on the OPM object are used to total the sub quantities of the Sub Item records. The lookup relationship between OP and OPM will be used in cross object formulas on the OP object to pull OPM roll-up summary field values onto the OP object.

1. If an OPM record has not been created and linked to the OP record, do so utilizing the AJAX toolkit.
2. If  an OPM record already exists, re-direct the user to the OPM detail page.

Here is the script:

{!REQUIRESCRIPT("/soap/ajax/34.0/connection.js")} 

var subMirrorId = '{!OpportunityLineItem.Opportunity_Product_MirrorId__c}'; 

if(subMirrorId == null || subMirrorId == ''){ 

 var oppProduct = new sforce.SObject("OpportunityLineItem"); 
 var oppProdMirror = new sforce.SObject("Opportunity_Product_Mirror__c"); 
 var mirrorLookup = '{!OpportunityLineItem.Opportunity_Product_MirrorId__c}'; 

 oppProduct.Id = '{!OpportunityLineItem.Id}'; 

 var queryRes = sforce.connection.query("Select UnitPrice, Quantity, Product2Id From OpportunityLineItem Where Id = '" + oppProduct.Id + "' LIMIT 1"); 

 var records = queryRes.getArray("records"); 

 oppProdMirror.Quantity__c = records[0].Quantity; 
 oppProdMirror.Sales_Price__c = records[0].UnitPrice; 
 oppProdMirror.Opportunity_Product_Id__c = '{!OpportunityLineItem.Id}'; 
 oppProdMirror.Product__c = records[0].Product2Id; 

 var oppProductMirrorInsertRes = sforce.connection.create([oppProdMirror]); 


 if(oppProductMirrorInsertRes[0].getBoolean("success")){ 
 oppProduct.Opportunity_Product_Mirror__c = oppProductMirrorInsertRes[0].id; 
 var oppProdUpdRes = sforce.connection.update([oppProduct]); 

 if(oppProdUpdRes[0].getBoolean("success")){ 

 window.top.location.href = '/' + oppProductMirrorInsertRes[0].id 
 } 

 else{ 
 alert('Error creating sub item container: ' + oppProdUpdRes[0]); 
 } 
} 

 else{ 
 alert('Error creating sub item container: ' + oppProductMirrorInsertRes[0]); 
 } 

} 

else{ 
 window.top.location.href = '/' + subMirrorId; 
}

If an OPM record already exists, the script will direct the user to the OPM page. Otherwise, if the Opportunity_Product_Mirror__c field is blank, a new OPM record will be created with its corresponding Quantity, Sales Price, Product, and Opportunity Product Id fields mirroring the same values as the Opportunity Product record’s fields. If the create operation is successful, the record Id generated will be stored in the lookup field on the Opportunity Product record and updated. The script will then re-direct the user to the page of the newly created OPM record.

The Return to Product button allows the user to return back to the Opportunity Product page. It is a custom URL button that utilizes the Opportunity_Product_Id__c field from the OPM object that stores the Opportunity Product record Id.

Syncing Opportunity Product Changes to OPM Object

Now that we have established a way to connect the OPM record to the OP record, we need to keep the OPM record up to date with any changes to the product. To accomplish that Process Builder will come into play.

The following fields will be synced:

• Quantity
• Sales Price

Here is the condition for the Process Builder flow that is created against the Opportunity Product object:

• Opportunity_Product_Mirror__c lookup field in not blank.
• Sales Price OR Quantity fields are modified.

The Update Records action is used with the Record option as [OpportunityLineItem].Opportunity Product Mirror as we want to update fields on the related OPM record.

The corresponding Quantity and Sales Price fields of the OPM object will be updated with the values of the Opportunity Product fields.

Each time an OP record is updated it will initiate this flow that will sync field changes from the OP object to the OPM object.

Roll-up Sub Items Quantities to the Opportunity Product Object

To create roll-ups of the sub items back to the OP object, we will start by creating roll-up summary fields on the OPM object.

The Sub Item object has a quantity field that represents a sub set of the items of the main product and also tracks when the item has been delivered. If we wanted to sum the quantity of each sub item record and calculate how many items remain un-delivered, a roll-up summary field will need to be created on the OPM object.

So now we have the Sub Item Quantity rolling up to the Quantity Delivered field on the OPM record. How do we move that value to the Opportunity Product record ? Well, cross object formula field of course! Using the lookup field to the OPM object on the OP object, we will be pulling the Quantity Delivered field value onto the formula field.

We’ll also add another formula to calculate Item Remaining as well to the OP object.

Deletion of Opportunity Products

As this is not a true master-detail relationship, Sub items are not deleted when an Opportunity Product record is deleted. To replicate this function we will override the Delete action of the OP object with a custom Visualforce page. The Visualforce page will execute JavaScript code that will perform the following.

• If the Opportunity Product record is related to an OPM record, delete the OPM first then delete the OP record.
• Otherwise, just delete the OP record.

Here is what the Visualforce page would look like.

<apex:page standardController="OpportunityLineItem">
   <apex:includeScript value="../../soap/ajax/34.0/connection.js"/>

   <script type="text/javascript">
     sforce.connection.sessionId = '{!$Api.Session_ID}';
     var oliId = '{!OpportunityLineItem.Id}';
     var oliMirrorId = '{!OpportunityLineItem.Opportunity_Product_Mirror__c}';
     var oppId = '{!OpportunityLineItem.OpportunityId}';
     
     If(oliMirrorId !== null && oliMirrorId !=== ''){
     var omDelRes = sforce.connection.deleteIds([oliMirrorId]);
     
     if(omDelRes[0].getBoolean('success')){
         
         var oliDelRes = sforce.connection.deleteIds([oliId]);
         
         if(oliDelRes[0].getBoolean('success')){
             window.top.location.href = '/' + oppId;
         }
         else{
             alert('Error deleting the product: ' + oliDelRes);
         }    
     }
     else{
         alert('Error deleting the product: ' + omDelRes);
     }

     }
     else{
          var oliDelRes = sforce.connection.deleteIds([oliId]);
         
         if(oliDelRes[0].getBoolean('success')){
             window.top.location.href = '/' + oppId;
         }
         else{
             alert('Error deleting the product: ' + oliDelRes);
         }
     }
     
   </script>

</apex:page>

So with a few custom buttons, Process Builder, and Visualforce pages we can (somewhat) replicate a lookup relationship to an Opportunity Product. Let’s hope this functionality will be supported natively in the near future.

Thanks for reading and I hope you found this helpful. I had planned this post before the new Lightning Design System announcement and before I attended this year’s Dreamforce so I wanted to do a follow-up post by building an interface to view and add Sub Items using LDS. Stay tuned by subscribing to the blog or  following me on Twitter.