0

there is a lookup relationship to the Opportunity object from the Order Object in my org. In every Opportunity there are 2 Opportunity split owners. I need to get the Opportunity Split owner who is not the Opportunity Owner.

Can anyone please let me know how can I achieve this? I need to send this info to an external system with all other other details related to the Order.

Is this possible through the SOQL? Thanks in advance.

Akshay Vasu
  • 445
  • 1
  • 12
  • 33

1 Answers1

0

I don't have orders enabled in my org so there might be typos but roughly like this:

Get all splits for this order's opportunity

SELECT OpportunityId,Opportunity.OwnerId, SplitAmount,SplitOwnerId,SplitPercentage 
FROM OpportunitySplit
WHERE OpportunityId IN (SELECT OpportunityId FROM Order WHERE Id = '...')

SOQL doesn't let you compare field to field (Opp owner to split owner), only field to value. So either you take this result and filter it out manually or you can cheat a bit. Try making a new formula field of type checkbox on OpportunitySplit. Call it IsOpportunityOwner__c or something, value Opportunity.OwnerId = SplitOwnerId. If that works OK you could then filter it in the SOQL

WHERE OpportunityId IN (SELECT OpportunityId FROM Order WHERE Id = '...')
AND IsOpportunityOwner__c = false
eyescream
  • 18,088
  • 2
  • 34
  • 46
  • Thank you for the answer. The latter isn't possible because there are already many Opportunity split records in the org. The thing is I have already written a soql query on orderlineitem to query Opportunity through relationship for orderLineItem to Order to Opportunity. Is there way I can query Opportunity Split also this way? – Akshay Vasu Aug 04 '20 at 11:50
  • Formula fields are bit like columns in a view in normal SQL. You wouldn't need to update old data, it'd just calculate on the fly. Give it a go. As for query to join everything... tricky. You probably can do something with subqueries like `select id, name, (select id, name from orders), (select splitownerid from opportunitysplits) from opportunity` but that can go only 1 relation "away". opp+splits and order+lines will be tricky, might need 2 queries. – eyescream Aug 04 '20 at 16:52