Last week I have been looking 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. Although these may not be the efficient techniques to get auto number work in CRM but they are all workable 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 approperiatly.
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 some more robust solution. Ideally I am looking for something like GUID. GUID’s are instantly available and 100% unique.
For your suggestions and improvements, please comment.
![View Ayaz Ahmad [MVP - MSCRM]'s profile on LinkedIn](http://www.linkedin.com/img/webpromo/btn_linkedin_120x30.gif){kind=small}
