Products and Prices synchronization between Dynamics CRM/365 and ERP

When Dynamics 365 is used as a Sales force platform, there is often a need to identify the products that customers are buying in order to determine information such as sales price, support eligibility or other more business and industry specific information. Dynamics 365/CRM offers a set of entities and processes to manage the sales cycles. However, in most cases, the sales have to be recorded in a Finance system, often referred to as ERP (Enterprise Resource Planning) software. This article talks about what Dynamics 365/CRM has to offer on the sales process, product and price management side, what the limitations are then it comes to integrating with an an ERP and discusses some possible solutions.

Managing Sales Process and Pricing in Dynamics 365/CRM

Dynamics 365/CRM comes with a pre-defined sales process that can take you from a lead to a sales order and an invoice. It’s up to each organization to decide what entities they want to use in the process and configure it to meet their needs. The image below shows the sales cycles as defines by Out of the Box Dynamics 365 for Sales.


During the sales process, there is a level of interaction with products and prices at almost every step.

  • In the Opportunity, you enter the list of products and prices that your prospect is interested in.
  • In the quotes, you can copy the products selected in the related opportunity and update them, change the prices, rework the quote until it is accepted by the customer
  • The Order uses the products and prices defined in the accepted quote and the Invoice has the same information as the order

Prior to being made available for selection, the products and prices have to be defined and published in the system. The principle (at a very high level) is illustrated in the image below:



I’m purposely not going over details related to the Price List setup (e.g. volume discounts setup) or Opportunity (write-in products, percentage or amount based manual discount etc.). There is much more to it but I just want to give the big picture here. To sell products in CRM, you must have pre-defined Price List per currency. If you sell in a lot of regions, have a lot of different bundles with lots of product types and variances, then you might end up with a system that is loaded with products and price lists. There is no issue with this model as long as there is an established process to manage those products and price lists in your CRM system.

What are the challenges when ERP comes into play?

There are a few challenges that we see in the field as it relates to the process and structure above.

The out of the box sales process ends with the creation of a sales order and an invoice in Dynamics CRM/365. The reality is that a lot of businesses have these steps in an ERP or have to synchronize what CRM produces to a Finance system. This can be complex because the details about the products selected in the Quote/Order/Invoice have to have matching elements in the ERP system (usually SKU). This means that your product and pricing information has to be synchronized between your CRM and your ERP. This is often a challenge.

ERPs typically have more advanced ways to manage products and prices. They are not as rigid as the price-list based Dynamics 365/CRM structure. As an example, you can have product masters that serve as models for variants (e.g. product is a t-shirt, variants can be size, color, drawing etc.) and prices are automatically calculated based on the selected variables, the region and various other configurable parameters. These mechanisms present in ERP systems are in many cases more adopted for complex products and pricing structures because they allow for more flexibility and result with a model that is easier to maintain. The direct consequence for synchronizing product and price lists with Dynamics CRM/365 is that the data structure of the ERP either has to be converted into a price-list based model, or CRM has to be customized to match the ERP system’s product and pricing structure. Here are a few suggestions and guidelines when you are faced with this type of decision:

  1. If the ERP data model is not too far from the CRM model, you can simply make adjustments as required in CRM and synchronize the data between the 2 systems as needed.
  2. If the ERP data model is complex (e.g. uses product variants), ask yourself the following questions
    • Is it even possible to adapt the CRM data model to match the complex ERP structure?
    • What is the effort (data modeling, data transformation) to keep the CRM and ERP products and prices in sync? (Some ERP systems have ways to automatically generate a price-list based data structure for extraction)
    • Considering that the data model and sync issues are resolved, does the structure and volume of the data (number of products and price lists and data model) allow for a good user experience on the CRM sales side? (i.e. can users still easily find their products and price lists in CRM while working with opportunities or orders etc.). – you might have to create a custom user interface to provide your users with a better experience in order to drive adoption.
  3. Consider using a Configure Price Quote (CPQ) solution as an add-on to Dynamics CRM/365. Some of them are extremely well designed and provide the ability to configure complex product pricing structures while taking care of the user interface to build quotes (add products and automatically calculate prices etc.) and being integrated with Dynamics 365 for Sales/CRM and some ERP products.

A question is often asked: if ERPs are more flexible for pricing, why not have your sales people directly working in ERP? And the answer is simple for Dynamics 365. First, the cost, it is generally much cheaper to buy a Dynamics 365 for Sales app license than to buy an ERP license. Second, Dynamics 365/CRM is much more configurable to adapt to the business processes (creating fields, forms, views, relationships, integrated reports, sales suggestions etc.). I could go further but I won’t. Don’t have your sales people working out of your ERP, it’s generally a bad idea.

Conclusion

    Product management in Dynamics CRM/365 is a relatively rigid process. When you have a complex scenario where the products and pricing info comes from another system, it is important to carefully analyse the gap with the Dynamics 365 structure and to decide how you want to close that gap.

Happy CRM’ing!


Exchanging data between CRM Forms and IFRAMEs

One of the most common requirements when we add content in IFRAMEs or Web Resourses in CRM is to have the ability to communicate with the calling or source CRM form to perform all sorts of operation. This article explains how this can be achieved using the postMessage JavaScript messaging mechanism.

What does postMessage do?

It’s a JavaScript method which was initially created to facilitate cross-origin communication between web pages. There are often valid scenario in which we need to display a page from another website in a new window or in an IFRAME. That’s the easy part. The more complicated part is when there is a requirement to perform some operations on the source page based on a action that occurs on the external page in the new window or IFRAME.

In the example below, consider we have a contact form that displays an external page in an IFRAME. When a button in clicked in the IFRAME, information (example, a lookup value) is passed to the CRM form, the value is set on the screen and the form is saved.

Step 1 – Write methods for the CRM Form: Register Listener, Message Handler  

The code below contains two methods, the first one (RegisterListener) sets a method that will be called if the postMessage is invoked (UpdateContactLookup). 


SADAX.Contact =
{
   RegisterListener: function () {

     if (window.addEventListener) {
       window.addEventListener('message', SADAX.Contact.UpdateContactLookup, false);
     }
     else { // IE8 or earlier
       window.attachEvent('onmessage', SADAX.Contact.UpdateContactLookup);
     }
   },

   UpdateContactLookup: function (event) {

     var origin = "";
     if (event.domain) origin = event.domain; // IE
     else if (event.origin) origin = event.origin; // FireFox - Chrome

     if ("https://source" == origin) {

       if (event.data != null) {

         var entity = SadaxJSON.parse(event.data);
         if (entity) {

           var value = new Array();
           value[0] = new Object();
           value[0].id = entity.Id;
           value[0].entityType = entity.LogicalName;

           var lookup = Xrm.Page.getAttribute("sadax_referencecontactid");
           lookup.setValue(value);
Xrm.Page.data.save();
}
}
else
{
alert("This message has been posted by an unknown source ('" + origin + "', expected 'https://source').");
return;
}
} 
}

Step 2 – Register the listener on Form’s OnLoad event

image

Step 3 – Write Code for the IFRAME to post the Message from the IFRAME

After the business logic execution, the postMessage method is called on the parent CRM form as follows:

function SetContactLookup(entityReference)
{
   var entity = {};
   entity.Id = entityReference.id;
   entity.LogicalName = entityReference.entityType;

   window.parent.postMessage(SadaxJSON.stringify(entity), "https://source");
}

Comments / Wrap up

At step 3, using the postMessage on the window.parent will cause the SADAX.Contact.UpdateContactLookup method to be fired on the parent CRM form. Notice that the method receives an event object as a parameter. The content of the object slightly differs based on the browser being used (IE vs rest of the world). Mostly, you should pay attention to the event.domain or event.origin attributes. This is used to validate the website that posted the message is safe (i.e. the one you are expecting).

There is also an event.data attribute that contains parameters that are sent from one page to another. In this case, we are using a renamed JSON library to stringify our custom object types. Passing an object without stringifying it would work, but we found it didn’t work well in all browsers/version. The reason for the renamed JSON library is again browser compatibility reasons. If IE8 is out of scope for you, you probably do not need this.

Notes:

  • This also work for web resources opened in a different window using window.open or Xrm.Utility.openWebResource
  • IE8 doesn’t allow to postMessage to other windows, only to iframes.
  • I haven’t tried this in CRM Online so you can try it by yourself if need be.

Leverage the power of Power BI with Dynamics CRM data

Yesterday I attended a presentation from felow MVP Sophie Marchand about Power BI. I had previous exposure to Power BI when it became generally available for free as part of the Office 365 suite. The content of the presentation was around multiple areas in Power BI, so I thought I’d revisit some of them and put some thought into how we could leverage the power of the Business Intelligence platform to manipulate and play with Dynamics CRM data.

Connecting Dynamics CRM Online to Power BI (web)

This area is well documented. When you create or log on to you Power BI account, you can get Dynamics CRM Online data by going to the “Get Data” section, selecting “Services” and finding the Microsoft Dynamics CRM content pack.

image

Once you click on Connect, you will need to provide your Dynamics CRM Online OData Service URL (make sure you don’t leave a trailing slash in the end).

image

Once this process is completed, Power BI will connect to your Dynamics CRM Online Organization, get a set of data and build a Sales Manager Dashboard. That dashboard is pre-configured as part of the native integration between CRM Online and Power BI. Keep in mind that you can also connect to CRM Online from Power BI for Desktop. The way the data is structured is a bit different and you don’t get the Sales Manager Dashboard by default. That being said, it bring the data in using the OData Service and gives you the ability to create your own visualizations and reports. Let’s take it a step further and see what options you have if you need to manipulate data from custom entities and create visuals, dashboards that are useful for your business.

Custom Dynamics CRM data sources for Power BI

If you want to leverage Power BI, which you should in many ways, there are other ways to connect it to your CRM data. Here are some of the options that you have.

Extract Dynamics CRM in Excel Spreadsheets and Import the files to Power BI

Power BI (web)

image

Power BI for Desktop

image

For CRM On-Premise organizations, using Power BI for Desktop, you can simply connect to your CRM Database start creating for dimensions, cubes and reports from there. If you need more information on how to get started with Power BI for Desktop, make sure you check out Microsoft’s article here.

image

Worth noting, you can export your Power BI for Desktop content and upload it to the Power BI web platform. This gives you the ability to view the dashboards online, question it differently and also share it with people in your organization. In the Dynamics CRM and Microsoft ecosystem, it has never been easier to build reports and intelligence around your operational data. Enjoy!

Synchronize CRM Online Data with Skyvia

I just discovered an interesting tool, thought I’d share a quick review.

Skyvia is a cloud data integration solution. It has connectors to a few data sources and is able to move data from one cloud based source to another based on configuration and schedule. Oh I forgot to mention, it’s FREE J

As a test, I configured it to connect to my Dynamics CRM Online sandbox organization and my Zoho CRM organization:

Once my connections are created and validated, I create and design a package to transfer contacts from Dynamics CRM Online to Zoho CRM:

As you can see on the screenshot above, Skyvia gives you the ability to create synchronization tasks (1) and also to schedule the execution your packages (2). The package can also be ran manually.

The tool can be very handy especially if you have simple synchronization needs between CRM and other systems. The supported data sources are Dynamics CRM Online, MySQL, PostgreSQL, SQL Server, Salesforce.com, Zoho CRM, Sugar CRM. Obviously such a tool does not replace the need for more complete and mature data integration products such as Scribe and SSIS connectors, but it provides an easy way to achieve simple data integration between multiple cloud based systems. Works perfectly for me demos! And it’s free (at least for now).

Dynamics CRM and Address Validation

Last year I was involved in a couple of CRM projects for which we had heavy requirements for address validation. The main scenarios that we encountered are the following:

  • All existing addresses must be validated and/or corrected (existing records)
  • New addresses saved in the system should be validated and/or corrected (synchronous validation)
  • When an address is entered, there should be an auto-complete functionality to help users enter the correct address

That was content for great research. First I want to share some facts about what you usually get with address validation solutions and then I will talk about the different solutions we used for the scenarios mentioned above.

About Address Validation Tools

An address validation solution is a tool that helps make sure an address is valid according to a standard (post service, government, other). It does so by taking an address as input, parsing it, comparing it to addresses in a proprietary database using match algorithms and returning a corrected/validated when it is found. There are plenty of address validation tools out there. Most of them have some common points.

First, they are sold as web services (cloud based or on premise) or as local APIs (.NET Libraries). If you are going with a local installation, it usually includes one or more address databases along with a web service. That means you will require little space on a server to install your address validation tool. Most vendors also offer a batch address validation/correction module.

Second, most of them function the same way: you send an address as an input and you get the validated/corrected address as an output. When an address is corrected, you get the changes that have been made (e.g. postal code changed, street type added etc.). In the same logic, when an address is not validated, you also get a validation status and sometimes a reason why the address is not validated. The batch processes usually provide a way to upload an address spreadsheet and get an output spreadsheet with the validation results.

Lastly, the pricing models are similar. You get can buy a certain amount of transactions (monthly, yearly or other). The price will vary based on the set of tools selected.

Obviously, there are other things to take into account. Some vendors only offer validation for specific countries and regions, some provide more than just address validation, for example address standardization, geocoding, multi-language support and more.

  • Correcting Existing Addresses

There are many ways to tackle this. The easier way is to use the validator’s batch processing module. When using Dynamics CRM, you can easily export your address using the “Export to re-import” functionality as shown here. Once you have your spreadsheet, you can easily import it to your address validation tool and get the corrected rows back into your CRM. There are two issues with this approach:

  • There is the 10000 records limit when you export data to Excel spreadsheets. If you have more rows to correct, then you need to consider an alternative solution
  • When an address is sent for validation, you may want to indicate a validation status in CRM which you can’t do by simply reimporting the corrected rows

In our case, we decided to write an application that reads all the rows for which we need to validate an address. For each row, a web service call is made to the address validation service provider, once we get the result, we store the corrected address as well as the correction details. When an address is not corrected, we store the details (if available) and we send the record through a manual processing for someone to perform the validation manually.

  • Address must be validated when saved to CRM

If you are adding this feature when building your system (before there is any address in your CRM), then you should never need the logic described in the point above.

The design here is very simple:

  1. Create a plugin on the address save or update (could be a custom entity or OOB such as Account or Contact)
  2. The plugin calls the address validation web service
  3. The plugin handles the business logic that is executed post validation (e.g. overwrite input address or creates a manual validation activity/task linked to the address being processed

Keep in mind that in point b, we call an external web service. This design works well for on premise installations. For CRM Online, you may not be able to reach those external web services if there are not hosted in Azure.

  • Address Auto-Complete Feature

For this client request, my colleague had experience with a CRM Add-On that does just that. It is called PostCodeAnywhere. It is a light weight solution for CRM (it only contains a few web resources). Here are a few screenshots.

It is worth noting that they support a lot of different countries so it makes it a very handy tool if you work for a company that stores international addresses. You can configure it to work on OOB address fields as well as custom fields.

I will also mention that using this tool is great to help system users entering correct addresses. However, there is no way to know (from an auditing perspective) that an address was entered using the add-on. Anyone could overwrite it manually.

A few tools

Here are some of the tools that we tried, used and/or tested for address validation:

  • Service Object : Great overall experience. The product is very good and the support and sales team are very responsive, efficient and flexible. They offer validation web services for US and Canada only.
  • Melissa Data : The product list is impressive (on prem, hosted web services, SSIS integrated tools etc.). They offer a global address validation web service.
  • nCode : The product has a lot of search algorithms for address validation. It comes as an on premise web service installation. It only support Canadian addresses.

The Google MAP and Bing APIs are also worth taking a look at.

Have fun!

SSIS Tools for Dynamics CRM

ETL is an everyday subject in medium to large enterprise ERP or CRM project implementations. When it comes to Dynamics CRM, there are many different ways to insert, update and delete data. Using Microsoft’s SQL Server Integration Services (SSIS) is a good solution because of its cost (included in the SQL Server suite), development environment familiarity (Visual Studio, SQL Server Management Studio).

The problem with Dynamics CRM in this context is that its database is not supposed to be accessed directly to read, create, modify or delete data (read operations are OK using Filtered Views). These operations must be done via the application’s API. Fortunately, there are third party tools that provide a “bridge” between SSIS and CRM through custom SSIS components. These tools transform their SSIS components into MS CRM API calls in the background, allowing to perform ETL by using Microsoft’s recommended and supported approach.

Here are some tools that I’ve seen or heard of that will help do your MSCRM data processing with SSIS:

  • Kingsway Soft’s SSIS Integration Toolkit for Microsoft Dynamics CRM
    • Notes
      • Supports CRM 3.0, 4.0, 2011, 2013 and Online
      • Comes with 4 major components: Connection Manger, Source Component, Destination Component, Option Set Mapping Component
    • Pricing Information
      • Free Developer License
      • Single Server Perpetual License @ USD $1295
      • Single Server One Year License @ USD $695
      • Single Server Annual Maintenance and Upgrade @ USD $295
      • Enterprise License – Contact for details
  • RSSBus‘s Dynamics CRM SSIS Components
    • Notes
      • Supports CRM 4.0, 2011 and Online (presumably 2013 as well but no official word on that on their website)
      • Free 30 day trial available
    • Pricing Information
      • Full SSIS Component Subscription (multiple workstation, Royalty-Free distrubiton) @ USD $999
      • Single Machine License @ USD $359
  • Team4 SSIS Connector for Microsoft CRM
    • Notes
      • Supports CRM 4.0, 2011
      • It looks like the product hasn’t been updated in a while given the lack of update on the website
    • Pricing Information
      • Connector is Licenses per server @ EUR 4600
  • CozyRoc Dynamics CRM Source and Destination Components
    • Notes
      • Mature product
      • The web site doesn’t mention support for CRM 2013 (it is supported though)
    • Pricing Information
      • Varies based on the Components you buy
      • Between $400 and $2500
  • Devart’s SSIS Data FlowDevart’s SSIS Data Flow Components
    • Notes
      • Devart has a large portfolio of data integration products
    • Pricing Information
      • Single License @ USD $249.99

 

Cheers!