CRM SharePoint Integration. Bulk Document Import.

Overview

The problem this post addresses is how to pre-load SharePoint with folders and files that CRM will detect when it integrates with SharePoint.  In order to do this, we need to understand how CRM names folders in SharePoint, and when it creates Document Location records.

The Basics

For those unfamiliar with CRM/SharePoint integration, Microsoft provides ample documentation.  Here is a good starting point: https://msdn.microsoft.com/en-us/library/gg327818.aspx.   The basic concept is that when the integration is set up, a user in CRM can view/upload/download documents from SharePoint from within the CRM user interface.   An entity in CRM (e.g. contact), must be selected during the integration set up in order for a user to manage documents from that entity.  Once on the entity record itself, a user can browse to the Documents tab.  If no SharePoint folder exists for this entity, CRM will prompt the user if they want to create one.

 

Example CRM prompt to create folder in SharePoint

If the user confirms the create, CRM will create a folder in SharePoint for that entity.  It will also create a Document Location record in CRM to keep track of that folder.  (A Document Location entity in CRM  stores a reference to the CRM entity and stores the URL to the SharePoint folder where the entity’s documents will be kept.)  Users are now able to upload and retrieve files from CRM.

Generating Folder Names in SharePoint

But, as mentioned earlier, what if we want to pre-populate folders and files in SharePoint so CRM users immediately see those files when they click on the Documents tab of an entity?

In order to do this, we need to understand how CRM names folders in SharePoint, and when it creates Document Location records.  When a CRM user clicks on the documents tab of an entity, CRM looks at SharePoint to see if a folder already exists with the correct name.  If the folder exists, CRM will create a Document Location record “on the fly” for that entity (if one didn’t exist previously) and immediately show the files in that folder to the CRM user.

Let’s use the contact entity as an example.  If we have set up document management on the contact entity, CRM will have created a contact folder in SharePoint at the following location: http://sharepointsiteurl/contact/.  When CRM creates a Document Location for a particular contact, it creates a folder in SharePoint with a name that is a combination of the contact entity’s fullname field, followed by an underscore, followed by the contact’s GUID (e.g. John Doe_4BECE72A534DE71181155065F38A4BA1).  If CRM finds that the folder already exists, it will not attempt to create a folder, but instead will use the existing folder.  The resulting Document Location record will have a reference to John Doe’s contact entity and the link to its folder in SharePoint at http://sharepointsiteurl/contact/John Doe_4BECE72A534DE71181155065F38A4BA1/.

Case Study:

A few weeks ago we were working with a client to move them from a legacy system to CRM and SharePoint.  We had a requirement to migrate a large number of documents to SharePoint and have them be immediately accessible to users in CRM under their contact entity.  From the legacy system, we exported users’ documents into folders that were named with their legacy id (sockeye_integrationid in CRM).

 

Exported files from legacy system

Exported files and folder structure from legacy system

 

Example CRM Contact record

Example CRM Contact record

 

In order to generate the correct name for the SharePoint folders, we queried the contact entity for the full name (fullname) and the Guid (contactid).  We also needed the id of the legacy system (sockeye_integrationid in CRM) in order to map the documents belonging to users from the old system to CRM contacts.  These records are retrieved by the GetContacts method below.

private static EntityCollection GetContacts()
{
    CrmServiceClient crmConn = new CrmServiceClient(ConfigurationManager.ConnectionStrings["CRM"].ConnectionString);
    IOrganizationService crmService = crmConn.OrganizationServiceProxy;

    string fetch = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                          <entity name='contact'>
                            <attribute name='fullname' />
                            <attribute name='contactid' />
                            <attribute name='sockeye_integrationid' />
                            <order attribute='fullname' descending='false' />
                          </entity>
                        </fetch>";

    FetchExpression exp = new FetchExpression(fetch);
    EntityCollection results = crmService.RetrieveMultiple(exp);
    return results;
}

Once we retrieved the contacts, we were ready to generate folders with the correct names, and copy the correct documents into them from the legacy system.

The CopyFiles method below takes two arguments. The first argument is the path to the exported files from the legacy system. The second argument is a path to where we want to place the folders and files that are made ready for moving into SharePoint.

In the CopyFiles method, we iterate over the folders that hold the exported files.  These folders are named with the legacy users’ ids (see image above).  We grabbed the folder name and retrieved the contact from our contacts EntityCollection where sockeye_integrationid matched the folder name.  We then created a new folder with the correct CRM Document Location name and copied the files from the exported folder to the new one.

private static void CopyFiles(string pathToExport, string pathToNewDirectory)
{
    var directories = Directory.GetDirectories(pathToExport);
    EntityCollection contacts = GetContacts();

    foreach (var dir in directories)
    {
        string folder = Path.GetFileName(dir);
        Entity contact= contacts.Entities.Where(x => x.GetAttributeValue("sockeye_integrationid") == folder).FirstOrDefault();
        if (contact != null)
        {
            string id = contact.Id.ToString().Replace("-", "");
            string fullname = contact.GetAttributeValue("fullname");
            fullname = SharePointifyFolderName(fullname);
            string folderName = string.Format("{0}_{1}", fullname, id);

            string directory = string.Format("{0}\\{1}", pathToNewDirectory, folderName);
            if (!Directory.Exists(directory))
            {
                Directory.CreateDirectory(directory);
            }

            string[] files = Directory.GetFiles(Path.Combine(pathToExport, dir));

            foreach (string file in files)
            {
                string fileName = Path.GetFileNameWithoutExtension(file);
                string extension = Path.GetExtension(file);
                fileName = SharePointifyFileName(fileName);
                string destFile = Path.Combine(directory, fileName + extension);
                File.Copy(file, destFile, true);
            }
        }
    }
}

When using the contact’s fullname field to generate the SharePoint file name, we had to account for special characters that aren’t allowed.  CRM replaces the “illegal” characters with a dash.  We replicated that functionality in the method SharePointifyFolderName.  Similarly, SharePoint has rules regarding file names, so we replaced illegal characters in the file name with an underscore in the SharePointifyFileName method.

public static string SharePointifyFolderName(string folderName)
{
    string[] illegalChars = new string[] { "~", "#", "%", "&", "*", "[", "]", @"\", ":", "<", ">", "?", "/", "|", "\"", "." };

    foreach (string s in illegalChars)
    {
        if (folderName.Contains(s))
        {
            folderName = folderName.Replace(s, "-");
        }
    }
    return folderName;
}

 

public static string SharePointifyFileName(string fileName)
{
    string[] illegalChars = new string[] { "~", "#", "%", "&", "*", "[", "]", @"\", ":", "<", ">", "?", "/", "|", "\"", "." };

    foreach (string s in illegalChars)
    {
        if (fileName.Contains(s))
        {
            fileName = fileName.Replace(s, "_");
        }
    }

    string extension = Path.GetExtension(fileName);
    string filenameOnly = Path.GetFileNameWithoutExtension(fileName);
    if (filenameOnly.Contains('.'))
    {
        //remove "." from within the file name (excluding extension) as sharepoint does not allow it.
        filenameOnly = filenameOnly.Replace(".", "-");
    }

    return filenameOnly + extension;
}

 



Once we generated folders with the correct names and files, it was time to move them into SharePoint.  There are several ways to do this.  The way we chose was a simple copy-paste with windows explorer.  In IE, when browsing to http://sharepointsiteurl/contact, you can open windows explorer from that page by clicking on the Library tab and choosing “Open with Explorer”, as shown below, and copying files as you would in your file system.   A caveat when using this method is that you are limited to copying 5000 files at a time.

SharePoint ribbon option to open windows explorer

 

2017-11-02T15:27:35+00:00

About the Author: