Join Entities in Dynamics 365 and why they are awesome

If you have ever worked with many-to-many (N:N) relationships in Dynamics 365 (the product formerly known as Dynamics CRM), you may have at some point created a N:N relationship between entities.  It is a useful relationship type for sure, but it has some serious ‘out of the box’ limitations.
The main issue I have always had with it is the complete lack of capability to execute a workflow process when the relationship is created, or any audit record of who and when the relationship was created or modified.

Example 1:

Contact has a N:N relationship between itself and a custom entity called ‘Web Roles’.  You assign new Web Roles to the Contact record to allow them access to pages on a custom portal.  But you need to know who added the role, and when the role was added.  Say you have delegated the web role assignment to customers that have an admin role to manage their own users on the portal?  How would you know who added the role and when?

Example 2:

Contact has a N:N relationship to Account.  For each Contact, they have a regular parental N:1 relationship to an Account, but they might also have a relationship to several other Accounts.  Perhaps they are a distributor of your products and have a company that they work for, but also work with several of your other Accounts to sell them products.  And each Contact may have a different role that they have in relationship to the other Accounts.

But Wait??

If you have worked with Dynamics 365 for any amount of time, you might be thinking “Hey, you can use the built-in Connections entity for this”. And you would be correct.  But only if you are only going to use Connections for just one type of N:N relationship.  Since Connections are basically available for ANY record to be linked to any other record, it’s a lot more generic than it needs to be.


The solution is what I like to call a ‘Join Entity’.  If you have ever done traditional database or application development that worked directly with a SQL database, you should already be familiar with this concept.  It’s basically a table that sits between two other tables and stores the primary key for records in each table that require a join.
In Dynamics 365 parlance, we create a Join Entity that works just like a join table.

Step 1: Create a new Custom Entity

When you create this entity, give a good name that reflects what you are joining.  In this example, we are going to create a join between Account and Contact to allow for multiple Contacts to be associated with multiple Accounts.  I’m going to call this one Account To Contact.

For the Ownership option, this one is up to you.  In most scenarios, it is safe to set this to Organization since we are just using this for joining up other entities and we don’t need all the overhead associated with User or Team ownership.  If the relationship needs it and perhaps some user or team needs to own the relationship then by all means, set it that way.
Most likely you won’t need any of the Communication & Collaboration options enabled, and you can always enable most of them later anyway.  As a rule, I like to keep them all OFF until I know I need them.
For Data Services options, I would Allow Quick Create, and Enable Auditing.
For Primary Field, you can leave the default name as Name, or give it something else more appropriate if you prefer.  We’ll talk about how to deal with this field in a later step.

IMPORTANT: You need to set the Field Requirement for the Primary Field to Optional at this step.  If you forget, don’t worry it can be changed by editing the field directly, but it’s best to do it at this time.

Side Note: For the color setting, I like to set all my custom entities color to plain white (#ffffff) and then get some nice flat black icons from Icons 8 (  It’s an awesome site with thousands of icons and I highly recommend it.

Click Save to create your new entity.

Step 2: Add the Relationship Lookups

Now we need to add the appropriate N:1 lookups to the entities we are trying to join on.
Click the Fields option and then click New in the toolbar.
Display Name should be something that makes sense to the user that will be adding this new record/relationship.  For our example, we are joining to Account so we will call it Account.

Tip: This will also default the Name field to Account, but it makes sense to add a suffix of Id to this SDK name field.  This will be helpful later if you have to write any JavaScript or .NET code to reference the field. You should be able to quickly recognize it as a GUID to lookup to another entity.

Field Requirement should be set to Business Required to avoid any orphaned join records.
Select a Data Type of Lookup and a Target Record Type of Account.
Save this new field and repeat these steps to add a lookup to the Contact entity.

  • Display Name = Contact
  • Name = new_ContactId
  • Field Requirement = Business Required
  • Data Type = Lookup
  • Target Record Type = Contact

Step 3: Edit Default Form

Click Forms in the left navigation to review the list of built in system forms.  Click the Information form listed first with a Form Type of Main.
Customize this form and add your newly created Account and Contact fields.  Click Save and Close to save your form changes.

Tip: Since you only get one section by default, the Name (and possibly Owner) field(s) will fill up the entire width of the form.  I find this annoying and completely impractical, but that’s just me.  I will usually edit the General Tab and set the Formatting option to use Two or Three Columns.

Step 4: Create Quick Create Form

While still looking at the list of Forms, click New on the toolbar and select Quick Create Form
Modify the form by adding your 2 lookup fields to the form.  Save and Close the new form.

Optional:  Probably a good idea to go ahead and publish your changes now if you haven’t already done so.

Tip: If you know what Source entity will be used a majority of the time, put the other entity lookup first on the form.  In this case, we are assuming that contacts will be added from the Account record, so we are showing the Contact lookup field first.  The Account field will already be filled in when the join record is quick created.

Step 5: Build a workflow to set the Name Field

Now that we have the basics setup, we need to set a Name for these new records.  This is the name that will show in any lookups to this entity, which the system uses by default.  We’ll build some views that will be used on forms, but we can’t just leave this field blank.  This workflow will set the Name to a combination of the Account and Contact names.

Note: Recall that in Step 1 we set the primary field requirement to Optional.  If you did not do that, now is the time.

Navigate in your Solution (or All Customizations) to Processes and click New.
Process Name: This should reflect the Entity Name, RT for ‘Real-Time’, and some description.  I like to call this one New to show that it runs when a new record is created.

Category = Workflow

Entity = Account To Contact

Run this workflow in the background should be Unchecked/Off
Click OK to create the new process.
Options for Automatic Processes
  • Scope = Organization
  • Start When =
    • After Record is Created
    • After Record Fields Change
      • Select Account and Contact (the custom join lookup fields added earlier)
In the logic area, add a step to Update Record
Click the Set Properties button next to the Update Record.
Click in the Name Field an add the dynamic values of Account and Contact.

Tip: Put some kind of delimiter like a dash, asterisk, or colons to separate the values.

Note:  This can be whatever dynamic values you like, but try to make it unique to this join record.  Remember that this will show up in all the lookups that may reference this join entity.

Save and Close the Properties
Activate the Workflow Process

Step 6: Customize a Form View

We are going to want to add a sub-grid to both entities we are joining to, so we need a view that shows the values.
At this point we haven’t modified any of the default views, but we will at least modify the Active Account to Contact view, then do a save as and modify that one for a form view.

  • Navigate in your solution (or All Customizations) to the Account to Contact entity.
  • Select Views from the left navigation tree
  • Select to edit the Active Account to Contact view
  • Add the Account and Contact lookups columns to the view, and order them as you prefer.
  • Save the view, but DO NOT CLOSE the window
  • Click Save As and enter Form View for the new view name
  • Remove the Name column from the view
  • Move the Contact name and the first column.  Note: This is because we will use this view on the Account Form, so it makes sense for the Contacts to be listed first.
·        Option: If you like, you can do another Save As, call it Contact Form View, and set the Account as the first column.

Step 7: Add it as a sub-grid to a form

Now that we have our entity, we set the fields correctly, and we built a form view, we can put this all together on the Account form and see how it works!
  • Navigate in your solution (or All Customizations) to the Account entity.
  • Select Forms from the left navigation tree
  • Select the Account form of Form Type Main from the view
  • Scroll to the appropriate spot on the form where you want to display the new entity
  • Insert a Section to contain the Sub-grid
  • With the Section selected, insert a Sub-Grid
The key fields here are in the Data Source area.
  • Records = Only Related Records
  • Entity = Account to Contact (Account)
  • Default View = Form View

Note:  If/when you add this to Contact, select Contact Form View instead of Form View.
Save, Publish, and Save and Close the form changes.

Step 8: Test it!

Tip: If you haven’t recently done so, now is a good time to Publish All Customizations.  We’ve made a lot of changes so we want to be sure everything shows up for testing.
Navigate to an Account record
Scroll down to where you added the sub-grid and it should look something like this
To add a new Contact to relate to this account, click the + button.  You should see the Quick Create form we created earlier.
Note: If you don’t see this + button, or you get the regular entity form instead of the quick create form, you missed a step somewhere in setting up the entity and lookups. Check the following:
  • Does the Account to Contact Entity set to Allow Quick Create
  • Are the Account and Contact lookup fields set to Business Required
Enter a Contact name, and click save.  You should now see the new relationship added!

Step 9: Extend it!

What we’ve done so far has really been pretty much what you get out of the box for N:N relationships, with the exception of step 8 where we added the new relationship with a quick create form.  Adding those with the out of the box functionality is painful (just my opinion…).  Now we can get to extending this new entity to do something that the out of the box functionality does not.
For our example, we are going to add a Role attribute to the entity.
  • Navigate in your solution (or All Customizations) to the Account to Contact entity.
  • Select Fields from the left navigation tree
  • Click New to add a new Field
  • Display Name: Contact Role
  • Data Type: Option Set
    • Add Options for Role 1, 2,3,4

Note:  This is just for example purposes, you can add any field type as required by your needs.

Save and Close the new field
Select Forms from the left navigation
Select the Quick Create form type
Drag the new Contact Role field on to the form
Save and Close
Also edit the main Information form and add the Contact Role field, Save and Close

Select Views from the left navigation
Select the Form View(s)
Add the new Contact Role field to the view(s)
Save and Close

Publish your changes
Now when you view the data on your Account entity, you’ll see the new Role value assigned to each record.


We have covered a lot of different topics, but you should have a good grasp of the power of using this Join Entity concept instead of the out of the box many to many (N:N) relationship.  You gain auditing capability to see who, what, when.  You gain the ability to extend the relationship and add additional fields to define the relationship (e.g. roles, etc.).
You can also extend the entity to run workflow processes when records are created or modified.  We did a simple name update, but this could perform many other tasks if needed (e.g. send an email, update a related record, etc.)

How to upload and connect files to CRM and SharePoint online

Ran into a problem recently where a client wants a web form to interact with CRM, allow the end user to upload a file directly to SharePoint online, then have it show in the associated documents on the CRM record.

I promptly went to my favorite search engine and began to research this problem…but after many hours and searches I came up with nada, zip, zilch.  A lot of stuff about SharePoint, and even some stuff about interacting with SharePoint from within a CRM plugin, but nothing related to the use case I was working on.

So after much research, time, and general reverse engineering of how CRM shows related documents in SharePoint, I came up with something.

The way that the SharePoint integration actually works when you click the ‘Documents’ link on a record is that it simply shows you the files in that document library and folder. CRM only stores a ‘location’ record for that regardingobjectid in an entity called SharePointDocumentLocation.  That record has a parent reference in the same entity.

Once you establish the location reference, you can go directly to that folder in SP, add a new file, go back to CRM and you will see the new file in the documents view.

First, let me explain that, as far as I know, this scenario only works with CRM Online and SharePoint Online in the same Office 365 tenant.  If you have some other combination of Online and On-Premise, or all On-Premise I have no idea if this will work.  Feel free to give it a try, but I don’t offer any warranty expressed or implied.

CRM and SharePoint Prep Work:

1. Make sure you have already setup the SharePoint integration in CRM.  There is plenty of documentation for this so I won’t bother to describe it here.
2. Make sure that your OOTB or Custom Entity is setup for document management.  Again, there is plenty of documentation on this.
3. As part of step 2, you ‘should’ end up with a document library in SharePoint with the same name as your entity.

Code Notes:
I have this code setup as a console app, but you should be able to convert it to use in a web page as necessary.  It won’t work with an CRM Online Plugin because it requires some SharePoint .NET libraries that you can’t get access to, or even ILMerge.  There are some docs on that process and using the SharePoint REST endpoint, but I didn’t use that method.
.NET Framework is 4.5.2 for this code

You will have to get the following nuget packages for this to work
“Microsoft.CrmSdk.CoreAssemblies” version=”7.1.0″
“Microsoft.CrmSdk.Deployment” version=”7.1.0″
“Microsoft.CrmSdk.Extensions” version=”7.1.0″
“Microsoft.IdentityModel” version=”6.1.7600.16394″
“Microsoft.SharePointOnline.CSOM” version=”16.1.3912.1204″

You also have to have generated your XRM Class file using the CRMSVCUTIL tool.


using Microsoft.SharePoint.Client;

using System;

using System.Linq;

using System.Security;

using XRMClass;

namespace zSPTest1


    class Program


        static void Main()


            string SharePointCRMDocumentsSiteName = “”;

            string SharePointCRMDocumentsLoginName = “”;

            string SharePointCRMDocumentsPwd = “ThisIsYourPassword!”;

            string SharePointCRMSchedulingRequestSiteName = “Account”;

            string fileName = “attachment.pdf”;

            byte[] fileContent = System.IO.File.ReadAllBytes(“C:\\temp\\attachment.pdf”);

            // Note, this would normally be the id of the CRM Record to relate the document to

            Guid recordId = new Guid();

            // This requires the CrmSvcUtil.exe code generator to create the class file.

            // Sample :: CrmSvcUtil.exe /out:XRMClass.cs /url: / /password:ThisIsYourPassword! /namespace:XRMClass /serviceContextName:XrmServiceContext  /codeCustomization:”Microsoft.Xrm.Client.CodeGeneration.CodeCustomization,Microsoft.Xrm.Client.CodeGeneration”

            XrmServiceContext xrm = new XrmServiceContext(“xrm”);

            // Setup the Connect context to the SP Server.           

            ClientContext ctx = SetupSPContext(SharePointCRMDocumentsSiteName, SharePointCRMDocumentsLoginName, SharePointCRMDocumentsPwd);

            // This is the path to the documents library in SharePoint to hold the attachments

            string sitePath = SharePointCRMDocumentsSiteName + “/” + Account.EntityLogicalName.ToLower();

            // Retrieve the Site record from CRM to get the relative path

            SharePointSite spSite = xrm.SharePointSiteSet.Where(n => n.Name == SharePointCRMSchedulingRequestSiteName).SingleOrDefault();


            // Here we build the URL with the site and path to the document library and folder to see if it already exists

            string relativeURL = GetSPFolderPathFromCRM(recordId, Account.EntityLogicalName, spSite.Id);

            // If it does not exist, we need to create a folder to hold the files for this record

            // Note: For simplicity, we are using the guid of the record, but you can use whatever you like.  

            //      CRM by default uses the record name, underscore, and the guid (e.g. Contoso_[record id guid] )

            //      When working with existing entity records and folders, you may need some more advanced code depending on how your 

            //      SharePoint integration with CRM is setup.

            if (string.IsNullOrEmpty(relativeURL))


                CreateSPFolder(recordId, ctx, SharePointCRMSchedulingRequestSiteName, recordId.ToString().ToLower(), sitePath);


            // Now we append the filename to the path

            string fullURLWithFileName = sitePath + “/” + recordId.ToString() + “/” + fileName;

            // Upload the file!

            UploadFileToSP(ctx, “account”, fullURLWithFileName, fileContent);




        public static ClientContext SetupSPContext(string documentLibrarySiteURL, string userName, string password)


            // This builds the connection to the SP Online Server

            ClientContext clientContext = new ClientContext(documentLibrarySiteURL);

            SecureString secureString = new SecureString();

            foreach (char c in password.ToCharArray()) secureString.AppendChar(c);

            clientContext.Credentials = new SharePointOnlineCredentials(userName, secureString);

            Web web = clientContext.Web;



            return clientContext;


        public static string GetSPFolderPathFromCRM(Guid regardingObjectId, string regardingObjectEntityName, Guid siteId)


            // We query the SharePointDocumentLocation entity to get the parentid

            string returnValue = string.Empty;

            XrmServiceContext xrm = new XrmServiceContext(“xrm”);

            SharePointDocumentLocation parentLocationRecord = xrm.SharePointDocumentLocationSet

                .Where(d => d.ParentSiteOrLocation.Id == siteId)

                .OrderByDescending(d => d.ModifiedOn)


            if (parentLocationRecord == null) throw new Exception(“parentLocationRecord not found, check CRM for SP Site!”);

            // Now we check if the location has already been entered into CRM for this record.

            SharePointDocumentLocation documentLocation = xrm.SharePointDocumentLocationSet

                .Where(d => d.RegardingObjectId.Id == regardingObjectId)

                .Where(d => d.ParentSiteOrLocation.Id == parentLocationRecord.Id)


            // If it’s there, then we return the path

            if (documentLocation != null)


                returnValue = documentLocation.RelativeUrl;




                // Document Location record does not exist, so let’s create it.

                CreateSPDocumentLocationRecord(regardingObjectId, regardingObjectEntityName, parentLocationRecord);

                returnValue = string.Empty;


            return returnValue;


        private static void CreateSPDocumentLocationRecord(Guid regardingObjectId, string regardingObjectEntityName, SharePointDocumentLocation parentLocationRecord)


            // We have to link up the document location with a new record in CRM.

            Microsoft.Xrm.Client.CrmConnection c = new Microsoft.Xrm.Client.CrmConnection(“xrm”);

            XrmServiceContext xrm2 = new XrmServiceContext(c);

            SharePointDocumentLocation newDocumentLocation = new SharePointDocumentLocation();

            newDocumentLocation.RelativeUrl = regardingObjectId.ToString().ToLower();

            newDocumentLocation.Name = regardingObjectEntityName + ” :: ” + regardingObjectId.ToString().ToLower();

            newDocumentLocation.RegardingObjectId = new Microsoft.Xrm.Client.CrmEntityReference(regardingObjectEntityName, regardingObjectId);

            newDocumentLocation.ParentSiteOrLocation = new Microsoft.Xrm.Client.CrmEntityReference(SharePointDocumentLocation.EntityLogicalName, parentLocationRecord.Id);



        public static void CreateSPFolder(Guid regardingObjectId, ClientContext ctx, string listName, string folderName, string folderUrl)


            // Create a new folder in the SP document library

            // Note: For simplicity, we are using the guid of the record, but you can use whatever you like.  

            //      CRM by default uses the record name, underscore, and the guid (e.g. Contoso_[record guid] )

            //      When working with existing entity records and folders, you may need some more advanced code depending on how your 

            //      SharePoint integration with CRM is setup.

            Microsoft.SharePoint.Client.List list = ctx.Web.Lists.GetByTitle(listName);

            folderName = folderName.Trim();

            ListItemCreationInformation info = new ListItemCreationInformation();

            info.UnderlyingObjectType = FileSystemObjectType.Folder;

            info.LeafName = folderName.Trim();

            info.FolderUrl = folderUrl;

            Microsoft.SharePoint.Client.ListItem newItem = list.AddItem(info);

            newItem[“Title”] = folderName.Trim();




        public static void UploadFileToSP(ClientContext ctx, string listName, string fullUrlWithFileName, byte[] content)


            //  Send the file content to SP

            Microsoft.SharePoint.Client.List list = ctx.Web.Lists.GetByTitle(listName);

            FileCreationInformation file1 = new FileCreationInformation();

            file1.Overwrite = true;

            file1.Url = fullUrlWithFileName;

            file1.Content = content;

            Microsoft.SharePoint.Client.File uploadFile = list.RootFolder.Files.Add(file1);