Free Address Validation Tool for Dynamics CRM

We’ve developed a simple and free Address Validation add-on for Microsoft Dynamics CRM. This article presents the add-on and also provides a download link.

SharpXRM Address Validation is a light weight add-on for Microsoft Dynamics CRM 2015 and 2016. As its name indicates, it is used to validate the address information of CRM record such as Contact, Account, Lead or custom entities containing address information. It uses the Bing Location API to perform the address validation and as such, we have very little control over the result(s) returned. It requires a Bing Maps API Key which you must generate from the Bing Maps portal

The solution contains a web resource that has to be inserted on the form of the target entity and configured with the appropriate fields. For example on the account entity, you can add the web resource and configure it to validate the out of the box address 1 fields. The web resource adds a button on the form (see Image 1 below). Clicking on the button will launch the validation and proceed to present the result (see Image 2). When the results are displayed to the end users, they have an opportunity to make further edits before accepting the changes.

SNAGHTML189812e

Image 1

image

Image 2

 

  • (1) Input address from the account record
  • (2) Validated address, editable (in case Bing doesn’t return all information or other changes need to be made for example to add or keep an apartment number removed by Bing)
  • (3) Confirm will save the information to the account and close the validation wizard

Want to give it a try? Download the Managed CRM solution and configuration guide.

Don’t hesitate to provide feedback @ contact@sadax-technology.com.

We also have a commercial version of the tool which uses a robust address validation API to correct single addresses or multiple in bulk using CRM workflows. For more information, reach out to us

Advertisements

Creating readable unique identifiers for your CRM records

A common requirement in CRM implementations is the need to have readable identification numbers auto-generated for custom attributes. There are various ways of achieving this. This article provides a few options to do that.  

Let’s use one scenario as a context. Let’s say we are building a Grants Management application and we are tracking scholarship applications in Dynamics CRM. We have an application entity which contains an Application Id field. For business reasons, we need to have a unique identifier to refer to the applications. What are out options? For starters, let’s look at some of the elements to consider when looking for an auto-numbering solution in Dynamics CRM?

  • Are you using an entity that is auto-number enabled? (e.g. Case, Quotes, Orders etc.)
  • Do you need the numbers to be sequential?
  • Do you have potential race conditions? (i.e. can the event assigning the auto-number be triggered by multiple processes simultaneously?)
  • Do the auto-numbers need to be human readable?
  • What is the format required? (numeric only, alpha-numeric, letters only, prefix, suffix etc.)
  • Can you and/or do you want to write code to generate these numbers or do you want an simple configurable solution?

Generate an unique number based on the current Date Time

Of course, this required a plugin on an event that will generate the application Id. From a technical standpoint, there are a few algorithms out there that will give you an indication as to how this can be done. The full date time and the “tick” should be use to generate the number to ensure uniqueness. But even with that, there is a very very very small chance of having race conditions depending on your application and on your (bad) luck. Also, this will not allow your to use sequential numbers.

Create a record of an auto-numbering CRM entity

If you are *not* using one of the CRM auto-numbering enabled entities (for example, Campaign), this solution is have a plugin (or a Workflow) on your triggering event that will create a Campaign record. This campaign will automatically be assigned a unique number based on your CRM configuration (image below). You can re-use that number as the unique ID for your record and delete the Campaign record that was created. While this is a simple method that can be implemented without coding, the numbers are not really sequential, “garbage” records are created at a cost (create/delete transactions time, what happens if you want/need to use that entity in the future?..). To be honest, it feels like a quick-and-dirty way of doing things, but it works.

The advantage here is that because the auto-numbers are platform generated, so you do not have to worry about race conditions in theory.


Use a model based on CRM Synchronous Workflow + Custom Auto-Number entity

I will not go too much into details about this one. There are lots of articles on the topic. A few “how-to” articles for reference:

Bottom line, this is a great solution if you cannot write any code and don’t want to spend any money for something that should be standard in the platform (yes, I’m ranting, can’t help it). That being said, it you have race conditions, I’m sure you can have a few duplicated numbers with this solution.

Using a 3rd Party Tool

There are plenty of options out there. Some are more flexible than others. Some explicitly guarantee uniqueness, some don’t really mention it. Some are free, some are not.

These are just a few, a research will allow you find more of these and decide the one that fits your needs the best.

Build your own solution

At this point, you can do it anyway you want.

  • If you have access to an external database, a CRM plugin that create a record in the database with an identity column will guarantee to always have unique and sequential numbers. This comes at a cost obviously (external database dependency, speed)
  • There is also a smart way of doing robust auto-numbering suggested by Ken Heiman of Green Beacon here. Good read, smart idea.
  • Some enterprises already have Auto-Numbering web services, you can simply consume that web service from a CRM plugin as well (same cost as database, dependency, speed)
  • I have also seen the idea of using the Guid as a unique identifier, by copying the value into a text field with a plugin. I don’t think it’s readable, but definitely unique.

 

It had been too long. Happy CRM’ing ! 

Selecting your Dynamics CRM developer(s) setup

If you are ever involved in a very large CRM implementation project, one of the decision that you need to make early is how to setup/structure your development environments and workstations. Not that I think it’s not as import on smaller project, but it is more critical in larger project.

First let’s start by defining what I mean by a large project (this is a completely personal):

  • Large development team (4+ people)
  • Multiple complex customizations pieces (plugins/custom activities/JavaScript/HTML) and/or integration points
  • Project length: variable, usually 6-8+ months

Last year, I wrote about how to setup your Dynamics CRM/Source Control environment properly for Dynamics CRM projects. Today, I want to focus more on the Development Machines setup and what I have found to be useful for decision making process to set them up.

In the typical CRM project, you will find a Dev CRM organization used for doing system configuration and basic customization. Then, for plugin/JavaScript development, we typically recommend to have either a dedicated VM with CRM installed for each developer, or a dedicated CRM Organization. The reason why we make this recommendation is very simple: development Isolation.

When you are developing say a web site or a mobile app, you don’t deploy your content on the test server or device that everyone uses until it’s ready to be tested. The same applies for CRM. If you have a primary development organization, why would you want developers to deploy work in progress on a regular basis? Plus, if they are working on the same module, they might overwrite each other’s code all the time. The loss of productivity will be at two levels

  • Deployed code that’s not finalized will cause bugs and people won’t be able to continue working, test in the CRM organization normally
  • Developers will likely overwrite each other if they are working on similar or dependent tasks which will result in a major loss of time and productivity

Now that I have sold you on the development isolation idea, here is a list of pros and cons that will help you decide whether you should go the individual CRM Organization way or the individual VM + CRM route.

Individual CRM Organization

  • Pros:
    • Easy to setup
    • (possibly) Part of your corporate domain which makes the access easier for developer and possibly other people if needed
    • Provides isolation for development
  • Cons
    • Developers lack control over the servers (i.e. can’t do IIS reset or restart services etc.)
    • Depending on how powerful your infrastructure is, sharing CRM/SQL Servers with multiple users, the servers might be overloaded quickly and you find yourself with an issue related to sharing your server resources
    • Removes the ability to debug the old fashion way by attaching to IIS/sandbox/async processes
    • Ongoing maintenance of the CRM/SQL server(s) hosting the organizations

clip_image002

Individual VM with CRM/SQL installed

  • Pros:
    • Provides developer with great control over the environment in which they develop
    • Provide isolation for development
    • Provide isolation for Hardware utilization (depending on how VMs are setup)
    • Easy to setup (build a VM once, create a capture and provision on need)
    • All development tools can be installed on the VM and isolated from workstation
    • Easy to do plugin/custom activities debugging by attaching to IIS/sandbox/async processes
    • Easier to perform integration test in an isolated VM
    • Relatively light use hardware resource if the VMs are hosted on developers’ workstations
  • Cons:
    • Possibly Windows OS Server license issue (each developer needs one license)
    • Network configuration can be tricky depending on your environment or Enterprise context (e.g. accessing TFS from VM, expose VM to the internet)
    • Heavy use of hardware resources (especially if you have VM Host server(s))
    • Ongoing maintenance of the VM host server and VMs

SNAGHTMLec1ea7

It’s worth noting that individual VMs can be created on a host server or on developers’ local workstations (laptops or desktop). You may or may not decide to join the VMs to the corporate domain, you could even setup your development environment in the cloud on a Virtual Network which we have done multiple times including for my company. 

Lastly, as noted by Darly Labar, if your project is with CRM Online, running VMs locally will prevent you from having a same version of Dynamics CRM in your developer VMs (CRM Online and On-prem are very similary, but not the same and not always in sync). The same applies for the individual developer CRM organization unless you have a CRM Online Sandbox for each developer or other instances of CRM Online they can use for development purpose.

There you have it. The great thing is that you always have options. From there, it’s making sure you select the one that is appropriate for your needs.

If you need help and guidance throughout this process, feel free to reach out to us at SADAX Technology. We offer “Hour Bucket” packages from 5 to 30 hours for Dynamics CRM and Azure consulting services. Request a quote here!

 

Dynamics CRM – What Options for Editable Grids?

I have yet to be on a Dynamics CRM project where no one mentions or asks about the possibility to edit information directly from a grid or a sub-grid. In the interest of saving time when I’m recommending solutions to my clients, I thought I’d build a grid to gather the options and additional information when available.

Product Name & URL Supported CRM Versions Cost Comment(s) and Links
Sparkle XRM Framework CRM 2011 CRM 2013

CRM 2015

CRM Online

Open Source Library OK. It’s not fair to call this an Editable Grid per say. Sparkle XRM is an Open Source development library to help build complex visual components for Dynamics CRM. It is initiative from MVP Scott Durow. With strong developer, you can leverage Sparkle to build your own editable grid. See how it can be done here.
AbleBridge EditAble Grid CRM Online CRM 2015

CRM 2013

Not public Out of the few products that I have tested and seen in action, it is easily the best one out there. It is also the most expensive. I guess it’s true when they say you get what you pay for. You can read my product review from last year. Editable Grid official overview
PowerObject Power Grid CRM Online CRM 2015

CRM 2013

1$ per CRM user per month MSDynamicsWorld article on PowerGrid.
Sonoma Partners Editable Grid for CRM 2013 CRM 2013 Free Anything that’s free, I happy about! That being said, it is a bit tricky to configure when you are not a bit technical. See configuration here.
MTC’s Super Grid CRM 2011 CRM 2013

CRM 2015

CRM Online

Org License : $1000 + User License : $15 per user Last I used this (about a 1.5 year ago). MTC was transitioning away from its Silverlight controls and implementing an HTML/JavaScript based editable Grid. It wasn’t perfect, but it did the job. It works by adding an HTML5 graphical UI “overlay”, to CRM standard or custom Views. What bothers me is that there is not an advanced view configurator. It does come with an API to perform these advance operations such as color coding rows/cells, hide columns, make fields required etc., so that is a good consolation prize.
BusiPlanIT’s free customizable editable Grid CRM 2013 CRM 2015

CRM Online

One grid for free – Pay for more than one. This is a great free add-on. I would pay for it. If your needs are simple and you just need to edit records inline for one record time, go for it. You need to contact BusiPlanIT for pricing information if you need more than one grid.

Obviously, there is always the possibility to build an editable grid yourself using JavaScript libraries of your choice. It’s just a matter of how much time you need and want to invest versus creating a dependency to an add-on. Eternal debate.

I thought I should also mention the availability of the Immersive Excel experience with CRM Online (2015 Update 1). Though it can be used to perform inline editing on list views, it is really meant to to do quick analysis right from CRM Online. There is plenty of articles on the subject out there. Check out Inogic’s article here.

Hope this helps!

 

Dealing with record level access restriction with Dynamics CRM

CRM works extremely well with giving users access to records based on a different set of security configuration tools : cumulative security roles with a “most permissive win” mechanism, business units, team memberships, access teams, hierarchy and so on. One thing it does not do well is restricting access to specific record. Imagine the following scenario:

  • Company ABC uses Microsoft Dynamics CRM to manage its cases
  • An employee leaves company XYZ to join ABC
  • After joining ABC, the new employee is given access to Dynamics CRM and has access to ongoing and past cases, say in his/her region
  • Company ABC has open cases against firm XYZ. The employee should not be able to see those cases because it represents a conflict of interest (e.g. he/she might give insights to his/her old friends at XYZ)

The following image provides an illustration of what we are trying to accomplish.

image

If you have worked with Dynamics CRM and know its security model, you can already read between the lines here. There are no easy ways to make this happen. This article provides a possible solution for this requirement based on a recent experience on a large CRM implementation.

Ruling out Team Membership & Ownership

In our scenario, members of a team or a business unit get access to all records  owned by their team or business unit. If we follow basic team/BU record ownership, the problem in this case is that once a record needs to be isolated from one individual member of the team, one of two things needs to happen:

  1. The record owner has to be changed (it can no longer be the team everyone is a member of) and the record needs to be shared with users who can still see it, but not with the individual that is getting the restriction applied OR
  2. The individual who is getting a restriction applied has to be removed from the team and all the records owned by his/her team need to be shared with him/her except the one he/she is restricted to see.  This technique implies heavy maintenance since the user will need to access new cases created for the team he/she used to be a member of.

Sharing in both cases can be done via the record share functionality, access team or any other mechanism that suits the business need. It should also be automated if there is a large number of records to share. You also need to save the information about the user and its restriction on a separate entity to be able to backtrack and understand why the records/ownership and so on have been modified.

Why we didn’t like the “Sharing” functionality

Using the out of the box sharing of record can cause problems in the long run. The PrincipalObjectAccess (POA) table gets bigger and bigger, causes performance issues and has to be cleaned up on a regular basis. In addition to that, the more access is controlled by sharing, the more you run the risk of having performance issues because of the complexity overhead in system lookups when it needs to display a record or list of records to users.

A solution using “Access Teams”

We decided to go with a design centered around Access Teams. The “Case” entity gets an “Access Team Template.” When a user is created and added to his/her group (team or business unit), we start a process to insert the user in the Access Team of each case related to his/her business unit. In order to restrict access to a specific case for a user, we can simply remove the user from the case’s Access Team.  For tracking purposes, we created a “Security Exception” entity that is created and has a relationship with a case and a user and an additional attribute that indicate the type and reason for the “Restriction”. To apply the access restriction on a case for a user (i.e. remove user from access team), we create a Security Exception record. We link the target case and user, and specify the restriction type. After the Security Exception record is created, a plugin is fired to perform validation and to remove the user from the case’s access team.

One of the reasons with went with that model was that we didn’t want to have a base security model that gets “broken” every time there a single person with restricted access, by changing record ownership or removing user from the team he/she is supposed to be a member of. With access teams, the record ownership remains the same, and users also remain on their team at all times which makes everybody happy and maintenance easier. The downside is that when a user is created, we have to look for all the cases in his/her business unit and manually (with code) add the user to each access team. Same when a case is created, users member of the case’s business unit have to be added to the case’s access team manually (also with code). That is the comprise we had to make.

As far as performance goes, the end results remain to be seen in the long run. Our system will have between 2000 and 3000 users when we complete our rollout. It will deal with over 1 million cases across multiple teams. Preliminary performance tests results look promising. I will repost if we get into any issue as it relates to performance.

It’s been a while! This feels great Smile  – Happy CRM’ing !

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!

Dynamics CRM Archiving Solutions Revisited

I wrote about Archiving solutions for Microsoft Dynamics CRM about 2 years ago. I still get a lot of questions from the article and from customers in the field about what they should do when it comes to archiving their Dynamics CRM data. While there is no obvious answer, my generic approach is to make sure my clients understand that they shouldn’t try to build an archiving solution before their system has become slower. They should see it more as an opportunity to perform a thorough review of their implementation and isolate the bottleneck(s) and pain point(s) and see if those can be fixed.

A few months ago, I gave a presentation on the topic that you can see below. I go from the reasons to archive data to recommendations. Hope this helps!