Migrate CRM close or resolution entity records in your ETL process

31 March 2017
Daniel Cai

Microsoft Dynamics 365/CRM uses some close entities to capture the reason why the primary record was closed with some additional information. For instance, the CRM opportunity comes with an opportunityclose entity. If you try to close a CRM opportunity record, you will be prompted with the following window.

CRM Dialog to Close Opportunity

All the values provided in the above window will be captured in the opportunityclose entity record while the opportunity record itself is closed with a statecode of Closed.

Opportunity entity is not the only entity with this behavior. There are a few other entities that have almost identical situation. The following is the list of such entities.

Primary Entity Close/Resolution Entity
incident incidentresolution
opportunity opportunityclose
quote quoteclose
salesorder orderclose

When it comes to data migration, you would typically want to retain the information stored in those close entity records when writing to the target CRM system. As far as CRM API is concerned, you can write to those close entity records directly, and they are available in our CRM destination component if you are using the Create action. However, there is a problem here if you choose to do so. Assuming that you will be migrating the primary entity (opportunity, quote, salesorder, and incident), all those primary records that are closed in the source system are supposed to be closed in the target system as well using an Update to its statecode and statuscode fields. When doing so, the Update action will create a new close entity record (for opportunity entity, it will be a new opportunityclose record). If you are also migrating the opportunityclose entity, you will end up having two duplicate opportunityclose records for the same primary entity record. This is an undesired situation for your migration, as you want your target system to have the full fidelity to your source system by having only one opportunityclose record.

For this particular reason, we have added support for those close entity fields in the primary entity in our software since our v7.2 release on August 10, 2016. The idea is, when you migrate the primary entity, you would also retrieve data from those associated close entity. The information from the close entity will be mapped to a few virtual fields in primary entity directly, when our software tries to close the primary entity record in the target system, we will try to write to the close entity record using the values from those virtual fields.

Let's see how it works in action, and we use opportunity entity as the example again. As you can see from the following screenshot, you can see that we have added a few virtual fields started with an opportunityclose_ prefix when you work with opportunity entity.

OpportunityClose Virtual Fields

In order for the destination component to work properly, we need to surface the information from the close entity properly within our source component. Assuming that we plan to read all fields from the opportunity entity in the source system, we can use a FetchXML query as below.

<fetch mapping='logical'>
    <entity name='opportunity'>
        <all-attributes />
        <!-- Left join with opportunityclose entity bring in the necessary info to generate corresponding close record -->
        <link-entity name="opportunityclose" from="opportunityid" to="opportunityid" alias='oc' link-type="outer">
            <attribute name="actualrevenue" />
            <attribute name="actualend" />
            <attribute name="description" />
            <attribute name="competitorid" />
            <attribute name="createdon" />
            <order attribute="createdon" descending="true"/>

Note that we are using a LEFT outer JOIN to get data from the opportunity. The query will look like something below in a CRM source component.

CRM Source Component to Read Opportunity and OpportunityClose records

After reading data from the source component, we can immediately send it to a CRM destination component for Upsert (or Create) action. In the destination component, we will configure the component to map those virtual opportunityclose fields to the upstream pipeline columns from the joined opportunityclose entity. The mapping will look something like the below.

OpportunityClose Virtual Fields Mapped

When the destination component receives a closed opportunity record, it will perform a Close operation of the opportunity record in the target system, at the same time, we will create a corresponding opportunityclose record with the information received in those virtual fields. The end result is we will get only one opportunityclose record for each closed opportunity record instead of 2 which would be the case without the capability.

The same technique can be used for all the above mentioned entities that support a corresponding close or resolution entity.

As a closing comment, I would like to mention, we have demonstrated this technique for all the above mentioned entities in our CRM Migration Starter Pack that I previously shared.

I hope this helps.