What is the best way to establish a document numbering system for a shared Lotus Notes database?

We have two company locations, one here and one in China.  Our database is replicated hourly, but we still have a duplication of document numbers, as the view used for creating the document number is not replicated frequently enough to update the newest number taken.  In luei of replicating more often, I'd like to know the better solution to this issue.

I should note that the company in China is running version 5.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

That is if you want to really have unique numbers throughout your whole system.
The idea is that scheduled agent, in a period when there's no document generation (if you even can get a window) processes all documents since it last ran and issues sequential numbers to documents (and also corrects all temporary references to those documents - you can use UNID for this, but only as a temp solution until the agent runs).

To implement real time numbering with
For version 5 you'll have to implement the locking solution (to lock the document that holds the current number while it's issuing a new one).

As far as "view counting" solution is concerned, it's not recommended, you'd soon get duplicate numbers even if you were working on one and only server.
That is because no one guarantees you that two users wont issue the request at the same time.
I sure yielded duplicates when I was testing it.

As I mentioned in one of those threads from previous post, I suggest, whatever solution you choose, it has to withstand at least 3 client machines with an agent running on each of them, requesting a new number in at least 5K iterations loop.
All that without duplicate numbers and you're good to go...

And remember, never lean on UNID. If you copy a db, unids change automatically.
Sjef BosmanGroupware ConsultantCommented:
As a simple alternative, use @Unique in a Computed when Composed field.

Here's a complete description:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

As another alternative, what we use : In the db profile document set a MASTER server. Only the documents created on the MASTER server get directly a number.
Other documents get a number when they are edited during the day on the MASTER server or get a number by an agent which runs during the time when it is night in china. In order to battle replication conflicts another agent runs to check by last modified and does auto cleanup.
You could also consider a location-specific pre- or suffix.
My experience with modifications in profile docs in replicating databases is not that good. I wouldn't recommend it.
A single agent running on a single server to tag documents is I.M.O. a better option.
I suggested that in my first comment, "...on the OU (org. unit) level", cuz' that's exactly how my solution (see the link) works.
You can base that on the name of the server (only if server-location is related one-to-one), but it's best to create profile document for each OU, where you'll store OU's key.

Each document is issued with the composite key in this form (without blanks):
    <docs_sequential_number> - <year> / <OU_name>

This is by no means a trivial job, but you cannot expect from sequential numbering in distributed system with document-based databases to be that easy.
Plan this carefully by studying instructions from the first link in first comment.

If you, on the other hand, don't need sequential numbering but only key uniqueness, that is trivial and lots of suggestions have already been posted.

We're here, so ask if you need explanation or get stuck somewhere...

fselliottAuthor Commented:
I went for the simple solution as I don't know enough about the programming end of it.  It worked beautifully, was quick to code, and is currently up and running.  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.