I have a "document" form and in that there is a field called currentvalue(Number, computed when display)and has the following formula "CurrentValue:=@TextToNumber(@GetProfileField("counter profile";"counter")); @SetProfileField("counter profile";"counter";@Text(CurrentValue +1))"
I also have created a profile form and called it "counter profile." I added one field in this form and called it counter(Number). I then created an agent called "Edit Counter" and added the following formulas "@Command([EditProfile]; "counter profile"); @All" when I try to run the database and hit the new button to create a new document, new document opens and in the currentvalue field the following text appears "@ERROR".
Do you have any suggestions? I appreciate your help.
Profile documents should not be used for sequential numbering. Profile documents are great for efficient access to user-specific settings, but they are cached in memory when a user opens a database. This will lead to duplicate numbers being generated. For example, if the last used number was 100 and two users open the database at the same time, then their new documents will both be assigned number 101, because the profile document in memory says that's the next number. It will not be updated in memory until they close the database and re-open it.
Here is a better design:
- Create a simple Counter form with a number field that is computed. Also include an authors field that is computed to "*", to ensure that all users have the ability to update the document, even though they won't edit it directly.
- Create a hidden lookup view that displays the document. (Make sure that all users have the ability to read the view -- just keep it out of sight.)
- When the user clicks the "New" button, trigger LotusScript or formulas to look up the counter document, get the number, put it on the form (in a computed field -- not a display field), and increment the counter document.
I have one additional suggestion for Step 3 in Brad Balassaitis' answer for sequential numbering. I created a sequential numbering method like Brad described a number of years ago, but one problem I had was users getting an occasional duplicate number until I incremented the counter document first before getting the number for the users document. In other words, "get the counter document, increment the counter document, then get the number," vs. "get the counter document, get the number, increment the counter document."
There are two reasons for incrementing the counter document before getting the users number:
- If some validation or something "blows up" before the counter document is incremented, another user could get the same number while the first user is trying to "fix" their validation problem. The first user already has the number, but has not incremented the counter doc.
- If by chance two users update the counter document within milliseconds of each other (i.e., a double increment, no replication conflict), there is the potential of both users then getting the same number when the lookup occurs. But this is not a terrible scenario, because you now should have a number that was not utilized and therefore you can "manually" populate one of the documents via an agent with the unused "previous" number.
Hopefully what I wrote is not too confusing as it's kind of hard to describe.
Do you have comments on this Ask the Expert question and response? Let us know.
Dig Deeper on Lotus Domino Designer
Related Q&A from Brad Balassaitis
Upon moving to Lotus Notes 8, some Lotus Notes users received an error message stating: 'Database has not been opened yet.' Find out why here. Continue Reading
Discover what security settings you need to fix if you encounter the Lotus Notes database error: 'database is not opened yet.' Continue Reading
Learn the Formula language code that you can use if you'd like your Lotus Notes form with embedded views to stop displaying all response documents. Continue Reading