There seems to be endless amounts of help documentation and blog posts about how to setup, interact, and manipulate various pieces of the out-of-the-box feature called SharePoint Document Locations. Unfortunately for me, I’m unable to use the default out-of-the-box functionality of SharePoint Document Locations. Instead, what I want is to utilize is my own cloud drive storage solution; for example Box.com. However, there doesn’t seem to be much help on the subject, nor are there any documented solutions with other third party cloud storage products. So I ended up just created my own.
So the goal of this article is to walk you through how one can use the current Dynamics 365 SharePoint Document Locations in a way that will allow you to keep the basic structure but instead, access an external cloud drive storage system. This first article will describe the requirements, helpful FetchXml queries, along with how to add the necessary pieces to SharePoint Document Locations. I will not walk you through how to setup and configure it from scratch as there are plenty of articles on that subject.
Series Links Parts 1-4:
Now comes the fun part, the problem. As I mentioned before the problem really stems from the fact that we store our related files in an online storage solution other than Microsoft’s own SharePoint system. However, the SharePoint Document Location is a ready-to-go document storage solution that I can use with really any 3rd party source. It just needs to be fudged a little to work correctly. What I needed to do was devise a way to pull and parse that 3rd party online source (Box.com) file and folder information and filter for valid folders where I could in turn build the necessary Absolute Urls to be stored and later accessed in Dynamics 365.
- Use SSIS (SQL Server Integration Services) to download, parse, and store online folder/file information
- Determine what is a valid folder
- Store that information in a local database and build necessary urls for upload
- Push urls to Dynamics 365 SharePoint Document Locations
In order to get things started I needed to make sure I had a few tools. Listed below are a few things you’ll need to get the ball rolling. Depending upon your 3rd party online storage solution and level of expertise you may deviate a bit from the list below.
- Microsoft Visual Studio 2017/2019
- SQL Server Data Tools (SSDT) for Visual Studio (Latest Version)
- KingswaySoft SSIS Ultimate Toolkit (Latest Version)
- XrmToolBox (Latest Version)
- Plugin: Ribbon Workbench 2016
I use Microsoft Visual Studio 2017 at the moment for building my Microsoft SQL Server Integration Services (SSIS) projects. I have installed the SQL Server Data Tools (SSDT) for Visual Studio and have provided the link for version 2017 and 2019 above.
As for gathering the required data from your online storage solution (Box.com) and then uploading the finalized data to Dynamics 365 Online I’ll be using the a 3rd party tool called KingswaySoft Ultimate Toolkit. You can download and use this toolkit in conjunction with SSIS in a development environment for free. I realize if something like this was setup and then published to a production environment then of course you would need to buy a license. However, for demoing purposes it serves its need, although any tool will work. The point is to get the necessary field information from whatever online cloud storage solution you are working with into a local or online database system where one can stage and then push that data to Dynamics 365.
Dynamics 365 SharePoint Document Location Setup
First we need to setup a few things in Dynamics 365 so that when we push our Absolute Url from our intermediate custom SQL database to Dynamics 365, everything will work as designed. First navigate to your Settings > Customizations screen in Dynamics 365.
Next, click the SharePoint Sites link.
Create a Site Collection
Click the New button and create a site collection that will be referenced with your online storage solution. Below is a final view of what your site collection might look like.
A view of the Active SharePoint Sites defined in Dynamics 365 is shown below. Although the Box Site Collection is invalid we really don’t care. It’s there for filtering and categorization. Remember, we aren’t using the site collection so that we can click and manage our 3rd party online storage solution from within Dynamics 365. We are simply using the schema and metadata already built into Dynamics 365 so as to not reinvent the wheel safeguarding our published Absolute Urls.
- This is not required but useful in Dynamics 365, especially if we have identical Absolute Urls in our system but stored in different site collections.
- Use the FetchXml query defined further down in this article to retrieve the id.
- regardingobjecttypecode=1 (account entity)
- This is required by Dynamics 365 when creating a record entry into the SharePoint Document Location entity. We really only need to create Absolute Urls for the account entity.
- Here is a cheat sheet for object type codes
- Not sure what this is used for, however seems to be in a lot of other help documentation. I don’t believe it’s required nor have a found a different value other than 0, so why rock the boat.
If you were to create Relative Urls and use the SharePoint Document Location as intended without a 3rd party online storage solution then these two values would also be required. Since we aren’t going that route you can ignore these below.
- parentsiteorlocationtypecode=9508 (sharepointdocumentlocation entity)
- Here is a cheat sheet for object type codes
A view of the Active Document Locations defined in Dynamics 365 with Absolute Urls uploaded for use with our Ribbon Workbench button.
Useful Fetch XML Queries
Below are FetchXml queries that can be used in conjunction with XrmToolKit and the FetchXml Tester plugin.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" > <entity name="sharepointsite" > <all-attributes/> </entity> </fetch> <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" > <entity name="sharepointdocumentlocation" > <all-attributes/> <order attribute="createdon" descending="true" /> </entity> </fetch>
This project was rather large and crosses over many different technologies. Keeping the topics organized in a way that makes sense to both myself and the reader was by far the most challenging part of writing this 4 part series. Thank you for sticking with me through this first part as I walked you through the problem at hand, tools, project requirements, helpful FetchXml queries, and how to setup your Dynamics 365 environment.
In my next post I’ll walk you through how my 3rd party online storage solution is setup, my SQL database for staging, and then how I process that data from Box.com to Dynamics 365 using a free for development version of KingswaySoft Ultimate Toolkit.
Series Links Parts 1-4: