Share via

How to add an "auto number" to a CRM entity

  • Article

Warning: unsupported territory ahead

Adding an “auto-number” field to MS-CRM is one of those features that has been requested several times. The problem is that there isn’t a solution that really meets everyone’s needs. I was asked during V3 to come up with a way to do this for an internal customer (actually, our development team) to track an ever-increasing number on customer issues. This was something that we needed so badly that we opened a DCR against ourselves to see if we could get it into the product. No dice.

However, there is an unsupported way to do this, much like there are unsupported ways to do many things. Here’s the way I put this together for our internal site. Note that this will only work once on a table and only if the table doesn’t already have an IDENTITY column (I don’t remember adding any IDENTITY columns in the V1.x databases, but one might have slipped by).

First, use Deployment Manager to add a new number attribute to your target entity (let’s call it myCounter). Once you’ve done this you should have a column named CFN_myCounter on the base table and in the entity view.

Next, find this attribute definition in the metadata. You’ll want to tweak the ValidForCreateAPI and ValidForUpdateAPI bits to 0 so you don’t accidentally supply a value from outside of the platform. This will also give you a read-only attribute on the form. Also, keep in mind that this attribute won’t have a value in the database until after the row is committed the first time. This means the edit control on the form will be empty until a Save operation (which calls CreateAndRetrieve).

Almost there. Next you need to drop the physical column from the underlying table (this might require some tweaks with replication which means you might need to use sp_repldropcolumn and sp_repladdcolumn instead). So,

ALTER TABLE FooBase DROP COLUMN CFN_myCounter

Then, turn around and recreate that column, but this time specify that the column is a non-NULL IDENTITY,

ALTER TABLE FooBase ADD CFN_myCounter INT IDENTITY NOT NULL

Finally, go add the attribute to the form, republish, and do all the other stuff we make you do in V1.x when you do a customization.

As usual, you’re mucking with the physical database here. This will likely break in a V3 upgrade (because we won’t migrate the IDENTITY information to the extension table). If you do this, you’re on your own, but you won’t have problems with callouts locking transactions, hotspots in a counter table, and all the other problems that might come up.

I haven’t tried this on a 1.x deployment (recently) so I can’t vouch for the correctness of the solution. I recommend backing up both your primary and metadata databases before starting with something like this (but then I recommend that even when you’re doing supported things).

Comments

  • Anonymous
    February 17, 2006
    It's stuck in LegalLand right now. It's not really Legal's fault either. When I put the original prototype...