Microsoft Dynamics CRM and Share

Microsoft Dynamics CRM and SharePoint can provide powerful tools for our customers. In an earlier post I covered how to create Dashboards to report vital business statistics in a easy-to-view format. Another great capability of SharePoint is the ability to store documents in a central location, making them available on the corporate intranet with features like versioning, access control, check-in/check-out, etc.

In this lab I am going to cover using SharePoint as the document store for MSCRM. Although MSCRM provides the ability to upload documents related to CRM objects, using SharePoint as the document store will give our customers access to a combination of the great features of both products.

NOTE: this lab assumes you are familiar with SharePoint, SQL Server, and MSCRM; have a working install of both MSCRM and SharePoint team services; that you have VS 2005 installed (you can also do this lab in VS 2003, if you need the files please send me a note); that you know how to create virtual directories in Internet Information Services Manager; that you know how to create VS ASP.NET web projects; and that you know how to create and administer simple SQL Server databases.

Since this lab is really a proof-of-concept as opposed to a well planned integration project, I decided to limit the scope to the Opportunities object of MSCRM. So the end result of the lab will enable the MSCRM user to upload documents related to opportunities to SharePoint.

The upload component will have 2 parts:

1. A form that will allow the MSCRM user to browse their hard drive to select a file to upload.
2. A button or a link connected to a particular opportunity which will allow the MSCRM user to display any documents stored in SharePoint that are related to that opportunity.

The Lab

For step 1, create a directory called "stsupload" in your VS 2005 WebSites directory, it should be something like "C:\My Documents\Visual Studio 2005\WebSites", download the project files here and copy the contents of the ZIP file into the directory. Project files

Please note that all postings and code samples on this site are provided "AS IS" with no warranties, and confers no rights. Use of any included script or code samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm

Next open IIS manager and create a new virtual directory in the default web site in IIS. You can name your virtual directory as you wish, I chose "stsupload". If you're not sure about how to do this, see the Web forms HOL. The web forms lab covers these steps and the steps needed to run an ASP.NET v2.0 web sites on the same IIS as ASP.NET v1.1sites..

The next thing we need to do is add a "SharePoint" link to the navigation for the Opportunity object. We are going to add a link to the navigation bar on the left, right below the "History" link.

In MSCRM (NOTE you must have Administrator rights on MSCRM to perform this step) Click Settings->Customization->Export Customizations.

Scroll down and Select Isv.Config, and then Select More Actions->Export Selected Customizations.

Click Save

Choose a location and give the file a descriptive name, I usually name the file <object_being_customized>_customizations.xml. So for this example I named my XML file isv_config_customizations.xml and I would suggest a similar name. Click "Save"

At this point it is a VERY GOOD IDEA to back up your exported XML file. That way you can make your modifications on a working copy and still restore the original if you make any mistakes. I use a directory cleverly called C:\XmlBackup :) I always save the XML file to the VS project directory I am working in and open the file in VS.NET, however this is personal preference and you can use whatever editor you prefer. I also create ANOTHER backup of the XML file in the project directory!

I included my XML file with the project files, it's called isv_config_customizations.xml.

So if you follow these steps, in VS right-click your project and select "Add existing item", select your XML file and after it's added, double-click on its name to open it.

From the Edit menu choose Search and look for: <Entity name="opportunity" />

Replace the <Entity name="opportunity" /> tag with the XML below:

The next step is to import your changes. So back in MSCRM click Settings->Customization->Import Customizations. On the next screen select "Browse" and find the file you made your changes to, Click "Open", and then click "Upload"

At the next screen, Select the Isv.Config row, then click More Actions->Import Selected Customizations

You should see the screen below. Click OK.

Next, open up the file Web.Config in the root of your MSCRM web install directory (ex on my machine it is C:\Inetpub\wwwroot\mscrm) and find the line:

<add key="ISVIntegration" value="None"/>

Change this line to:
<add key="ISVIntegration" value="All"/>

Then open up a command-prompt window (Start->All Programs->Accessories->Command Prompt) and type iisreset.

Now, open up MSCRM and Click Sales->Opportunities and open an Opportunity record. You should see the link "ISV Test 1" added as shown below:

Note that these steps simply illustrated the process for changing the navigation menu to a generic http://www.microsoft.com value; we will change the URL to our actual application later.

The next step is to create some kind of a tracking system to track the CRM objects and their associated SharePoint documents. Since MSCRM uses SQL Server I decided to create a new DB and table in SQL Server for this. Note that you do not have to follow this route, you can use Access, flat files, or whatever, however I think SQL is probably the easiest way.

Open up SQL Server Enterprise Manager and expand the SQL Server Group node, then expand your SQL Server node, then expand the DataBases node. Right-click on "DataBases" and select "New DataBase". Name the new DB "crmdocs" and click OK.

Next right-click on the "crmdocs" db and select New->Table

Build the new table as shown:

and then save the table as "crmdocs".

The next thing we must do is check our SQL Server security settings. In SQL Enterprise Manager, right-click on your server, select Properties, and click the "Security" tab. Make sure the Authentication is set to "SQL Server and Windows" as shown, click OK.

If the initial setting was "Windows Only" and you changed it, you'll then see the screen below, Click "Yes". If the setting was already "SQL Server and Windows" then the window will just close.

OK so now back to VS.NET. Open up the project in VS, and double click on the upload.aspx form. Open up the code view and find the line:

// This needs to be changed to match your SQL Server
string conStr = "data source=<your server>;initial catalog=CRMDOCS;persist security info=False;user id=<valid SQL user>;password=<password>;packet size=4096";

change the text inside the <> signs to match your server and a valid username and password.

Find this line:

//This needs to be changed to match your SharePoint server
string sURI = "http://<your server>/General%20Documents";

and again change the <your server> text to match your SharePoint server URL. If you're not sure about the URL you can open up IE, browse to your STS Shared Documents Directory (It might show as General Documents or Shared Documents) and copy that info from the IE location bar, just be sure to replace any spaces in the name with %20. If using SBS 2003 the path will probably be:

http://companyweb/General%20Documents

For example, if I type http://companyweb/General%20Documents in IE, I get:

So I know http://companyweb/general%20documents is the correct path. If you click the "Documents and Lists" button at the top of the page, you will see a list of document libraries, any one of which is valid as an upload path. Clicking on any of the folders will load that folder in IE and show you its path. You can also create a new Document Library called CRM if you wish.

At this point we can build and run the project. Note that the "txtDebug.Text=" lines are there for debugging and testing. As the code runs and completes a section a line will print to the txtDebug box so you can see how far the code is getting. Click the "run" button and you should see something similar to the picture below. Notice how the status label reads "Status: Ready".

Now we can click the browse button to find a document. Any document will do for now; .txt, .doc, whatever. I created a document on my
desktop called "sts_test.txt" with some text in it. Select a document and click "Open".

Next give the file a name and then click "Upload to SharePoint". The name is intended to be descriptive, so an
end-user working on an RFP might name the document "customer rfp" or something similar.

The code will run and you should end up with something that looks similar to the screen below. Note that in addition to the lines
showing in txtDebug, the label has changed to "Status: Done".

Now the big test, did the document actually upload to SharePoint?!

If you still have your SharePoint folder open in IE hold down the <shift> key and press the refresh button. If not, open IE and browse to the SharePoint folder that you used as the path for uploaded files. The file should be there as shown below:

We also need to check SQL Server to be sure we saved the document information. Open SQL Enterprise Manager and expand the CRMDOCS DB. Right-click on the crmdocs table and select Open Table->Return all rows. There is the document info. Don't worry about the blank crmid column, that is blank because we did not load the file from within MSCRM, we'll add that later.

OK we had a successful test! The next step is to test this form from within MSCRM...

Since I usually use the same file for each test, I delete the info from the crmdocs table and the file from SharePoint in between tests. Since we already have the crmdocs table open, we can select the row and press the delete key to delete it. Select "Yes" at the prompt.

If you click on the file in SharePoint a drop-down will appear and you can select "Delete". Select Yes at the prompt. Note that you do not NEED to delete the file and SQL row, but it keeps things neater.

Ok so now we need to copy all the files from the project directory to the directory the IIS virtual directory points to.

Now we need to test again so open IE and type http://<yourservername>/stsupload/upload.aspx and you should see a screen similar to the one shown below. This test is running the form via IIS whereas the previous test was running the form from inside VS.NET.

OK, all working! Now we need to add a Button to our Opportunity form in MSCRM to enable the MSCRM user to call the form to upload a document. Open up the XML file that you saved when we did the export (isv_config_customizations.xml or whatever you named the file), and add the following code to the Opportunity Entity, the <toolbar> XML comes before the NavBar XML we entered earlier and on my server my web form URL is http://sbsserver/stsupload/upload.aspx, so my XML ends up like this:

Follow the steps to import the customizations as we did earlier. After the successful import, open an Opportunity record and the button should be there as shown below:

Click the button and the form will load and now notice that the Debug text box displays the GUID of the MSCRM opportunity the form was loaded from! If you choose a file and go through the upload steps as we did before you will see the GUID information is now saved to SQL Server along with the other columns.

Now the last step for this form is to hide the Debug text box and size the form. In the VS.NET designer pane, select the txtDebug component and set its visible property to "False". Rebuild the project and copy the project files to the IIS directory as we did earlier. Now open the Isv.config.xml file again and go back to the tag where we added the button.

Change the tag to the text as shown below, this will size the window the upload form appears in:

<Button Title="STS Upload" ToolTip="Upload a file to SharePoint" Icon="/_imgs/ico_18_debug.gif" Url="http://sbsserver/stsupload/upload.aspx" PassParams="1" WinParams="height=190,width=315,status=yes,toolbar=no,menubar=no,location=no" WinMode="0" />

Re-import the XML file, open an Opportunity in MSCRM and click the button. The form should be a much nicer size as shown below:

Run a test upload and then following the same steps as earlier verify the file is in SharePoint and the information has been updated in SQL Server. Do not delete the file or the SQL Server data, we need it for the next part of the lab. NOTE: Make sure you remember which Opportunity you upload the SharePoint document to!

OK so now we have completed the code to enable us to upload a document to SharePoint and have that document related to our CRM Object via the Object GUID. Next we are going to link the other form in the project which will display any documents stored in SharePoint that are related to a particular CRM record. This form is called sts_list.aspx. Go back to the Isv.config.xml file, to the Navbar tag we added to the Opportunity entity earlier and change it to read as shown below:

NOTE: If you are confused about the format of the XML file, look at the XML file included with the project files, isv_config_customiations.xml.

The import the customizations as we did earlier, open the same Opportunity record where you earlier uploaded a document to SharePoint and you should see something similar to the following screen with the Navbar item now named "List Files"

Click the "List Files" button and the sts_list.aspx form will run and return the list of files stored in SharePoint that are related to this CRM opportunity:

Click the filename, the file contents display in the CRM window

We can also right-click on the file and select "Save Target As"...We can see the file being accessed from SharePoint.

And of course we can always access SharePoint and see the file...

So the custom button on the toolbar is one possibility for accessing our upload form, but now with v3 MSCRM gives us the ability to display IFRAMES as part of our record forms. So let's display the upload form in an IFRAME.

In MSCRM click Settings->Customization->Customize Entities and then double-click the "Opportunity" row to load the customization pane for Opportunities. Click "Forms and Views" and then double-click the "Form" row:

We are going to add a new tab for a SharePoint Upload, so on the right-side click "Add a Tab"

Name the Tab "SharePoint Upload" and click "OK".

Click "Add a Section" and name it "Upload a document to SharePoint" and click "OK"

Click in the new section to highlight it, and click "Add an IFRAME". Fill in the fields as shown, make sure the URL field has the URL to your SharePoint Upload Form, it will be the same URL we added to Isv.config.xml file. Be sure to uncheck the "restrict cross-frame scripting" box. Also be sure to check the "Pass record object-type code and unique identifier as parameters" box.

Next click the "Formatting" tab and select "Automatically expand to use available space". Click "OK" and then click "Save and Close", and then "Save and Close" again.

Back in the main screen, make sure "Opportunity" is highlighted and then select "Publish" from the toolbar.

We can now test the form. Click Sales->Opportunities and open an Opportunity record and then click the "SharePoint Upload" tab. Our form appears in an IFRAME on the Tab!

You can now follow the steps to upload a document to SharePoint as we did earlier.

So this lab has demonstrated how to create a simple, yet powerful, integration between MSCRM and SharePoint. A good next step would be to create some kind of administration mechanism for the files that are uploaded. The way this project stands right now if the file is deleted from SharePoint the file information will still remain in SQL Server. Another good next step would be to expand the project so that ALL CRM Records (i.e. not just opportunities) can use SharePoint as the document store.

If you have any ideas on cool expansions for this project please drop me a line!