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
IPO Prep & SOX Compliance: Instacart & Armanino Share Hard-Earned Knowledge
Webinar
Know your compliance requirements today to avoid obstacles tomorrow.

December 16, 2021 | 11:00 AM - 12:00 PM PT
General Contractor Trends to Consider in 2022 Webinar
Webinar
Hear from experts how you can better manage your subcontracts — and more.

December 16, 2021 | 10:00 AM - 11:00 AM PT
Why COP26 Matters for Your Business Webinar
Webinar
Get informed of COP26 developments and how you can act now.

December 15, 2021 | 10:30 AM - 11:00 AM PT