Were Moving !!

I plan to keep this blog up for a while, but I'm moving everything over to a new Wordpress site at www.informaquest.com

Thanks!

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.

Solution:

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 (http://www.icons8.com).  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.

Wrapup

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.)

CRM 2016 – Insufficient Permissions Error when Downloading a Word Template

Problem

We recently ran into an issue with CRM 2016 where the user was trying to download a Word Template to begin to work on a new report, and kept getting an error about Insufficient Permissions.  We could easily reproduce this error by navigating to Settings, Templates, click New, Select Word Template, pick any entity from the list, and click Select Entity.  Once you select any related data, then click Download Template.  This error message would appear:

Insufficient Permissions
You do not have permission to access these records. Contact your Microsoft Dynamics CRM administrator.

To the Documentation!

Microsoft has some pretty good documentation on how to use the Word Templates, you can check out the docs page here: Using Word templates in CRM.

On this page if you navigate to the Additional Considerations area at the bottom, it describes the security rights needed.  After assigning these rights, we asked the users to try again…but the error persisted.

Solution

After much trial and error, and review of practically EVERY SINGLE security right available, we finally found the one that was needed.

On the Business Management Tab, in the Privacy Related Privileges area, there is an option for Document Generation. Turning that on for the security role assigned to the user fixed the issue.

We will be reporting this oversight to Microsoft using the Was this Information Helpful option on the page and hope it will be addressed in a future update to the documentation.

Uploading Files directly to SharePoint and link in CRM

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:  

1. 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.
2. .NET Framework is 4.5.2 for this code
3. 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" 
4. 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 = "https://contoso.sharepoint.com/sites/crmdocs";
            string SharePointCRMDocumentsLoginName = "user.name@contoso.onmicrosoft.com";
            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:https://contoso.crm.dynamics.com/XRMServices/2011/Organization.svc /username:user.name@contoso.onmicrosoft.com /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;
            clientContext.Load(web);
            clientContext.ExecuteQuery();
            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)
                .FirstOrDefault();
            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)
                .FirstOrDefault();

            // If it's there, then we return the path
            if (documentLocation != null)
            {
                returnValue = documentLocation.RelativeUrl;
            }
            else
            {
                // 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);
            xrm2.Create(newDocumentLocation);
        }

        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();
            newItem.Update();
            ctx.ExecuteQuery();

        }

        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);
            ctx.Load(uploadFile);
            ctx.ExecuteQuery();
        }

        
    }

}

Updating Icons blows up Auditing?

UPDATE: Apparently this is a known function in CRM, except it doesn't make sense that it happens when you are updating Icons.  

Please take a moment and vote this up on Microsoft Connect: http://bit.ly/1ERbj0i

----------
I've been working for the past few weeks on the upgrade of a CRM v2011 instance to a v2015 instance.

One thing I noticed in CRM 2015 is that all of the icon images are flat and typically a monochromatic color.  Usually they are a Shade of Grey... the color, not the book/movie.  :)

So working with a colleague of mine, I asked him to make up some nice monochromatic icons to represent the 2 dozen or so custom entities we have.  He made up some nice icon files in 16x16 and 32x32 format, and to save time I asked him to go ahead and update the icons in our 2011 instance so we don't have to go through and do it during the final upgrade process.

Then a few hours later I get a Lync message from a CRM user asking what's happened to the Audit logs for a record?

My reply was... Huh?  Nothing that I know of?

So I check the record, and lo and behold, all the new value entries on the audit log are missing, replaced by an strange icon image indicating that it's not there.

After the Icons were updated...

I'm stumped...  I can see an entry where auditing was turned off, and it turned out that was when the icons were updated. It decided to turn off the auditing for a moment, and then back on, which caused this problem.  I can easily reproduce it over and over.

Anything updated after this time is fine, and gets audited properly.

So I did a search and the only thing I could find was this Forum post.  It's not exactly the same thing because they were intentionally turning auditing off and back on, but the result was the same.  All the 'new value' entries are messed up.

FYI... this problem also occurs in a totally clean, not upgraded, on-premise instance of CRM 2015 as well.  

I also updated my CRM 2011 to UR 18, just to be sure that this wasn't fixed in an update release.

I have opened a support case with Microsoft to see if they are aware of this issue, and if there is a fix.  I will update this blog entry if I get a fix.

Visual Studio (VS) 2013 and CRM Explorer

I ran into this problem today after I needed to change the CRM URL on the Dev Server I am working with CRM 2015 on.

I had previously created both a Plugin project and a Web Resources project and used the CRM Explorer to browse these to extract items, setup new Plugin Steps, etc.

I was tweakign the CRM Dev server and discovered that I needed to modify the URL for the server in the Deployment Manager.

After doing so, my Visual Studio project stopped working correctly, and the CRM explorer would throw an error about 

"No Organization has been specified"

Did some searching and came across this forum post.

I figured it out: informations about CRM Server are stored in *.suo file. I just deleted this file, opened my solution in VS and entered all informations for CRM Server connection. The suo file was regenerated automatically and I can work now with my CRM solution

The answer was the *.suo file in the same directory as the VS solution file.  While the solution is closed, just delete or rename that file.  When you re-load Visual Studio next time, you may still see the error, but you can then reconnect to the new CRM server or URL.

CRM2013 : Reporting Extensions not installed?

I ran into this problem today when I had top stop the install of CRM 2013 at the end when it tried to put in the reporting extensions.

http://www.interactivewebs.com/blog/index.php/general-tips/crm-2013-reporting-is-not-enabled-message-in-crm-deployment-manager/

The original reason it wouldn't install was that the reporting services (SSRS) service account matched the same account that CRM was using for it's services.