Manage Learn to apply best practices and optimize your operations.

Creating sequence numbers

How to create numbers in sequence in a workflow application.

VIEW MEMBER FEEDACK TO THIS TIP

When creating a workflow application, there is a need to create unique numbers. For example, consider a form that is used to create numbers. You may want to create a unique number, and each time you create a new number, you would want this number to be incremented to show the next number.

  • Create a form, "formNumber" and add a field in it, "fieldNumb."
  • Make this field a "computed when composed field."
  • Create a view (could be hidden), "viewNumber" based on the form "formNumber."
  • This view should have "fieldNumb" as the first sorted column.
  • Now write the following code in the value for that computed field "fieldNumb."
NumberValue := @Subset(@DbColumn
("NOTES":"NOCACHE";"";"viewNumber";1); -1);
@If(@Elements(NumberValue) 
< 1;1;NumberValue + 1)
@DbColumn -- Looks up and returns an entire column of values from a view or folder in either the active database or another Notes/Domino database.

Out of these range of values, you need to pick up the last value -- as you have already sorted the view by the first column. This is done using @Subset and using -1 to search from right to left (if view sorted ascending) and return the first value.

@Elements is used to check if the list has any values, which will happen for the first time. The If function then returns the incremented task number in the computed field.

Always do error handling (as in the above example) whenever you use an @Db function.

MEMBER FEEDBACK TO THIS TIP

This is not a great tip -- if more than one user does the same task at the same time it will generate the same sequence number for multiple documents.
—Mike B.

********************************************************

Just use a specific document (e.g., created with a different form), lock it, update and unlock it! Search the Lotus Domino Designer 6 Help for the string "doc.Lock" for an example in LotusScript.


—Marco C.

********************************************************

Mike B. is correct. This could create a duplicate sequence number. I would modify the original idea.

  1. Create a hidden form (FORM="X") and have two fields:
    • NUMBER/COMPUTED (integer)
    • AUTHORS/EDITABLE/MULTI-VALUE/USE ACL FOR VALUES
    Populate the AUTHORS field with anyone in the ACL who has AUTHOR access. (You don't have to worry about the EDITORS, because they'll be able to modify the document, anyway).

  2. Suppose the sequencing pertains to FORM="MAIN." Put LotusScript in the QuerySave Event of FORM="MAIN" and specify that the code will run only when the document is NEW so that, when a document is created, a lookup is done to the document created with FORM="X," and the existing sequence number is incremented by 1, and DOCUMENT="X" is saved. That way, whenever a document created with FORM="MAIN," the increment takes place. It's also not likely that two people would create a document with FORM="MAIN" at exactly the same second. Even if one document is created one second earlier than another one, the sequence # will still differ.

HOWEVER, this applies if it's a SINGLE-SERVER application! If it's MULTI-SERVER, watch out! Say, on SERVER="A", a document is saved with a particular sequence #, at which time DOCUMENT="X" is incremented by 1. Meanwhile, a document is created on SERVER="B", but replication hasn't taken place, so it's now using the same sequence # used for a document on another server! Of course, since it's a manual process, you can always have the lookup done -- regardless of the server being used -- to the DOCUMENT="X" specified on one particular server.

Not exactly performance friendly, but it's much better for maintaining the integrity of the sequencing.
—Daniel T.

********************************************************

This tip doesn't work in clusters because sometimes a number could be duplicated. Lookups at the same moment won't work. Does anyone know how to solve this problem?

This could be a very good tip.
—Carlos G..

********************************************************

When working in a multi-server environment, there are only two ways to solve this problem, as I see it.

  1. Use a single shared locked document (as suggested by Marco C. above) on a single specific server. When assigning a document number, open it, lock it, locate the highest number, compute and write the newly assigned number, and unlock it.

  2. Write an agent that periodically (say, nightly at 4 a.m.) reviews all documents looking for duplicate numbers. If a duplicate is found, assign the next available sequence number to one of the documents and send a notice to the author and other relevant personnel. I've used this approach with one of my clients and they are quite happy with it.

—Kith N.

********************************************************

This tip will not generate the correct sequence in any workflow application that has a "security in the workflow cycle," since the result of the @DBLookup differs according to the Lotus Notes documents that you are permitted to see.
—Howida E.

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip library by member Amit Ashar. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.

This was last published in January 2004

Dig Deeper on Lotus Notes Domino Formula Language

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchExchange

SearchContentManagement

Close