• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Setting ticket number using latest work request

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
notesrookie
Asked:
notesrookie
  • 8
  • 5
  • 4
  • +1
2 Solutions
 
notesrookieAuthor Commented:
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
 
SysExpertCommented:
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
 
notesrookieAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Sjef BosmanGroupware ConsultantCommented:
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
 
notesrookieAuthor Commented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
marilyngCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
> 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
 
notesrookieAuthor Commented:
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
 
marilyngCommented:
I think you're ok with that, just have an agent ready to check for and resolve duplicates.
0
 
marilyngCommented:
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
 
notesrookieAuthor Commented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
notesrookieAuthor Commented:
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
 
notesrookieAuthor Commented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
marilyngCommented:
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
 
notesrookieAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now