Home > CRM Development, MSCRM General > Auto Numbers in MSCRM

Auto Numbers in MSCRM


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.

Advertisements
  1. May 2, 2008 at 6:24 pm

    We use a custom entity in CRM called AutoNumber that tracks the max used autonumber by entity type. We use a post create callout to populate that entity type’s max value + 1 to the newly-created record, and also to write that new max value to the entry in the AutoNumber record for the entity type. That way the process fires when offline clients sync their new records to the central database and you end up with no duplicate record contention, since that part of the post callout is (I believe) transaction bound to ensure we don’t get dupes.

  2. May 4, 2008 at 2:33 am

    Well, Thanks for another good way of handling Auto Numbers.

  3. May 6, 2008 at 9:23 am

    We have created a little custom free product for this purpose. Have a look at this here: http://www.crmextensions.com/Page.aspx?id={3920FF75-FEFE-DB11-9607-0003FF3B5E8E} It creates mutex’es ect. to ensure unique creation of numbers. It can also do pre-fixing, post-fixing, count up/down ect.

  4. May 6, 2008 at 9:26 am

    And here’s a fix for the link 🙂

  5. May 6, 2008 at 9:27 am
  6. June 10, 2008 at 9:49 am

    One more thing i missed in the post is, please keep in mind if you have offline client then file and database techniques may not work. In this scenario, using a custom entity would be the ideal solution.

  7. June 19, 2008 at 11:09 am

    Somehow i missed the point. Probably lost in translation 🙂 Anyway … nice blog to visit.

    cheers, Isotropism.

  8. July 2, 2008 at 8:32 pm

    Hi, Great ideas above. Will any of these work through client-side scripting only? We are missing a coder!

    -Randy

  9. July 4, 2008 at 3:45 am

    Randy,

    Its not a good idea to use client side scripting for generation of readable incremental numbers (Autonumber). Although you can create uniquenumbers using some date and time mechanism at client side but they will not be 100% unique.

  10. Andrew Whiteside
    July 9, 2008 at 4:34 am

    Hi Ayaz

    Great post! There is no need for the extra field in the table for method 2. Just create a table that contains the auto-number and insert DEFAULT VALUES. The number will increment on its own.

    ie

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

    Then

    INSERT INTO ProjectNumbers DEFAULT VALUES

    in the SP…

  11. July 14, 2008 at 4:13 am

    Yes andrew, you are right!

    But i recommend developers, not use external DB. As its an overhead. While you can easily create the same in CRM entities. There are still few considerations that i missed int his article are:

    CRM Laptop client : when offline access is required your external db will no longer available. Also if user makes changes offline and add some records, how are you going to handle them when connecting online. So you need to plan your plugin to generate autonumbers very carefull and keep in mind all possible limitations and considerations.

  12. Hans Nellestijn
    October 2, 2008 at 8:27 am

    Ayaz,

    I understand your recommendation for not using an extarnal DB.

    But, I think using an external DB is the only way to implement a correct, supported way of locking the number while increasing. Using a custom CRM entity and accessing that in a supported way (i.e. update it using sdk webservices) does NOT garantee unique numbers in a multi user environment (I can’t figure out a way to lock the value during read-increase-update).

  13. MaSahara
    October 7, 2008 at 1:29 pm

    Thanks for this blog.

    I used a similar file lock method, and it works very well.

    The only issue was granting write access rights the the directory i was using, as i elected to store the file in a sub directory within the crm ISV folder. It seems the async service does not have write access to the file system, or if it does, i couldnt figure out where.

    The logic was built into a workflow that fires on record creation.

  14. October 7, 2008 at 10:49 pm

    Hi Hans Nellestijn,

    You are right that we cannt have 100% pure autonumbers. But if you are using CRM offline client then you dont have access to External DB and that created a deployement issue and hence a bad design. So i think its a job at design time to specify how we are going to generate autonumbers.

  15. October 7, 2008 at 10:52 pm

    Hi MaSahara,

    Generating autonumber through workflows is not a good idea. and using file locking will create issue while working in offline mode. It depends upon how you are designing deployements architecture for your crm implementation.

  16. MaSahara
    October 13, 2008 at 11:16 am

    Hi Ayaz,

    Why is workflows a bad idea? too much overhead?

    Some background:

    We are creating appointments enmasse from an external source. These need to be allocated a unique and human readable autonumber. these appointments are then propogated to an offline third party application.

    This offline third party applicationcan also generate new appointments, which then get synced back to the server, althoguh these are far less as far as volumes go.

    It made the most sense to therefore set an autonumber workflow to fire on appointment creation, redardless of the source of that appointment, to fill the autonumber field.

    In the case of the mass load, this does cause hundreds of workflows to fire.

    The appointments created in the third party application will therefore not have an autonumber allocated until those records arrive on the server, and these are then passed back to the that application in a subsequent sync.

    it seemed the best way to ensure a unique ID across all records, regardless of their source, was to use a post create workflow.

    It is critical that we do not duplicate this ID.

    That said…

    the problem have found this morning for the first time during the mass load, is that if one of these autonumber workflows freeze for whatever reason, then no other workflows fire, regardless of their function, across all tenants on the server.

    So that would seem that the async service does not thread instances of workflows, but rather executes them sequentially. so the file lock is not really needed, and you could rather use a workflow and a custom Autonumber entity.

    I am investigating Casper Jensen at CrmExtensions.com ‘s free solution that uses entities and Mutex’s.

    Though i would love to hear your input on this.

  17. October 13, 2008 at 11:51 pm

    as per my understanding of your problem. you need a unique id for each appointment.

    Its a bad idea to generate autonumbers through workflows because they are async in nature. And we are creating a custom entity to carry the next autonumber.

    Rather using a workflow, i advise you to use a plugin assembly, syncronous one and on post create event. Although it works fine most of the time but there is probability of error (non unique numebrs).

  18. MaSahara
    October 14, 2008 at 8:26 am

    Thanks for the speedy replies, Ayaz.

    I have moved the logic back to to a plugin as per your suggestion, and discovered the reason i went for w workflow.. haha

    it just does not fire. i have attached it to the create appointment event, tried parent and child and messed around with everything i can think of.

    if i attach it to another entity, it fires every time.

    I have asked around the office, and another developer said he had the same problem with plugins on appointment creation, and was never able to resolve it. His solutionis now also in a workflow.

    Any idea why? should i be using a different event instead of create?

  19. October 20, 2008 at 10:18 pm

    Hi MaSahara,

    You can have plugin that fire on create of appointment. I have created a simple plugin and its working for for me. No fency coding. I can attach a debugger and place a breakpoint. If you look into CRM SDK install folder and go into tools folder and there you can find an excel file. That excel file lists all the plugin events available to appointment entity.

    using System;
    using System.Collections.Generic;
    using System.Text;

    using Microsoft.Crm.Sdk;
    using Microsoft.Crm.Sdk.Query;
    using Microsoft.Crm.SdkTypeProxy;

    namespace testplugin
    {
    public class Class1 : IPlugin
    {
    public void Execute(IPluginExecutionContext context)
    {
    string message = context.MessageName;
    string entityname = context.PrimaryEntityName;
    ICrmService service = context.CreateCrmService(false);
    string wfname = “”;

    try
    {
    throw new InvalidPluginExecutionException(“Test Error”);
    }
    catch (System.Web.Services.Protocols.SoapException ex)
    {
    throw new InvalidPluginExecutionException(String.Format(“Exception with {0} during plugin {1}, with message {2}, {3}”, message + ” on ” + entityname, wfname, ex.Message, ex.Detail.InnerText));
    }
    }
    }
    }

    Let me know, if you are still facing issues. Add me in your MSN or messanger.

  20. muhad197
    November 24, 2008 at 9:32 am

    is the “CRM extentions” FREE with no TIME LIMIT?

  21. muhad197
    December 2, 2008 at 6:44 am

    Hi,
    1) Can you please paste your full code of “OPTION1”
    with the callout? (or any other) Thanks
    2) I downloaded the counter extension of “CRMextensions”. Installation went fine
    untill the final phase when the installation
    guide points to importing a customizatios xml file.
    Now, I searched for that file and it does not
    produced by the installation. This is the file
    that CRM creates upon request to export the published entities. So, if any of you did succeed to install it, please give a note on how you created the xml file in the last phase of the installation.
    If I export the file from the CRM, I don’t get the Counter entity within the xml.
    p.s: If the assenbly registered and the dll is created, can I create a counter entity and use
    the assenbly and dll? There is a problem loading
    the customization file (of any entity)during import.
    Thanks a lot!!!

  22. fahad
    February 26, 2009 at 6:51 pm

    i have 2 question
    1 Question:
    IN MS Access when we insert text for the numbers feilds we get error while for the text field when we insert number we dont get error .give the reason why ?
    2 question
    How does MS Acces ACess autosaves in output table

  23. February 28, 2009 at 11:10 am

    salam ayaz,
    i wanna ask the same question as fahad has asked that why in ms access when we insert text in the number feild we get error while for the text feild when we insert number we dont get error…can u plz explain me the reason for both?
    and my second question is……
    how does ms access save data in output views in tables?
    plz give me the answer as early as possible its my assignment of it and iam not getting any reliable answer for it
    i would be very thankful to you….and waiting for your reply……….

  24. Jonas R
    November 24, 2009 at 8:14 am

    Hi
    I have a fully supported way of doing this, and 100% unique numbers.
    Like dwaingleason suggests, a custom entity containing entity+attribute to be autonumbered, and some additional info for prefix, suffix, increment etc. This entity also holds the current max number.
    Then you use another custom entity “autonumlock” where you handle the locking of the numbering, per entity/attribute.
    This lock entity uses common latch/lock functionality.

    Works like a charm.

  25. November 24, 2009 at 9:02 am

    Gr8.

    Can you share your way of generating autonumbers.

  26. anr
    December 14, 2009 at 9:04 pm

    How about having an entry in windows registry?

    Again, if multiple users are creating the entity at the same time, then there are problems with duplicate values.

    Is there a way to retrieve the last entity id using web services?

  27. Tay Le
    June 10, 2010 at 4:20 pm

    How about time stamping the record number via Javascript. Ie Year-Month-Day-Time

  28. June 11, 2010 at 6:33 am

    Hi Tay,
    What if a record is created from plugin/workflow?

  29. Elisa
    September 8, 2010 at 4:58 pm

    Jonas R,

    Please share you way of generating autonumbers.

  30. Carolina
    October 5, 2010 at 8:29 pm

    Hi!
    To generate autonumber without gaps, I’ve used a plugin that queries the last ID+1 from my entity.
    I’ve locked the method not to obtain duplicates.
    //This is my code
    private static object lockingObject = new Object();
    public void Execute(IPluginExecutionContext context)
    {
    DynamicEntity entity = null;
    entity = (DynamicEntity)context.InputParameters[ParameterName.Target];
    lock (lockingObject)
    {entity.Properties[“cpba_numero”] = getLastNumber();}
    }

    That works OK for records generated manually, but when records are generated automatically by a workflow, lock is not taken to account and duplicates are generated….

    could anyone give me an idea of what could be going on?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: