Creating a Purchase Order Number

I've read a lot of post on this type of incrementing numbers but it as usual with notes there are some caveats. This is my problem: I need to create a PO Number using  DeptNum(field on the form) + yyyymmdd + a sequencial 2 digit number, I need to make sure that the number is not duplicated (biggest issue from my understanding) The application resides on one server, the PO number will be added by one of 2 people, once the request has been approved. It can be generated at any point after the VP has approved the request.

My thoughts:  The form has a field that will display approved once the VP clicks a button.  He will then close the form and the workflow will send an email to the group that does the ordering. I'd like the number to be generated from the time the VP closes the form (via agent I think would be best) this way when the order group opens the db they see the PO number. I know this is not an easy one, so all your help and recommendations will be taken into account, I'll try anything!
padillrrProject CoordinatorAsked:
Who is Participating?
 
Sjef BosmanGroupware ConsultantCommented:
You've got a single point where those numbers are generated (but what when he's away, e.g. on holiday??). The solution is usually the same:
1) create a view with documents that already have numbers
2) first column is sorted on the number
3) your agent:
    open the view
    get the highest number
    if none
        number= 1
    else
        number is this one + 1
    fi

The numbers should be on a day-to-day basis? Then the code you need should be a little bit different:
    open the view
    get all documents for this day
    get the highest number from thos documents
    if none
        number= 1
    else
        number is this one + 1
    fi
0
 
padillrrProject CoordinatorAuthor Commented:
One Day Sjef, I will be good enough to put into code (lotuscript) what you just posted for right now, I'm not. I do understand that I have to create a view this will increment the last 2 digits, however I also have to change the date format and add that field with the deptnum field then add the last 2 sequencial digits and have this display on a field on the form AFTER the VP has approved the request. When the VP is away he assigns a delegate which I add to the role of VP approver so there will always be somebody approving the request. I have been trying to come up with how to do this for a while on my own with no success, I will be attending a scripting class the enod of this month which I hope will greatly improve my coding.
0
 
Sjef BosmanGroupware ConsultantCommented:
padillrr,
> I will be attending a scripting class
Good!!

The view:
- selection based on (easy attempt)
    POnumber !=""
- first column, sorted
    POnumber

The agent:
- I assume fields are correctly filled in, and the agent is called from a document
    Dim ns As New NotesSession
    Dim ws As New NotesUIWorkspace
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim vdc As NotesDocumentCollection
    Dim vdoc As NotesDocument
    Dim doc As NotesDocument
    Dim ponumber As Integer

    Set doc= ws.CurrentDocument.Document
    Set db= ns.CurrentDatabase
    Set view= db.GetView("POnumberView")
    ' your job to create the key
    Set vdc= view.GetAllDocumentsByKey(key, True)
    ponumber= 0
    If vdc.Count>0 Then
        Set vdoc= vdc.GetLastDocument
        ponumber= CInt(Right$(vdoc.POnumber(0), 2)
    End If
    ' ponumber is the value you want
   
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Sjef BosmanGroupware ConsultantCommented:
Ah, blast, typo:
        ponumber= CInt(Right$(vdoc.POnumber(0), 2) + 1

Correctness not guaranteed...
0
 
padillrrProject CoordinatorAuthor Commented:
Is this an agent then? When is the PO number being populated? Is this checking the TSD_Appr field to verify that it is approved before populating the PONumber field?
0
 
Sjef BosmanGroupware ConsultantCommented:
> Is this an agent then?
Yes, it is a part of an agent or action button

> When is the PO number being populated?
That's your part of the show, where it says "your job to create the key"

Is this checking the TSD_Appr field to verify that it is approved before populating the PONumber field?
No, not yet, same story. It is also the first time you bring up this field...

C'mon, I think it's up to you to get the logic right, and then I think you really have to try to make at least one attempt to create the button. Write down the code, but in pseudo-code, not in LotusScript. What is supposed to happen when the VP clicks the button to approve the PO?
0
 
padillrrProject CoordinatorAuthor Commented:
I'll work on that tonight, but you can't laugh if I really srew it up!
0
 
Sjef BosmanGroupware ConsultantCommented:
I won't laugh nor cry. Any serious attempt from your side is infinitely better than just accepting any code I write. But always the very first thing should be: get the logic right. Write pseudo-code and try to find out if all cases are covered. Like you said with the TSD_Appr field... There are no real basic statements for pseudo-code, but usually it's a line per "thought". For some looping and conditional statements, use the following examples

    forall documents in the view
        ...
    end forall

    if something then
        ...
    else
        ...
    end if

Don't think variables yet, nor values, but logic: what's supposed to happen when.
0
 
cezarFCommented:
this might help http:Q_21729313.html
0
 
padillrrProject CoordinatorAuthor Commented:
I'm having a really hard time with this, I will try it when I get out of the class.....
0
 
padillrrProject CoordinatorAuthor Commented:
OK Guys, I have must of it working the only problem I have is that it is not incrementing can you tell me if I'm missing something?

Sub Querysave(Source As Notesuidocument, Continue As Variant)
      Dim Session As Notessession
      Set session = New Notessession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim POdoc As NotesDocument
      Dim TSD_Appr As String
      Dim Thisdoc As Notesdocument
      Set thisdoc = source.Document
      
      If Not source.FieldGetText("TSD_Appr") = "Approved" Then
            Exit Sub
      End If
      If Not source.FieldGetText("NET60_SeqNum") = "" Then
            Exit Sub
      End If
      
      Set db = session.CurrentDatabase
      Set view = db.GetView("(POSeq)")
      If view Is Nothing Then
            Messagebox "INVALID VIEW"
            Exit Sub
      'End If
      
      Dim CompareDate As String
      Dim ViewDate As String
      comparedate = Format(Today, "YYYYMMDD")      
      Set POdoc = view.GetLastDocument
      viewdate = POdoc.Columnvalues(0)
      Dim newSequence As Integer
      If viewdate = comparedate Then            ' If the PO is created on same day
            newsequence = doc.ColumnValues(1) + 1  ' INCREMENT THE SEQUENCE
      Else
            newsequence = 1 ' START WITH 1
      End If
      
      thisdoc.NET60_SeqNum =newsequence
      thisdoc.ReverseDate = Format(Today, "YYYYMMDD")
      thisdoc.OrderNumber =       Format(Today, "YYYYMMDD") &   Source.FieldGetText ("NET60_DeptNum")  & _
      Format(newsequence, "00" )
      End Sub

I have to mention that there are several scripts running because of some workflow application. Whne  irun this code trhrough the debugger I don't see the Sequence Number get incremented.
0
 
Sjef BosmanGroupware ConsultantCommented:
Things that my built-in semantics checker found:

- something strange: there's a quote in front of the End If, remove it please. Or is it a typo??
     If view Is Nothing Then
          Messagebox "INVALID VIEW"
          Exit Sub
     'End If  <--- remove the first quote!

- whether it is correct to use uppercase letters in the format-string, I don't know.

- POdoc could be Nothing, when there are no documents yet! You need code like
     Set POdoc = view.GetLastDocument
     Dim newSequence As Integer
     newSequence= 1
     If Not(POdoc Is Nothing) Then
          viewdate = POdoc.Columnvalues(0)
          If viewdate = comparedate Then          ' If the PO is created on same day
              newsequence = doc.ColumnValues(1) + 1  ' INCREMENT THE SEQUENCE
          End If
     End If
       
0
 
padillrrProject CoordinatorAuthor Commented:
I think there is another agent running that may be causing this problem, would it be better to run an agent called from the query save?
0
 
Sjef BosmanGroupware ConsultantCommented:
The VP will save the document? I assumed (wrongly maybe) that the VP looks at the documernt in edit-mode, then presses a button called APPROVE. That button could contain the code, or trigger an agent, or do anything else. It doesn't have to be an agent, though.

What's the exact sequence of events?
0
 
padillrrProject CoordinatorAuthor Commented:
I Got it  Here is the CODE:

Sub Querysave(Source As Notesuidocument, Continue As Variant)
      Dim Session As Notessession
      Set session = New Notessession
      
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim doc As NotesDocument
      Dim Approval As String
      
      If source.FieldGetText("TSD_Appr") = "" Then
            Exit Sub
      End If
      If Not source.FieldGetText("NET60_SeqNum") = "" Then
            Exit Sub
      End If
      
      Set db = session.CurrentDatabase
      Set view = db.GetView("POSeq")
      If view Is Nothing Then
            Messagebox "INVALID VIEW"
            Exit Sub
      End If
      Dim CompareDate As String
      Dim ViewDate As String
      comparedate = Format(Today, "YYYYMMDD")      
      Set doc = view.GetLastDocument
      Dim newSequence As Integer
      If doc Is Nothing Then
            newsequence = 1 ' START WITH 1
      Else
            viewdate = doc.PODate(0)
            If viewdate = comparedate Then            ' If the PO is created on same day
                  newsequence = doc.ColumnValues(1) + 1  ' INCREMENT THE SEQUENCE
            Else
                  newsequence = 1 ' START WITH 1
            End If
      End If
      thisdoc.PODate = Format(Today, "YYYYMMDD")      
      thisdoc.NET60_SeqNum = newsequence
      thisdoc.ReverseDate = Format(Today, "YYYYMMDD")
      thisdoc.OrderNumber = Format(Today, "YYYYMMDD") & Source.FieldGetText ("NET60_DeptNum")  & _
      Format(newsequence, "00" )
            
End Sub

This plus a change to my POSeq View has it working here, is my slect statement:
SELECT @LowerCase(Form) = "net60" & NET60_SeqNum != ""
0
 
Sjef BosmanGroupware ConsultantCommented:
Perfect!

You had to modify the code I gave, eh? Instead of

     If doc Is Nothing Then
          newsequence = 1 ' START WITH 1
     Else
          viewdate = doc.PODate(0)
          If viewdate = comparedate Then          ' If the PO is created on same day
               newsequence = doc.ColumnValues(1) + 1  ' INCREMENT THE SEQUENCE
          Else
               newsequence = 1 ' START WITH 1
          End If
     End If

it is shorter and more reliable to use this

     newsequence = 1 ' START WITH 1
     If Not(doc Is Nothing) Then
          viewdate = doc.PODate(0)
          If viewdate = comparedate Then          ' If the PO is created on same day
               newsequence = doc.ColumnValues(1) + 1  ' INCREMENT THE SEQUENCE
          End If
     End If

because ONLY when a document exists of the same day, you will have to adapt newsequence. I agree with you that your code is according to the books, pero no es muy importante...

0
 
padillrrProject CoordinatorAuthor Commented:
I'm learning, the class was great and the instructor incredible, it's good to finally understand back end classes versus front end classes, instantiating, etc etc......
0
 
ChrisTooheyCommented:
Don't know if this will help ya, but in the past I've done this for clients requesting this type of functionality.

The document doesn't receive it's "key", or in your case, the PO#, until it's reached a certain point in it's lifecycle. This allows us to throw away false-starts and tests in the system without burning through would-be-valid numbers.

I suggest the following:

Let's say you have 3 steps in this part of the lifecycle of the PO. 1) Creation, 2) Validation/Review, 3) Request PO#.

You can create the document, filling out what you need, triggering UI validation to make sure your data is good. Step 2 can be triggered in the UI (as I just mentioned) with some application logic, or via actual workflow by routing the document to a reviewer. Step 3 can occur when it's passed validation and is finally ready to receive the PO#. To do this, I've simply created a "sleeper" agent that runs on the server. If a document is ready to have a PO# assigned, it grabs the doc, checks to see what the next valid PO# in scheme is, and assigns the value to the document.

In the past, I've used a "report profile"-method (seen here: http://www.dominoguru.com/pages/thereportprofile.html ) to simply keep track of what the next valid number is so I don't have to run around my application looking for various bits of data.  The PO#-assignment agent grabs the next value after looking it up in the report profile, then sets the report profile PO# recording to +1 (or whatever the appropriate value is). The nice thing about using this method, IMHO, is that adopting this approach allows you to design your other applications to look for information in a common place, instead of having to write one-offs that scour through an application to return relatively simple data.

Just a suggestion, but the lifecycle-approach certainly addresses several of the issues that local-replica database applications can produce. At least it's worked for me in the past!

HTH,

-Chris

Chris Toohey
http://www.dominoguru.com
0
 
padillrrProject CoordinatorAuthor Commented:
Thanks Chirs but as you can see I actually was able to make it work, I am generating the PO# after the last approval, so there is no chance of conflict. When the items are ready to be purchased the PO number is already on the order. I do however appreciate the comments and suggestions.
0
 
padillrrProject CoordinatorAuthor Commented:
Marilyng, I actually was able to figure this one out but I would like to split the points between all that assisted in this question. Thanks
0
 
marilyngCommented:
If you post your solution, then you can request a refund.  If you don't close the question, then I will come back in two days and post my recommendation. :)
0
 
padillrrProject CoordinatorAuthor Commented:
no problem here it is:

Created a view with 2 columns Date and a combination of the dept number and reverse date then I put this code in the query save:

Sub Querysave(Source As Notesuidocument, Continue As Variant)
      Dim Session As Notessession
      Set session = New Notessession
      
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim doc As NotesDocument
      Dim Approval As String
      
      If source.FieldGetText("TSD_Appr") = "" Then
            Exit Sub
      End If
      If Not source.FieldGetText("NET60_SeqNum") = "" Then
            Exit Sub
      End If
      
      Set db = session.CurrentDatabase
      Set view = db.GetView("POSeq")
      If view Is Nothing Then
            Messagebox "INVALID VIEW"
            Exit Sub
      End If
      Dim CompareDate As String
      Dim ViewDate As String
      comparedate = Format(Today, "YYYYMMDD")      
      Set doc = view.GetLastDocument
      Dim newSequence As Integer
      If doc Is Nothing Then
            newsequence = 1 ' START WITH 1
      Else
      viewdate = doc.PODate(0)
      If viewdate = comparedate Then      ' If the PO is created on same day
      newsequence = doc.ColumnValues(1) + 1  ' INCREMENT THE SEQUENCE
      Else
      newsequence = 1 ' START WITH 1
      End If
      End If
      thisdoc.PODate = Format(Today, "YYYYMMDD")      
      thisdoc.NET60_SeqNum = newsequence
      thisdoc.ReverseDate = Format(Today, "YYYYMMDD")
      thisdoc.OrderNumber = Source.FieldGetText ("NET60_DeptNum") & Format(Today, "YYYYMMDD")  & _
      Format(newsequence, "00" )
      End Sub

Works Like a charm and there is no worries about dupes because it is generated at one of the approval steps.
0
 
Sjef BosmanGroupware ConsultantCommented:
IMHO my answers were very helpful, so why a refund?
0
 
padillrrProject CoordinatorAuthor Commented:
I'm sorry Sjef I didn't want the refund I was just happy to post a solution for a change....
0
 
Sjef BosmanGroupware ConsultantCommented:
It's better to accept the right answer, in case someone else is looking for a similar solution.

Thanks!!
0
 
padillrrProject CoordinatorAuthor Commented:
actually I based all I did on that recommendation that I accepted.....that's what helped me out the most.....but I will the next time and as always thanks for your help.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.