Quotes are considered commonplace in almost any business. The sales team issue quotes to potential clients providing an estimate of the cost required to provide a service or product. In Microsoft Dynamics CDS/CRM, quotes can be created and tracked from opportunity to close with information added by users along the way. In most cases, a Dynamics CDS/CRM quote involves an opportunity, a customer, notes and attachments, and a customer address in addition to information on the product or service and its price. During the entire sales process, it is a given that your quote goes through many revisions as the customer and sales team negotiate a proposal. In Dynamics, the edited quote is given a new revision number incremental to the previous one assigned to the quote record. Therefore, the entire history of a quote is stored and available for review. In a prior blog post, we shared a Dynamics CDS/CRM Quote migration solution to Migrate Quote From One Dynamics CRM System to Another. In that example, we were only migrating the most recent quotes. Nevertheless, we understand the importance of quote revisions in helping businesses track the sale development process. In this blog post, we expand on our previous post and show you how to use our software to migrate Dynamics CDS/CRM quotes with revisions in SSIS.
To start, let’s take an in-depth look at the Dynamics CDS/CRM quote lifecycle.
Dynamics CDS/CRM Quote Lifecycle
The Quote Lifecycle, briefly described above, looks like this:
A quote is in the draft state by default after being created. A quote in the draft state is editable, and can also be activated, after which it is no longer editable until it is revised. When the “Revise” action takes place, the state of the quote changes back to draft, and a new quote record is generated with an incremented revision number.
The quote can only be converted to order (won) or closed when it is in the Active state. A quote in Won/Closed form can also be revised. A quote record can be revised multiple times, with the revision number increasing by one for each revision. The quotenumber field is the link to the revisions, which would remain the same among all revisions.
Quote Migration
When it comes to quote migration with the revised quote records, you may come across the following challenges:
- The revision number cannot be set directly.
- CDS/CRM does not allow creating multiple quote records with the same quote number.
- When a quote is revised, CDS/CRM automatically creates a new quote record in the draft state with a system-generated quoteid guid value.
Considering the behavior during the quote lifecycle, we are separating our migration solution into a two-step process. First, migrate the quotes with 0 revision number, then migrate the revised quote by each revision number. We use quotenumber and revisionnumber as the matching criteria when migrating the revised quotes.
Migrate Quotes with 0 Revision in Draft State
To read 0 revision quotes from the Source system, a FetchXML query such as the following can be used:
<fetch mapping='logical'>
<entity name='quote'>
<all-attributes />
<filter>
<condition attribute='revisionnumber' operator='eq' value='0'/>
</filter>
</entity>
</fetch>
Next, map the Dynamics CDS/CRM Source component to a Dynamics CDS/CRM Destination component to perform Upsert quote action and use Primary Key as the matching criteria, so that the quoteid guid value remains the same between the two systems. Note the statecode and statuscode fields are unmapped, so that all the 0 revision quotes are migrated in the draft state.
This step is a simple field-to-field mapping data flow design like this:
Migrate quotedetails for 0 Revision Quotes
After the quote records are migrated, it’s time to migrate quote product line items, which are stored in quotedetail entity.
Use a FetchXML query such as this to read quote product line items from the 0 revision quotes:
<fetch mapping='logical'>
<entity name='quotedetail'>
<all-attributes />
<!-- Left join with systemuser entity to bring in user's full name in order to perform Text Lookup-->
<link-entity name="systemuser" from="systemuserid" to="ownerid" alias='user' link-type="outer">
<attribute name="fullname" />
</link-entity>
<!-- Left join with team entity to bring in team's name in order to perform Text Lookup-->
<link-entity name="team" from="teamid" to="ownerid" alias='team' link-type="outer">
<attribute name="name" />
</link-entity>
<!-- Left join with quote entity to filter on the revisionnumber -->
<link-entity name="quote" from="quoteid" to="quoteid" alias="quote" link-type="inner">
<attribute name='revisionnumber'/>
<filter>
<condition attribute="revisionnumber" operator="eq" value="0"/>
</filter>
</link-entity>
</entity>
</fetch>
Next, use Text Lookup on the ownerid field and owner name since it is not available in the quotedetail entity. Perform two left joins to bring in a user’s full name and team’s name. With a Derived Column expression, generate the owner name as follows:
- [owneridtype]=="team"?[team.name]:[user.fullname]
Next, direct the input data into the target CDS/CRM system to Upsert quotedetail records. The data flow of the migrate quote product would be:
Update 0 Revision Quote Status
The following step updates the 0 revision quotes’ status. We migrated all 0 revision quotes in the draft state and therefore need to update the quote status for quotes in Active, Won and Closed states.
Use the following FetchXML query to retrieve quote records in Active/Won/Closed state from the source CDS/CRM system:
<fetch mapping='logical'>
<entity name='quote'>
<attribute name='quoteid'/>
<attribute name='statecode'/>
<attribute name='statuscode'/>
<filter>
<condition attribute="statecode" operator="ne" value="0" />
<condition attribute='revisionnumber' operator='eq' value="0"/>
</filter>
<link-entity name="quoteclose" from="quoteid" to="quoteid" alias='quoteclose' link-type="outer">
<attribute name="subject" />
<attribute name="actualend" />
<attribute name="actualstart" />
<attribute name="description" />
<attribute name="createdon" />
</link-entity>
</entity>
</fetch>
A left join with quoteclose entity is used in the FetchXML query to bring quoteclose activity details from the Source CDS/CRM system. When a quote is closed, a “Quote Close” activity is created with the details of the “Quote Close” action. When updating the closed quotes’ status, map the virtual quote close fields in Dynamics CDS/CRM Destination component so that when you close a quote along with supplying these virtual fields, our component sends these values to the corresponding fields when closing the quote records. This is done through only one service call.
The entire update quote status data flow looks like this:
As of now, we have completed the quote records migration for 0 revision quotes. Next, we migrate the revised quotes.
Get Max Revision Number from Source CDS/CRM
The revised quote migration begins with getting the maximum quote revision number from the source CDS/CRM system. Use the FetchXML query below to read the max quote revision number:
<fetch mapping='logical' aggregate='true'>
<entity name='quote'>
<attribute name='revisionnumber' alias='max_revision' aggregate='max'/>
</entity>
</fetch>
Create two Int32 type variables in SSIS, @[User::MaxRevision] and @[User::Revision], to temporarily store the max and the current revision number. Then use WriteValueToVariable() function offered in our Premium Derived Column component to set the variable values. Our Premium Derived Column component offers a familiar UI to that of the built-in Derived Column component with over 225 additional functions, an advanced expression editor, advanced data type detection and the ability to validate and test expressions with test values. The WriteValuetoVariable() function sets the value of a variable to the result of a variable expression.
- WriteValueToVariable( @[User::MaxRevision], [max_revision] )
- WriteValueToVariable( @[User::Revision], 1 )@[User::MaxRevision] variable value is set to the max quote revision number retrieved from the source CDS/CRM instance and @[User::Revision] variable value is default to 1.
Revise Quote
In our v20.1 release, we have added ReviseQuote action support to Dynamics CDS/CRM Destination component. Use the following FetchXML query to read quotenumber and revisionnumber for the particular revision from the source CDS/CRM system.
<fetch mapping='logical'>
<entity name='quote'>
<attribute name="name" />
<attribute name="quotenumber" />
<attribute name="revisionnumber" />
<filter>
<condition attribute='revisionnumber' operator='eq' value="@[User::Revision]"/>
</filter>
</entity>
</fetch>
Note at this moment, the revision number of the quote records in the target CDS/CRM system is one less than the records in the source system. Hence, a Derived Column expression is used to generate the target’s current revision number:
- current_revision_in _target: @[User::Revision]-1
Due to the fact that quote lifecycle needs to be respected during the migration, use quotenumber and revisionnumber as the matching criteria when working with the revised quotes. As shown in the screenshot below, use Text Lookup with Manually Specify option with quotenumber and revisionnumber fields on the quoteid field when performing the ReviseQuote action.
Delete quotedetails of Revised Quotes in Target CRM
When a quote is revised, Dynamics CDS/CRM creates a new quote with the same values for the fields. That means the product line items of the revised quotes are the same as the previous revision when revising a quote. The quote products may change between the quote revisions, so first delete the quotedetails of the revised quotes, then migrate quotedetails of the revised quotes from the source system.
Use the following FetchXML to retrieve the specific quote revision’s product items from the target system:
<fetch mapping='logical'>
<entity name='quotedetail'>
<attribute name='quotedetailid'/>
<link-entity name="quote" from="quoteid" to="quoteid" alias='q' link-type="inner">
<filter>
<condition attribute='revisionnumber' operator='eq' value='@[User::Revision]'/>
</filter>
</link-entity>
</entity>
</fetch>
Then link the Source component to Dynamics CDS/CRM Destination component to delete the quotedetails from the target system.
Migrate quotedetails of Revised Quotes and Update Revised Quote Status
Similar to the step taken to migrate 0 revision quotes, we need to migrate the quotedetails of the revised quotes and update the quote status after completing the revised quote migration. The data flow designs are very much the same as the “Migrate quotedetails for 0 Revision Quotes” and “Update 0 Revision Quote Status” steps, except use quotenumber and revisionnumber fields to identify the quote for the quoteid fields.
Increase Current Revision Value and Set For Loop Container
The final step is to increase the @[User::Revision] current revision variable value and configure a For Loop Container to iterate over the migration of each revision until the max revision quotes are migrated.
Use our Premium Expression Task, a control flow component that can be used to evaluate an expression and assign the result to a variable during runtime, to increase the variable value:
Then configure the For Loop Container with the following EvalExpression to specify when the loop stops.
- @[User::Revision]<=@[User::MaxRevision]
The overall Control flow design of the package is shown in the screenshot below:
Sample Package
To help make it easy to understand, we have uploaded a sample SSIS package, which you can try out by downloading from the link below:
The sample package was prepared in a SQL Server 2008 R2 format, and it should automatically upgrade if you are using any newer version of SSIS.
Important Notes
Here are some final notes before we conclude this blog post.
- You need to use version v20.1 or later of our SSIS Integration Toolkit for Microsoft Dynamics 365 and SSIS Productivity Pack toolkits to run the provided sample migration package.
- Our migration solution is designed for one-time migration usage. In case you have two systems running at the same time, you can use our sample package as a reference to design the migration flow with the mechanism to distinguish the synced quotes and the newly created/updated quotes.
The full history of a quote is important for businesses as an analysis tool and to help set KPIs. There's no need to leave quote revisions behind when migrating your data from one Dynamics system to another. Have a question about the procedure discussed in this blog post, feel free to contact us; we are more than happy to assist you.