Friday, 6 March 2015

How to reset Auto-numbering and where does CRM stores Prefix and Number

I Recently came across a question on a forum about how to update Auto-numbering number and where does CRM stores it in Database? I have looked in CRM tables and found that CRM stores it in [OrganizationBase] table. But as it is unsupported to update in SQL, I was not sure that if updating the number will work or not. I personally avoid doing unsupported things. But to find out the consequences I decided to give it a go in my Development environment. 

Note: To update Auto-Numbering by Supported way, have a look at How to reset Auto-numbering in Dynamics CRM Online.

Following query returned the current auto number values.

SELECT [KbPrefix]
      ,[CurrentKbNumber]
      ,[CasePrefix]
      ,[CurrentCaseNumber]
      ,[ContractPrefix]
      ,[CurrentContractNumber]
      ,[QuotePrefix]
      ,[CurrentQuoteNumber]
      ,[OrderPrefix]
      ,[CurrentOrderNumber]
      ,[InvoicePrefix]
      ,[CurrentInvoiceNumber]
      ,[UniqueSpecifierLength]     
  FROM [dbo].[OrganizationBase]

Then executed the following query to reset number:

Update [dbo].[OrganizationBase]
set [CurrentKbNumber] = 10000;

Resetting number only can cause duplication if you already have records with that number. So to avoid it I have updated the Prefix as well. 

CRM allows to update Prefix using UI but Number field is locked as shown below:

Got to Settings > Administration > Auto-numbering


Now on new Article creation, I can notice that Number has been set to 10000. Done some light testing and could not find any issues.

Note: It is not a Microsoft supported way of updating Auto-numbering, I would recommend to update Auto-Numbering Supported Way by updating the Organization using sdk.

P.S. Hayer