You need to sign in to do that
Don't have an account?
Nathan Prats 17
SOQL Query - Opportunity Owner NOT EQUALS TO Account Owner
Hi,
I'm trying to build a query to returns a list of Opportunity IDs where the Opportunity Owner doesn't match the Account Owner.
I've tried this :
SELECT Id FROM Opportunity WHERE OwnerId != Opportunity.Account.OwnerId.
But it doesn't work.
Any Idea ?
What you need is a formula (Checkbox or Boolean type) field on Opporutnity Object, which will have the following formula.
If you name your new formula field as Ownership_Check__c, your SOQL query would be as follows. Hope this helps.
All Answers
What you need is a formula (Checkbox or Boolean type) field on Opporutnity Object, which will have the following formula.
If you name your new formula field as Ownership_Check__c, your SOQL query would be as follows. Hope this helps.
SELECT Id, Name, OwnerId FROM Opportunity WHERE OwnerId NOT IN (SELECT OwnerId FROM account)
If a user owns an Account1 and creates Opportunity2 on Account2 (owned by someone else), Opportunity2 will not be returned by your SOQL query.
I guess Nathan needs Opportunity2 listed because the opportunity and the related account are owned by two different users.
Unfortunately I don't think this can be done in a single shot SOQL query anyways. I'm not going to have the right technical explanation here, but your WHERE clause can't compare and field value against another field value. You have to compare it against a specific value or list of values or another query. (see "semi-joins" here (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm))
An alternative though is to create a custom formula field on your opportunity that compares the owner Ids and returns a boolean. Something like...
OwnerId = Account.OwnerId
...then use that in your query...
SELECT Id FROM Opportunity WHERE Opp_Owner_Equal_Account_Owner__c != true
You can come up with a better name for your custom formula field of course. :) Using formula fields in this way is a long time common solution for some of the trickier queries you may need to do.
Use this soql query.It will work perfectly.
SELECT Id, Name, OwnerId FROM Opportunity WHERE OwnerId NOT IN (SELECT OwnerId FROM account)