Integrating Source System Standardized Values to CRM

Many times, when people are building integrations they build the front end and then worry about the integration after.  While it is important to focus on the UI, having an integration that performs consistently without user intervention is also important.

I was recently building an integration for a customer that was pushing data into CRM.  The source data was provided in a data mart from multiple backend systems that the customer already had in place.  In discussing the data with IT, since this was a one way feed, the thought was to simply push the text value to CRM.  There are a couple of issues with this approach that are listed below along with a solution.

Issues:
1. The assumption that the users know what the field values mean.  For instance, if you have a "contact type" field with C, P, X, and Y as the values.  Do the users understand what those values represent?  It is possible that the users do not interact with those source systems and therefore are not aware of what those values mean.

2. Do the users know what the valid values are?  Even if they know what C, P, X, and Y represent, will they always remember that there are 4 different values and what those value are?  It may be easy to remember if you are on a contact and see the value, but what if they wish to search.  If they wish to filter their contacts by contact type or search their contacts with that field as part of the criteria, they will need to know what the possible valid values are for that field.

3. How do we ensure that the values in the source system are kept up in CRM?

Solution:
Based on the issues identified above, we realize that we will need to represent this data in CRM as an option set or lookup to a related entity.  Using one of those two controls will allow users in the front end user interface to select from a list of valid values when building an advanced find. Great, now that we have identified two design solutions for the user interface issue, on to the integration.  With the choices of Option Set or Look Up to a related entity, we need t decide which one to use.  From a user interface perspective, both controls are easy to use.  I don't want to go into the UI pluses and minuses of option set versus lookup for the user interface perspective.  However, when dealing with an integration, a look up to a related entity is the best solution.

From an integration stand point, a look up to a related entity is the clear choice.  Here is why.  Option set values must be added or removed from the CRM user interface.  Also, you cannot deactivate option set values.  It either exists or doesn't.  You may be thinking to yourself that you could use jscript to hide unwanted values from the option set choices.   That is true, but jscript only applies to forms.  It does not work with the Advanced Find interface.  All values for an option set will show up.  If that does not convince you, option sets in CRM have a text value which is displayed to the user but the value stored in the database is actually a numeric value associated with that.  This makes the integration more difficult as well.  I will cover the detail of option set text and numerical values in another blog.

If the values in your source data for the integrated field never change, things are good.  However, we all know that those things can change and will.  Maintaining an option set through an integration or having a user remember to add that value to CRM when it gets added to the backend system puts your integration at risk.

With a look up to another entity, this is much more reliably and easily accomplished.  Since each value in a look up represents a unique record in CRM, those values can be maintained via the integration as well.  For instance, if a value is added to “contact type” in the backend system, we can evaluate the source table for any additions.  If a new value is found, we can have our integration add a new record to the “contact type” lookup.  Along the same lines, if a value is removed from the “contact type” choices in the source system, that record can be deactivated in the CRM contact type table.  By deactivating the contact type record, any records in CRM referencing that value will retain the value.  It would be expected that the source systems would be updated so that invalid value no longer is associated with any contacts, but sometimes this does not happen in a timely manner.
Using this approach, we will need to evaluate the reference tables (contact type) in CRM to the valid values in the source before pushing any data to CRM.

This approach can take a little more time on the front end but will save you time on back end trying to chase down missing “contact type” values, data integration failures, etc.

In summary, this approach has you maintaining your reference or look up values prior to pushing your actual data into CRM and allows the users to know what the valid values are make the names more easily understood by end users if needed.

Show Buttons
Hide Buttons