Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

dblookup, take highest number in view +1

I have a hidden view called $nn12
I have a field called NextNumber.

On my form I have an action hotspot/button to that I want to set the value of NextNumber to the highest value in coloum 1 of the $nn12 view, but increase the value by an increment of 1.
Also please note this value a numberical value but it has been stored in a text field.

Formula language is easier for me to understand but willing to accept LS so long as it is commented so I can see whats what.

0
scribla
Asked:
scribla
  • 6
  • 6
1 Solution
 
mbonaciCommented:
Sort the hidden view's column ascending.

Use function to get max value from view's column.

In your button (I presume the button is visible only in edit mode):

      Call uidoc.FieldSetText( CStr( getMaxNum( ) + 1 ) )
Function getMaxNum( ) As Long
	On Error Goto ErrHandler
	Dim s As New NotesSession
	Dim w As New NotesUIWorkspace
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim lastDoc As NotesDocument
	
	Const fieldName$ = "NameOfNumberField"
	Const viewName$ = "NameOfView"
	
	Set db = s.CurrentDatabase
	Set v = db.GetView( viewName )
	Set lastDoc = v.GetLastDocument	
	If lastDoc Is Nothing Then Exit Function
	
	getMaxNum = Clng( lastDoc.GetItemValue( fieldName )(0) )
	
out:
	Exit Function
ErrHandler:
	Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
	Resume out	
End Function

Open in new window

0
 
scriblaAuthor Commented:
Thanks, could you tell me where I put the LS example, does this go on the form somewhere or into a script library? Excuse me I'm a total newb with LS
0
 
mbonaciCommented:
OK, to clarify,
your column has to be the first sorted column in the view!

By sorting the view entries ascending, we will get the highest value at the bottom (and vice versa, descending - top).
That means that we can get last doc and get its field's value (the first sorted column's formula).

Now, when we have the max value it's easy, increment it and write to field.

If you're new to LotusScript, to add function to your hotspot:
 - copy the function to clipboard (ctrl+c)
 - locate the hotspot's Declarations section
 - position the cursor in it and press paste (ctrl+v)
The function will be created bellow Terminate event.

Now you can call it (only) from hotspot's events (in our case - Click).
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.

 
scriblaAuthor Commented:
I am pasting "Call uidoc.FieldSetText( CStr( getMaxNum( ) + 1 ) )" into the buttons Click section.
The code in  the code snippet I am pasting into Declarations section.

When I try to save the form is say there is an LS error: Not a sub or function name: UIDOC

The click section when shows Call uidoc.FieldSetText( Cstr( getMaxNum( ) + 1 ) ) in red.
0
 
mbonaciCommented:
The code for button:

Dim w as New NotesUIWorkspace
Dim uidoc as NotesUIDocument

Set uidoc = w.CurrentDocument

Call uidoc.FieldSetText( Cstr( getMaxNum( ) + 1 ) )


Remember to change the fieldName and viewName constants in the function.
0
 
scriblaAuthor Commented:
After changing the code in the Click section to the above, it now says 'Missing argument for FIELDSETTEXT'

I have changed fieldname & viewname to the field and view as nessisary in the function.

0
 
mbonaciCommented:
Sorry,
of course, the editor asks "What field do you want me to change?" :)

Call uidoc.FieldSetText( "NextNumber", Cstr( getMaxNum( ) + 1 ) )
0
 
scriblaAuthor Commented:
Thanks works great, thank you.

I was just wondering... Is there any way I could change "Const viewName$ = "NameOfView" to get the "NameOfView" from a frield on the current document?

0
 
mbonaciCommented:
Of course you can:
'The new code for the button:
 
Dim w as New NotesUIWorkspace
Dim doc As NotesDocument
Dim uidoc as NotesUIDocument
Dim viewName as String
 
Set uidoc = w.CurrentDocument
Set doc = uidoc.document  'here you get backend document (uidoc is front end - user interface)
 
viewName = doc.FieldThatHoldsTheValue(0)  'we use (0) because every field is actually an array
 
Call uidoc.FieldSetText( Cstr( getMaxNum( viewName ) + 1 ) )
 
 
 
 
'Change the function code like this (only function signature (first line) and Const line is changed):
Function getMaxNum( viewName As String ) As Long
        On Error Goto ErrHandler
        Dim s As New NotesSession
        Dim w As New NotesUIWorkspace
        Dim db As NotesDatabase
        Dim v As NotesView
        Dim lastDoc As NotesDocument
        
        Const fieldName$ = "NameOfNumberField"
                
        Set db = s.CurrentDatabase
        Set v = db.GetView( viewName )
        Set lastDoc = v.GetLastDocument 
        If lastDoc Is Nothing Then Exit Function
        
        getMaxNum = Clng( lastDoc.GetItemValue( fieldName )(0) )
        
out:
        Exit Function
ErrHandler:
        Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
        Resume out      
End Function

Open in new window

0
 
mbonaciCommented:
Be careful in multiuser environment.

If two users open the new doc at the same time (before other user saves his doc) what would happen?


I'm glad I was able to help you.
MbĀ¤
0
 
scriblaAuthor Commented:
Many thanks.

I am validating the generated number against the same view using DBLookup when the doc is saved.
Just means the user will have to generate another number using our new button if this happens, which should be very rare.
0
 
scriblaAuthor Commented:
mbonaci, I have asked a related question if you would like to take a look for me. Thanks.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/Lotus_SmartSuite/Lotus_Notes/Q_23502727.html
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now