Sonoma Partners Microsoft CRM and Salesforce Blog

There’s never been a better time to join Sonoma Partners!

Thanks to the great success of Microsoft CRM, our company has been growing consistently over the past few years. And right now, we have multiple job openings that' we’re trying to fill. These positions include:

  • .NET Software Developer  (Chicago)
  • Microsoft SQL Database Developer  (Chicago)
  • Microsoft CRM Project Manager/Consultant  (Chicago, Denver)
  • Microsoft CRM Sales and Business Development  (Denver)
  • Business Systems Analyst (Chicago, Denver)

We already offer a great employee benefit package, but today we expanded it even further by announcing new employee benefits for 2010:  

  • We’re expanding our work-from-home program to EVERY Wednesday (it used to be just every other Wednesday).  From Memorial Day to Labor Day, we still offer Summer Hours where people can adjust their work so that they can work from home on Friday (not Wednesday) and take off at 1:00pm. After Labor Day, it will be work-from-home on every Wednesday again.
  • Everyone will get their birthday off as paid time-off in 2010! We got this idea from the Gen Y award application. We liked it so much, we decided to steal it for ourselves. :) Don’t worry if your birthday falls on a Saturday or Sunday, you can take off the preceding Friday or following Monday.

If you’re interested in any of the jobs listed above, please contact us ASAP! We’d love to speak with you. You can even apply online for these positions through our website.


As part of the Gen Y award that we recently won, they put together a quick photo tour of our Chicago office if you want to see what our place looks like.

Data Integration/Migration using SQL Integration Services (SSIS) 2008

Today we welcome our guest blogger and fellow Dynamics CRM MVP, Darren Liu who discusses using SSIS with Dynamics CRM.

I wrote a blog article last year on how to integrate Microsoft Dynamics CRM using SQL Integration Service (SSIS) 2005 (http://tiny.cc/BQiiR) . I hope that article provided you with an alternative solution for your integration/data migration project with CRM. Due to the limited capabilities in SSIS 2005 with CRM web services, we created a proxy class as a work around to provide easy access to the CRM API.

With the new release of SQL server 2008, there were some improvements which simplify the integration of SSIS and CRM. It does not require you to create the proxy class anymore since SSIS 2008 allows you to add web references within the script component object. It also allows you to code in the language that I like the most, C#.

Here I would like to share with all of you again how to leverage SSIS 2008 to integrate with CRM without the proxy class so that you can use it on your next CRM data integration/migration project.

Before we get started, here’s the list of requirements:

  • SQL Server 2008 Standard/Enterprise Edition with SQL Integration Service Installed
  • Microsoft Dynamics CRM 4.0
  • Visual Studio 2008 Professional Edition SP 1 with Business Intelligence Tools Installed
  • CRM SDK and C# knowledge

In this blog, I will use a similar example to show you how to send contact data stored in an Excel document to MSCRM 4.0 via CRM Web Services using SSIS.

Source Data

Source data is data from the other system that you would like to send to the CRM system. Your source data can be a text file, a database, etc… Since we often use Excel to collect our information, I will use a simple Excel document as my source data for this blog article.

Source Data: Excel Spread Sheet

First Name

Last Name

Phone

Email Address

Darren

Liu

312-888-8888

liu@sonomapartners.com

Mike

Snyder

312-999-9999

snyder@sonomapartners.com

Jim

Steger

312-555-5555

steger@sonomapartners.com

Create SSIS Package

Launch Visual Studio 2008 to start a New Integration Services Project

After creating the project, follow the steps below to set up the SSIS package.

  • Rename Package.dtsx to Contact.dtsx

Add Control Flow Items

Drag and drop a “Data Flow Task” from the Toolbox to the Control Flow Design Pane.

Add Data Flow Items

Double click on the Data Flow Task item that you just added and it will take you to the Data Flow Design Pane. Here we will specify the source data and also to write script to send data to CRM.

Specify Source Data

  • Since our source data is an Excel document, drag and drop the Excel Source from the Toolbox to the design pane.
  • Double click Excel Source to open the Excel Source Editor.
  • Click New… button to open the Excel Connection Manager to specify the Excel file path, and then click OK.
  • Select “Table or View” from Data access mode dropdown box.
  • Select “Sheet1$” from Name of the Excel sheet dropdown box.
  • Click OK to close the Excel Source Editor window.

Setup Script Component

  • Drag and Drop Script Component to the design pane.
  • Select Transformation and then click OK.
  • Connect the two shapes by dragging the green arrow from Excel Source to Script Component.
  • Double click the script component to open up the Script Transformation Editor.
  • Select the columns that you would like to send to MSCRM from the Input Column window. In this example, I selected First Name, Last Name, Phone and Email Address.
  • Remove Output in the Inputs and Outputs section since we are not going to output anything in this example.
  • Click on the Script tab, click on Edit Script button. The Visual Studio window should open.

Add CRM Services

Since SSIS 2008 allows you to add web references in the Script Component, we will add the two web service references in this step. Please be aware that you must save the script component project by clicking the Save button on the toolbar after you added the CRM web references, otherwise the web references will not load next time you reopen the script component.

  • Right click on the project in the Project Explorer window.
  • Select Add Web Reference… from the menu.
     
  • Repeat the steps above to add the CRM metadata service if necessary.

Coding the Package

In order to use the web reference in our code, we need to include the CrmSdk web reference to the script component project. To get the script component namespace, right click on the project and select Properties… from the menu. The namespace is in the Default namespace textbox. In this example, my script component name space is SC_ad0e4b91cb7e48cdb8fa2d240e3e5c30.csproj.

 

I added the following statement to my project.
using SC_ad0e4b91cb7e48cdb8fa2d240e3e5c30.csproj.CrmSdk;

Lastly, copy and paste the following code to the ScriptMain section:

    private CrmService service = null;

public override void PreExecute()
{
base.PreExecute();

CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0;
token.OrganizationName = "AdventureWorkCycles";

service = new CrmService();
service.Url = "http://localhost/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
}

public override void PostExecute()
{
base.PostExecute();
}

public override void ContactInput_ProcessInputRow(ContactInputBuffer Row)
{
contact cont = new contact();

if (!Row.FirstName_IsNull)
{
cont.firstname = Row.FirstName;
}

if (!Row.LastName_IsNull)
{
cont.lastname = Row.LastName;
}

if (!Row.Phone_IsNull)
{
cont.telephone1 = Row.Phone;
}

if (!Row.Email_IsNull)
{
cont.emailaddress1 = Row.Email;
}

service.Create(cont);
}


After coding the SSIS package, right-click on the Contact.dtsx package and then select Execute Package. After the package has executed successfully, you should see the records in CRM.

  

Deploy the SSIS Package

After successfully testing the package, deploying the package is pretty easy. It requires the same steps as the previous version of SSIS. I have included the steps again below.

  • Right-click on the CRM 4.0 SSIS project and then select Properties.
  • Click on the Deployment Utility tab and set the Create Deployment Utility property to True.


  • Recompile the CRM 4.0 SSIS project. You should see CRM 4.0 SSIS.SSISDeploymentManifest in the bin\Deployment folder.
  • Double-click on the manifest file and follow the wizard to deploy the SSIS package to your SQL server.

Summary

That’s all there is to it! Hopefully you have gotten the idea of how to use the latest version of SSIS to send data to CRM. SSIS 2008 has a lot of improvements to make our jobs easier to integrate systems. In this sample, I only demonstrated how to import records in CRM. In an actual data integration or migration implementation, we still have a lot more to consider such as updating, deleting and error handling. This is one of the many approaches that you can use to integrate/migrate data with CRM. I hope this will help you in your next CRM project.

IntelliSense for Sitemap and ISV.Config

My colleague, Jeff, recently reminded me about setting up schema validation for the Sitemap and ISV.Config files. Microsoft provides an excellent article covering all aspects of configuration of modifying these two important files including setting up the schema validation. However, if you are only interested in setting up the schema validation, you can do so by doing the following:

  1. Download the CRM 4.0 SDK. The files you will need are located in the schemas folder of the SDK.
  2. Open the xml file in Visual Studio.
  3. Press F4 to bring up the properties dialog.


  4. Copy the following lines into the Schemas value. Keep all as one line and be sure to update your paths to where your files are located:
    "G:\Source\CRMSDK\server\schemas\_root\SiteMapType.xsd"
    "G:\Source\CRMSDK\server\schemas\importexport\customizations.xsd"
    "G:\Source\CRMSDK\server\schemas\importexport\isv.config.xsd"

Now, when you start to edit your file, you will see the IntelliSense for your nodes and any malformed nodes will be highlighted.


Adding Dynamic Menu Items

Today we welcome our guest blogger and colleague, Blake Scarlavai who discusses adding dynamic menu items to the CRM application.

The ISV config is helpful for when you need to add static menu items to the top of your entity form, but what about when you need dynamic menu items?  In this blog post I will show you how to easily add dynamic menu items to the top of your entity form with the help of JavaScript and jQuery.  I will be using the Opportunity entity and adding a menu of links to web pages at the top of the form.

First we need to add our base menu and a dummy menu item that will be used to help us create our dynamic menu items.  We will do this in the ISV config like so:

<Entity name="opportunity">
      <MenuBar>
            <CustomMenus>
                  <Menu>
                        <Titles>
                              <Title LCID="1033" Text="Links" />
                        </Titles>                
                        <MenuItem JavaScript="">
                              <Titles>
                                    <Title LCID="1033" Text="Dummy Node" />
                              </Titles>
                        </MenuItem>
                  </Menu>
            </CustomMenus>
      </MenuBar>
</Entity>

Here is what the menu looks like on our Opportunity form before we add any code:

Now that we have added our Links menu and dummy menu item, we can start writing the code. 

My colleague Jeff Klosinski showed us how to dynamically load jQuery onto our CRM form in his blog post, http://blog.sonomapartners.com/2009/10/streamline-the-creation-of-new-related-records-with-jquery.html, which I reference below.

As we are going to be using jQuery, we will need to load it onto the form.  In this example, I dynamically load jQuery from Microsoft CDN (Content Delivery Network).  I’ll also declare Entity_OnLoad which is the function that will be called when the form is loaded.  Here is what the code looks like:

var arr = new Array(),
jQueryUrl = 'http://ajax.microsoft.com/ajax/jquery/jquery-1.3.2.min.js',
obj = document.createElement("<script src='" + jQueryUrl + "' type='text/javascript'>");
arr = document.getElementsByTagName("head");
arr[0].insertAdjacentElement("beforeEnd", obj);
obj.attachEvent("onreadystatechange", Script_OnLoad);

function Script_OnLoad()
{
var rs = event.srcElement.readyState;

// check to see if the script is done loading
if (rs == "loaded" || rs == "complete")
{
Entity_OnLoad(); // jQuery is done loading and we can continue
}
}


function Entity_OnLoad()
{
// nothing yet
}


Note: Loading the jQuery library directly from Microsoft CDN may not be permitted in some environments and may not be available for offline/disconnected environments. Alternatively, you could copy the library locally to your CRM Web server or even paste the code from the library directly to the top of your form’s onLoad event.

Next we will create the function that returns our links:

function getLinks()
{
    return [{ Name: "Bing", Url: "http://www.bing.com" }, { Name: "Sonoma Partners", Url: "http://www.sonomapartners.com" },
            { Name : "Facebook", Url : "http://www.facebook.com" }, { Name : "Twitter", Url : "http://www.twitter.com" }];
}

For the sake of this blog post I am just returning an array of an object that contains the name of the link that I want to display for the menu item and the actual url that I want to send the user to when they click the menu item.  In production use, consider storing these links in a custom entity and use a web service to return the data. 

Next we will create the function to return our dummy menu item that was created in the ISV config.

function getDummyMenuItem()
{
    return $("li.ms-crm-MenuItem-Label[innerText='Dummy Node']");
}

In the code above, I am using jQuery to get the <li> element where the class is “ms-crm-MenuItem-Label” and the inner text is “Dummy Node”.  This will find our “Dummy Node” menu item that was created in the ISV config and note that we are returning it as a jQuery object.

Next we will create our main function that we will call when the entity form loads:

function createLinksMenu()
{
    // Get our dummy node so we can clone it
    var dummyNode = getDummyMenuItem();

var links = getLinks();
if (!links || links.length == 0)
{
// Remove our dummy node if there is nothing to add
dummyNode.remove();
return;
}

// Loop through all the links
// then clone the dummy node, update it as necessary and append it to the menu
for (var i = 0; i < links.length; i++)
{
var clonedNode = dummyNode.clone(true);
clonedNode.find("span.ms-crm-MenuItem-Text")
.text(links[i].Name)
.end()
.attr("action", "window.open('" + links[i].Url + "');")
.attr("title", links[i].Name)
.appendTo(dummyNode.parent());
}

// Lastly remove our dummy node
dummyNode.remove();
}


In the code above, I am first getting the “Dummy Node” menu item as a jQuery object.  We are returning this first as we may have to remove it if no links are returned.  Next we are returning our links that we will use for our menu items.  Then we check if any links are being returned, if there are then we can continue on, if not we will remove our “Dummy Node” menu item from the menu.  If any links are returned then we loop through each one, clone the dummy node using jQuery, find the span with the class of ms-crm-MenuItem-Text (the actual display of the menu item), and we change it’s text to the name of our link.  We then call end to revert the jQuery collection back to the cloned node and change the “action” attribute to open our link in a new window.  Then we append our new cloned node to the dummy node’s parent.  Lastly we remove our “Dummy Node” menu item from the menu.

Now we have all the functions written and we can update our Entity_OnLoad function to call our main function createLinksMenu:

function Entity_OnLoad()
{
    createLinksMenu();
}

Once we import the ISV config and add this JavaScript to the entity form, then all we have to do is publish the entity and we are complete.  Here is the finished product:   

You could also easily extend this approach to conditionally display links based on record attributes (such as status) or the user’s security role.

Enjoy!

Option for a “Quick Create” of CRM Records

Back in the early Microsoft CRM days (remember those?), you had the option to ‘Quick Create’ a lead, contact or account. This was a native form that only included the required (and maybe the recommended fields, but can’t remember anymore!). This concept comes up from time to time with our customers who are sometimes need a simple and fast way to get records entered into the system.

Of course, we could custom develop a Web page for them, but another alternative could be to use the Mobile Express feature. As you know Mobile Express comes standard with your CRM installation assuming you are at least on UR6 or above, but can also be downloaded separately for those on UR5 or earlier. Mobile Express allows for a separate form configuration natively that can be customized specifically for mobile form entry. Now just take the resulting edit form URL, pop it into an ISV.Config button/menu, and viola a simple data entry form!

The URL would typically look like: https://[your specified domain]/[Organization]/m/ef.aspx?etn=[entity].

You can then specify the link either directly in the ISV.Config as a button or menu item, or even open using JavaScript to provide even more control of the resulting window. An example of each approach would resemble the following:

<Root>
  <MenuBar>
    <CustomMenus>
      <Menu>
        <Titles>
          <Title LCID="1033" Text="Quick Create Record" />
        </Titles>
        <MenuItem Url="https://[domain]/[org]/m/ef.aspx?etn=account">
          <Titles>
            <Title LCID="1033" Text="Account" />
          </Titles>
        </MenuItem>
        <MenuItem JavaScript="window.open('https://[domain]/[org]/m/ef.aspx?etn=account','','scrolling=yes,toolbar=no,status=yes,resizable=yes,menubar=no,location=no,height=400,width=300');">
          <Titles>
            <Title LCID="1033" Text="Account w/JS" />
          </Titles>
        </MenuItem>
      </Menu>
    </CustomMenus>
  </MenuBar>
</Root>

So, even if you aren’t exposing the Mobile Express Web pages to the Internet (i.e. your mobile device), you could leverage this approach as a simple and supported way for your users to get data into CRM via an alternative form interface.

Upcoming CRM User Group Webinar - Using CRM with Outlook

Back by popular demand, I will be repeating the Using CRM and Outlook session from the recent CRM User Group Conference with a free webinar.

Here are the details:

CRMUG SIG - Sales Process, Using CRM with Outlook
12/10/2009, 11:30 AM -  1:00 PM Eastern Standard Time

Users can leverage the functionality of Dynamics CRM directly from within Outlook.  Join us to learn more about the native CRM integration with Outlook, including synchronizing appointments, contacts and tasks, tracking e-mail, configuration options, and helpful tips.  We also delve into CRM’s offline access through Outlook and show you how to use a simple customization to better control contact synchronization.

This is a free event, but you need to be a CRMUG member to register for the session. Once you register, you will receive the Live Meeting and conference number details. If you aren’t a CRMUG member yet, CRMUG does offer a complimentary Preview Membership (90-day trial).  Click on Membership on the left menu at www.crmug.com

Hope to see you at the session.

Microsoft Dynamics xRM – Professional Services (Part 2)

Abstract: This is a continuation of my last blog were I spoke about using CRM as an xRM platform to to track time.  In this blog I will discuss how you can use Microsoft Dynamics CRM to track project expenses. 

Business Requirement: Track and submit project expenses tied to cost categories.  Integrate to any accounting system to facilitate and process expense reports.

Proposed Solution: Microsoft Dynamics CRM

First of all, I am not recommending that Microsoft Dynamics CRM replaces your accounting package.  On the contrary, your accounting package is vital to adhering to proper accounting principals/practices and for business critical reporting needs.  However, I am proposing that we can streamline data entry and accessibility by utilizing Microsoft Dynamics CRM as the primary user interface. 

For a professional services organization expense reports are tied to projects (those projects are either tied directly to a customer or are deemed internal expenses).  Because you are using CRM to track your customers it makes sense that we create Expense Report entities within CRM to track corresponding expenses.  If you look at my previous post you’ll see I discussed tracking time against your customers and their projects.  We now take that one step further by tracking expenses and providing data entry screens within the CRM application.

MyExpenseReports

The first step is to create a new expense report.  The expense report contains 1 to many expense items. Think of the expense report as a container holding your specific expense items.

ExpenseReportForm

Once your expense report is created you can add expenses to the report.  Because CRM is so extensible you are able to create whatever fields you require when tracking expense items.  Expense codes, budgets, vendors, etc. can all be integrated to your accounting application for validation purposes.  Add and remove fields as desired – this is simply an example of what’s capable with the application.

ExpenseReportItem

Once you are finished adding expenses you simply submit the expense report for approval.  Microsoft Dynamics CRM utilizes the Windows Workflow Foundation so we can build our own customized workflow rules to manage the expense report process.  In this example we have a simple rule that notifies the appropriate individuals when a report needs to be approved and when it has been paid.  These workflow rules are created using the Microsoft Dynamics CRM workflow admin interface so you can create workflow rules specific to your business needs.

ExpenseReportWorkflow

The expenses have now been created, submitted, approved, and paid all through Microsoft Dynamics CRM.  In addition, you can now create reports for expenses while pivoting on data that resides in any related entity like projects. 

The opportunities are truly endless with Microsoft Dynamics CRM. 

Adding Related Money Fields to a View

Recently Mike tried adding a money attribute from the Account entity on one of our existing Contact views. For illustration purposes, let’s assume it was the contact’s parent account’s credit limit field as shown below:

Much to his surprise, he received a generic CRM dialog box when he tried to run the resulting contact query.

After a bit of research, it turns out that CRM is expecting a transactionCurrencyId for that related entity, in this case account. Therefore, the solution was to either add the Currency field (transactioncurrencyid) from the related entity or you can add the money attribute’s Base value instead (assuming you aren’t utilizing multi-currency).

Note that if the money attribute of the records returned from your query are null, the query will work. However, once that field is populated with data for one of the returned records, you will receive the error.

Also, we had a case where adding a money field from the main entity caused a similar problem. In that instance, we had some records missing a transactioncurrencyid value (possibly from a mangled import). So, be sure to check your data and ensure that valid transactioncurrencyid exists on all your records.

Hope this helps!

Building Appropriate Relationships

When CRM 4.0 came out, one of the announcements that had me very excited was the introduction of Many:Many relationships. At the time, it sounded like such a useful feature! I couldn’t begin to count how many times in the past did I need to relate many of one entity to several of another. However, the first time that I went to use this new feature, I found myself stuck. The main dilemma is that you are unable to capture any details about that relationship. Let’s look at an example of different relationships that may need to exist between Contacts and Accounts.

Using the out of the box relationship between Accounts and Contacts limits you in that each Contact must have the Account listed as the Parent Account.

Shot 1 

However, if you wanted to track that Jim is the Principal at Sonoma Partners and a Microsoft MVP, you could not relate that Contact record to both Accounts. This appears to be a very appropriate use of N:N relationships.

Shot 2 
One advantage to this approach is that the user interface is very simple to use. Simply click Add Existing Account from a Contact record and select the Account to create the relationship for. However, no additional information about the relationship between the Contact and Account can be stored, for example, the role of the Contact within that Account. For Accounts, Contacts, and Opportunities, you could also use the Relationships feature but this would not allow you to track information above and beyond the role, such as when Jim became an MVP at Microsoft.

To handle the tracking of this additional information, the best approach remains an additional entity to serve as a cross reference between Accounts and Contacts. This entity would store lookups to the Account and Contact along with any additional information about the relationship that needs to be tracked.

Shot 3

Scribe vBooks

Our friends at Scribe have a new training resource available called vBooks. For Scribe users, check it out by creating a login to the Scribe vOffice and use the new Technical Resources vBook. Use this link to register for the Technical Resources vBook  and use the registration code "technical".  You'll have immediate access to the Scribe Technical Resources, including:

  • Introduction to Support  
  • Configuring Your Scribe Insight Server, and
  • Optimizing Performance (also read Brendan's blog on this topic)

Further the content (video) is indexed, allowing you to search for your subject easily!

Also, Scribe is hosting a 45 minute vBook Webinar this Thursday, December 3rd at 11am EST. Click here to register for the event!