Extending Microsoft Dynamics CRM to Office OneNote 2007

Summary: Using an extensibility model, you can build a solution that integrates Microsoft Office OneNote 2007 with a line-of-business CRM system. (13 printed pages)

Jeff Cardon, Microsoft Corporation

Rachel Drossman, Microsoft Corporation

November 2007

Applies to: Microsoft Dynamics CRM, Microsoft Office OneNote 2007

Contents:

  • Integrating CRM and OneNote 2007 with CRM2OneNote

  • Using CRM2OneNote

    • Using Show Accounts/Show Contacts

    • Downloading Notes from an Entity

    • Attaching Notes to an Entity

    • Creating an Account or Contact from OneNote 2007

  • Known Limitations of the Solution

  • Conclusion

In today’s business world, collecting customer information is essential to building customer relationships and a sales pipeline. Through advances in customer relationship management (CRM) systems, organizations are better able to track sales opportunities and capture customer information.

You can use additional tools like Microsoft Office OneNote 2007 in conjunction with a CRM system to capture rich notes about customers.

When a sales representative takes notes during a customer visit using OneNote 2007, these notes can contain images, audio recordings, embedded files, and other rich information.

Integrating a CRM system and OneNote 2007 with CRM2OneNote provides sales representatives with a streamlined process for capturing, storing, and finding relevant customer notes.

Integrating CRM and OneNote 2007 with CRM2OneNote

CRM2OneNote is a tool that joins Microsoft Dynamics CRM with the note-taking capabilities of OneNote 2007.

Figure 1. CRM Server with OneNote Clients (High-level architecture)

CRM2OneNote High level Architecture

Written in Microsoft Visual C# and designed to work with the Microsoft .NET Framework 2.0, the CRM2OneNote tool uses features of the OneNote API to communicate and collaborate with Microsoft Dynamics CRM, or other CRM systems.

This technical article presents using CRM2OneNote to update contacts and accounts in a CRM system with notes taken using OneNote 2007.

NoteNote

This tool is designed to work with CRM 3.0 and 4.0. For more details about known limitations, see Known Limitations of the Solution.

The following images demonstrate how a sales representative uses CRM2OneNote to track his customer visits.

Peter is a sales rep who works for Contoso, Inc. He is responsible for all of the Fabrikam Grocery Stores in his area and he regularly visits the stores to meet with the store manager and other department managers. He uses OneNote 2007 to take notes in his Fabrikam Grocery notebook on his laptop computer and then uses CRM2OneNote to store his customer notes in his company’s CRM system.

He starts his day in OneNote and uses the Download from CRM button that CRM2OneNote added to his OneNote tool bar to get the OneNote file that contains all of his Fabrikam Grocery information. He can select the latest section of notes and easily download them to his OneNote notebook on his laptop.

Figure 2. Before running CRM2OneNote

Before running CRM2OneNote

After Peter’s visit to Fabrikam Groceries, he can easily upload his new notes back to the CRM system, by clicking Upload to CRM in OneNote.

One of the benefits of using a CRM system is that it gives all members of the account team information about their customers. Jeff, a colleague of Peter's, also works on the Fabrikam account. Because notes from OneNote are associated with the account in the CRM system, Jeff can download the latest notes about Fabrikam before his next meeting. Since these notes are taken in OneNote 2007, Jeff not only has basic text, he also has any picture, audio, and video that his colleagues capture during their visits. When Jeff finishes his meeting at Fabrikam, he can easily upload his new notes to the CRM system to ensure that the entire account team is kept up to date.

Figure 3. Uploading new notes

Uploading new notes

Finally, as Peter and Jeff attend meetings, they may encounter new contacts or even new accounts. Using CRM2OneNote, they can create additional accounts or contacts in their CRM accounts from within the OneNote client. They can use OneNote on site with customers and use CRM2OneNote to ensure that their data is also available in the CRM system after they add it.

Figure 4. Creating accounts in CRM from OneNote

Creating new accounts in CRM from OneNote

Using CRM2OneNote

CRM2OneNote is available to customers and partners as a no-charge, shared-source download through CodePlex, the Microsoft open-source project-hosting Web site.

Setting Up CRM2OneNote

  1. Download the CRM2OneNote application from CodePlex.

  2. Run Setup.msi.

    Setup adds a CRM2OneNote button in the Standard tool bar in OneNote.

  3. Click CRM2OneNote to display the CRM2OneNote tool.

    The tool displays two tabs: one for CRM-related actions and one for OneNote-related actions.

Figure 5. Adding CRM2OneNote icon

Adding CRM2OneNote icon

The CRM tab displays all accounts or contacts in the CRM service.

Figure 6. Viewing the CRM tab

Viewing the CRM tab

The OneNote tab displays all accounts and contacts created in CRM during the session.

Figure 7. Viewing the OneNote tab

Viewing the OneNote tab

When you select an action that requires communication with the CRM service, such as Show Accounts or Show Contacts, the tool does the following:

  • Attempts to find the address to the CRM service from the registry.

  • Creates a key in the registry HKEY_CURRENT_USERS\Software\CRM2OneNote, PlatformRoot where it stores the address for future reference.

  • Prompts the user to type an address, if the tool is unable to find a valid address to the CRM service in the registry.

  • Writes the user-entered value to the key identified earlier.

  • Establishes a connection and carries out the requested action.

Using Show Accounts/Show Contacts

To retrieve a set of accounts or contacts (entities) from the CRM service, the tool must first log onto the service. It does so by supplying default credentials and making a request. The following code carries out the request.

//Retrieve a request using the following methods.
RetrieveMultipleRequest request = new RetrieveMultipleRequest();
request.ReturnDynamicEntities = true;
request.Query = query;
RetrieveMultipleResponse response = (RetrieveMultipleResponse)crmService.Execute(request);
BusinessEntity[] dynamicEntities = response.BusinessEntityCollection.BusinessEntities;

After the tool has the data, it populates a grid with the information and displays the entities. The user can then select an entity and perform an action.

Figure 8. Selecting an entity

Selecting an entity

Downloading Notes from an Entity

You can download and open notes that are currently attached to a CRM entity in OneNote 2007 to review or add additional notes.

Downloading notes

  1. Highlight an entity and click Download Notes to OneNote.

  2. The notes associated with the entity are opened in OneNote for review.

Clicking this button directs the tool to request and retrieve the attached file using the service. To do so, use the DownloadFile method as follows.

//Download the attachment using the DownloadFile method.
DownloadFile(url, fileName);
  • If the attachment is a OneNote section, CRM2OneNote copies the file to the active notebook location and opens it as a section in OneNote.

  • If the attachment is a notebook, CRM2OneNote creates a folder called _CRM_Notes in the default notebook folder and opens it in OneNote as a new notebook.

Typically, an account team member taking notes on a customer creates a single OneNote notebook per customer, with sections for the different people or departments that the account team works with.

To open a section in OneNote, the tool uses the OneNote OpenHierarchy method followed by the OneNote NavigateTo method as follows.

//Open the CRM attachment using the method.
OpenHierarchy(fileName, null, out newObjectId, OneNote.CreateFileType.cftNone);
onApp.NavigateTo(newObjectId, null, false);

The process is more complex if the attachment is a notebook. First, the tool unpacks the file and then opens it. Because it is a notebook, and OneNote is working against the local cache, the tool must ensure the cache is updated before navigating to it. After the cache is updated, the navigation can occur. The code is as follows.

//Extract the package using the OpenPackage method.
OpenPackage(fileName, crmNotebookPath, out pathOut);
 
//Open the notebook in OneNote using the OpenHierarchy method.
OpenHierarchy(pathOut, null, out newObjectId, OneNote.CreateFileType.cftNone);
 
//Ensure the notebook is synced with the cache.
while (EnsureSync(crmNotebookName))
   break;
 
//Navigate to the notebook using the NavigateTo method.
NavigateTo(newObjectId, null, false);
NoteNote

If the entity does not currently have an attachment, then generic data about the entity, such as name, address, and phone number are inserted on a new page in the current notebook section.

Attaching Notes to an Entity

You can attach OneNote notes to an entity in the CRM service. Using CRM2OneNote, you can determine whether to upload a page, a section, or notebook to an entity in the CRM system.

Attaching notes

  1. Click Upload Notes.

  2. Select the current page, section, or notebook to attach, and then click OK.

CRM2OneNote publishes the specified set of notes to the Microsoft Windows temporary folder.

NoteNote

We recommended that you use one notebook per a customer with each section representing different people or departments.

To publish a page or a section, use the Publish method.

//Publish a section using OneNote’s Publish API
Publish(objectId, fileName, OneNote.PublishFormat.pfOneNote, null);

To upload an entire notebook, use the Publish method with the publish format specified as pfOneNotePackage constant to package all sections in the notebook together into a single file, as shown in the following.

//Publish a notebook using the Publish method.
Publish(objectId, fileName, OneNote.PublishFormat.pfOneNotePackage, null);

After you create the file that contains the note, you must convert the file to a base64 string to be stored on the CRM service. To do so, use the System.Convert.ToBase64String method, as shown in the following.

//Use System.Convert.ToBase64String method to convert the file to base-64.
byte[] byteData;
using (FileStream reader = new FileStream(filePath, FileMode.Open))
{
    byteData = new byte[reader.Length];
    reader.Read(byteData, 0, (int)reader.Length);
}
 
string encodedData = System.Convert.ToBase64String(byteData);

After the file is contained in the base-64 string, upload it to the CRM service using the following code example.

//Generate the upload request using the UploadFromBase64DataAnnotationRequest method.
UploadFromBase64DataAnnotationRequest upload = new UploadFromBase64DataAnnotationRequest();
upload.AnnotationId = annotationId;
upload.FileName = strFilename;
upload.MimeType = "message/rfc822";
upload.Base64Data = encodedData;
 
//Execute the request using the UploadFromBase64DataAnnotationResponse method.
UploadFromBase64DataAnnotationResponse uploaded = (UploadFromBase64DataAnnotationResponse)crmService.Execute(upload);

Finally, after you upload the file successfully, the published notes are removed from the Windows temporary folder.

Creating an Account or Contact from OneNote 2007

In addition to attaching notes to an entity, CRM2OneNote also allows users to create a account or contact in Dynamics CRM based on data stored in OneNote. The data must be in a minimum of a 2-column table in OneNote, with the field names in the left column and the data in the right column, similar to this:

Table 1. Sample column for creating accounts in OneNote

First Name:

Brian

Last Name:

Cox

Address:

123 Main Street

The tool gets the data from the table in OneNote and parses it by the columns in the table. The tool uses the following logic to match the field names found in the table with the field names in the CRM service:

  • Strips out all spaces and non-text characters.

  • Converts all text to lower case.

For example, the field name First-Name becomes firstname.

To create an account or contact from OneNote

  1. With the cursor positioned inside the table, click Create Account in CRM or click Create Contact in CRM to create the CRM entity.

  2. An account containing information found in OneNote is automatically created in the CRM system.

Table 2. List of supported field names for Accounts

Accountnumber

*upszone

*city

Description

*country

Accountdescription

*county

*email

*fax

Mainemail

*address

fax1

*address1

ftpsite

address2

ftp

address3

accountname

Headquarters

*name

Mainaddress

Account

Addressname

Sic

*zip

Stockticker

*zipcode

Stock

*postalcode

Ticker

addressline1

Tickersymbol

Primaryaddress

Stocksymbol

*pobox

Mainphone

*postofficebox

Web

*primarycontact

*website

Contact

Webpage

*state

url

*province

*websiteurl

*phone

Internet

*telephone

Internetsite

*ups

Interneturl

Items with asterisks (*) are supported for both accounts and contacts.

Table 3. List of supported field names for Contacts:

Firstname

Cellphone

Fullname

Cell

givenname

email1

middlename

email2

middleinitial

email3

Lastname

Job

Surname

jobtitle

Street

department

streetaddress

description

mailingaddress

employeeid

street1

managername

streetaddress1

managerphone

mailingaddress1

nickname

addressline1

pager

primaryaddress

primarycontactname

address2

contactname

street2

salutation

streetaddress2

spousesname

mailingaddress2

spousename

addressline2

nameofspouse

address3

Suffix

street3

assistant

streetaddress3

assistantname

mailingaddress3

assistantphone

addressline3

children

St

childrensnames

Dayphone

externaluserid

phone1

externaluseridentifier

phonenumber

externalid

telephonenumber

ftp

workphone

Ftpsite

worknumber

ftpsiteurl

phone2

governmentid

eveningphone

Gid

eveningnumber

note

homephone

Notes

mobilephone

When CRM2OneNote receives the data from OneNote, it creates the entity in the CRM service by accessing the service and using the following code to create the entity.

//Create  an entity using the CRM Create function.
service.Create(entity);

CRM2OneNote prompts you to associate the new contact with an existing account when it creates a new contact.

Click Yes to select an account from a list of all existing accounts in the service and then click OK.

The contact is now associated with the account. The following example demonstrates the association.

//Associate contact with account using the CRM parentcustomerid function.
Customer customer = new Customer();
customer.Value = gAcctGuid;
customer.type = EntityName.account.ToString();
crmContact.parentcustomerid = customer;

CRM2OneNote creates the entity and updates the content in OneNote with metadata that contains the CRM entity’s ID. If you try to create another entity based on the same OneNote data, CRM2OneNote prompts you to update it, create an additional entity, or delete it from the CRM service.

Known Limitations of the Solution

CRM2OneNote is a proof-of-concept solution that demonstrates how OneNote 2007 can interact with Microsoft Dynamics CRM 3.0 and 4.0 server. As a proof-of-concept, it has not been rigorously tested. The following are known limitations of the solution:

  • This solution was designed and tested against the Microsoft Dynamics CRM 3.0 and 4.0 server. It may work with beta versions of later CRM systems, but was not thoroughly tested.

  • CRM supports various types of fields, including Boolean, Currency, Lookup, and String. CRM2OneNote only supports fields of type String. For a list of supported string fields, see Table 1 and Table 2 in Creating an Account or Contact from OneNote 2007.

Conclusion

CRM2OneNote shows the power and possibilities for integrating OneNote 2007 with CRM systems such as Microsoft Dynamics CRM. Using this solution allows organizations to empower their sales representatives to use the tools they feel most comfortable with to gather customer information informally, and then capture and retain the most complete customer information in the enterprise CRM systems as well.

Additional Resources