Saturday, December 11, 2010

Auto-Numbering Using Workflow

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!

29 comments:

Russell Munro said...

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.

Leon Tribe said...

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.

Russell said...

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!

Leon Tribe said...

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.

Bob G said...

Would you agree that this technique can result in duplicate ids, depending on timing?

Leon Tribe said...

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.

Ben Borja Jr said...

HUGE THANKS ! others suggested plugins to create auto increments, you did something just using native CRM . Great! It worked for me.

Guido said...

Excelent1! Thanks!!

greg.hewitt said...

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

Leon Tribe said...

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)

Leon Tribe said...

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.

Celso said...

Hi Leon

I have this error on workflow populate " Cannot find record to be updated"

Can you help me?

Leon Tribe said...

I see this on the last step if I try to run the steps in the one workflow. If you've created your record which holds the current value and you have linked to this record it should work.

Art Small said...

How do you put in the "Two slugs" divided by the dash? I normall use the "Look For" to choose my entity, then select my fields, Add them to the box below, then hit ok.

Leon Tribe said...

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 ;)

Terri said...

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.

Dan K said...

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!

Leon Tribe said...

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.

Dan K said...

Leon,

Thank you for this very helpful tip, saved the day! Great Blog btw!

Dan

syeema mahadi said...

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?

Leon Tribe said...

Hi Syeema,

If you are integrating with SharePoint I would seriously consider using a plugin instead as you get much better event control

syeema mahadi said...

Can you give me sample of code or any reference website?

Leon Tribe said...

Here you go ;) http://advcrm2011autonumber.codeplex.com/

Jane Doe said...

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 ,

Leon Tribe said...

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

Jane Doe said...

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...

Leon Tribe said...

I haven't tried these directly but there is a free one on codeplex (http://crm2011autonumber.codeplex.com/). Also, I am hearing good things about North 52's Formula Manager (http://www.north52.com/formulamanager.html). While this costs money I understand there is a demo/community version which gives you 10 formulae for free.

John Grace said...

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

Leon Tribe said...

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