Skip to main content
    Country/region select      Terms of use
     Home      Products      Services & solutions      Support & downloads      My account     

developerWorks  >  Lotus  >  Forums & community  >  Best Practice Makes Perfect

Best Practice Makes Perfect

A collaboration with Domino developers about how to do it and how to get it right in Domino

The nice thing about @Unique is that it's unique -- at least for a given user on a given workstation.  The value contains a user initials part and a date/time based part, and the client keeps track of the last value assigned. If you call the function twice close together, and will bump up the date/time part, if needed, to give you a different value each time. The date/time part consists of six alphanumeric characters constituting a base-32 number.  But wait (you might think), 26 letters + 10 numerals = 36, not 32. The reason for the discrepancy is that 0 and 1, O and I are excluded to avoid ambiguity when you see the number written down.

There are a few problems with using @Unique as an identifier, though:

  • The alphanumeric identifier is non-optimal for reading over the phone, because many letters have similar names.  Most end-users (and phone staff) have not memorized the word-codes (alpha, bravo, charlie) that military and firefighters use on the radio.
  • The user initials aren't necessarily unique to each user, causing potential duplication.
  • The date-based part comes at the end, which is handy if you want to sort all a user's documents together but less useful if you prefer documents to sort based on when created.  Not that you couldn't sort documents either way based on other fields, it's just a matter of what do you want the first part of your identifier to represent.

Here's a formula to address these issues while still maintaining the useful aspect of @Unique (not giving dups to a particular user):

_initials := @DbLookup(""; ""; "(UserPrefixes)"; @V3UserName; 2; [FailSilent])[1];
_tmp := @Unique;

_suffix := @If(@IsError(_initials); @Left(_tmp; "-"); _initials = ""; @Left(_tmp; "-"); _initials);

_from := "2":"3":"4":"5":"6":"7":"8":"9":"A":"B":"C":"D":"E":"F":"G":"H":"J":"K":"L":"M":"N":"P":"Q":"R":"S":"T":"U":"V":"W":"X":"Y":"Z";

_i := -1;

_to := @Transform(_from; "x"; @Text(_i := _i + 1) + ",");

_digits := @ToNumber(@Explode(@ReplaceSubstring(@Right(_tmp; "-");_from; _to); ","));

_cur := _digits[1];

@For(_k := 2; _k <= @Elements(_digits); _k := _k + 1; _cur := _cur * 32 + _digits[_k]);

@Text(_cur) + "-" + _suffix

This assumes you have a view of "user prefix" documents with a unique code for each user, set up in advance. If no matching document is found, this formula uses the initials that @Unique provides automatically, but you could instead opt to error out in that case, to make extra sure the ID is unique.

It's up to you whether the user codes in your lookup view are letters or numbers, but I like letters.  Unless your application is very high-volume, the chances of duplicate date/time parts are small.  So, your call center staff could ask for the "number part" of the ID (9 digits), and if they find they have more than one match, ask for the letters after the dash (or just see which of the matching records has the customer's name on it).

Andre Guirard | 7 October 2008 11:28:56 AM ET | Home, Plymouth, MN, USA | Comments (10)


1) Will there ever be a way to get a sequential number do you think
Sean Cull | 10/7/2008 12:48:12 PM

Hi Andre,

getting sequential numbers ( or something approximating to them ) is a very frequent customer request. I resist where possible but it does keep coming up a lot.

Is there any chance that something will be built into a domino server to allow these codes to be issued. I have seen workarounds involving seq numbers, files on the operating system and view.refresh, get last document.

Thanks, Sean

2) Convert @Unique to a decimal number (for easier call center use)
Travis Hiscock | 10/7/2008 6:35:43 PM

I agree, whenever this comes up, customers never like the output from @Unique, and always want sequential numbers.

I have always thought an option for @Unique to generate a number with no alpha characters would be good. Not sequential, just unique, and Bingo! your code achieves this. Nice work, I think I will use it.

More customers will accept this, although I doubt we will ever overcome customer instinct to want things numbered sequentially.

We use a 'Sequential Number Generator' holding the last issued number on a document that is only updated on a nominated server to eliminate duplicates.

3) Convert @Unique to a decimal number (for easier call center use)
Andre Guirard | 10/7/2008 11:34:41 PM

I'm working on a Designer Wiki entry that covers sequential/unique numbering and discusses how to establish with customers whether it's a real requirement and inform them what price they pay for it.

4) Convert @Unique to a decimal number (for easier call center use)
Hans Fokine | 10/8/2008 6:58:59 AM

Hi Andre,

It seems like IBM is still avoiding to add this function and instead ask the customer to re-consider using a different approach. In some countries, i.e. Sweden, you must follow a sequential order when creating invoices for example.

I still don't understand how it can be that hard to implement this functionality into the core of Lotus Notes. During the eleven years I've been developing in Domino this issue has always been raised but never solved. By clients, developers and people at Iris (at the time). I believe all other database systems have this built in as a standard function.

Why not have a special type of field then automatically increments when a document is created using a form which contains this field.

It would seem like you have already solved the problem of keeping track of the last number by using the same technique as for document and design locking using an administration server.

I am absolutely certain the entire Notes-community would say "Finally" if this would become available.


Hans Fokine

P.S. Nice tip on the conversion of @Unique

5) Convert @Unique to a decimal number (for easier call center use)
Charles Robinson | 10/8/2008 9:07:02 AM

I'm with Hans on this one. It doesn't matter that a unique identifier can be generated that isn't sequential, customers want sequential numbers. I can do it in pretty much every other environment there is, so why not Notes? It's ludicrous and absurd to expect businesses to change their requirements to fit Notes' limitations.

6) Convert @Unique to a decimal number (for easier call center use)
Charles Robinson | 10/8/2008 9:07:48 AM

This is pretty cool, by the way. I won't use it because I use sequential numbers, but it is very interesting nonetheless. :-)

7) Convert @Unique to a decimal number (for easier call center use)
Travis Hiscock | 10/8/2008 2:06:05 PM

We all know Notes operates in a disconnected multi replica world. How do you generate a sequential number on your laptop replica of a multi Server database when you are offline? That's the challenge. What other environments can solve this?

If you only operate a single database on a single Server, then coming up with a reliable sequential numbering method is not difficult. I wrote my first one using @Functions about 14 years ago and it worked just fine. I seem to remember the 'window' for creating duplicate numbers was about a second then.

It's when you get to multiple replicas that the fun starts! But not impossible to come up with a working method. Granted it takes hard work, and a system level solution would be nice, but I have never not been able to deliver Sequential Numbering that satisfies a customer.

8) Convert @Unique to a decimal number (for easier call center use)
Charles Robinson | 10/8/2008 3:11:39 PM

@7 - I understand that, but I only operate on a single server (or servers in active/passive clusters) and the application is not replicated to end users. In this scenario why do I have to write code to create a sequential number? In Access they handle it by breaking the autonumber feature (and warning you) when you create a replica. To me that is preferrable to not having a sequential number at all.

I can get sequential numbers into a Notes application, and I've even done it for replicated databases. It's a lot more difficult than it should be, though.

9) Convert @Unique to a decimal number (for easier call center use)
Sean Cull | 10/9/2008 2:54:53 PM

Charles, in three lines or less if you want, how do you do it ? Cheers, Sean

10) Convert @Unique to a decimal number (for easier call center use)
Brian Miller | 10/17/2008 3:18:07 PM

There's a typo!

This line of the formula:

_to := @Transform(_from; "x"; @Text(_i :_i + 1) + ",");

should read:

_to := @Transform(_from; "x"; @Text(_i := _i + 1) + ",");

The worst part is that the erroneous code parses! But, if you leave the typo in, the total formula always evaluates to zero.

 Add a Comment
Comment:  (No HTML - Links will be converted if prefixed http://)
Remember Me?     Cancel

Search this blog 


    About IBM Privacy Contact