CODEWITHSUNDEEP
dynamics-crm
Konrad Viltersten
Konrad Viltersten

Reputation: 39058

FetchXML with data from many-to-many-relations

I've got my solution working with a serious drawback that it makes a lot of smaller calls to the CRM. Instead, I'd like to make a single one (or at least only a few) and instead allow for redundancy.

I'd like to obtain information consisting of all the contacts that are on any marketing lists combined with the name of those lists. One can also view it as listing all the marketing lists and their members. In case a contact is represented in multiple marketing lists, I'd like it to be listed multiple times, once for each membership on a list.

In order to avoid redundancy, I asked another question here but it's my understanding that it's not possible. Perfect output data would be equivalent to this.

<allLists>
  <list name="hazaa">
    <contact name="Donald Duck" />
    <contact name="Speedy Gonzalez" />
  </list>
  <list name="shazoo">
    <contact name="Donald Duck" />
    <contact name="Kermit Frog" />
  </list>
<allLists>

Apparently, getting it via fetchXML is not as easy as one'd hope for so I'm retiring to acceptance of the following, redundant data set.

<contact name="Donald Duck" list="hazaa" />
<contact name="Speedy Gonzalez" list="hazaa" />
<contact name="Donald Duck" list="shazoo" />
<contact name="Kermit Frog" list="shazoo" />

Still, I don't see how to get there. I've tried to use The FetchXML Wizard but an evil 404-error put that idea to a stop (and who knows, maybe it wouldn't help anyway, haha). Suggestions?

I'm afraid that all I'll get is a link to here, telling me to forget it. Is it really so?

Upvotes: 1

Views: 2455

Answers (1)

James Wood
James Wood

Reputation: 17552

So first off, the last link Can you write a single FetchXML query to get 1:many relationship? doesn’t appear to have been answered correctly to me. I've added what I feel to be a correct answer.

In terms of using the FetchXml wizard from stunnware it looks like the author is in between moving sites. You can still get tool however from this link: http://www.stunnware.com/products/tools4/download.htm (Google to the rescue).

I would suggest using that, I believe it will help you make a lot of progress in creating the FetchXml.

In terms of 'data redundancy' in the result sets, I don't think you will be able to avoid this (or actually need to).

Effectively you are running a sql query, a sql query with joins would also return 'redundant data' or repeated data. I think you will just need to accept thats how the data is returned and parse it into a format you require.

So that said this moves me onto my second point, which is; have you considered Dynamic Lists?

A marketing list in crm can be static or dynamic. Static is a simple case of one record being associated to another - easy to query. Dynamic is based upon a query (which is defined in the UI, which is actually FetchXml). My point here is I'm not sure if your FetchXml query will retrieve these dynamic members - (I suspect it wont, but I'm not sure, never tried myself).

So, I don't think FetchXml is the right solution for this problem, fortunately there are other options - which actually look a lot easier to implement in any case. I would suggest having a look at the links below to be sure you are implementing the correct solution.

  1. Retrieving Marketing List Members in CRM 2011 - The difference between static and dynamic
  2. Where are the List Members of a Dynamic Marketing List ? - Getting the dynamic members
  3. Retrieve Marketing List Members - Getting the static members

Upvotes: 2

Related Questions