none
2011 Advanced Find and AND conditions

    Question

  • 2011, Update 11, On Premise

    I have a query I can't seem to get working no matter what I try or what entity I base the query from.

    I have an event attendance entity that allows me to link each record to an account.  I want to find accounts that have attended event A AND event B.  When I build the query I have two lines:

    • Event field equals event A
    • Event field equals event B

    I tried running the query as stated and no results.  Next I grouped the two statements and used the AND condition.  Still no results. This work like a charm when switch the AND condition to an OR but it's not the result set I need.

    Is this supported?


    Scott Clancy

    Wednesday, May 01, 2013 10:49 PM

Answers

  • This should be possible in one query, but to make it work you need to reference the event attendance entity twice, as the condition would be met via two different event attendance records. The query would look something like:

    Accounts
     Event Attendance
      Event = Event A
       Accounts
        Event Attendance
         Event = Event B   

     

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, May 02, 2013 3:52 PM
    Moderator

All replies

  • Hi Scott,

         The default behaviour for fetchXML condition is AND and that is why when you tried with no grouping and when you tried with ANd grouping you got same result(no results). OR gives you events for event A or Event B. All data in the system is either event A or event B so this returns data. This is fully supported and working as expected.


    Hope this helps.
     
    -----------------------------------------------------------------------
     Minal Dahiya
     
    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    • Proposed as answer by Minal Dahiya Wednesday, May 01, 2013 11:32 PM
    Wednesday, May 01, 2013 11:32 PM
  • What is the type of relationship between Account & Attendance entity? If its N:N then advance find query might not help you.

    If its not N:N relationship, can you please paste the screenshot of advance find query in this forum?


    Garfield!!


    • Edited by KunuChopra Thursday, May 02, 2013 5:13 AM
    Thursday, May 02, 2013 5:12 AM
  • Hi,

    Yes, it is supported!By default the query will take AND Condition .If you don't get the expected data, then it would be good to check your query or data!


    VidhyaM


    • Edited by VidhiyaM Thursday, May 02, 2013 5:50 AM
    • Proposed as answer by VidhiyaM Thursday, May 02, 2013 5:51 AM
    Thursday, May 02, 2013 5:49 AM
  • You will have to make two separate queries. FetchXML (or at least Advanced Find) is not able to retrieve this kind of a query result directly, as there are no matching attendance records where the event field would be both A and B at the same time. Store the results of query A into a temporary marketing list of accounts, then build a further query where the criteria includes both event B and the membership of the marketing list created earlier.

    If you need to frequently build such queries to manage target groups, there are ISV solutions out there that allow you to perform intersect queries. Here's one example.


    Jukka Niiranen - My blog: Surviving CRM - Follow @jukkan on Twitter

    Thursday, May 02, 2013 12:44 PM
  • post the query ... there is something not very right in it.

    - Arvind

    Thursday, May 02, 2013 1:31 PM
  • This should be possible in one query, but to make it work you need to reference the event attendance entity twice, as the condition would be met via two different event attendance records. The query would look something like:

    Accounts
     Event Attendance
      Event = Event A
       Accounts
        Event Attendance
         Event = Event B   

     

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, May 02, 2013 3:52 PM
    Moderator
  • That's a great tip, David! I've never thought that you could actually reference back from the child to the parent and just continue building the queries. Wonder if there's any limit in how many levels deep you can go with this in Advanced Find? I did a test with four different values and didn't hit any barriers yet.

    Jukka Niiranen - My blog: Surviving CRM - Follow @jukkan on Twitter

    Thursday, May 02, 2013 4:32 PM
  • This should be possible in one query, but to make it work you need to reference the event attendance entity twice, as the condition would be met via two different event attendance records. The query would look something like:

    Accounts
     Event Attendance
      Event = Event A
       Accounts
        Event Attendance
         Event = Event B   

     

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Excellent!


    My Weblog | My Website

    Sunday, May 05, 2013 6:12 AM
    Moderator
  • This totally worked.

    What I found very interesting is the result set of accounts that came back.  I thought I would get back the account name for each event but it consolidated to one account.  Very much like a query for an N:N relationship.

    FYI - Account to Event relationship is 1:N. One account many events.

    Scott


    Scott Clancy

    Monday, May 06, 2013 7:43 PM
  • I tried this approach when I wanted to return all Contacts who are members of 2 specific Marketing Lists, so I set up an Advanced Find like this:

    Contacts

      Marketing Lists

      Marketing List = List A

        Contacts

          Marketing Lists

          Marketing List = List B

    However, this just seems to return Contacts in List A, regardless of whether they appear in List B as well or not. Am I missing something?

    Thursday, August 25, 2016 3:59 PM