locked
Need fetch xml for N:N relationship, to insert into a report - CRM 2011 RRS feed

  • Question

  • Hi,

    I need a fetchXML builder for CRM 2011. I've seen other similar questions, and they are often answered by telling the person to use Advanced Find and download the XML there. However, when I try to use Advanced Find to create my XML, the related entity I want is not available in the drop down - I have an entity that has a subgrid on it, so it is a N:N relationship.

    Does anyone know where I could gt a fetchXML builder for this?

    Thanks.


    Monday, July 22, 2013 3:45 PM

All replies

  • Hi,

    Try reversing your query in Advanced Find. For example, I have two entities linked with an N:N relationship - Contact and Ethnicity.

    If I search for "Contacts" in Advanced Find, I can see "Ethnicities" under "Related Records". However, if I search for "Ethnicities", I cannot see "Contacts" under related records.

    I hope this helps.

    • Proposed as answer by Roshan Mehta Monday, July 22, 2013 8:05 PM
    Monday, July 22, 2013 8:05 PM
  • Thanks for that. I realise now that I probably explained myself badly - The related entity has a lookup on the primary entity. I can get that lookup in my Advanced Find, however, I suppose what I need is something like a join in SQL - as I need values in a subgrid which is actually on the related entity. So, I need (in pseudo SQL!):

    select primaryEntityName, relatedEntity.subgridvalue
    
    from primaryEntity
    
    inner join relatedEntity on primaryEntity.lookupvalue == relatedEntity.Id
    
    where
    
    primaryEntity.lookup is not null
    
    and relatedEntity.subgridvalue is not null
    

    in FetchXML. That's why I was thinking a FetchXMl builder would work.

    Thanks.

    Tuesday, July 23, 2013 8:51 AM
  • roscoNZ is correct.  Using the method that roscoNZ describes gives us this XML - replacing primaryEntity with Contact, relatedEntity with Account, primaryEntity.lookupvalue with Contact's Parent Customer and relatedEntity.subgridvalue with Account's Account Number:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="contact">
        <attribute name="fullname" />
        <attribute name="contactid" />
        <order attribute="fullname" descending="false" />
        <link-entity name="account" from="accountid" to="parentcustomerid" alias="ab">
          <attribute name="accountnumber" />
          <filter type="and">
            <condition attribute="accountnumber" operator="not-null" />
          </filter>
        </link-entity>
      </entity>
    </fetch>
    Good luck
    Tuesday, July 23, 2013 8:10 PM
  • It would be unusual to have a lookup on either side of a many to many.  So you should have no problem generating this from advanced find.  While the fetch looks (and is) hierarchical, for an inner join it doesn't matter which one is entity and which is the linked entity to get your grid records.  All the contacts whose parent account is PrivateCorp, Inc:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="contact"> <attribute name="fullname" /> <attribute name="telephone1" /> <attribute name="contactid" /> <order attribute="fullname" descending="false" /> <link-entity name="account" from="accountid" to="parentcustomerid" alias="aa"> <filter type="and"> <condition attribute="accountid" operator="eq" uiname="PrivateCorp, Inc." uitype="account" value="{DF14D97B-2BF4-E011-8637-1CC1DE799302}" /> </filter> </link-entity> </entity> </fetch> OR

    @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' aggregate='true'>
      <entity name='opportunity'>
        <attribute name='actualvalue' aggregate='sum' alias='new_totalrevenue' />
        <attribute name='opportunityid' aggregate='count' alias='wonOpportunities' />
        <filter type='and'>
          <condition attribute='statecode' operator='eq' value='1' />
          <condition attribute='campaignid' operator='eq' uitype='campaign' value='" + cpid + @"' />
              <condition attribute='actualclosedate' operator='on-or-after' value='" + sd + @"' />
              <condition attribute='actualclosedate' operator='on-or-before' value='" + ed + @"' />
        </filter>
      </entity>
    </fetch>";



    • Edited by mardukes Tuesday, July 23, 2013 9:49 PM punctuation
    Tuesday, July 23, 2013 9:47 PM