Solved

Lotus script

Posted on 2009-05-06
24
1,031 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
Comment
Question by:aib_it
  • 10
  • 9
  • 4
  • +1
24 Comments
 
LVL 22

Assisted Solution

by:mbonaci
mbonaci earned 313 total points
Comment Utility
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
jitendrasachdeva earned 63 total points
Comment Utility
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
Comment Utility
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
mbonaci earned 313 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
mbonaci earned 313 total points
Comment Utility
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
mbonaci earned 313 total points
Comment Utility
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
Comment Utility
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
Sjef Bosman earned 124 total points
Comment Utility
> 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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 124 total points
Comment Utility
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
mbonaci earned 313 total points
Comment Utility
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
Comment Utility
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
Comment Utility
How the agent knows which number it should give to the document?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Is this resolved?
0
 

Author Comment

by:aib_it
Comment Utility
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
Comment Utility
let this case be open for some time,

0
 

Accepted Solution

by:
aib_it earned 0 total points
Comment Utility
let this case be open for some time,

0
 

Author Closing Comment

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

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

6 Experts available now in Live!

Get 1:1 Help Now