Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Setting ticket number using latest work request

Posted on 2006-07-17
18
Medium Priority
?
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +1
18 Comments
 

Author Comment

by:notesrookie
ID: 17123491
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
ID: 17125682
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
ID: 17126343
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 1800 total points
ID: 17126379
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
ID: 17126470
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
ID: 17126543
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 200 total points
ID: 17127105
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
ID: 17128100
> 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
ID: 17131325
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
 
LVL 18

Expert Comment

by:marilyng
ID: 17132200
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
ID: 17132210
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
ID: 17133945
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
ID: 17134267
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
ID: 17135039
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
ID: 17135070
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
ID: 17136407
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
ID: 17137410
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
ID: 17147815
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

636 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