Problem with Reference No function!!

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
IanWoodAsked:
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.

HemanthaKumarCommented:
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
IanWoodAuthor Commented:
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
GunsenCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

HemanthaKumarCommented:
Here is some excerpt from knowledge base, it also discuss some other alternates.

http://support.lotus.com/sims2.nsf/eb5fbc0ab175cf0885256560005206cf/de5f6cdc47715a32852564a0004986a2?OpenDocument
0
marilyngCommented:
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
IanWoodAuthor Commented:
Hi all

It's resides on a single server... The two profile documents sounds interesting....
0
IanWoodAuthor Commented:
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
marilyngCommented:
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

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
HemanthaKumarCommented:
Ian, did you try any of the alternates specified in the technote ?
0
marilyngCommented:
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
GunsenCommented:
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
marilyngCommented:
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
marilyngCommented:
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
IanWoodAuthor Commented:
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
snocrossCommented:
Listening...
0
IanWoodAuthor Commented:
Thanks everyone...
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.