Solved

Setting ticket number using latest work request

Posted on 2006-07-17
18
335 Views
Last Modified: 2013-12-18
Most learned colleagues,

I inherited a database and am trying to resolve an intermittent (yes, I hate these) issue where sometimes a work request will not get a number when it is saved. Well, at least it appears intermittent to me. The Querysave snippet of code that I am looking at is below. I can see where the code is testing for a work request that has no ticket number. I am looking at this particular snippet of code because that is the only place that I can see where the ticket number field is assigned with no value. Is there anyway I can ensure that the work requests will always get the next sequential number? The All Requests by Number view has the first column, the ticket number column, sorted in descending order and the ticket number field is computed with a value of TicketNumber. Thank you.

If currentdoc.IsNewDoc Then ' set ticket number
            Set db = session.CurrentDatabase
            Dim view As NotesView
            Dim selected As NotesDocumentCollection
            Set view = db.GetView( "(All Requests by Number)" )
            Set doc = view.GetFirstDocument
            If Not ( doc Is Nothing ) Then
                  Set item = doc.GetFirstItem( "TicketNumber" )
                  If (item Is Nothing) Then
                        doc.TicketNumber = ""
                        Set item = doc.GetFirstItem( "TicketNumber" )
                  End If
                  If (item.Type <> 768)Then
                        ' for some strange reason the first document doesn't have a ticket #!
                        Do While Not ( doc Is Nothing )
                              Set doc = view.GetNextDocument( doc )
                              Set item = doc.GetFirstItem( "TicketNumber" )
                              If (item Is Nothing) Then
                                    doc.TicketNumber = ""
                                    Set item = doc.GetFirstItem( "TicketNumber" )
                              End If
                              If (item.Type = 768)Then Exit Do
                        Loop
                  End If
                  acount = doc.TicketNumber(0) + 1                  
                  note.TicketNumber = acount
                  ckey = acount
                  Set selected = view.GetAllDocumentsByKey( ckey , True )
                  count = selected.Count
                  While count <> 0
                        acount = acount + 1
                        ckey = acount
                        Set selected = view.GetAllDocumentsByKey( ckey , True )
                        count = selected.Count
                  Wend
                  note.TicketNumber = acount
            End If
      End If
0
Comment
Question by:notesrookie
  • 8
  • 5
  • 4
  • +1
18 Comments
 

Author Comment

by:notesrookie
Comment Utility
BTW - This application is only on one server and a number needs to be generated when the doc is saved because the customer is notified that that ticket has been opened with a reference to the ticket number. Thanks.
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
If there are no local replications, and no one is using it for hogh volume ( simultaneous access ), then this should work in most cases.

I am sure that this can be simplified and less eror prone though.

I hope this helps !
0
 

Author Comment

by:notesrookie
Comment Utility
I used the post below, sloeber's first comments, to use another way to set the ticket number. I'll be testing to see if that works better.

http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_20738578.html?query=auto+number&topics=133
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 450 total points
Comment Utility
I hope you didn't write that code...

I think the following few lines should do the same:

    If currentdoc.IsNewDoc Then ' set ticket number
        Set db = session.CurrentDatabase
        Dim view As NotesView
        Set view = db.GetView( "(All Requests by Number)" ) ' assume this view is sorted by number, ASCENDING
   
        Set doc= view.LastDocument ' yep, it exists
        acount= 1
        If Not(doc Is Nothing) Then
            acount= Cint(doc.TicketNumber(0)) + 1
        End If
        note.TicketNumber = acount
    End If

I suppose you want to test whether documents do have a valid ticketnumber. Seems easy enough: just don't include those documents in the view. In the SELECT of the view, add something like
     ... & @IsNumber(TicketNumber)
0
 

Author Comment

by:notesrookie
Comment Utility
I've only implemented it in a test database. If you don't mind, would you please let me know what you didn't like about it? The odd thing about the script I presented in my question is that every once in a while, a work request will get saved WITHOUT a number. It's left me scratching my head and I'm trying to ensure that doesn't happen any more. Thank you.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Okay, 01:11 here, but for this once...

If currentdoc.IsNewDoc Then ' set ticket number
          Set db = session.CurrentDatabase
          Dim view As NotesView
          Dim selected As NotesDocumentCollection
          Set view = db.GetView( "(All Requests by Number)" )

-> always use views with alias names, so you never have to use parentheses in those names
-> and why walk the full view, when you have GetFirstDocument and GetLastDocument in a sorted view

          Set doc = view.GetFirstDocument
          If Not ( doc Is Nothing ) Then
               Set item = doc.GetFirstItem( "TicketNumber" )
               If (item Is Nothing) Then
                    doc.TicketNumber = ""
                    Set item = doc.GetFirstItem( "TicketNumber" )
               End If
               If (item.Type <> 768)Then

-> better to test types with IsNumeric(doc.TicketNumber(0))

                    ' for some strange reason the first document doesn't have a ticket #!
                    Do While Not ( doc Is Nothing )
                         Set doc = view.GetNextDocument( doc )
                         Set item = doc.GetFirstItem( "TicketNumber" )

---> doc can be Nothing here!! code will fatally stop here

                         If (item Is Nothing) Then
                              doc.TicketNumber = ""
                              Set item = doc.GetFirstItem( "TicketNumber" )
                         End If
                         If (item.Type = 768)Then Exit Do
                    Loop
               End If

---> same, doc can be Nothing here!

               acount = doc.TicketNumber(0) + 1              
               note.TicketNumber = acount
               ckey = acount
               Set selected = view.GetAllDocumentsByKey( ckey , True )
               count = selected.Count
               While count <> 0
                    acount = acount + 1
                    ckey = acount
                    Set selected = view.GetAllDocumentsByKey( ckey , True )
                    count = selected.Count
               Wend
               note.TicketNumber = acount

-> where's the Else??
-> if there is no document in the view (depends on the SELECT statement),
-> TicketNumber will not be filled in

          End If
     End If

Yawn... Bye...
0
 
LVL 18

Assisted Solution

by:marilyng
marilyng earned 50 total points
Comment Utility
Agree with sjef.. but anytime you try to do sequential numbers from a view that might be replicated, you risk getting duplicates.   For example, I could be saving five documents on a local replica having numbers 5,6,7,8.  And four people could have added documents on the server copy, 5,6,7,8.  The second I replicate, I have duplicate numbers.  

The only way to insure that this doesn't happen is to make sure you don't have any replicas running about, and default ticketNumber to 99999 when the document first opens.  Then you'd almost always need a cleanup agent to correct duplicate or blank numbers.  

To correct duplicates you start with a collection of all the numbers, smallest to largest.
Then do a set coll= view.getalldocumentsbykey(num,true)
            0 returned = skipped number
            >1 return, duplicates
            1 returned, OK.

Once you've trapped the duplicates, you have to assign then new numbers.  Also, in this scenario you can catch those documents having 99999.

---
Agree with sjef, the original code is a bit unfocused and doesn't trap errors correctly.  However, rather than:

Set doc= view.LastDocument ' yep, it exists
        acount= 1
        If Not(doc Is Nothing) Then
            acount= Cint(doc.TicketNumber(0)) + 1
        End If
        note.TicketNumber = acount

I would do this:
Set doc= view.LastDocument ' yep, it exists
        acount= 99999 'or some number I know we'll never use
        If Not(doc Is Nothing) Then
            acount= Cint(doc.TicketNumber(0)) + 1
        End If
        note.TicketNumber = acount


0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
> sequential numbers from a view that might be replicated, you risk getting duplicates...
True, very true, but notesrookie wrote:
> BTW - This application is only on one server ...

>  acount= 99999 ' or some number I know we'll never use
1- never say never ;)
2- you'd get errors with this statement, either compilation or runtime... (I assume)
0
 

Author Comment

by:notesrookie
Comment Utility
Sorry, all. I meant to say that I implemented sloeber's code in my test database. I didn't write the original code, as I have been taught by the both of you and the others. I knew I didn't like the original code because it was causing problems with the ticket numbering. Thanks for explaining where it went wrong. Now I know why I didn't like it. That's the issue with inherited databases and this one has passed through several hands before landing in mine. The strange thing about the original code I presented is that the document would get saved but the status would be wrong and a bunch of other things would be missing (including the ticket number) and now I see why. And even stranger, no compilation or runtime errors, hence my post :).

As for sloeber's suggestion, I created a form (one field) and view (one doc) and modified the code a little in my test database:

If note.TicketNumber(0) ="" Then            'no ticket num, chk no matter status
            Set db = session.CurrentDatabase      
            Dim view As NotesView
            Set view=db.GetView("vGetNum")      'get ticket num view
            Set doc = View.GetFirstDocument      'get doc - there's only one
            note.TicketNumber = doc.get_ticket_number(0)      'get # from tix num doc
            doc.get_ticket_number = doc.get_ticket_number(0) + 1      'increment by 1
            Call doc.Save(True,True)            'save the tix num doc      
      End If

The above code sure simplifies it quite a bit, no tangled web to sift through.  Does this look OK to you or do you think I should also check for a number I know we'll never use. Thank you.
0
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!

 
LVL 18

Expert Comment

by:marilyng
Comment Utility
I think you're ok with that, just have an agent ready to check for and resolve duplicates.
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
sjef.. the only reason I used any type of 999999, or 000000 number in any sequential database is for the local, offline people that are creating document in the field and can't get the next number.  I've done variations on this including a flag set so that when they replicate with the server, an agent runs and numbers the offline documents with the next available number.
0
 

Author Comment

by:notesrookie
Comment Utility
There is code in the Postopen event to prevent folks from using a local copy, they must use the server. Hopefully, not too many of them will know how to use the debugger to get around that.

I also implemented your check for the number 9999999. I figure even if the database gets 100 requests a day everyday of the year, I really won't be too worried about it in 274 years!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
10 million... I hope you declared acount as a Long??

274 years? I heard you're going to install this database in over 100 countries, so I assume you'll be back in April 2009 ;)
0
 

Author Comment

by:notesrookie
Comment Utility
OK - I made some mods to the code and it appears to be working for me in my test database. I decided to do this instead so that I could also test for the possibility of the ticket number being blank (don't mind me I'm being slightly paranoid). And I also don't need to test for a really, really large number.

New code:
Set view = db.GetView( "vAllNum" ) 'this view sorted by num, descending
      Set doc= view.GetFirstDocument ' yep, it exists
      Do Until Typename(doc.TicketNumber(0)) <> "STRING"      'chk for blank ticket num
            Set doc=view.GetNextDocument(doc)      'if blank go get next doc
      Loop            'break out of loop, valid ticket num
      acount = doc.TicketNumber(0) + 1      'increment ticket number
      note.TicketNumber = acount                  'set ticket number in current work request
etc. ...

Thanks sjef and marilyng. I'll be divvying up the points accordingly.
0
 

Author Comment

by:notesrookie
Comment Utility
I'll also be implementing marilyng's suggestion about running an overnight agent to check if there are any work requests with no ticket numbers and assign them numbers. Hopefully this agent will not find any after I implement the mods above.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Thanks! :)

Just a question regarding your last code. Do two mental runs:
- one with only one document in it that has an empty TicketNumber,
- one with no document at all in the view

The "yep it exists" referred to the method "GetLastDocument", so you can use a normal ascending view. By the way, as I said earlier, the loop checking for a numeric value in TicketNumber isn't necessary: just make sure that documents of that kind never enter the view. See http:Q_21921926.html#17126379 "Seems easy enough..."
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
So you know, and I don't mean to be a party pooper.. I inherited nine or ten of these sequential number type databases that looked up to a view.   All had a variety of "workarounds"  - look up to the view, look up to profile document, do both, etc.  Script to grab the number, validation event on a field to grab the number.  Both.

All had duplicate numbers, skipped numbers, and missing numbers.. 70,000 + documents.   The missing and duplicate numbers were the real problem in the workflow.

These were R4 and R5 applications.  And the coding was terrible, too.  So that might have been half the problem.

Good luck to you on this.
0
 

Author Comment

by:notesrookie
Comment Utility
sjef - I see what you mean about doing the mental run. And here I thought I was testing for all contingencies or at least most of them. I'll use your suggestion on what to include in the view. Thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

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…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

12 Experts available now in Live!

Get 1:1 Help Now