Automumber Custom List Field

Question

I need to create a custom list which includes a field wich will be updated when the user changes the "status" custom field (option with several values) to an auto-incrementing number.

 

This is, if the user updates the status field to an specific value.. I need to set the value to this custom field... but it must be unique and incremented by one in respect to other items in the list.

 

This is the classic "autonumber" column... Is it possible to do this in MOSS?

 

How could I achieve this behaviour? Any tips?

 

Thank's

Answer 1

I implemented a custom autonumber functionality the following way:

 

- 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 1000. So I add an item to this list:

Title = CUSTOMERNO

LastNumber = 999

 

Then, you need to create a workflow in Sharepoint Designer that will trigger on "New item created" (or in your case, I guess it would be on item changed). This workflow should then perform a lookup against "Unique Number", find the item where Title=CUSTOMERNO, get the "NextNumber" value into a variable, store this value into the CustomerNo column, and then update "Unique Numbers" setting LastNumber = your NextNumber variable.

 

/Bill.

Answer 2

Bill,

 

Thanks, I tested it out and it worked like a charm... I will be showing this to my customer on monday.

 

Fedro

Answer 3

Dear Bill and Fedro,

 

I'm trying to figure out the solution you provide here for autonumbering. I'm a newbey on designing WF, please could you provide me with some more details?

 

With regards,

Eric Dubbelaar

Answer 4

I can try, but my environment is in Swedish so it's hard for me to accurately describe all the steps in English.

 

I will assume you have the "Unique Numbers" list setup as described in my post above. Continuing on my "CustomerNo" example, here's what you need to do in Sharepoint Designer (SPD).

 

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.

 

Now, everytime you add a new item to the "Customers" list, this workflow will run and assign an auto-incrementing number to the "CustomerNo" field.

 

Hope this helps,

 

/Bill.

Answer 5

Dear Bill,

 

Many thanks for your clear explantation. It works!

Many thanks again...!

 

 

With warm regards,
Eric Dubbelaar

Answer 6

Dear all,

im really new with sharepoint & try to create the auto increment number by following the steps. but somehow, when i open the SPD, but i could not find workflow in File --> New. Am i missing something?

Tks.

Answer 7

 

Hello all,

 

First of all thanks for the clear explanation on how to create the autonumbering field.

I have it working, but there is only one thing that won't work.

Maybe there is here someone who can help me with this.

I cann't search on the number that's created by the autonumbering workflow.

Does someone have a solution for this?

 

Regards

Answer 8

Hello Syamei,

 

The option new -> workflow is available within sharepoint designer when you have installed SharePoint Services 3.0 of the newest portal version.

 

Regards

Answer 9

Sytze wrote:

Hello all,   First of all thanks for the clear explanation on how to create the autonumbering field. I have it working, but there is only one thing that won't work. Maybe there is here someone who can help me with this. I cann't search on the number that's created by the autonumbering workflow. Does someone have a solution for this?   Regards

 

I can search on the numbers. I have a second step in my autonumber workflow, that will create a string consisting of the autonumber + a persons name and location (eg. "1002 - John Doe, New York"). This string is then stored in the Title field of the object. I guess this might be the reason search is working for me.

 

/Bill.

Answer 10

I am sure I have done this right... Is anyone having their workflow doing nothing?

Answer 11

Bill,

I followed all your steps outlined above but I'm having a problem assigning the "nextnumber" value in step 5; it doesn't show up on the list .  I'm not sure if it has something to do with the fact that NextNumber is a calculated value.  Is there something that I need to do to force it to show up?

 

Thx.

 

HJ

Answer 12

[e-mail] wrote:

I am sure I have done this right... Is anyone having their workflow doing nothing?

 

My problem was because System Account can not start workflows with SP1...

Answer 13

fedroponce wrote:

I need to create a custom list which includes a field wich will be updated when the user changes the "status" custom field (option with several values) to an auto-incrementing number.   This is, if the user updates the status field to an specific value.. I need to set the value to this custom field... but it must be unique and incremented by one in respect to other items in the list.   This is the classic "autonumber" column... Is it possible to do this in MOSS?   How could I achieve this behaviour? Any tips?   Thank's

Answer 14

Hi Bill, This is very good!!!!!!  I'm pretty new on sharepoints and got SPD and this worked very good.  

Now, how can I set this new "CustomerNo" to read-only?

Answer 15

LHA wrote:

Hi Bill, This is very good!!!!!!  I'm pretty new on sharepoints and got SPD and this worked very good.   Now, how can I set this new "CustomerNo" to read-only?

 

 

You can use some javascript...

here is a start...
http://wss.collutions.com/Lists/FAQ/DispForm.aspx?ID=118

Answer 16

Hi Jamie,

 

Because of company policies I should not use coding in the sharepoint, I'm only allowed to do some design changes, but should not change the site with programming.

 

Thanks anyway  (Although I was not able to see the link you sent)  I appreciate you took a moment to send it

 

Answer 17

No Problem LHA.

I have been working with MOSS 2007 for only a couple months now... and I have come to the realization that sharepoint "out-of-the-box" will not handle very many of the basic needs of a client, be it simple or complex. Customization through Javascript or .NET development is almost a necessity and even they may not solve your solution, without letting go of many of the great built in features that had clients wanting to purchase the product in the first place.

 

 

Answer 18

I Think I tend to agree with kova81, admittedly MOSS is quite a powerful product out-of-the box. You can achieve quite a great deal of stuff with the product itself and a few customizations using SPD.

 

But, whenever the user tries to "customize" their SP experience beyond what was envisioned by MS, things get progresively complicated. During my 6 months with MOSS experience by now I have found several bugs, shortcomings and limitations in the product. MS will not say when or how will they solve them (and yes I have openes several support cases with MS). Programming and deploying custom code is not an easy task (although some improvement has been done en the past few months).

 

Overall, I would say that, if you restrain your project to what MOSS already offers, it is a powerful and easy to develop application framework. but, If you deviate from that (and users usually do), then sometimes the whole RAD aspect of MOSS simply vanishes away.

 

 

Answer 19

 

Shouldn't step 3 reall use a list called "Unique Numbers" instead of a list called "Customers"?

 

 

Answer 20

I do not think so, step three refers to selecting the custom list where the "autonumber" field will be assigned, that is, the custom list which needs to be "auto numbered" so, selecting "customers" here and assigning the workflow to it will let the workflow assign a calculated number to the "customer id" field whenever an item is inserted.

Answer 21

 

Actually this does not work.  I followed the same and it works fine, but there is an slight possibility that 2 users do this process at the same time, in which case it might give you the same number.

 

 

 

Answer 22

LHA wrote:

Actually this does not work.  I followed the same and it works fine, but there is an slight possibility that 2 users do this process at the same time, in which case it might give you the same number.

 

You are probably right. In my system, there is only one concurrent user, so it's not a problem for me. But I did think about it, and searched but I could not find any information if the workflow would run in a transaction. I'm guessing it does not. So this solution is fine for a "small" system, but if I was to build this functionality again today, I would use an Event Handler instead and perform the number lookup and update in a transaction.

 

Answer 23

I hust had the same problem. Set the NextNumber field to Calcutated and "The data type returned from this formula is: Number (1, 1.0, 100) "  Then it appears.

Answer 24

Hi Bill
Thanks for your excellent explanation on how to create the autonumbering field.
Can you please create a similar descripten for your second step with the autonumber + a persons name and location, so that I can search on the numbers.
Thank's in advance

Devin

Answer 25

English:

Hello everybody,

I tried "a__Bill__F__"'s instruction  and it works fine! So I translated his expaination into German in my own words. Hope it helps some users, which don't understand English.

_____________________________________

Deutsch:

Hallo zusammen,

ich habe die Anleitung von "a__Bill_F__" getestet und muss sagen sie funktioniert echt gut. Da es sicherlich hier Leute gibt, die kein Englisch können, habe ich es mal in eigenen Worten auf deutsch zusammengefasst.

_____________________________________

Auto-Inkrement in WSS 3.0

 

1.         In WSS eine neue Liste erstellen (hier im Bsp. „Liste1“)

2.         Einstellungen/Einstellungen für 'Liste'/Spalte erstellen

a.    1x Name: „LastNumber“ als Typ: „Zahl (1/10/100)“

b.    1x Name: „NextNumber“ als „Berechnet“ mit der Formel: „[LastNumber]+1“

c.     1x Name: „CustomerNo“ als „Eine Textzeile“

3.         Dann in der Liste1 ein neues Element hinzufügen
Titel: CUSTOMERNO
LastNumber: z.B. 999 (somit beginnt die erste Rechnung mit der Nummer 1000)

4.         SharePointDesigner 2007 starten -> Datei/Webseite öffnen/‘Webseite eingeben‘

5.         In den Ordner wechseln, der relevant ist (d.h. wo „Liste1“ liegt)

6.         Neu/Workflow

7.1     Name des Workflows: „Inkrement“ (Beispiel)

7.2     Ordner wählen, in welcher Liste das Workflow angeführt werden soll. Hier-> „Liste1“

7.3     Nur mittleres Kästchen wählen „Diesen Workflow automatisch starten, wenn ein neues Element erstellt wird“

7.4     (unten) Variablen…/Hinzufügen
Name: mNumber
Typ: Nummer -> 2xOK, weiter >

8.1     Aktionen/Workflowvariable festlegen

8.2     Workflowvariable: mNumber
Wert->fx-> Quelle: Liste1 | Feld: NextNumber
(unten) Feld: Liste1:Titel |Wert: CUSTOMERNO | OK, Ja

8.3     (erneut) Aktionen/Feld im aktuellen Element festlegen
Feld: CustomerNo | Wert->fx->Quelle: Workflowdaten | Feld: Variabel:mNumber | OK

8.4     (nochmal) Aktionen/Listenelement aktualisieren
„diese Liste“/Liste1->Hinzufügen
Festzulegendes Feld: LastNumber
Diesen Wert zuweisen -> fx->Quelle: Workflowdaten | Feld: Variable:mNumber | 2xOK
(unten) Feld: Liste1:Titel | Wert: CUSTOMERNO | OK, Ja

9.         Fertig stellen

Answer 26

  Thanks a lot Bill...its works.... ur a life savior...

Answer 27

Hello -

I'm trying to implement an autonumber field for contracts.  Would you be able to detail out the instructions for using the event handler you mention above?

Thanks!

Answer 28

I know this was discussed some time ago, but I was hoping the Bill or someone could explain to me how to add the second step that is referenced above.  I need to do that and am very unfamiliar with workflows.

Thanks

---

kelz1004

Answer 29

I can try, but my environment is in Swedish so it's hard for me to accurately describe all the steps in English.

 

I will assume you have the "Unique Numbers" list setup as described in my post above. Continuing on my "CustomerNo" example, here's what you need to do in Sharepoint Designer (SPD).

 

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.

 

Now, everytime you add a new item to the "Customers" list, this workflow will run and assign an auto-incrementing number to the "CustomerNo" field.

 

Hope this helps,

 

/Bill.

Hi Bill,

Great and helpfull, Can we make it more dynamic , without specifying any column in workflow as we did

Title=Customer No

it means list will be updated only when a user will provide title 'Customer No', my question is can we update it what ever will be title added by user.

Thanks
Shahab
Thanks.

Answer 30

Hi Bill,

Excellent description and talk through. I only have one problem...

The autonumber list is set up, and the workflow has been created. The workflow is assigned to the correct list. However when I create a new item the number dosen't appear. At the moment the field is set up as required, and to only accept numbers. Do i have to change anything?

Best regards

Bruce

Answer 31

This is a great thread, but in running through the example, on small thing and one larger thing jumped out that are not making full sense to me.

The small thing -- one (6) of Bill's list, it asks for Set field in current object and I don;t see such a choice in my Designer 2007 working on enterprise MOSS 2007, although I do see Set field in current item , which seems to do the same thing.  Is this in fact the same?

The slightly bigger thing --  I am targeting using this as a techniques to auto-number contracts created in Word 2007 using a Word Template as the content type in a document library.  The ContractNumberAssign work flow starts as advertised on an item create in the document library, but it errors out before it completes with the following log entry:

The following events have occurred in this workflow.

Date Occurred Event Type User ID Description Outcome 9/8/2009 10:49 AM Error System Account Error updating a list item Document checked out

However, when I then run the work flow manually, it assigns the value, no problem.

So my questions is, is there something specific in this procedure that would work for updating lists but would fail for updating documents libs, and if so, are there any clever ways around the issue (other than the manual assignment, which may work, but seems a little crude is all)?  Or perhaps I am totally out to lunch, wouldn't be the first time.

Cheers,

- Michael

Answer 32

I encountered same problem.  I think it is because the document is still checked out when the workflow tries the update.

Answer 33

Autonumber is a default field on all lists.  You can't see it through list settings.  To see it, you need to either create or modify a view and choose to display the ID field.

Answer 34

Hi Bill, many thanks for the solution here, this works a treat, especially as I have never used SPD before, installed the package, followed your instructions and bingo....

One question I would like to ask though, can the number be padded, i.e. instead of 1, 2, 3 etc. is there a way off putting 0's in front, i.e. 001, 002, 003 etc.

Thanks once again though, very good solution

Kevin

Answer 35

That a major help.  Do you think it would be safe to use this quasi-reserved field as part of a calculation to assigned customers numbers, which one assigned should be stable?  Like add 1000 to make the 1, 2,4, etc, look more like 1001, 1002, 2003, etc.

Thanks,

- Michael