I have a child object that I need to create which would be related to Opportunity. Let's call it RepPay. I wanted to know what is the best way to create this relationship for querying purposes which I will need to perform later. I want to filter out Opportunities through SOQL bases on a boolean value on the RepPay object. The goal of this is to keep track when one of our reps has been paid. Currently we have a boolean on the opportunity object however we want to move this boolean into another related object so there is no issue with resource locking happening on the opportunity object when we try to update the boolean through .NET code.

I have three methods that I considered doing, but I am not sure which one follows best practices:

  1. Create a Lookup field on the Opportunity object. This lookup would get set when the opportunity is first created and the boolean value on the RepPay object would be defaulted to false. Then in the SOQL query reference this look up object by saying [Select Id, RepPay__r.Id, RepPay__r.Paid From Opportunity where Id = 'asdfadf' and RepPay__r.Paid = false] to obtain opportunities where our rep hasn't been paid. (Side note: I noticed with this solution only the ID of the RepPay object is displayed on the Opportunity page, I'm not sure if there is a way to display something other than the ID of the lookup object, maybe the boolean field on the RepPay object? One solution I found was creating a lookup from the RepPay object to the Opportunity object which would display in the Related Lists area of the Opportunity, however creating the lookup on my child object doesn't allow me to use dot notation when querying from the Opportunity object, so I think that I would need to create a lookup in both directions to accomplish my goal with this method)

2.Use a subquery to pull all opportunties which don't have RepPay object related to them, that way we can update those opportunities that have not had their reps paid. (There is additional criteria which qualifies the opportunity) Ex: SELECT Id from Opportunity WHERE Id NOT IN (SELECT Opportunity_Id__c FROM POC_Ob ject__c)

3.Use a master-detail relationship on the RepPaid object and then use a rollup field on the opportunity object to keep track if there is a reference to RepPaid. If there is not at least one reference then we will assume this rep has not been paid yet. Then my code will create the relationship once an opportunity meets the criteria for paying the rep. Therefore creating an entry in the RepPaid object and referencing it to the opportunity.

My question is would Method 2 or method 3 have a better performance time. I'm not sure when the rollup field is usually calculated and if there are hundreds or thousands of opportunities if this would cause a performance issue. Method 1 is a little extra work since there needs to be two lookup references created and set for everything to be displayed properly.

Please let me know if any of this doesn't make sense and I can clarify/provide some better examples.

Thank you!

  • I think you're putting the cart before the horse here. Are you currently experiencing row locking issues? If you are, which SObject is the row lock happening for? The current approach (Boolean on Opportunity) sounds the most appropriate. #2 would run into query selectivity issues, and #3 would likely only exacerbate any row lock issues you're currently having. – Derek F Dec 22 '20 at 18:01
  • Hi Derek, sorry for the late response, let me clarify, so there was a locking issue on Opportunity, but I believe that was when we were doing a bulk update. The current issue that is also happening on opportunity object is related to validations on certain fields that sometimes get modified through other means which fail validation when we try and update the boolean value on the opp object. So the goal was to remove the boolean from the opp object and have it in a standalone object in order to prevent validations from being triggered on opp object. So looking for the best method to handle this – leko Jan 13 at 20:19
  • Could you maybe explain what you mean by query selectivity issues? – leko Jan 13 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.