Chapter 21. Office Integration

Microsoft Dynamics CRM 2016 has improved and enhanced integration with Microsoft Office, especially with Microsoft Word, Excel, OneNote, and OneDrive for Business. In addition, these integrations extend from the desktop to the web client and also to the mobile client, making the usability and experience more interesting than ever before.

For more information about creating custom reports, refer to CHAPTER 16, “Reporting and Dashboards.”

Because there are no out-of-the-box Word templates for contacts, the first option you see is Create Word Template. Clicking this option starts a wizard to help you create a Word template (see Figure 21.2).

Using this wizard, you can create either Word or Excel templates. This section focuses on Microsoft Word templates; the following section focuses on Excel templates.

Because you fired this wizard from a contact record, you see the Word Template button selected and the Filter by Entity drop-down set to Contact by default. All you need to do is click the Select Entity button to open the Select Entity dialog, shown in Figure 21.3.

In the Select Entity dialog, you must explicitly select the relationships you want to have included in the template. All fields are included by default, with the exception of the lookup fields in the N:1 relationships section. Select the N:1 relationships and click the Download Template button.

When you are asked to download the Word document file to your local computer, as shown in Figure 21.4, click Open. You now see an empty Word document in which you can start writing your correspondence.

To make sure you have the Developer tab visible in Microsoft Word, click File > Options, as shown in Figure 21.5.

In the Word Options dialog that appears, click Customize Ribbon on the left and then make sure the check box for Developer is checked on the right (see Figure 21.6). Click OK to close the Word Options dialog.

Click the Developer tab that now appears on the ribbon and click XML Mapping Pane. The XML Mapping pane opens to the right, and in it you need to select urn:Microsoft-crm from the Custom XML Part drop-down (see Figure 21.7).

You now see the entity you selected for the template—in this case Contact—with the all the fields and relationships you selected before.

You choose fields here that you want to insert into the document where you want the CRM data to be replaced. To do this, right-click the field you want to insert and select Insert Content Control > Plain Text (see Figure 21.8).

When you click the text field control, you see the field inserted in the Word document with a square around (see Figure 21.9).

Drag and drop the file you edited before from Windows Explorer to the Upload Template dialog or use the Browse link to select the file (see Figure 21.11).

After you click the Upload button, you see information for the document you just uploaded. You can change the name of the document to something more descriptive name, and you can select the language. All the other fields are read-only, so you cannot change their values (see Figure 21.12).

The Exporting to Word dialog tells you your template is being generated. This process might take some time, depending on the field and relationships you have on the document (see Figure 21.14). When it is done, you are asked to download the new Word document.

When you open the new Word document. you see it populated with the CRM data of the record you selected, as shown in Figure 21.15.

To do this, you need to go to the Account entity, select a record, and then click Word Templates to create a Word template as described earlier in this chapter. When selecting entities, make sure you also select Contact from the 1:N Relationship section, as shown in Figure 21.16.

Now you need to insert a table to show the contacts. To do that, go to the Insert tab and add a table with two rows as many columns as you have fields you want to display. In this example, you can add three columns to display the first name, the last name and the email address (see Figure 21.17).

Now enter the column headers for the table. Then select the second row of the table completely and right-click contact_customer_accounts in the XML Mapping pane and select Insert Content Control > Repeating (see Figure 21.18).

Map the first name, last name, and email address from the child nodes under contact_customer_accounts similarly to the way you did it before for the account fields, but make sure you’re adding them inside the row of the table you added (see Figure 21.19).

To test the solution, save the Word document and upload it as explained earlier in this chapter. Then run it to see the results. You should see something similar to what’s shown in Figure 21.20.

Using Word templates allows you to easily incorporate advanced graphics and designs. To find design templates online, open Word and search for “invoice,” as shown in Figure 21.21. You get results that include some well-designed documents. You can use them to copy content and paste it to the Word document you downloaded from Dynamics CRM.

Personal templates are templates you create, and they are not shared with other users. If you want to have other users use your templates, you must create them as system templates. To do this, you select Settings > Document Templates (see Figure 21.22).

CRM comes with the following out-of-the-box Microsoft Office document templates, ready for you to use and modify as a quick start (see Figure 21.23):

Account Summary—You can find this template in the Account entity.

Campaign Summary—You can find this template in the Campaign entity.

Case Summary—You can find this template in the Case entity.

Invoice—You can find this template in the Invoice entity.

Opportunity Summary—You can find this template in the Opportunity entity.

For the system templates, you can select the roles you want to make the template available to by selecting the template from the list and clicking Enable Security Roles in the command bar. You then select the roles as shown in Figure 21.24. By default, the system document templates are available to all roles.

You can see that there is already an Excel template for the Opportunity entity. However, in this case, you should select Create Excel Template (see Figure 21.26) to start a wizard to help you create a Word template.

From the Create Template from CRM Data dialog, you can either create a Word or Excel template, as you saw earlier in this chapter. At this point, of course, we are focusing on Microsoft Excel templates.

Because you fired this wizard from the Opportunity entity, you see Excel Template selected by default, and you see Filter by Entity set to Opportunity. Unlike for a Word template, for an Excel template, you also need to select the view you want to use. Click Download File to download the template, as shown in Figure 21.27.

Unlike with Word templates, with Excel templates you are not asked to select the relationships because the fields that are included in the template are the ones that are part of the CRM view you selected. However, you can click the Edit Columns link to modify the fields in the Edit Columns dialog (see Figure 21.28).

When the Excel document you downloaded is opened, you see a document with the list of the CRM records that belong to the view criteria you selected (see Figure 21.29).

One of the cool things you can do now with Excel is add a chart that will become resident to the Excel document even after you upload it; this chart will show updated data the next time it’s used. To do this, select an empty cell and then select Insert > 3-D Column, as shown in Figure 21.30.

After you insert the chart, right-click the empty space inside the chart and click Select Data, as shown in Figure 21.31.

Now select the values in the Est. Revenue column in the chart data range (see Figure 21.32).

Drag and drop the file you edited before from Windows Explorer to the Upload Template dialog or use the Browse link to select the file (see Figure 21.34).

After you click the Upload button, you see information for the document you just uploaded. You can change the name of the document to something more descriptive name, and you can select the language. All the other fields are read-only, so you cannot change their values (see Figure 21.35).

You have two options here: First, you can open the Excel template in Excel Online, which doesn’t require you to have the Microsoft Excel application installed on the computer or device you are using. The second option requires you to have the Microsoft Excel application installed on your computer or device. Figure 21.37 shows the interface when you select Excel Online.

You can work with your Excel document in Excel Online. If you make changes to the Est. Revenue column, the chart changes when you do updates (see Figure 21.38).

When you are done and want to go back to the CRM list of opportunities, you just click the Return to CRM List button on the command bar, and the system alerts you that any changes will be lost, as shown in Figure 21.39. Select your appropriate action, as the data will not be saved otherwise.

Personal templates are the ones you create that only you can see. You created a personal Excel template earlier in this chapter. If you want to have all users use the templates you create, you must create them as system templates. To do this, you must select Settings > Templates.

CRM comes with the following out-of-the-box templates, ready for you to use and modify as a quick start (see Figure 21.40):

Campaign Overview—You can find this template it in the Campaign entity.

Case SLA Status—You can find this template in the Case entity.

Case Summary—You can find this template in the Case entity.

Pipeline Management—You can find this template.

Then you need to enable SharePoint integration by clicking Enable Server-Based SharePoint Integration, which starts the configuration wizard shown in Figure 21.42.

To learn more about SharePoint integration, SEE CHAPTER 27, “SharePoint.”

Click Next to continue and then select where you want to have the SharePoint server located (see Figure 21.43). For Dynamics CRM Online, you need to have an Online subscription. This example shows how to integrate with SharePoint Online, but the integration with On-Premises is very similar.

Click Next and then enter the SharePoint site URL in the dialog shown in Figure 21.44. You must use a site with HTTPS if you are working with CRM Online. Also make sure you add a / character at the end of the URL.

Click Next to start a validation process, If everything is okay, you see the word Valid in green (see Figure 21.45).

Click the Enable button, and when the configuration finishes, you see a congratulations message. Click Finish to close this dialog. When the SharePoint integration is successfully configures, you see the OneNote Integration option in the Settings > Document Management page, as shown in Figure 21.46.

Click Document Management Settings to complete the SharePoint configuration on the page that appears (see Figure 21.47).

Select the entities you want to configure, enter the SharePoint Site URL, and click Next. For this example, you can leave the default entities selected.

Under Select Folder Structure, select Base on Entity and leave it set to Account (see Figure 21.48).

After you click Next, if a confirmation dialog appears, alerting you that a document library will be created in the site you entered, click OK. When the process finishes, you see the status, as shown in Figure 21.49. Click Finish to close the dialog.

Now you can click OneNote Integration to start the OneNote integration process. You see a list of the entities that are document enabled, as shown in Figure 21.50.

Select the entities for which you want to have OneNote integration and click Finish. For this example, leave the default entities—Account, Lead, Opportunity, and Product—selected.

To verify that everything is working fine, go to any account record, and you see the OneNote link near the notes (see Figure 21.51).

If you have a custom entity or if you want one of the existing system entities not listed in the OneNote configuration list, you can go to Settings > Customizations > Customize the System. Then you can click the entity for which you want to enable OneNote integration and make sure you check the Document Management check box and OneNote Integration check box, as shown in Figure 21.52.

Clicking the Untitled link opens OneNote in the web browser, and you can now work with this document, which will be associated with the CRM record (see Figure 21.54).

There is a free version of OneDrive, and there is a paid version called OneDrive for Business. Dynamics CRM integrates with the paid version, which can be obtained through an Office 365 subscription or as a standalone product. (It is included with the enterprise plans.)

It’s a good idea to use an external repository to store CRM data–related documents for a couple reasons:

CRM storage is usually more expensive than external storage such as SharePoint or OneDrive.

CRM has a file size limitation (which can be uploaded into the notes section) that is usually lower than the SharePoint and OneDrive limits.

Click the Enable OneDrive for Business option (see Figure 21.56).

To complete the integration, check the Enable OneDrive for Business check box and click OK.

You now see a new option in the Document Management page: OneDrive for Business Folder Settings (see Figure 21.57).

If you click this option, you can specify the CRM folder where the documents will be stored, as shown in Figure 21.58.

Make sure to configure the entities for which you want to use OneDrive for Business by clicking Document Management Settings in the Document Management page, selecting the entities as shown in Figure 21.59, and clicking Next.

When you are in the document associated view, you can upload a document, and when you click the Upload button, you can select the CRM folder location, which now has the option to select OneDrive, as shown in Figure 21.61.

The first time you try to upload a document, you see a dialog asking you to change the folder location if you don’t want to use the default one, CRM (see Figure 21.62).

After you click Continue (or change the location), the file is uploaded to your OneDrive folder, as shown in Figure 21.63.

Check the Enable Office Graph Integration check box and click Next. When the process is complete, you see the success message shown in Figure 21.65. Click Finish to close the dialog.

You have to manually create a dashboard by going to the system dashboards (or your personal dashboards, if you want this only for yourself).

When you create a new dashboard, you can select any layout, and then you see the Delve button in the command bar. You can either click that or click the new icon that appears with the tooltip Insert Trending Documents (see Figure 21.66).

Insert the trending documents component and increase the width and height to fill the complete area of the dashboard, as shown in Figure 21.67. Enter a name for the dashboard and click Save.

To work with Delve, you need to create a folder in OneDrive and share it with some people. Then you can upload documents that can be shared; they might take a while to appear in Delve. If you manually open Delve and try the search feature, you see lots of results similar to what’s shown in Figure 21.68.