scribla
asked on
Question based on previous solution from mbonaci
Previous Solution: https://www.experts-exchange.com/questions/23501788/dblookup-take-highest-number-in-view-1.html?anchorAnswerId=21831904#a21831904
I've just reaslised a serious flaw in my plan. The problem is that the 1st 2 digits of the number are a divisional code, and must remain the same, only the tail end of the number can be incremented +1. E.G. 74991 should roll to 741000, rather then 75001.
I've just reaslised a serious flaw in my plan. The problem is that the 1st 2 digits of the number are a divisional code, and must remain the same, only the tail end of the number can be incremented +1. E.G. 74991 should roll to 741000, rather then 75001.
ASKER
What I have tried to do is put a formula on my views columns: @TextToNumber(@RightBack(E nquiryNumb er;2))
This works fine for the view, but the lotusscript seems to ingnore my column formula and pull the full number in? or perhaps it is cached?
This works fine for the view, but the lotusscript seems to ingnore my column formula and pull the full number in? or perhaps it is cached?
Try this:
'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( "74"+ 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
ASKER
Thanks, I haven't tried this yet but I'm not sure it will do what I need. Sorry as what I have failed to mention is that the 1st 2 digits are dynamic, departments eg 12, 14, 17, 22 etc.
Looking at the code above it would seem that we 1st 2 digits are assumed to be 74?
Looking at the code above it would seem that we 1st 2 digits are assumed to be 74?
Yes..it is assumed to be 74.
If it is based on dept., then
dim tmpstr as string
If curdoc.dept(0)="First Dept" then
tmpstr="74"
end if
If curdoc.dept(0)="second Dept" then
tmpstr="12"
end if
Like that .. continue..
Instead of below...
Call uidoc.FieldSetText( "74"+ Cstr( getMaxNum( viewName ) + 1 ) )
use
Call uidoc.FieldSetText( tmpstr+ Cstr( getMaxNum( viewName ) + 1 ) )
If it is based on dept., then
dim tmpstr as string
If curdoc.dept(0)="First Dept" then
tmpstr="74"
end if
If curdoc.dept(0)="second Dept" then
tmpstr="12"
end if
Like that .. continue..
Instead of below...
Call uidoc.FieldSetText( "74"+ Cstr( getMaxNum( viewName ) + 1 ) )
use
Call uidoc.FieldSetText( tmpstr+ Cstr( getMaxNum( viewName ) + 1 ) )
ASKER
I have rather a lot of departments to define in that case, could the 1st 2 digits just not be grabbed and stored as the string? using the LS equivelent of @left(fieldname;2)
or, I have a field on my document that chooses which view gets used to get the next number, the above LS uses the data in the field to decide which view it should use. As it happens the field choices/view names are the very same departmental prefixes, 12, 14, etc.
or, I have a field on my document that chooses which view gets used to get the next number, the above LS uses the data in the field to decide which view it should use. As it happens the field choices/view names are the very same departmental prefixes, 12, 14, etc.
then if it is the case, then use below code:
'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 ) ) )
'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) )
dim tmpstr as string, tmpval as string
dim tmpvalue as integer
tmpval = StrRightBack(lastDoc.GetItemValue( fieldName )(0),2) 'Gets values after the first 2 chars from Left.
tmpvalue=cint(tmpval)+1 'Increment the value ...
tmpstr=Left(lastDoc.GetItemValue( fieldName )(0),2) 'this gives the first 2 characters starting from Left.
tmpstr=tmpstr:cstr(tmpvalue) 'Now concatenate both the values....
getMaxNum = Clng( tmpstr) 'Convert it to Long .... even we make it as a return String
out:
Exit Function
ErrHandler:
Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
Resume out
End Function
ASKER
When I paste in function code I get an error with this line:
tmpstr=tmpstr:cstr(tmpvalu e) 'Now concatenate both the values....
Error is "Unexpected: cstr; Expected: Statement"
tmpstr=tmpstr:cstr(tmpvalu
Error is "Unexpected: cstr; Expected: Statement"
do this ....
tmpstr=tmpstr & cstr(tmpvalue) 'Now concatenate both the values....
While you use Debugger check what values it is assigning to tmpstr and tmpvalue and tmpval.
And check the type of variants .. is it string or integer ...based on that use cstr
even u can use tmpstr=tmpstr & tmpvalue 'Now concatenate both the values....
Just try R&D ... you can figureout easily ....
tmpstr=tmpstr & cstr(tmpvalue) 'Now concatenate both the values....
While you use Debugger check what values it is assigning to tmpstr and tmpvalue and tmpval.
And check the type of variants .. is it string or integer ...based on that use cstr
even u can use tmpstr=tmpstr & tmpvalue 'Now concatenate both the values....
Just try R&D ... you can figureout easily ....
ASKER
Ok, but I'm not sure how to use the debugger to view the values of tmpstr tmpvalue etc.
Here is how the code looks now, I have a couple of questions:
On the Function code, is " 'getMaxNum = Clng( lastDoc.GetItemValue( fieldName )(0) )" intended to be commented out?
The button code is showing an error on the line "Call uidoc.FieldSetText( "EnquiryNumber",Cstr( getMaxNum( viewName ) ) )" Error: Type Mismatch on VIEWNAME
Here is how the code looks now, I have a couple of questions:
On the Function code, is " 'getMaxNum = Clng( lastDoc.GetItemValue( fieldName )(0) )" intended to be commented out?
The button code is showing an error on the line "Call uidoc.FieldSetText( "EnquiryNumber",Cstr( getMaxNum( viewName ) ) )" Error: Type Mismatch on VIEWNAME
'Function Code
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$ = "EnquiryNumber"
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) )
Dim tmpstr As String, tmpval As String
Dim tmpvalue As Integer
tmpval = Strrightback(lastDoc.GetItemValue( fieldName )(0),2) 'Gets values after the first 2 chars from Left.
tmpvalue=Cint(tmpval)+1 'Increment the value ...
tmpstr=Left(lastDoc.GetItemValue( fieldName )(0),2) 'this gives the first 2 characters starting from Left.
tmpstr=tmpstr & Cstr(tmpvalue) 'Now concatenate both the values....
getMaxNum = Clng( tmpstr) 'Convert it to Long .... even we make it as a return String
out:
Exit Function
ErrHandler:
Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
Resume out
End Function
' Button Code (click)
Sub Click(Source As Button)
Dim w As New NotesUIWorkspace
Set uidoc = w.CurrentDocument
Set doc = uidoc.document 'here you get backend document (uidoc is front end - user interface)
viewName = doc.DeptPrefix(0) 'we use (0) because every field is actually an array
Call uidoc.FieldSetText( "EnquiryNumber",Cstr( getMaxNum( viewName ) ) )
End Sub
May be you are not passing ViewName properly..
to use debugger:
File -> Tools->Lotus Script Debugger.
To turn off use the same...
to use debugger:
File -> Tools->Lotus Script Debugger.
To turn off use the same...
ASKER
I'm really struggling here.
I have around 20 views, of those twenty, two are working correctly with the button.
15 or so are failing with "Type Mismatch" the remainder don't fail with an error, but compute the wrong answers, normally very low values such XX+4 or something like that.
All the views are clones of view #1 (which works) just the select statement is modified to only get numbers for that department.
If I have not mentioned before, the numbers being worked on here are actually stored in a text field, and for the single column in each view I have used the following formula @TextToNumber(EnquiryNumbe r). There are a few numbers in some of the view that contain alpha charachters, but this is also true for view #1, which works fine, and in any case the numbers that are alpha-numeric are at the opposite (top) end of the view so should not even been seen by the LS.
Using the debugger I am not really able to tell what is happening here. The ones that fail with type mismatch: don't get any data pushed into variables TMPSTR or TMPVAL, but they are getting passed the correct viewname from my 'DeptPrefix' field.
I have modified your code example somewhat, as it did not work to begin with (missing Dim's, and a line was commented out accidently) So if you would be as so kind to review the code snippet and see If I have made some mistake I am not aware of.
I have around 20 views, of those twenty, two are working correctly with the button.
15 or so are failing with "Type Mismatch" the remainder don't fail with an error, but compute the wrong answers, normally very low values such XX+4 or something like that.
All the views are clones of view #1 (which works) just the select statement is modified to only get numbers for that department.
If I have not mentioned before, the numbers being worked on here are actually stored in a text field, and for the single column in each view I have used the following formula @TextToNumber(EnquiryNumbe
Using the debugger I am not really able to tell what is happening here. The ones that fail with type mismatch: don't get any data pushed into variables TMPSTR or TMPVAL, but they are getting passed the correct viewname from my 'DeptPrefix' field.
I have modified your code example somewhat, as it did not work to begin with (missing Dim's, and a line was commented out accidently) So if you would be as so kind to review the code snippet and see If I have made some mistake I am not aware of.
Sub Click(Source As Button)
'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.DeptPrefix(0) 'we use (0) because every field is actually an array
Call uidoc.FieldSetText( "EnquiryNumber",Cstr( getMaxNum( viewName ) ) )
End Sub
-------------------------------------------------------
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$ = "EnquiryNumber"
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) )
Dim tmpstr As String, tmpval As String
Dim tmpvalue As Integer
tmpval = Strrightback(lastDoc.GetItemValue( fieldName )(0),2) 'Gets values after the first 2 chars from Left.
tmpvalue=Cint(tmpval)+1 'Increment the value ...
tmpstr=Left(lastDoc.GetItemValue( fieldName )(0),2) 'this gives the first 2 characters starting from Left.
tmpstr=tmpstr & Cstr(tmpvalue) 'Now concatenate both the values....
getMaxNum = Clng( tmpstr) 'Convert it to Long .... even we make it as a return String
out:
Exit Function
ErrHandler:
Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
Resume out
End Function
What is the error it is coming and in which line?
Can you upload the screen shot of the error?
Try this code now...
Can you upload the screen shot of the error?
Try this code now...
'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( "EnquiryNumber",getMaxNum( viewName ) )
'Change the function code like this (only function signature (first line) and Const line is changed):
Function getMaxNum( viewName As String ) As string
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) ) 'this is not needed anymore.
dim tmpstr as string, tmpval as string
dim tmpvalue as integer
tmpval = StrRightBack(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'Gets values after the first 2 chars from Left.
tmpvalue=cint(tmpval)+1 'Increment the value ...
tmpstr=Left(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'this gives the first 2 characters starting from Left.
tmpstr=tmpstr:cstr(tmpvalue) 'Now concatenate both the values....
getMaxNum = tmpstr 'Return value
out:
Exit Function
ErrHandler:
Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
Resume out
End Function
ASKER
Line that causes error: tmpvalue=Cint(tmpval)+1 'Increment the value ...
Error 13: Type Mismatch.
Seems to behave in identical fashion as old code.
Please note that I changed:
tmpstr=tmpstr:cstr(tmpvalu e) 'Now concatenate both the values....
to:
tmpstr=tmpstr & Cstr(tmpvalue) 'Now concatenate both the values...
As it would not compile.
Screenies (Expanded LastDoc section as I guess this is the best clue)
Using View "12" http://scribla.pwp.blueyonder.co.uk/images/EE/Success.jpg
Using View "17" http://scribla.pwp.blueyonder.co.uk/images/EE/Failure.jpg
Success.jpg
Failure.jpg
Error 13: Type Mismatch.
Seems to behave in identical fashion as old code.
Please note that I changed:
tmpstr=tmpstr:cstr(tmpvalu
to:
tmpstr=tmpstr & Cstr(tmpvalue) 'Now concatenate both the values...
As it would not compile.
Screenies (Expanded LastDoc section as I guess this is the best clue)
Using View "12" http://scribla.pwp.blueyonder.co.uk/images/EE/Success.jpg
Using View "17" http://scribla.pwp.blueyonder.co.uk/images/EE/Failure.jpg
Success.jpg
Failure.jpg
the code looks perfect...the first attachment is giving the appropriate results.
then what is the problem now?
then what is the problem now?
ASKER
The problem is I only get the success result when using view 12 & 42. All other view fails when the button is clicked with "Error 13: Type Mismatch" (Caused by line 23 which is 'tmpvalue=Cint(tmpval)+1 'Increment the value ...')
This must be a problem with the views themselves but I can't imagine what that is as they where all copies of the successful view '12'.
If there is any other info I can give you please let me know.
This must be a problem with the views themselves but I can't imagine what that is as they where all copies of the successful view '12'.
If there is any other info I can give you please let me know.
Check this line:
tmpval = StrRightBack(cstr(lastDoc. GetItemVal ue( fieldName )(0)),2) 'Gets values after the first 2 chars from Left.
It's obviously from debugger image that tmpVal = "" (doc either hasn't got that item or item value is empty string), so you get error in line
tmpvalue=cint(tmpval)+1 'Increment the value ...
cuz CInt can't work on empty string.
Hope this helps,
mb¤
tmpval = StrRightBack(cstr(lastDoc.
It's obviously from debugger image that tmpVal = "" (doc either hasn't got that item or item value is empty string), so you get error in line
tmpvalue=cint(tmpval)+1 'Increment the value ...
cuz CInt can't work on empty string.
Hope this helps,
mb¤
This code works only if there are documents in those views... I am not sure how you want to tackle the dept., code... if there are no docs..
Either you need to have a config doc for this, so that we can maintain the codes of depts., or maintain atleast one document in all the views or capture the dept code in another field and append it while saving..
I am sure the error is to do with the value..it is not finding any value from other views.
So..the code is OK...it is the value which we need to pass... if there are no docs in that view.
use the modified code
Either you need to have a config doc for this, so that we can maintain the codes of depts., or maintain atleast one document in all the views or capture the dept code in another field and append it while saving..
I am sure the error is to do with the value..it is not finding any value from other views.
So..the code is OK...it is the value which we need to pass... if there are no docs in that view.
use the modified code
'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( "EnquiryNumber",getMaxNum( viewName ) )
'Change the function code like this (only function signature (first line) and Const line is changed):
Function getMaxNum( viewName As String ) As string
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) ) 'this is not needed anymore.
dim tmpstr as string, tmpval as string
dim tmpvalue as integer
if cstr(lastDoc.GetItemValue( fieldName )(0))<>"" then
tmpval = StrRightBack(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'Gets values after the first 2 chars from Left.
tmpvalue=cint(tmpval)+1 'Increment the value ...
tmpstr=Left(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'this gives the first 2 characters starting from Left.
tmpstr=tmpstr & cstr(tmpvalue) 'Now concatenate both the values....
getMaxNum = tmpstr 'Return value
else
msgbox "Unable to retrieve value. Failed"
getMaxNum =""
end if
out:
Exit Function
ErrHandler:
Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
Resume out
End Function
ASKER
There are docs in every view I have created, as this is an existing app, the only change is that data in the EnquiryNumber field was entered manually by the user.
The only column used per view is for the very same EnquiryNumber field that the code looks to extract from the last doc.
To compare view 12 to view 17 (or any other) both have show docs sorted in ascending order. When previewing each view in notes, both views show docs a plently, and opening the last doc of each view the number is definatly present.
Each view has a selection, (simple search) (uses 'Enquiry' form AND field Department contains xxxxxxxx)
It's almost as if the LS is seeing docs with null fields that I cannot see when previewing the view in notes.
The DB uses softdeletions, but the trash folder is currently empty. The DB uses lots of different forms but these are filtered by the view selection.
I hope the above makes sense guys, and thank you for your patience!
Perhaps I should run a fixup and see if changes anything...
The only column used per view is for the very same EnquiryNumber field that the code looks to extract from the last doc.
To compare view 12 to view 17 (or any other) both have show docs sorted in ascending order. When previewing each view in notes, both views show docs a plently, and opening the last doc of each view the number is definatly present.
Each view has a selection, (simple search) (uses 'Enquiry' form AND field Department contains xxxxxxxx)
It's almost as if the LS is seeing docs with null fields that I cannot see when previewing the view in notes.
The DB uses softdeletions, but the trash folder is currently empty. The DB uses lots of different forms but these are filtered by the view selection.
I hope the above makes sense guys, and thank you for your patience!
Perhaps I should run a fixup and see if changes anything...
may be check the old docs., if the EnquiryNumber exists or not. if it exists what is the format of it.
This is the great example of problem that needs use of debugger. You have to familiarize with it if you are going to maintain L/N applications.
Simply start the debugger while in Designer and click on Notes preview. Then start clicking "Step into" and observe what happens line by line. You'll get it in no time...
mbonaci
Simply start the debugger while in Designer and click on Notes preview. Then start clicking "Step into" and observe what happens line by line. You'll get it in no time...
mbonaci
ASKER
Would it need to exist in every doc, or just the one at the bottom of the view?
Anyway I have modified selection of the non-working view 17 to the following
uses 'Enquiry' form AND field Department contains Washington AND field EnquiryNumber contains 17
So, in theory, there should be no docs in the view if the EnquiryNumber field is null???
Well the result is the same, the code still doesn't think the last doc contains any data in that field.
I will run through the debugger again MB, see if I can spot anything, please bear with me, I had no idea the debugger even existed until yesterday, I'm a formula language kiddie.
Anyway I have modified selection of the non-working view 17 to the following
uses 'Enquiry' form AND field Department contains Washington AND field EnquiryNumber contains 17
So, in theory, there should be no docs in the view if the EnquiryNumber field is null???
Well the result is the same, the code still doesn't think the last doc contains any data in that field.
I will run through the debugger again MB, see if I can spot anything, please bear with me, I had no idea the debugger even existed until yesterday, I'm a formula language kiddie.
ASKER
There must be some other factor here I'm missing, please see screen shots for a department 12 Success and dept 17 fail. With the values for the field in question expanded.
As you can see from the pics, in both cases the script manages to find the last doc, and in both cases the field EnquiryNumber is populated.
However, I did notice something the last doc that is found in the 12 view is around 3 years old, due to user error the used an EnquiryNumber far above the sequence that in use at the time. The 17 view shows an 2 week old doc. It is possible that the design of the form has changed over this period, however, both docs are storing the number as text according to the docs properties. I even edited and saved both docs so see if it would cause a different result. Sadly not. Sorry I'm clutching at straws here!
12-Success.jpg
17-Fail.jpg
As you can see from the pics, in both cases the script manages to find the last doc, and in both cases the field EnquiryNumber is populated.
However, I did notice something the last doc that is found in the 12 view is around 3 years old, due to user error the used an EnquiryNumber far above the sequence that in use at the time. The 17 view shows an 2 week old doc. It is possible that the design of the form has changed over this period, however, both docs are storing the number as text according to the docs properties. I even edited and saved both docs so see if it would cause a different result. Sadly not. Sorry I'm clutching at straws here!
12-Success.jpg
17-Fail.jpg
I don't see a thing in your images, except what I already said, the values of tmpStr and tmpVal are empty string.
You expanded the NotesDocument, but to view the field values of document expand its' Items section (array of objects of type NotesItem).
Check whether:
- all the views are sorted by number you are looking up, ascending
- it is the first (from left to right) sorted column in a view
- the sorted column is sorted as text (1200 < 130) or as numbers (1200 > 130)
- there are any letters at the bottom of the view in that column
- you have changed the fieldName and viewName accordingly for the current view
You expanded the NotesDocument, but to view the field values of document expand its' Items section (array of objects of type NotesItem).
Check whether:
- all the views are sorted by number you are looking up, ascending
- it is the first (from left to right) sorted column in a view
- the sorted column is sorted as text (1200 < 130) or as numbers (1200 > 130)
- there are any letters at the bottom of the view in that column
- you have changed the fieldName and viewName accordingly for the current view
ASKER
MB: "You expanded the NotesDocument, but to view the field values of document expand its' Items section (array of objects of type NotesItem)."
Unless I'm completely in the wrong place, that is what I think I have done. Have you clicked the images to make them bigger? or am totally in the wrong place here? I have expanded LASTDOC > ITEMS > FIELD (EnquiryNumber) > VALUES (I am using LN8.0.1 client/designer, just incase it works diffently)
To clarify the view situation.
The view(s) contains 1 column (field = EnquiryNumber) and the column is sorted ascending as a number (120 > 13)
Each view created is actually a 'copy of' view 12 (which works) the only difference being the selection statement.
Regards the fieldname and viewname, is consistant everywhere, as its the same field everytime (as per your initial solution MB) and the viewname is selected from a diaglog box like-for-like (users chooses "12", the code then uses view "12")
There are no letters or anything other than expected when I look the views. Just those EnquiryNumbers.
What is very interesting is that the solution you provided me previously MB works perfectly, and yet it utilises those very same views as the ones I'm having trouble with today. And I've just checked it again incase I'm loosing the plot and it works perfectly, with those very same views.
This problem has only appeared since we've now split the first two digits away, incremented the remainder and re-assembled.
If there is something in this debugger that I should be investigating further, please let me know.
Unless I'm completely in the wrong place, that is what I think I have done. Have you clicked the images to make them bigger? or am totally in the wrong place here? I have expanded LASTDOC > ITEMS > FIELD (EnquiryNumber) > VALUES (I am using LN8.0.1 client/designer, just incase it works diffently)
To clarify the view situation.
The view(s) contains 1 column (field = EnquiryNumber) and the column is sorted ascending as a number (120 > 13)
Each view created is actually a 'copy of' view 12 (which works) the only difference being the selection statement.
Regards the fieldname and viewname, is consistant everywhere, as its the same field everytime (as per your initial solution MB) and the viewname is selected from a diaglog box like-for-like (users chooses "12", the code then uses view "12")
There are no letters or anything other than expected when I look the views. Just those EnquiryNumbers.
What is very interesting is that the solution you provided me previously MB works perfectly, and yet it utilises those very same views as the ones I'm having trouble with today. And I've just checked it again incase I'm loosing the plot and it works perfectly, with those very same views.
This problem has only appeared since we've now split the first two digits away, incremented the remainder and re-assembled.
If there is something in this debugger that I should be investigating further, please let me know.
If you are using my last code, if the number is empty it will give messagebox. .... check the last document EnquiryNumber data. Let me know what is the value of EnquiryNo field in the last document of 17 (which is not working).
ASKER
Good morning madheeswar.
I've applied your new code but I'm not seeing a message box, just the usual default error output: type mismatch.
I've just deleted and remade views 12 & 17 from scratch, no cloning. Same result.
As the document that is at the bottom of 12 is very old, and the document at the bottom of 17 is very recent; I've created a new document for each department 1299999 & 1799999 which both show up at the very bottom of each view.
So with these two new docs in place behaves differently.
12 now fails "Line: 22 - Error No: 6: Overflow"
17 now fails "Line: 22 - Error No: 13: Type Mismatch"
So, to avoid running into problems with exiting data, I've created a new department 99, 99 view, and created one document EnquiryNumber 9999. (Which shows up in the view)
Result is "Line: 22 - Error No: 13: Type Mismatch" Hmmm... and yet again; 12, 17 & the new 99 work prefectly fine using the old code that just increments the entire number rather than the split, increment, rejoin. What are we missing?
Thanks for sticking with me on this, I'm sure this is becoming rather frustrating, especially with my lack of experiance with LS.
I've applied your new code but I'm not seeing a message box, just the usual default error output: type mismatch.
I've just deleted and remade views 12 & 17 from scratch, no cloning. Same result.
As the document that is at the bottom of 12 is very old, and the document at the bottom of 17 is very recent; I've created a new document for each department 1299999 & 1799999 which both show up at the very bottom of each view.
So with these two new docs in place behaves differently.
12 now fails "Line: 22 - Error No: 6: Overflow"
17 now fails "Line: 22 - Error No: 13: Type Mismatch"
So, to avoid running into problems with exiting data, I've created a new department 99, 99 view, and created one document EnquiryNumber 9999. (Which shows up in the view)
Result is "Line: 22 - Error No: 13: Type Mismatch" Hmmm... and yet again; 12, 17 & the new 99 work prefectly fine using the old code that just increments the entire number rather than the split, increment, rejoin. What are we missing?
Thanks for sticking with me on this, I'm sure this is becoming rather frustrating, especially with my lack of experiance with LS.
Please use this code now...
While you get each message, make a note of it in sequence and paste it here and let me know.
Better take screen shots of each (ALT+PrintScreen) message box and paste it into a Word doc and upload it to here.
While you get each message, make a note of it in sequence and paste it here and let me know.
Better take screen shots of each (ALT+PrintScreen) message box and paste it into a Word doc and upload it to here.
'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
msgbox "View Name:" & viewname 'You need to get the view name.
dim tmpfinal as string
tmpfinal =getMaxNum( viewName ) " I hope the ViewName is passing correctly.
msgbox "The Final Value is ..." & tmpfinal
Call uidoc.FieldSetText( "EnquiryNumber",tmpfinal )
msgbox "Final value set:" & cstr(doc.EnquiryNumber(0))
'Change the function code like this (only function signature (first line) and Const line is changed):
Function getMaxNum( viewName As String ) As string
msgbox "Came into Function"
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" 'this is field name which you will be giving .. I think it should be EnquiryNumber
msgbox "Field Name:" & fieldName
msgbox "View Name passed to Function:" & viewname
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) ) 'this is not needed anymore.
dim tmpstr as string, tmpval as string
dim tmpvalue as integer
if cstr(lastDoc.GetItemValue( fieldName )(0))<>"" then
msgbox "Came into If condition function"
tmpval = StrRightBack(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'Gets values after the first 2 chars from Left.
msgbox "TMPVAL:" tmpval
tmpvalue=cint(tmpval)+1 'Increment the value ...
msgbox "TMPVALUE:" & cstr(tmpvalue)
tmpstr=Left(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'this gives the first 2 characters starting from Left.
msgbox "First TMPSTR:" & tmpstr
tmpstr=tmpstr & cstr(tmpvalue) 'Now concatenate both the values....
msgbox "Second TMPSTR:" & tmpstr
getMaxNum = tmpstr 'Return value
else
msgbox "Unable to retrieve value. Failed"
getMaxNum =""
end if
Msgbox "FINAL:" & getMaxNum
out:
Exit Function
ErrHandler:
Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
Resume out
End Function
ASKER
Thanks, just small problem compiling.
On Click, The Line: tmpfinal =getMaxNum( viewName ) " I hope the ViewName is passing correctly.
Error: Unterminated string constant
This is just a typo I think, I replaced the quote " with '
But I can't fix this one is in GetMaxNum:
The Line: msgbox "TMPVAL:" tmpval
Error: Unexpected: tmpval, Expected: End-of-statement; Operator; ,
On Click, The Line: tmpfinal =getMaxNum( viewName ) " I hope the ViewName is passing correctly.
Error: Unterminated string constant
This is just a typo I think, I replaced the quote " with '
But I can't fix this one is in GetMaxNum:
The Line: msgbox "TMPVAL:" tmpval
Error: Unexpected: tmpval, Expected: End-of-statement; Operator; ,
ASKER
I've changed it to "Msgbox "TMPVAL:" & tmpval" Hope it works, will post results soon.
msgbox "TMPVAL:" tmpval
make it as
msgbox "TMPVAL:" & tmpval
make it as
msgbox "TMPVAL:" & tmpval
ASKER
It seems for 99, there is no value for EnquiryNumber or the length of the value is too less.
For 12, not sure why it is giving overflow.... we are not using any arrays...
You just try this code...
If we store only the sequence number separately it would be good for future needs. Else, we need to separate and concatenate. Anyway, you are passing the view name, if we store the sequence number separately, we can concatenate Viewname+sequenceNumber.
For 12, not sure why it is giving overflow.... we are not using any arrays...
You just try this code...
If we store only the sequence number separately it would be good for future needs. Else, we need to separate and concatenate. Anyway, you are passing the view name, if we store the sequence number separately, we can concatenate Viewname+sequenceNumber.
'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
msgbox "View Name:" & viewname 'You need to get the view name.
dim tmpfinal as string
tmpfinal =getMaxNum( viewName ) ' I hope the ViewName is passing correctly.
msgbox "The Final Value is ..." & tmpfinal
Call uidoc.FieldSetText( "EnquiryNumber",tmpfinal )
msgbox "Final value set:" & cstr(doc.EnquiryNumber(0))
'Change the function code like this (only function signature (first line) and Const line is changed):
Function getMaxNum( viewName As String ) As string
msgbox "Came into Function"
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" 'this is field name which you will be giving .. I think it should be EnquiryNumber
msgbox "Field Name:" & fieldName
msgbox "View Name passed to Function:" & viewname
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) ) 'this is not needed anymore.
dim tmpstr as string, tmpval as string
dim tmpvalue as integer
if cstr(lastDoc.GetItemValue( fieldName )(0))<>"" then
msgbox "Came into If condition function"
msgbox "Actual Value from last doc:" & cstr(lastDoc.GetItemValue( fieldName )(0))
tmpval = StrRightBack(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'Gets values after the first 2 chars from Left.
msgbox "TMPVAL:" & tmpval
if tmpval<>"" then
tmpvalue=cint(tmpval)+1 'Increment the value ...
else
tmpvalue=1
end if
msgbox "TMPVALUE:" & cstr(tmpvalue)
tmpstr=Left(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'this gives the first 2 characters starting from Left.
msgbox "First TMPSTR:" & tmpstr
if tmpstr<>"" then
tmpstr=tmpstr & cstr(tmpvalue) 'Now concatenate both the values....
else
tmpstr=cstr(viewName) & cstr(tmpvalue)
end if
msgbox "Second TMPSTR:" & tmpstr
getMaxNum = tmpstr 'Return value
else
msgbox "Unable to retrieve value. Failed"
getMaxNum =""
end if
Msgbox "FINAL:" & getMaxNum
out:
Exit Function
ErrHandler:
Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
Resume out
End Function
ASKER
try this..
'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
msgbox "View Name:" & viewname 'You need to get the view name.
dim tmpfinal as string
tmpfinal =getMaxNum( viewName ) ' I hope the ViewName is passing correctly.
msgbox "The Final Value is ..." & tmpfinal
Call uidoc.FieldSetText( "EnquiryNumber",tmpfinal )
msgbox "Final value set:" & cstr(doc.EnquiryNumber(0))
'Change the function code like this (only function signature (first line) and Const line is changed):
Function getMaxNum( viewName As String ) As string
msgbox "Came into Function"
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" 'this is field name which you will be giving .. I think it should be EnquiryNumber
msgbox "Field Name:" & fieldName
msgbox "View Name passed to Function:" & viewname
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) ) 'this is not needed anymore.
dim tmpstr as string, tmpval as string
dim tmpvalue as long
if cstr(lastDoc.GetItemValue( fieldName )(0))<>"" then
msgbox "Came into If condition function"
msgbox "Actual Value from last doc:" & cstr(lastDoc.GetItemValue( fieldName )(0))
tmpval = StrRightBack(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'Gets values after the first 2 chars from Left.
msgbox "TMPVAL:" & tmpval
if tmpval<>"" then
tmpvalue=cint(tmpval)+1 'Increment the value ...
else
tmpvalue=1
end if
msgbox "TMPVALUE:" & cstr(tmpvalue)
tmpstr=Left(cstr(lastDoc.GetItemValue( fieldName )(0)),2) 'this gives the first 2 characters starting from Left.
msgbox "First TMPSTR:" & tmpstr
if tmpstr<>"" then
tmpstr=tmpstr & cstr(tmpvalue) 'Now concatenate both the values....
else
tmpstr=cstr(viewName) & cstr(tmpvalue)
end if
msgbox "Second TMPSTR:" & tmpstr
getMaxNum = tmpstr 'Return value
else
msgbox "Unable to retrieve value. Failed"
getMaxNum =""
end if
Msgbox "FINAL:" & getMaxNum
out:
Exit Function
ErrHandler:
Msgbox "Error in action - getMaxNum" & Chr$(10) & "Line: " & Cstr( Erl ) & Chr$(10) & "Err No: " & Err & ": " & Error$
Resume out
End Function
ASKER
It appears to be giving me the exact same output to the previous code, so I haven't posted any pics. I can't spot any difference between the new code and old either, perhaps you pasted an old copy?
I made tmpvalue as long. As integer cannot take that much numbers.
do below changes in the code:
dim tmpvalue as integer
change to:
dim tmpvalue as double
tmpvalue=cint(tmpval)+1 'Increment the value ...
change to :
tmpvalue=cdbl(tmpval)+1 'Increment the value ...
dim tmpvalue as integer
change to:
dim tmpvalue as double
tmpvalue=cint(tmpval)+1 'Increment the value ...
change to :
tmpvalue=cdbl(tmpval)+1 'Increment the value ...
ASKER
Thanks, looking good, almost. 12 is now fine, 42 is fine, 99 giving odd results.
MsgOutput3.doc
MsgOutput3.doc
ASKER
I've just looked again at the doc I just posted, Final Value Set: Doesn't alwasy seems to produce the goods. So my comment about 12 being spot on was not totally true. However it is setting the fieldvalue on the form.
use this:
msgbox "Final value set:" & uidoc.FieldGetText( "EnquiryNumber")
As for 99, I am not sure...it should be some problem with the last doc...try to create one more document with manual entry for 99...so that it has some other value otherthan 9999.
msgbox "Final value set:" & uidoc.FieldGetText( "EnquiryNumber")
As for 99, I am not sure...it should be some problem with the last doc...try to create one more document with manual entry for 99...so that it has some other value otherthan 9999.
ASKER
Hi,
Yes msgbox "Final value set:" & uidoc.FieldGetText( "EnquiryNumber") certainly does the trick for nailing the final value in the msgbox.
I've added new docs, deleted old docs, and generally experimented to try and find why this works with 2 departments and not others. Please see the attached excel sheet for some data, perhaps it will give you an idea of what is going on!
I've also tried using LN7 client today just incase there is a bug or issue with LN8.
Results-matrix.xls
Yes msgbox "Final value set:" & uidoc.FieldGetText( "EnquiryNumber") certainly does the trick for nailing the final value in the msgbox.
I've added new docs, deleted old docs, and generally experimented to try and find why this works with 2 departments and not others. Please see the attached excel sheet for some data, perhaps it will give you an idea of what is going on!
I've also tried using LN7 client today just incase there is a bug or issue with LN8.
Results-matrix.xls
could you pls upload the db here with few document for testing? Provide manager access to default.
ASKER
Please download from http://scribla.pwp.blueyonder.co.uk/Enquirybook.zip
I have created a couple of docs for dept 12 and 17, using the original Enquiry form as the views use this for selection. (Use 'Create New Enquiry' from the outline)
There are also the two modified versions of the original enquiry form, EnquiryMbonaci, and EnquiryMadheeswar (which contains you latest msgbox code)
When adding yourself to the ACL, please select the following roles [GlobalSuper] [UKSuper] [EuroSuper] else the outline will hide all content from you.
Cheers scribla.
I have created a couple of docs for dept 12 and 17, using the original Enquiry form as the views use this for selection. (Use 'Create New Enquiry' from the outline)
There are also the two modified versions of the original enquiry form, EnquiryMbonaci, and EnquiryMadheeswar (which contains you latest msgbox code)
When adding yourself to the ACL, please select the following roles [GlobalSuper] [UKSuper] [EuroSuper] else the outline will hide all content from you.
Cheers scribla.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appears to be working great! Would you mind telling what the problem was that you over came? I've learned a lot about some thing I know very little about here, so I'd be interested to know what it was.
ASKER
A well deserved 500 points, many thanks!
This is the change which it worked:
Dim tmplen As Integer, tmplen1 As Integer
tmplen=Len(Cstr(lastDoc.Ge tItemValue ( fieldName )(0)))
tmplen1=tmplen-2 ' 2 is the first 2 characters of the Enquirt number.
I need to remove StrRightBack as it is was looking for a string...not the no.of characters.
If you EnquiryNo has "121223", my prv code is supposed to take 1223. But in 1223, there is a 2, so it took 3 and gave an increment value of 4. If it didnot found, it is starting from 1.
using StrRightBack was the problem....it takes the string which starts from right to left. In the above case when I used StrRightBack(EnquiryNo,2), it took the 3.
So, it was my fault and I aplogize. There is no equivalent for @RightBack in LS. So I need to take the length and minus that many characters( in our case 2) and get the output.
It has been long time since I used these.... Blame it on my poor memory.
Dim tmplen As Integer, tmplen1 As Integer
tmplen=Len(Cstr(lastDoc.Ge
tmplen1=tmplen-2 ' 2 is the first 2 characters of the Enquirt number.
I need to remove StrRightBack as it is was looking for a string...not the no.of characters.
If you EnquiryNo has "121223", my prv code is supposed to take 1223. But in 1223, there is a 2, so it took 3 and gave an increment value of 4. If it didnot found, it is starting from 1.
using StrRightBack was the problem....it takes the string which starts from right to left. In the above case when I used StrRightBack(EnquiryNo,2),
So, it was my fault and I aplogize. There is no equivalent for @RightBack in LS. So I need to take the length and minus that many characters( in our case 2) and get the output.
It has been long time since I used these.... Blame it on my poor memory.
ASKER
No problem at all madheeswar, just glad we got it solved in the end, and I've learned alot in the process.
I would have had no chance of figuring it out myself thats for sure.
I would have had no chance of figuring it out myself thats for sure.
ASKER