CRM 2011 – N:N relationships and advanced finds quirks

I was trying to set up a new entity and link it to a contact.

The example in this case was they wanted to have a Language Entity which they could add languages to a contact. They would then want to search for contacts which had certain languages.

I set up my new language entity put a drop down on it with 5 languages to test with.

I initially created it as a 1:N relationship but this meant every language linked to a contact was a completely new language entity. so you could have English for contact a and then a new language entity called English for contact b. When you did an advanced search the two different values would appear.

So I went back and changed the relationship to N:N.

When you create an N:N relationship you get these choices below. The display options are a bit unusual, do not display?

Although these options seem harmless the display option has big ramifications. Luckily the CRM Guru Richard Knudson wrote a detailed blog about this which gave me the answer, which you can read here

Initially I chose to display the link on one side of the relationship, on the contact. This caused advanced searches when searching a contact with languages of English to return no results. Why?

The reason is if you do not display the relationship on both entities it won’t return any results in an advanced find!!!

Richard Knudson sums this up perfectly

Interestingly, it also turns out that if you select Do not Display in the relationship properties, you will not be able to create an Advanced Find query between two record types related with an N:N. So the Do not Display option apparently means “do not display in Advanced Find”, as well as “do not display on the form”.

Below I changed the display options this returned no rows, which perplexed me.

Another interesting point from the picture above is when you select multiple drop down values it selects those as an or, so I had to add another select to create the AND value I wanted.

Finally someone else in the office was saying this didn’t work in CRM 4 and I think this article from Richard Knudson again backs that up, saying there was something not quite right with N:N relationships and CRM 4

http://www.dynamicscrmtrickbag.com/2010/01/18/many-to-many-relationships/

once again thanks to the mighty Knud for that.

So there we go, once again CRM can act in unusual ways, which shows that experience using CRM is invaluable because the only way you learn all these quirks is by trying things.