Solved

Lotus script

Posted on 2009-05-06
1,016 Views
Last Modified: 2013-12-18
HI;
I have a Helpdesk DB when a case is raised a case number is assigned automatically from a network file. I have not found any way to make it auto via script only. Now some times when network is slow or due to time out the auto number is not picked when a new case is raised.I want either the field be made in such way that it has to wait unless the number is picked or to be automated via lotus script so i may not need the network file. I have pasted the script here. Please help.
Thanks

Sub Querysave(Source As Notesuidocument, Continue As Variant)
      Dim fileNum As Integer
      Dim fileName As String
      Dim NrCrt As Long
      Dim NewNrCrt As Long
      fileNum% = Freefile()
      fileName$ = "\\server\uniqueNo\SRegNo.txt" 'This is the path to the file that keeps the current number
 'If is new doc assign a new number
      If source.IsNewDoc Then
 'Open for reading the file, read the value then close the file
            Open fileName$ For Input Lock Read Write As fileNum% '
            Input #fileNum%,NrCrt
            Close fileNum%
            NewNrCrt=NrCrt+1 'Increment the value
 'Open for writing the file, write the value then close the file
            Open fileName$ For Output Lock Read Write As fileNum%
            Write #fileNum%,NewNrCrt
            Close fileNum%
            If Isnull(NewNrCrt) Then
                  Msgbox("Can't generate the registration number ! Please check the path to the file: " & fileName$ )
            End If
            If Source.FieldGetText("Department")="" Then
                  Msgbox "Please enter the required values such as Department and phone number"
                  continue=False
                  Exit Sub
            End If
            Call source.FieldSetText("sRegNo","ITHELP-" & Str$(NewNrCrt)) 'Complete the current number in the form
      End If
End Sub
0
Question by:aib_it
    24 Comments
     
    LVL 22

    Assisted Solution

    by:mbonaci
    If I understood well, you need sequential numbering of documents?

    Sequential numbering is not well supported in Lotus Notes.

    Here is the thread that goes through all pitfalls:
    http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/Lotus_SmartSuite/Lotus_Notes/Q_22631498.html
    0
     
    LVL 2

    Assisted Solution

    by:jitendrasachdeva
    Hi,
    I agree to great extent with above comment. But we do have alternatives to generate sequantial numbers.

    1. You can store the number in some special document in the same database and update the counter. This way you don't need to do a file read on network.
    2. You can create a special view which contains all ticket document in decending order. Access that view and get the latest ticket number on first document.

    and many more ways....

    Link posted in above post can give you an idea for that lotus script.

    Cheers !
    Jiten
    0
     

    Author Comment

    by:aib_it
    HI Brothers;
    Thanks for the replies.
    I agree its difficult to get the number sequence via lotus script. The sequence which i have i want to make sure that document must get a number, i mean that if connection is not ok to contact the network shared folder then the system must wait unless the document is assigned a number. Is this kind of restriction applicable.

    Thanks
    0
     
    LVL 22

    Assisted Solution

    by:mbonaci
    Counting documents from a view is not a reliable way to go.
    Let's analyze how that would work:
     1 - new document receives save request from the user (QS) and calls the function for next number
     2 - the function gets a database and a view
     3 - the function sets the reference to the top view document
     4 - the function reads the value from the document field (or counts the number of docs in a view)
     5 - the function returns last number incremented by 1
     6 - new document receives value from the function and stores it in a field
     7 - new document is saved

    Now, what if there are multiple new documents being saved on, lets say 3 workstations, at the same time.
    Steps 2 to 7 are all critical. Until your new document is saved, all other documents are going to receive the same sequence number.

    The point is that you need some kind of locking mechanism.

    If you want to read more about this subject search on Wikipedia:
     - Thread
     - Mutex
     - Semaphore
     - Monitor


    As stated in posted url, to say you have implemented full prof sequential numbering you'll need to create an agent that requests 1000 sequence numbers (one by one, in a loop, of course).
    Then start the agent on at least three workstations at the same time.

    If all the numbers are unique you're "good to go" - to implement it in production environment.
    0
     
    LVL 22

    Expert Comment

    by:mbonaci
    No, the users are going to kill you :)
    It's not at all difficult from version 6, when IBM introduces Lock method on NotesDocument.

    Use special document in a special view.
    In that doc create a field where you'll store the last number.
    All this, along with the code for the function that gets the new number is described in the URL I posted. Read it...
    0
     

    Author Comment

    by:aib_it
    Yea correct but the problem is that now its difficult to do all from the beginning, i wanted the current one to be reliable.

    0
     
    LVL 22

    Expert Comment

    by:mbonaci
    What from the beginning?
    I suppose you have a function that does all that with reading/incrementing the file and returns the new sequence.

    All you have to do is change it. As I said, the code is already there.

    Start, step by step, and ask when you get stuck... Post the existing code, I'll help you...
    0
     
    LVL 22

    Assisted Solution

    by:mbonaci
    OK, I forgot you posted the code.

    Instead of all that in QS, place the call of getNextSeq function (I posted the code previously - see URL).
    The function should be placed in script library if it's used on multiple forms.

    Maybe it's really difficult, but then you haven't posted enough information for me to conclude that...
    0
     
    LVL 22

    Assisted Solution

    by:mbonaci
    Do you have multiple replicas of that db (more then one Organization Units)?
    Is it used locally, on user's laptops, while they are disconnected?

    Tell me more about setup...
    0
     

    Author Comment

    by:aib_it
    HI Mbonaci;
    Thanks for all the replies, due to holidays here i could not reply.  Can you please modify the code i provided although if you check the following exact code for sequence generator. In the last line its mentioned that if the sequence is not geting generated a message should get displayed.

    If you are suggesting auto sequence instead of network file please paste the modified code so i can paste the same in querysave section of the designer.

    :) thanks

    Sub Querysave(Source As Notesuidocument, Continue As Variant)
          Dim fileNum As Integer
          Dim fileName As String
          Dim NrCrt As Long
          Dim NewNrCrt As Long
          fileNum% = Freefile()
          fileName$ = "\\server\uniqueNo\SRegNo.txt" 'This is the path to the file that keeps the current number
     'If is new doc assign a new number
          If source.IsNewDoc Then
     'Open for reading the file, read the value then close the file
                Open fileName$ For Input Lock Read Write As fileNum% '
                Input #fileNum%,NrCrt
                Close fileNum%
                NewNrCrt=NrCrt+1 'Increment the value
     'Open for writing the file, write the value then close the file
                Open fileName$ For Output Lock Read Write As fileNum%
                Write #fileNum%,NewNrCrt
                Close fileNum%
                If Isnull(NewNrCrt) Then
                      Msgbox("Can't generate the registration number ! Please check the path to the file: " & fileName$ )
                End If
    0
     
    LVL 46

    Assisted Solution

    by:Sjef Bosman
    > I wanted the current one to be reliable
    Isn't that what they said when they launched the Titanic...?

    Personally, I don't believe in sequential numbering. There are too many things that can go wrong, for example when
    - there are replica's of the same database on other servers or on client PCs;
    - you have to go back to a backup database;
    - you use document locking, you can get lots of save conflicts
    - the sequence number is stored somewhere, that storage can get corrupt

    The only way that seems practically doable to me is to have an independent agent on ONE server hand out the sequence numbers, in the background. A document is saved without a sequence number. The agent picks up all documents with an empty number and sets a sequence number. Until the sequence number is set, the document is in a locked state.

    On the main database, where the agent runs in the background, you can set a server parameter to make the agent react quicker. On a replica database, you may have to wait until two replications have been done.

    Let me guess... Marko won't agree with me... ;-))
    0
     

    Author Comment

    by:aib_it
    HI sjef_bosman;
    We don't have replicas its only a single DB.Now i am little confused. Creating agent is not a difficult task but altering the structure of the DB is now difficult, becuase the HELP desk DB is now used for more than 2 years with alot of requests inside. So the only way i can see is that the sequence which is currently picked from  a network file that needs to be made in such a way that if a user is accessing this DB from any other subnet (DB willbe still on the main server) and incase a timeout occurs the document should popup the message "Can't generate the registration number ! Please check the path to the file" and when user is trying to save it again the number should get picked up.

    Or

    Sequence should be prepared in such way that if the doc is saved the number should get generated automatically using the script without connecting to network file.

    I hope you guys will help me :)

    Thanks
    0
     
    LVL 46

    Assisted Solution

    by:Sjef Bosman
    The first you can do using the Continue parameter again, as was done a few lines down the code as well:

                If Isnull(NewNrCrt) Then
                      Msgbox("Can't generate the registration number ! Please check the path to the file: " & fileName$ )
                      Continue= False
                      Exit Sub
                End If

    The second requires a lot more (without really altering the structure of the database!):
    - find a suitable place for the sequence number
    - create the agent (you might use the existing QS-code)
    - modify the form (remove the QS-code, disallow editing a document without a sequence number, and change the field's formula)

    If you are sure that there is only one replica (you might have some local backup replicas not used for data-entry), maybe the first approach will be sufficient.
    0
     
    LVL 22

    Assisted Solution

    by:mbonaci
    Yes Sjef, I wont agree with only a few things:

    Lock method cannot cause Save conflicts, it prevents them. This is how it works:
     - if the document is not locked, this method places the lock and returns True
     - if the document is locked and the current user is one of the lock holders, this method returns True
     - if the document is locked and the current user is not one of the lock holders, this method returns False
     - if the document is modified by another user before the lock can be placed, this method raises an error

    I have the solution implemented in very distributed environment and it has never failed.

    On my first implementation, years ago, I used view sorted descending and would simply get the last number from it. This solution yielded duplicate numbers all the time. It was unreliable and very soon I dumped it.
    Then I thought of how it could be done (it was R5 then) and I facilitated the fact that you cannot have two documents with the same UNID inside a single db. I used that to generate lock, by appending a fixed string to the UniversalID of the doc I need to lock. Then if anyone else tried to get lock the method would try to create document with the same UNID, then I would catch the error and return false to lock method.

    In v6, Lock method uses Administration server defined in ACL, so it's reliable, intuitive and simple.

    The agent, you mentioned, solves nothing. You still need sequencer, which will assign numbers when called by the agent. If you had something different in mind, please post it, I would be very interested...

    aib_it,
    you really haven't read the post I mentioned (link)?
    For something to get done here, you need to be proactive, read all that is posted, answer all the questions we ask and use the suggestions we provide.

    This will work very reliable (this solution is working in production environment for 5 years now):
    For the solution bellow, you need to create one new form (name it "SeqNoFrm") with only one field (name the field "strSeqNo").
    Also, create a view and name it "SeqNoView". Selection formula should be
    SELECT Form = "SeqNoFrm"

    From Designer, while the SeqNo form is opened, click on toolbar menu: Design - Preview in Notes, enter 0 in the field and save the doc (Ctrl + s) and close it.
    Now you have everything you need.

    Open db ACL and on the advanced tab, set Administration server.
    Then open db properties and, on the first tab, check option "Allow document locking"

    In your form, for which you want sequence numbers, place the function GetSeqNo in Globals code section of your form (you have only that form to sequence, or multiple forms? - answer this question), and replace QuerySave with bellow.

    One more thing, I noticed you used FieldSetText when setting number to current document, which lead me to believe that your sequence field is editable. I suggest you make it computed, otherwise you wont be able to guarantee uniqueness.

    Read this carefully, implement the solution step by step while carefully examining each step I provided and ask if you have any questions or you get stuck...
    Sub QuerySave( Source As NotesUIDocument, Continue As Variant )
    
    	Dim newSeq As String
    
    	
    
    	If source.IsNewDoc Then
    
    		newSeq = GenSeqNo( )
    
    		
    
    		source.Document.sRegNo = "ITHELP-" & newSeq
    
    	End If
    
    	
    
    End Sub
     
    
    Function GenSeqNo() As String      
    
          Dim session As New NotesSession
    
          Dim w As New NotesUIWorkspace
    
          Dim db As NotesDatabase
    
          Dim lookupView As NotesView
    
          Dim doc As NotesDocument, lookupDoc As NotesDocument
    
          Dim nextNum As Integer      
    
          
    
          Set db = session.CurrentDatabase
     
    
          REM Exit if locking is not enabled
    
          If Not db.IsDocumentLockingEnabled Then
    
                Msgbox "Document locking not enabled"
    
                Exit Function
    
          End If
     
    
          'This is how you get current document, not using session
    
          Set doc = w.CurrentDocument.Document
    
          
    
          If doc.IsNewNote Then        'Exit if this is not a new document
    
                Set lookupView = db.GetView( "SeqNoView" )      'get the view containing the sequencer doc
    
                Set lookupDoc = lookupView.GetFirstDocument( )      'only one doc in view
    
                Call lookupDoc.Lock( True )
    
                nextNum = Cint( lookupDoc.strSeqNo(0) ) + 1      'retrieve next number            
    
                lookupDoc.strSeqNo = Cstr( nextNum )                  'increment existing number in dummy doc
    
                Call lookupDoc.Save( True, False, True )
    
                Call lookupDoc.Unlock( )
    
                
    
                GenSeqNo = Cstr( nextNum )
    
          End If
    
          
    
    End Function
    

    Open in new window

    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    What I meant with getting Save Conflicts is that, once you activate document locking in a database, you have to be aware of the fact that ALL edit-actions are locking. We developed a database with main-response documents, originally without locking and on one server (they said). After one month, they introduced a 2nd server and Save conflicts started popping up. We thought to use those nice Locking features, but after activating that we got even more conflicts per day. When a response document was updated, they wanted the main document also updated. The update could be manual, but also by an agent in the background. In short, we had lots of trouble correcting the design flaws that weren't a problem at the start, of which we were told that there were to be neglected (we told them it would be a problem if...) but proved to be a real PITA in the end.

    The agent-idea would be:
    - a single agent
    - running in ONE replica only
    - runs on all new and modified documents
    - if a (new) document with an empty sequence number is found, it adds a sequence number

    Since there's only one agent, and documents without a sequence number cannot be edited (QueryOpen or QueryModeChange), you don't need locking. Drawback: you have to wait 5-15 minutes, till the agent passed by.
    0
     
    LVL 22

    Expert Comment

    by:mbonaci
    How the agent knows which number it should give to the document?
    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    It could use the same technique of the external file, as in the current QuerySave. Or from a profile document.
    0
     
    LVL 22

    Expert Comment

    by:mbonaci
    To avoid potential problems with locking that Sjef mentioned you should do all that I mentioned in a new database which you'll create and simply change the line of code of GenSeqNo:

     Set db = session.CurrentDatabase

    with these lines:

      Set db = s.GetDatabase("YourServerName", "DatabaseRelativePath")

      If db Is Nothing Then
        Messagebox "Lock db doesn't exist"
      End If  
    0
     

    Author Comment

    by:aib_it
    Thanks guys for all the posts, i have created the copy of the current DB and now going to start the testing, i hope every thing may work as expected. :)

    Really appreciate your replies.

    Thanks
    0
     
    LVL 22

    Expert Comment

    by:mbonaci
    Is this resolved?
    0
     

    Author Comment

    by:aib_it
    Sorry guys, it is still in pending list as i have alot of other tasks to be completed. I will shortly let you guys know of the result.

    Kind Regards

    0
     

    Author Comment

    by:aib_it
    let this case be open for some time,

    0
     

    Accepted Solution

    by:
    let this case be open for some time,

    0
     

    Author Closing Comment

    by:aib_it
    Could not got the proper solution for which i was looking.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
    Article by: Rob
    Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    659 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

    29 Experts available now in Live!

    Get 1:1 Help Now