locked
2011 Advanced Find and AND conditions RRS feed

  • 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 1, 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 2, 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 1, 2013 11:32 PM
    Wednesday, May 1, 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 2, 2013 5:13 AM
    Thursday, May 2, 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 2, 2013 5:50 AM
    • Proposed as answer by VidhiyaM Thursday, May 2, 2013 5:51 AM
    Thursday, May 2, 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 2, 2013 12:44 PM
  • post the query ... there is something not very right in it.

    - Arvind

    Thursday, May 2, 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 2, 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 2, 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 5, 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 6, 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