Auto-Numbering Using Workflow

Standard

This is a trick one of my co-workers (Amreek Singh) showed me a little while ago. This will work in CRM 4 and CRM 2011, although, for convenience, I’m doing it in the 2011 beta.

Current Auto-Numbering Options

The first option is for a limited set of entities which have auto-numbering out of the box.

image

For Contracts, Cases, Articles, Quotes, Orders, Invoices and Campaigns, auto-numbering can be set in the Administrative settings in CRM. The prefix for each of the entity numbers can be set and the final suffix can be of a length from four characters to six characters. A word of warning, if you change the length of the prefix, this changes for ALL the entities, not just the one you’re on.

For other entities, if you are using CRM 4, there is also an auto-numbering plugin available from codeplex.

http://crmnumbering.codeplex.com/

Of course, if you are using the online version of CRM 4 or CRM 2011, you will need to resort to another option. Here is one using workflow.

Step One: An Entity to Store the Current Incrementing Value

To store the current value of the incrementing value, we need a new entity. For this entity we create one record to hold the values.

image

In this case I’m storing the prefix and the suffix I am going to use for an incrementing value on the account record.

The record also needs to have 1:N relationship to the records it will increment. In this case, the Account entity.

Step Two: Set up the Field on the ‘Numbered’ Record

Account already has an ‘Account Number’ field so I will use this field to hold the value of the field.

Step Three: Set up the Workflows

I tried doing this with one workflow but for some reason the workflow could not link to the record with the values in it and then populate them. Therefore, I’m using two. The first workflow runs on the creation of the Account record and links to the record storing the increment values.

image

Incidentally, there is no need to add the lookup to the Account form as workflows can populate fields whether or not they have been added to the form. This is great when you need to store values but do not want to clutter the forms up.

The second one is triggered when the link to the value store record is populated. It does the populating of the Account record and also increments the value store by one so the next Account record gets a different suffix value.

image

For the Account Number, we add the two ‘slugs’ divided by a dash.

image

For the incrementing of the storing record, I use a little known trick which is available in both versions of CRM.

image

When you use the Form Assistant to populate a field, drop the ‘Set To’ Operator down and you will see a whole range of different operators available, depending on the field type. In the case of integer fields, one of the options is ‘Increment By’. By adding ‘1’ to the Default Value and hitting OK we tell the workflow to increment by 1 every time the workflow fires.

What We End Up With

After all this we end up with a process which will populate the Account Number of an Account with a unique value every time an Account record is created. As usual with workflows, this runs asynchronously. In other words, you will NOT see the field populating on the initial creation but rather only after you have saved and closed the record and reopened it a little time later, after the workflows have finished.

image

We can extend this to multiple entities by adding other fields to the store record and setting up additional 1:N relationships from the store record entity.

Have fun!

Advertisements

29 thoughts on “Auto-Numbering Using Workflow

  1. This is just what I am after thanks! but I am having some trouble.

    All I am doing is: incrementing an int field on a custom entity by 1.

    I recieve an error in the workflow: “A record required by this workflow job could not be found”.

    I thought it might be a scope/ permission thing but no change I make makes a difference?

    Can you offer any suggestion on a solution? I am using CRM 4

    thanks in advance.

    Like

  2. The error suggests that one of the yellow fields is linking to a related record but the record you're running the workflow against does not have the link set.

    It could be that the workflow is not linking the increment record to the custom entity before you reference it to find the current record number.

    In other words, the workflow(s) need to firstly link your custom record to the increment holding record and then reference this record to find the current value, increment it by one and populate the custom entity with it.

    Like

  3. This idea of linking records is a new one to me (as is Crm) can you explain how? (I've tried googling an answer but no luck) It feels like this has been glaced over in your excellent post, but is proving to be critical info to know! 🙂

    Specifically could you elaborate on the settings of the only step in first workflow?

    thanks again!

    Like

  4. So you first need to set up a 1:N relationship between the increment counter entity and custom entity. Go to the Settings-Customisation-Customise entities – custom entity. Go to relationships and add in a N:1 relationship to the increment counter entity.

    Like

  5. Of course. This is the drawback of asynchronous workflows.

    As a general rule for all workflows, if the time between workflow triggers is of the order of the time it takes workflows to execute then a plugin may be a better solution to stop workflows impacting on each other or conflicting.

    Like

  6. Could you please post a video tutorial for this? I am not entirely sure of the full steps needed to achieve this.
    I am at the stage of creating a new entity, and need to know exactly what to do.
    Cheers,
    Greg

    Like

  7. A video tutorial may take a bit of time but here is a few of the intermediate steps expanded.

    How do we create the increment store (CRM 2011)?
    Settings-Customizations-Customize the System-Entities-New (Fill in the compulsory fields)-Save (not Save and Close)

    1:N Relationships-New 1 to Many Relationship (Change Related Entity to Account, Display Name to Increment Record)-Save and Close

    Forms-Main Form-New Field (named Suffix of type Whole Number-Save and Close (drag it onto the Main Form)-Save and Close-Publish

    You then create a new one of these to store the current values for the prefix and suffix (the name field will store the prefix in this case)

    Like

  8. Next Question: Which fields do we hook into in our workflow update steps?

    The linking workflow adds the increment record we created to the account via the increment lookup field created from setting the 1:N relationship between the two entities. This lookup does not need to be on the form to be updated although you do see it in my final picture in the artcle as I did add it to the form.

    For the second workflow I have screenshots in the article but basically we set the account number to – . We then update the increment store record by adding 1 to the suffix value.

    Like

  9. Hi Art,

    If you select two fields and hit ok what you are telling CRM is “use the first field but if you don't find a value, use the second value”. To get the two slugs and a dash you hit ok on one value, type in a dash in the box and then select another field and click ok.

    I hope that makes sense. If not, let me know 😉

    Like

  10. Thanks for your post. Would it be possible to show the view properties in step one where you updated the account? I am not able to make it work and am just guessing as to how you set the information up.

    Like

  11. I recently did a rollup which disabled previous incremental numbering plugin. This is the perfect solution but i need a little clarification on the 1st workflow…

    What do you have update on the account form in the workflow?

    The second workflow is setup, but i am having a disconnect understanding exactly what needs to be done in the first workflow…

    Any help is appreciated!

    Like

  12. Hi Dan,

    The first workflow links the Account to the record where we are storing the next increment value and the prefix i.e. it populates the lookup to the increment entity. Initially I had a workflow which linked and then referenced the value in the entity but it was breaking.

    Like

  13. I have problem created unique id when it is created concurrently. I have set the rules in MSCRM but it does not detect duplicate value when two application submitted concurrently from Microsoft Sharepoint. Any idea?

    Like

  14. this helped out however, how can i set the counter to not comma format. for example i am now at 1000 and i use Year-Sequence.

    My ID shows 2012-1,000. How can you set the format of a number not to have the ,

    Like

  15. Excellent question. Given the field is a number field, its format is set by the user setting formatting for all numbers. You may be able to introduce some jscript to strip is out but I can't think of any other suggestion short of resorting to a plugin for the function

    Like

  16. I thought about stripping, but that would mean the stipping would have to on load and then you might get duplicate numbers.. what plugin do you recommend and do you know of any free ones…

    Like

  17. Thank you for mentioning us Leon.

    I just came across this blog post & just wanted to say that our Formula Manager product does have a AutoNumber() function which is very powerful as we use as an Excel like syntax to build up the formula.

    And best of all is that our standard edition allows you to create the first 10 formulas for free & use in production environments.

    John
    North52

    Like

  18. Hi John,

    Coincidentally enough I am about to embark on a review of North 52 for my team. For non-developers like me, any tool which helps me generate code through a GUI can only be a good thing.

    Leon

    Like

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