Answered by:
FetchXml --> List all accounts that don't have any opportunities

Question
-
Hi
I want to create a FetchXML query that returns only the accounts which do not have an opportunity created.
It's easy to do this by SQLSELECT DISTINCT fa.name, fo.name from FilteredAccount fa LEFT OUTER JOIN FilteredOpportunity fo ON fa.accountid = fo.Customerid where fo.name is null
<fetch mapping='logical'> <entity name='account'> <attribute name='name'/> <link-entity name='opportunity' from='customerid' to='accountid' link-type='outer'> <attribute name='statecode'/> <attribute name='name'/> <filter type='and'> <condition attribute='name' operator='null'/> </filter> </link-entity> </entity> </fetch>
I have tried with this one aboe, but it only returns all accounts:
Any suggestions?
Regards,
Gjøran VeisetFriday, October 2, 2009 11:54 AM
Answers
-
I'm hoping others show me to be wrong but my understanding is Fetch simply can't handle these kinds of searches. It is for this reason that you can't do this kind of search in Advanced find through the client.I'm wondering if you could draw an analogy from the client workaround of using a marketing list and return all accounts and then all accounts WITH opportunities and work out the difference in code.Leon TribeWant to hear me talk about all things CRM? Check out my blogor hear me tweet @leontribe
Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe- Proposed as answer by Leon TribeMVP Friday, October 2, 2009 10:30 PM
- Marked as answer by Gjøran Sunday, October 25, 2009 10:30 AM
Friday, October 2, 2009 10:30 PM -
Hi Leon,
yes you are right. This task could not be accomplished with a single query. You have to use two queries.
With the first query you retrieve all accounts which have at least one opportunity
<fetch mapping="logical" distinct="true" version="1.0"> <entity name="account"> <attribute name="accountid" /> <link-entity name="opportunity" from="customerid" to="accountid" /> </entity> </fetch>
With the second query, you retrieve all accounts which aren't not part of the first result
<fetch mapping="logical" version="1.0"> <entity name="account">
<attribute name="name" /> <filter> <condition attribute="accountid" operator="not-in"> <value>{accountid 1}</value> <value>{accountid 2}</value> </condition> </filter> </entity> </fetch>
I've made only a short test, but this should solve the issue.- Marked as answer by Gjøran Sunday, October 25, 2009 10:30 AM
Sunday, October 4, 2009 2:06 PM
All replies
-
Hi, Goran.
Check this amazing tool .
Truth is opened the prepared mind My blog - http://a33ik.blogspot.comFriday, October 2, 2009 12:03 PMModerator -
Hi thanx.
I am using this tool, but haven't managed to rebuild my SQL into FetchXMLFriday, October 2, 2009 12:16 PM -
Or the Stunnware tools http://stunnware.com/ which contains a great designer for FetchXM queries or QueryExpressionsFriday, October 2, 2009 12:18 PM
-
I'm hoping others show me to be wrong but my understanding is Fetch simply can't handle these kinds of searches. It is for this reason that you can't do this kind of search in Advanced find through the client.I'm wondering if you could draw an analogy from the client workaround of using a marketing list and return all accounts and then all accounts WITH opportunities and work out the difference in code.Leon TribeWant to hear me talk about all things CRM? Check out my blogor hear me tweet @leontribe
Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe- Proposed as answer by Leon TribeMVP Friday, October 2, 2009 10:30 PM
- Marked as answer by Gjøran Sunday, October 25, 2009 10:30 AM
Friday, October 2, 2009 10:30 PM -
Hi Leon,
yes you are right. This task could not be accomplished with a single query. You have to use two queries.
With the first query you retrieve all accounts which have at least one opportunity
<fetch mapping="logical" distinct="true" version="1.0"> <entity name="account"> <attribute name="accountid" /> <link-entity name="opportunity" from="customerid" to="accountid" /> </entity> </fetch>
With the second query, you retrieve all accounts which aren't not part of the first result
<fetch mapping="logical" version="1.0"> <entity name="account">
<attribute name="name" /> <filter> <condition attribute="accountid" operator="not-in"> <value>{accountid 1}</value> <value>{accountid 2}</value> </condition> </filter> </entity> </fetch>
I've made only a short test, but this should solve the issue.- Marked as answer by Gjøran Sunday, October 25, 2009 10:30 AM
Sunday, October 4, 2009 2:06 PM