asked on

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

notesrookie

ASKER

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.

SysExpert

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 !

notesrookie

ASKER

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.

https://www.experts-exchange.com/questions/20738578/Auto-Numbering.html?query=auto+number&topics=133

ASKER CERTIFIED SOLUTION

Sjef Bosman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

notesrookie

ASKER

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.

Sjef Bosman

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...

SOLUTION

marilyng

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sjef Bosman

> 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)

notesrookie

ASKER

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.

marilyng

I think you're ok with that, just have an agent ready to check for and resolve duplicates.

marilyng

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.

notesrookie

ASKER

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!

Sjef Bosman

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 ;)

notesrookie

ASKER

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.

notesrookie

ASKER

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.

Sjef Bosman

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..."

marilyng

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.

notesrookie

ASKER

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.