This entry was posted on Thursday, December 13th, 2007 at 4:59 pm and is filed under General CRM. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Removing Closed Orders and Opportunities in CRM
Ok so i am sure that everyone has seen the following error. I ran into this during deployment of our own integration. We had a few users that had completed some records as “tests” only. We quickly found out that this was a problem that needed to be fixed without dragging out the CRM Re-Re-Deployment tool…
To make this happen all you are trying to do is update the StateCode of a particular entity, and assign a related StatusCode. If you do not know the numbers for proper crm statecode and statuscodes there is a great article that lists these codes here: http://msdn2.microsoft.com/en-us/library/ms914670.aspx
I first tried Workflow to accomplish the task at hand. I got a cool error that basically said that i cannot update the selected read-only attribute. There is a list of usefull error codes and their meanings from the following site: http://msdn2.microsoft.com/en-us/library/ms936570.aspx
I also tried through Javascript, and then i even delved into the CRM SDK and API (Truly the developer coming out in me). So after all that, i decided to result to more primitive means of modification – Directly updating MSSQL!
So… I resorted to the depths of directly modifying CRM SQL Tables for some resolution.
It is important to note that a backup of your *_MSCRM & *_Metabase databases should be performed before you update any settings in Microsoft CRM Databases.
Although unsupported from Microsoft it is possible to remove records in CRM that have been completed.
The most important thing to remember is that you DO NOT want to remove the record completely within SQL. You only want to perform an update to the StateCode field on the record you want to remove. This will then safey let MSCRM remove the opportunity, lead, or quote programmatically and disassociate records properly through the application.
For the sake of this example we will say that we are trying to update and remove a closed quote.
I would first open the quote in CRM that you are wanting to remove and take down it’s QuoteID Number. In my case the QuoteID is QUO-01042-0PH7Z7.
Next, we simply open up SQL 2005 Management Studio, or Enterprise Manager (For SQL 2000) and then run the following query on the (Companyname_MSCRM) Database.
* Running the select statement below change the QUO# to the quote you wish to remove.
select * from dbo.QuoteBase where QuoteNumber = ‘QUO-01042-0PH7Z7′
The query above should return (1) result. If it has returned a result then you are ready to update. You can also skip the first step of selecting the quote, but i always like to make sure that the quote is available to edit.
* The following 2 statements will update the quote and make it available as a Draft and in progress for updating.
update dbo.QuoteBase set statecode = ‘0′ where QuoteNumber = ‘QUO-01042-0PH7Z7′
update dbo.QuoteBase set statuscode = ‘1′ where QuoteNumber = ‘QUO-01042-0PH7Z7′
After the Quote has been updated you can now remove the quote from CRM. The process is the same for leads, invoices, opportunities and quotes. The following quote above is in draft mode and in progress and can be deleted now from CRM.
It is also very important to remember you must work backwards when deleting these records from CRM. For example if this quote had an order and an invoice you would change the status and statecodes on the invoice then delete it, then the order and delete, then the quote…. etc, etc…..
Leave a Reply
You must be logged in to post a comment.
