Querying many-to-many relationship in CRM Portal using JavaScript

How do you programmatically query an N:N relationship in CRM Portal using JavaScript without developing additional services?

You can do this with FetchXml and Liquid, and a small hack. Here is a good walkthrough of using FetchXml in Liquid to provide a data service in CRM Portal. I will explain how to adapt this to work for N:N relationships and the hack that would be required.

One thing you should understand is that when you create an N:N relationship in CRM, a “Relationship Entity” is also created behind the scene. This relationship entity is the “joining” entity that you would typically find in a normalised database. You can specify the name of this relationship entity when creating the N:N relationship in CRM as shown below.

To query an N:N relationship in CRM Portal, we will use FetchXml/Liquid to query this relationship entity instead of the actual entities on either end of the relationship.

For example, say I have a Degree and a Subject entities with an N:N relationship, and I want to query all the Subjects for a given Degree (retrieving Subject Name and Subject Code). First thing first, the FetchXml would look as follow (bnh_degree_bnh_subject is the name of my relationship entity):


  
    
      
    
    
      
      
    
  

If you use this FetchXml in Liquid as described by the link earlier on in the post however, you will not get any result. This is because FetchXml in Liquid (as of v8) uses Entity Permission. You need to grant Entity Permission for the relationship entity. (I would guess that Entity Permission for Degree and Subject would also be required – although I have not tested it without).

Here is where the small hack comes in. OOTB you cannot create Entity Permission for a relationship entity. To workaround this:

  1. Create an Entity Permission for a random entity. Set the Scope to Global and grant it the Read privilege.
  2. Add appropriate Web Roles to it.
  3. Create a workflow to update the field adx_entitylogicalname of this Entity Permission record to the name of the relationship entity (bnh_degree_bnh_subject in this example).
  4. UPDATE 02/01/2018: Unfortunately you cannot use a workflow to update the field above. This is because the field is read-only on the form, and therefore the workflow editor does not allow you to set a value for it. Some options to workaround this:
    1. Write a simple C# script
    2. Use a Chrome extension to effectively hack the form and edit the field. Below are some options I would recommend:
      1. Dynamics CRM Power Pane
      2. Level Up

That’s it! Your FetchXml/Liquid should now return results!

Is this hack supported?

Strictly speaking, probably not.

Is it likely to stop working in future updates to CRM and CRM Portal?

I’d say no, but you need to make your own judgement.

So in conclusion…

You can query an N:N relationship in CRM Portal using JavaScript without developing additional services. You just need to query the relationship entity, and apply a small hack to grant the required permission.

 

Advertisements

About Bernado

Based in Australia, I am a freelance SharePoint and Dynamics CRM developer. I love developing innovative solutions that address business and everyday problems. Feel free to contact me if you think I can help you with your SharePoint or CRM implementation.
This entry was posted in Adxstudio, CRM, CRM Portal. Bookmark the permalink.

One Response to Querying many-to-many relationship in CRM Portal using JavaScript

  1. Thanks Bernado. It worked like a charm on dynamics 365 online v9.0. You saved my day.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s