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.