Armanino Blog
Article

Making an NN searchable in views or advanced in Microsoft Dynamics CRM

March 05, 2014

By default, the NN relationship isn't directly accessible in advanced find or filter views. When you create a new N:N relationship it is set so as not to be displayed on either side.

Creating a new N:N relationship in Microsoft Dynamics CRM

The "Display Option" and "Searchable" properties control if the relationship is displayed in views and advanced find. To make the NN searchable from one side or both sides, you'll need to set the appropriate display option.

Set Display Option as Searchable to make NN searchable in Dynamics CRM

Here I made the "NN Example" side visible. It's a bit confusing at first, however, when going to Advanced find and selecting the NN Example entity as you cannot see the "related" relationship.

NN Entity in Advanced Find - In Microsoft CRM

From the "Related" entity side you can see the "NN Examples" as an option to filter by.

NN Example in Advanced find within Microsoft Dynamics CRM

Setting the display option on the "Related" side will make it visible from the "NN Example" side. One major limitation is that there is no way to display fields in the view from the NN relationship, but you can add criteria to the relationship.

Setting Display Option as Related to make NN Visable in Microsoft CRM

This Display option setting also controls the display in the entity form nav panel as a sub option, this can be hidden from form in the form editor by updating the nav panel.

Updating Display options in the Nav Pannel in CRM

The display option part of the relationship is editable so you can change it at any time.

Fetch-Based SSRS reports:

NNs are stored in a simple intersect entity in the system matching the "Relationship Entity Name" in the relationship.

NNs in a simple intersect entity matching the Relationship Entity Name

This entity is very simple. It contains two attributes, and as far as I know, the attributes always match the Primary key field name of both entities. In my example, those are:

NN Entity Fields in Microsoft Dynamics CRM

and

NN Related Entity in Microsoft Dynamics CRM

To build the fetch you will need to know these three values. The fetch query works regardless of the display property outlined above.

First, you query your primary entity, then the NN intersect entity, and finally, your related entity:

<fetch distinct=“false” no-lock=“false” mapping=“logical” >
 <entity name=“am_nnexample” >
        <attribute name=“am_name” />
        <link-entity name=“am_am_nnexample_am_related” to=“am_nnexampleid” from=“am_nnexampleid” link-type=“outer” alias=“nn” intersect=“true” >
            <link-entity name=“am_related” to=“am_relatedid” from=“am_relatedid” link-type=“outer” alias=“related” >
                <attribute name=“am_name” />
            </link-entity>
        </link-entity>
    </entity>
</fetch>

This same query can be reversed:
<fetch distinct=“false” no-lock=“false” mapping=“logical” >

 <entity name=“am_related” >
        <attribute name=“am_name” />
        <link-entity name=“am_am_nnexample_am_related” to=“am_relatedid” from=“am_relatedid” link-type=“outer” alias=“intersect” intersect=“true” >
            <link-entity name=“am_nnexample” to=“am_nnexampleid” from=“am_nnexampleid” link-type=“outer” alias=“example” >
                <attribute name=“am_name” />
            </link-entity>
        </link-entity>
    </entity>
</fetch>

The first link entity clause is to the intersect from the parent’s primary key to the lookup attribute on the intersect entity. Now that we have the intersect, we create another link entity from the intersect to the other side of the NN, once again matching the to and from based off the primary key name. Finally, you can select individual attributes of the related entity or add criteria to filter the result set.

Stay In Touch

Sign up to stay up-to-date with the latest accounting regulations, best practices, industry news and technology insights to run your business.

Resources
Related News & Insights
The Data Advantage: Unlocking Data Insights that Drive Growth and Investment
Webinar
An exclusive conversation with Mainsail Partners.

January 29, 2025 | 10:00 AM - 11:00 AM PT
Create Value and Thrive in the AI Era: 4 Right-Now Strategies for Manufacturers & Distributors
White Paper
Smart manufacturing is set to boom. Will your company be ready to seize the moment?

January 07, 2025
Reshoring, Nearshoring & Offshoring: 10 Questions to Help You Solve Your Production Puzzle
Article
Can moving operations make your business more competitive? Tax credits and incentives are just one piece of the puzzle.

January 07, 2025