The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Auto Numbers in Microsoft Dynamics CRM

Auto Numbers in Microsoft Dynamics CRM

  • Comments 7

Today we welcome our guest blogger and CRM MVP Ayaz Ahmad.

Last week I looked for the best approach to use auto numbers in Microsoft Dynamics CRM. After discussion with my peers I have come to the following possible options that can be used for auto numbering. These may not be the most efficient techniques to get auto numbers to work in CRM, but they are all workable and easy to implement solutions.

Option 1:

Very simple, supported and mostly acceptable way of auto numbering is to read the attribute Max value and then add 1 to get next auto number. For example you have a custom entity named Project and you want to auto number Project_Ref_Number attribute. But do not retrieve all records. Just retrieve the record with maximum number value by using following two properties of PageInfo Class in SDK and set descending order.

PageInfo.Count = 1;
PageInfo.PageNumber = 1;

One more consideration should be taken in account to register your auto number plugin to at PreCreate rather at PostCreate.

Issues:

In multi-user environment, this approach can come up with duplicate numbers in Project_Ref_Number when more than one user is creating project records.

Option 2:

This technique requires a bit more work but still supported way of doing things and all the time you will be getting 100% unique number.

You need to create a new table for project entity in a different database and not in CRM default database.

create table dbo.ProjectNumbers
(
Project_Ref_Number int identity(1,1) primary key clustered,
Projectid uniqueidentifier not null
)
go

Create a stored procedure for inserting a record into new database. This will increase the performance of your insert query.

create procedure dbo.proc_new_ProjectNumber

@ProjectId uniqueidentifier,

@Project_Ref_Number int output

as

insert into dbo.ProjectNumber

(

ProjectId

)

values

(

@ProjectId

)

select @Project_Ref_Number = scope_identity()

go

In your Plugin/Callout:

1. Access you database using ADO.NET

2. Execute stored procedure and get next number from returned results

3. Set the value of the returned number to target entity attribute appropriately.

4. All Done

Issues:

1. You have to setup database.

2. You have to read and insert into external database.

Option 3:

This option used a lock mechanism on a text file placed somewhere at your webserver. You can create one file to store next number for all entities or you can create one file for each entity. In your Plugin/Callout:

1. Put a lock on file.

2. Read the file and read the number there.

3. Update the number by adding 1.

4. Release the lock.

5. Use this number and set your target entity attribute appropriately.

string ProjectAutoNumber = "FilePath"

lock(this)

{

TextReader textReader = File.OpenText(ProjectAutoNumber);

AutoNumber = textReader.ReadLine();

textReader.Close();

AutoNumber = (long.Parse(AutoNumber) + 1).ToString();

TextWriter textWriter = File.CreateText(ProjectAutoNumber);

textWriter.WriteLine(AutoNumber);

textWriter.Close();

}

Issues:

1. Resource locking

2. File system Read/write cost

Although all these options work well but I am still looking for a more robust solution. Ideally I am looking for something using GUIDs which are instantly available and 100% unique.

For your suggestions and improvements, please comment.

Ayaz Ahmad

  • PingBack from http://www.travel-hilarity.com/airline_travel/?p=4500

  • We are also interested in auto-number implementations for our CRM deployment.  We are working on creating a very simple webservice that takes a string and returns a number.  This way you can pass in the entity name (or anything else for that matter) and it will return to you the next number for that string.  Behind the scenes, the webservice will probably use the locking mechanism you describe (option 3).  However, because the webservice provides a nice facade above this logic, we can change the backend implementation if needed and all the plugins will continue working with no changes.

  • Hi Ayaz,

    We faced a similar problem on a project recently. We needed to provide a unique, but human readable id number for opportunities.

    We ruled out an external database as it was essential the functionality worked for the offline client too. The locking option was not a good fit for the same reason, and in terms of performance (1000+ users)

    The solution we used was to create an integer attribute on the opportunity. We leverage a post create callout on the opportunity to fill in this value.

    Similar to option 1, the callout performs a read operation sorting the opportunities by date created desc. This ensures the most recently created opportunity is returned at the top of the result set.

    By adding 1 to the value of the integer attribute we can determine the next number (or use 1 if no records are returned)

    Issues:

    A duplicate number can occur if the most recent opportunity is deleted - this should not be an issue in most scenarios as the new number effectively replaces it, but it can be an issue if an ETL has extracted data into an external data repository

  • Hi

    We have created a counter functionality. It is available for free on http://www.crmextensions.com, under free extensions.

    It works for 3.0 and 4.0 with prefixes, negative counting. The solution is fully supported build on a callout/plugin and a custom entity.

    Best regards

    Mads Møgelvan Nielsen

    CRM Extensions

  • Hi Josh Painter,

    Thanks for sharing your solution. Its always a good idea to use facade design pattern in solutions like this. But i personally recommend you to use CRM custom entity for generating auto numbers. In this way your solution will work both in offline/online and also for increasing number of user licenses. Secondly you can add lot more functionality to auto numners as minimum number, maximum number. Reset numbers ofr all custom entities and everything will be in a supported environment.

    Thanks again for sharing.

    Ayaz

  • I am sure this is not supported, but on a custom entity we accomplished auto numbering by creating an integer field and then manually editing the field in SQL to set it to an auto number field.  This was setup in CRM 3.0 and works fine in 4.0.

  • I have also implemented auto numbering feature in the same way. Can anybody suggest what the downside for this SQL db change is? I am completely aware with the fact that in case of upgrade I have to put these changes explicitly in newer version of MS CRM.

Page 1 of 1 (7 items)
Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post