?
Solved

Creating a Purchase Order Number

Posted on 2006-04-18
27
Medium Priority
?
260 Views
Last Modified: 2013-12-18
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!
0
Comment
Question by:padillrr
  • 13
  • 10
  • +2
26 Comments
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 2000 total points
ID: 16478327
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
 

Author Comment

by:padillrr
ID: 16478402
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16478621
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16478640
Ah, blast, typo:
        ponumber= CInt(Right$(vdoc.POnumber(0), 2) + 1

Correctness not guaranteed...
0
 

Author Comment

by:padillrr
ID: 16480285
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16482145
> 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
 

Author Comment

by:padillrr
ID: 16482162
I'll work on that tonight, but you can't laugh if I really srew it up!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16482269
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
 
LVL 14

Expert Comment

by:cezarF
ID: 16484257
this might help http:Q_21729313.html
0
 

Author Comment

by:padillrr
ID: 16510923
I'm having a really hard time with this, I will try it when I get out of the class.....
0
 

Author Comment

by:padillrr
ID: 16564011
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16568109
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
 

Author Comment

by:padillrr
ID: 16579307
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16584046
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
 

Author Comment

by:padillrr
ID: 16586231
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16587259
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
 

Author Comment

by:padillrr
ID: 16588146
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
 

Expert Comment

by:ChrisToohey
ID: 16618712
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
 

Author Comment

by:padillrr
ID: 16630660
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
 

Author Comment

by:padillrr
ID: 16733927
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16738939
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
 

Author Comment

by:padillrr
ID: 16742308
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16742886
IMHO my answers were very helpful, so why a refund?
0
 

Author Comment

by:padillrr
ID: 16742940
I'm sorry Sjef I didn't want the refund I was just happy to post a solution for a change....
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16743096
It's better to accept the right answer, in case someone else is looking for a similar solution.

Thanks!!
0
 

Author Comment

by:padillrr
ID: 16743179
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

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.

Question has a verified solution.

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 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