0

I'd like to make an advanced search that combines fields from two different entities that are in many-to-many relation to each other. In SQL it's very straight-forward but in Dynamics I get stuck.

I'd like to get a general answer but if it helps, here are the specifics. The task is to create a list of all the contacts in the DB (column "name" and "e-mail") but combined with a third column that is the name of the marketing list that the contact in question belongs to. Of course, the same person might be listed multiple times but the uniqueness is not a requirement (besides, each row will have an equivalent to PK by the combination of the names of the contact and the marketing list).

Is that doable? If so, how?

Greg Owens
  • 3,878
  • 1
  • 18
  • 42
Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • 2
    You might want to check out the Stunnware Fetch Xml designer, makes like a lot easier. http://www.stunnware.com/ > products > stunnware tools – James Wood Sep 13 '12 at 11:29

1 Answers1

2

Firstly, here's your query:

<fetch mapping='logical'>
    <entity name='listmember'>
        <link-entity name='list' from='listid' to='listid'>
            <attribute name='listname'/>
        </link-entity>
        <link-entity name='contact' from='contactid' to='entityid'>
            <attribute name='fullname'/>
            <attribute name='emailaddress1'/>
        </link-entity>
    </entity>
</fetch>

Example results:

<resultset morerecords='0' paging-cookie='<cookie page="1"><listmemberid last="{E1B03485-0000-0000-0000-00155D107003}" first="{A74C877A-0000-0000-0000-00155D107003}" /></cookie>'>
    <result>
        <listmemberid>{A74C877A-0000-0000-0000-00155D107003}</listmemberid>
        <listid.listname>List 1</listid.listname>
        <entityid.fullname>Amy Pickle</entityid.fullname>
        <entityid.emailaddress1>apickle@domain.com</entityid.emailaddress1>
    </result>
    <result>
        <listmemberid>{A94C877A-0000-0000-0000-00155D107003}</listmemberid>
        <listid.listname>List 2</listid.listname>
        <entityid.fullname>Andrew Colley</entityid.fullname>
        <entityid.emailaddress1>colley@domain.com</entityid.emailaddress1>
    </result>
</resultset>

Not sure how well I can describe this in general terms but:

  • You're looking for members of lists so listmember must be the root entity you return
  • For each relationship to that entity that contains attributes you want to display, you need to define a link-entity (in this case, one to list to get the list name and another to contact to get contact's details
  • you want to only display certain attributes, so they each need to be defined within your link-entity
  • you didn't specify any filtering so all memberships of all lists that are contacts will be returned

I never do these by hand. Previously I used James Downey's FetchXML Builder but I believe this is no longer available - Stunnware do a similar tool.

Greg Owens
  • 3,878
  • 1
  • 18
  • 42
  • Is there an easy way to plug the fetch XML into CRM Dynamics or do I have to pass it in the way I've done this far, using my C# code and using the string with XML tags as a `FetchExpression` object? Also, I'm not clear (likely due my ignorance and rookieness) on how you handles the many-to-many issue. – Konrad Viltersten Sep 13 '12 at 13:17
  • 1
    You can use the fetchXml as an input parameter to the `RetrieveMultiple` method (CRM2011) - returning an `EntityCollection` or to the `Fetch` method (CRM4.0) - returning an XML string ("FetchResult"). You can also convert the FetchXml to a query expression via the `FetchXmlToQueryExpressionRequest`. Consult the SDK topics in [CRM2011](http://msdn.microsoft.com/en-us/library/gg328332.aspx) and [CRM4.0](http://msdn.microsoft.com/en-us/library/bb928434.aspx) – Greg Owens Sep 13 '12 at 13:43
  • Just realised I misread your comment. It depends on what you plan to do with the fetchXml... – Greg Owens Sep 13 '12 at 13:45
  • Yes, you misread the comment **but** since it's a very good info, it turned out to be a positive thing! As for your question - I'd like to play around with different fetch XML's to get the feeling of what they do and how CRM Dynamics behaves. It's more convenient to just paste in some XML and click a button inside the software. – Konrad Viltersten Sep 13 '12 at 14:32
  • In which case, I refer you back to the Stunnware tool - the fetchXml builder allows you to build and run queries from within the Stunnware app UI - the only thing that would differ is the styling of the results (i.e. its not in a crmGrid!) – Greg Owens Sep 13 '12 at 15:02
  • A follow-up to this, Tanguy Touzard's awesome [XRM Toolbox](https://xrmtoolbox.codeplex.com/) now has a "FetchXml Tester" featurewhich allows you to run arbitrary FetchXml statements, so no need for custom a harness to do your query testing. – Greg Owens Nov 15 '13 at 15:45