locked
FetchXml --> List all accounts that don't have any opportunities RRS feed

  • 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 SQL
    SELECT 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 Veiset

     

    Friday, 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 Tribe
    Want to hear me talk about all things CRM? Check out my blog
    or 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.com
    Friday, October 2, 2009 12:03 PM
    Moderator
  • Hi thanx.

    I am using this tool, but haven't managed to rebuild my SQL into FetchXML
    Friday, October 2, 2009 12:16 PM
  • Or the Stunnware tools http://stunnware.com/ which contains a great designer for FetchXM queries or QueryExpressions
    Friday, 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 Tribe
    Want to hear me talk about all things CRM? Check out my blog
    or 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