Solved

dblookup, take highest number in view +1

Posted on 2008-06-20
12
459 Views
Last Modified: 2013-12-18
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
Comment
Question by:scribla
  • 6
  • 6
12 Comments
 
LVL 22

Accepted Solution

by:
mbonaci earned 500 total points
ID: 21830244
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
 
LVL 2

Author Comment

by:scribla
ID: 21830295
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 21830352
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
 
LVL 2

Author Comment

by:scribla
ID: 21830467
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 21830694
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
 
LVL 2

Author Comment

by:scribla
ID: 21830731
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 22

Expert Comment

by:mbonaci
ID: 21830760
Sorry,
of course, the editor asks "What field do you want me to change?" :)

Call uidoc.FieldSetText( "NextNumber", Cstr( getMaxNum( ) + 1 ) )
0
 
LVL 2

Author Comment

by:scribla
ID: 21831070
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 21831865
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 21831904
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
 
LVL 2

Author Comment

by:scribla
ID: 21832042
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
 
LVL 2

Author Comment

by:scribla
ID: 21832500
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Validating Email Address 13 285
DAMO - "Overall status" hangs on 0% or 100% even though tranfer has finshed. 3 127
Email relaying to outside address 4 161
Lotus notes email code 13 90
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now