Solved

Problem with Reference No function!!

Posted on 2001-09-13
16
189 Views
Last Modified: 2013-12-18
Hi

I have a function that is called when a new doc is saved to give it a unique reference number:-

Function GenRefNo(sFieldName As String) As Long
     Dim s As New NotesSession
     Dim db As NotesDatabase
     Dim Doc As NotesDocument
     
     Dim nRef As Long
     Dim vRef As Variant
     Dim item As NotesItem
     
     Set db = s.CurrentDatabase
     Set Doc = db.GetProfileDocument("Counters")
     If Not (Doc.HasItem(sFieldName)) Then
          Call Doc.AppendItemValue(sFieldName, 0)
          Call Doc.Save( True, False )
     Else
          vRef = Doc.GetItemValue(sFieldName)
          nRef = vRef(0)  + 1  
          Set item = Doc.ReplaceItemValue(sFieldName, nRef)
          Call Doc.Save( True, False )
     End If
     GenRefNo = nRef    
End Function

It's supposed to look up the value in a profile document add 1 to it, save it and then return the new number.  I works fine for me, but on the live db it is generating duplicates every now and then...

Any ideas?

Many thanks in advance

Ian
0
Comment
Question by:IanWood
  • 5
  • 5
  • 3
  • +2
16 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 6479851
It does generate duplicates, because more people are trying to create the ref# at a single instance.

Check this link for alternate solution on sequence numbering:

http://www.keysolutions.com/NotesFAQ/howdo.html

~Hemanth
0
 

Author Comment

by:IanWood
ID: 6480026
Cheers

I've checked the creation times of the documents in question - In one case, there are 3 duplicates, with 5 minutes difference between the first 2 and then an hour until the 3rd was written...

The db is fairly small in terms of users and I'm prepared to take the relatively small chance that there may be a duplicate every now and then, but it seems to be duplicating at different times!!

Any ideas?

0
 
LVL 3

Expert Comment

by:Gunsen
ID: 6480506
Is this database replicated to different servers ??
If so each server holds a different Profile (copy) and then replicates the "LAST saved change" to other servers at given intervals...(last save wins)
If this is the scenario, a dedicated server/db should be used for counting if this is possible...
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 6480919
Here is some excerpt from knowledge base, it also discuss some other alternates.

http://support.lotus.com/sims2.nsf/eb5fbc0ab175cf0885256560005206cf/de5f6cdc47715a32852564a0004986a2?OpenDocument
0
 
LVL 18

Expert Comment

by:marilyng
ID: 6481098
Gunsen is correct, when you're dealing with replicated databases, then the suggested sequential numbering solutions don't work, especially if you have people working in the DB remotely.  The only alternative is to create a separate counter database that has it's unique numbering system, and then deal with isonserver checks to see if you're going to provide a temporary number until the user replicates and a scheduled agent to renumber the item correctly. (the increment the view document number solution does not work, either, for replicated databases)

Another possibility is that you have two instances of your profile document.. If it shows up in an AllDocuments view, then you do, and you need to delete the one you can see.  Also, open the database in Notes Peek to see how many instances of the profile document exist.

Duplicate instances of a profile document usually occurs when you edit the profile document form while a document exists.

hope this helps.
marilyng

0
 

Author Comment

by:IanWood
ID: 6482045
Hi all

It's resides on a single server... The two profile documents sounds interesting....
0
 

Author Comment

by:IanWood
ID: 6482879
I've checked the all doucments view, and can't see another copy of the profile document..which is strange, is it worth checking with Notes Peek as well?  if so, how do I go about doing that?

I've increased the points to 150 if anyone else has any suggestions!!!!!

so, in summary, I have a database on a single server that has a routine to call a profile document (see above)which returns the next available number.  It is producing duplicates even when the documents in question are more than 5 minutes apart.

HELP!??!?!??!?!?!??!

:-)

0
 
LVL 18

Accepted Solution

by:
marilyng earned 150 total points
ID: 6483149
Ian,
I'll copy down your code to a test db and see what's happening, could be the quality of the If statement.

Notes peek you can download for free from Notes.Net, be sure to download the correct version, there's one for R4 and another for R5.

Also, the appenditemvalue and the hasitem may be causing the problem.  Appenditemvalue creates a new instance of the field, even if another exists.  And I've found that the hasitem doesn't always work because the cache on theprofile document on the SERVER level isn't always updated immediately, you have to exit the database and reopen it to refresh the cache for the profile document LOCALLY, hence, when you check for hasitem, it may not see the update locally, since your local cache collected the profile document item five minutes earlier. So, using the appenditemvalue command causes the application to add another item. Using the profile document on a server may not be the best to count..even if you have no other replicating databases.  In this case, you may need a unique document.  You can check for multiple instances of the same field with
forall i in Profiledoc.items
 if i.name = sfieldname then
  etc.
end forall

But, this still won't equalize the cache. To confirm this, do one document, save it, exit the database, reopen the database and do another.

Just a quick thought, where is your function being called?

Try this, put your "function" in the querysave event of the document and remove the  extra save calls:

Sub Querysave(Source As Notesuidocument, Continue As Variant)
     Dim s As New NotesSession
     Dim db As NotesDatabase
     Dim ProfileDoc As NotesDocument
     Dim doc As NotesDocument
     Set db = s.CurrentDatabase
     Set doc = source.document
     
     Dim nRef As Long
     Dim vRef As Variant
     
     Dim docitem As NotesItem
     Dim profileItem As NotesItem
     Set docitem = doc.getfirstitem("GenRefNo")
     sFieldName = docitem.Name
     
     
     Set ProfileDoc = db.GetProfileDocument("Counters")
     Set ProfileItem = ProfileDoc.getfirstItem(sFieldName)
          'Check to see if field exists
     If (Profileitem Is Nothing)  Then
          Set Profileitem = profiledoc.AppendItemValue(sfieldname, 1)
          nref = 1              
     Else
          nRef = ProfileDoc.getItemValue(sfieldname) +1          
          Set ProfileItem = ProfileDoc.replaceitemvalue(sFieldName,nRef)
                                   
     End If    
     ProfileDoc.save True,True
     doc.GenRefNo = nRef    
     
End Sub  



This is quick, so there may be errors..

marilyng
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 6483332
Ian, did you try any of the alternates specified in the technote ?
0
 
LVL 18

Expert Comment

by:marilyng
ID: 6483541
HermanthaKumar,

He's using a script function to pass a variable, which assumes that he wants the profile document to collect more than just one fieldname, he wants to be able to see if the fieldname exists in the profile document and if not, create a new item in the profiledoc and start a new numbering sequence so he can keep a record of a various number of field names.

The document referenced is a @Formula function that gets the profile document field, adds 1 to it, and saves the document.

It's also a note for 4.6.. smile.

Now, there's no reason why he can't use formula functions in his query save event, or in a button to do this.. it still won't change the fact that a database profile document only refreshes on the local cache when the user exits the database and reopens it.  So, it's not a good place to store global, constantly changing variables.


Does this make sense?
marilyng.

0
 
LVL 3

Expert Comment

by:Gunsen
ID: 6484044
I think this may be a cache issue as marilyng explains.
U could either try to replace the Profile documents with ordinary notes document .... or split the documents in to separate documents since this is a multi-user multi-field scenario all acting and updating different fields and values of the very same doc (BAD):

Set Doc = db.GetProfileDocument("Counters" & sFieldName)
0
 
LVL 18

Expert Comment

by:marilyng
ID: 6484120
Agreed. Personally, I would create a new counterdoc with two fields in it.. the sFieldName, and the countervalue. Then create a categorized view by the sFieldName, with the second column the countervalue..

So my queryclose event would look like this:

Sub Querysave(Source As Notesuidocument, Continue As Variant)
     Dim s As New NotesSession
     Dim db As NotesDatabase
     Dim ProfileDoc As NotesDocument
     Dim doc As NotesDocument
     Set db = s.CurrentDatabase
     Set doc = source.document
     
     Dim nRef As Long
     Dim vRef As Variant
     
     Dim docitem As NotesItem
     Dim counterItem As NotesItem
     Dim view As NotesView
     Set view = db.getview("counters")
     
     Set docitem = doc.getfirstitem("GenRefNo")
     sFieldName = docitem.Name
     
     Set counterdoc = view.getdocumentbykey(sFieldName)
     
     If Not (counterdoc Is Nothing) Then
          vref = counterdoc.CounterValue(0)
          nRef = vref +1
          counterdoc.CounterValue = nRef
          counterdoc.save True, True
     Else
          Dim newdoc As New NotesDocument(db)
          With newdoc
               .form = "Counters"
               .CounterName= sFieldName
               .CounterValue = 1
               newdoc.save True, True
          End With
          nRef = 1
     End If
     
     doc.GenRefNo = nRef    
     
End Sub
0
 
LVL 18

Expert Comment

by:marilyng
ID: 6484121
Adding this note.. this will not prevent two users from trying to update the same Counterdoc at the same time, so you need to restrict your counterview not to collect saveconflicts.  If you put the value somewhere other than the save event on the form, the value might change between the time you collect it and the time you save it.
0
 

Author Comment

by:IanWood
ID: 6484565
Cheers guys, I'm going on holiday in half an hour, so I'll have to be quick...

I tried Marilyng's first bit of code and it seems to work - I haven't really go time to check over the details though..

I'll look into the improved proper versions when I get back!!

I'll leave this open until I get back if anyone still wants to discuss...

Thanks for all the help guys!!!

Ian :-)

0
 
LVL 5

Expert Comment

by:snocross
ID: 6491708
Listening...
0
 

Author Comment

by:IanWood
ID: 6530855
Thanks everyone...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now