Ever have a SharePoint 2007 list where you need to create a custom autonumber column? ...and the ID system column just won't give you what you need?  Recently I found a fix for this issue.

First, create a "lookup" SharePoint list:

• Created a new list that holds autonumber information, named it "Unique Numbers".
• Add one integer field, "LastNumber" and one calculated field "NextNumber" to this list. NextNumber formula is "[LastNumber]+1"
• Add item(s) in this list for any autonumber columns I wish to have. For example, for my "Customers" list I want to have an auto-incrementing CustomerNo column. I want to start numbering my first customer as 1. So I add an item to this list:
  • Title = CUSTOMERNO
• LastNumber = 0

Now, create the workflow against the targeted list as follows:

1. Open your web site in SPD.
2. Create a new workflow. In the File menu, select New->Workflow...
3. In the dialog that appears, give the workflow a name, e.g. "Assign Customer Number". In the drop-down list below, choose to connect the workflow to the list you want to assign autonumbers to (In this case, this would be a list called Customers). Then check the middle option of the three check boxes below, "Start this workflow automatically when a new item is created".
4. On the same dialog, there is a button at the bottom called "Variables...". Click it. Choose "Add", name it "mNumber" and make it of type "Number". Click OK, and OK again. Now, click "Next" in the dialog.
5. You can leave the step name as is ("Step 1"). Leave the "Condition" empty, and click the "Actions" button. In the menu that appears, select "Set workflow variable". This will add an action to the list to the right of the Actions button. Now you need to set up this action. Click the link "Workflow variable", this will open a small dropdown list. Select "Variable: mNumber". Then click the next link, "value". This will display a textbox and a small button in place of the link. Click the button ("Show databindings"). This will bring up a new dialog, where you will select the value to assign to the "mNumber" variable. In the "Data Source" drop down, select "Unique Numbers". In the drop down just below ("Field:") select "NextNumber". Now, the dialog will be expanded with a "Find list object" section. In the drop down "Field:", select "Unique Numbers:Title", and in the value box below, type CUSTOMERNO. Click OK. SPD will now give a warning that the look up might return more than one value, just select "Yes" to continue here.
6. Now you add one more action. Click the "Actions" button and select "Set field in current object". Click the "field" link in the new action, and in the list that appears, select the field in your list that is the target of the autonumber (e.g. "CustomerNo"). Then click the "value" link, and once again click the little button that appears ("Show databindings"). This time, in the "Data Source" drop down, select "Workflow data", and then in the next drop down, select the variable "mNumber". Click OK.
7. Add yet another action, this time choose the "Update list item" action. Click the link in the new action, "update list object in this list". This brings up another dialog, in the "List" drop down, select "Unique Numbers". Click the "Add" button just below to the right, and in the new dialog's first drop down ("Set this field:"), select "LastNumber" and to the right of the "To this value:" text box, click the formula button. Again, a new dialog appears, in the "Data Source" dropdown, select "Workflow data" and then in the "Field" drop down, select "Variable: mNumber". Click OK twice to get back to the "Update list item" dialog. Now, once again in the "Find list object" section, select "Unique Numbers:Title", and type in the value CUSTOMERNO. SPD will give a warning again, click "Yes".
8. That's it. Click the "Finish" button, and if all is well, the workflow will be created.

Special thanks to Daniel Bomgardner on this thred :: http://social.msdn.microsoft.com/forums/en-US/sharepointdevelopment/thread/867bcacd-d378-411a-849a-fc040de9e5da/