Lotus Notes 7 - Auto-calculation for one column from an editable view - InViewEdit event

Question

I have a problem that gives me some headaches lately. I hope I can find a solution with your help.

I have a view : "vwTest" which is embedded on a form. It is an editable view. The view has 3 columns: Number , Cost , Difference. All the 3 columns have as their default values some field names which exist on a form called "fmTest", the field names are: Number , Cost , Difference.

On the main form ( which contains the view ) there is a field ( computed ) called: TotalValue.

The view has 2 actions: AddLine and DeleteLine.

What I want to do is:

Let say TotalValue = 5000

user complete the first line of the view:

Number | Cost | Difference

1 | 50 | 4950 => The 3rd column value to be calculated automatically as the difference between 5000 ( TotalValue ) and 50 ( the value of the 2nd column )

user complete the second line of the view:

2 | 60 | 4890 => the 3rd column value to be calculated automatically as the difference between the last 3rd column value from the view and 60 ( the current value of the 2nd column )

I think that's like a recursive algorithm.

The value of TotalValue exists, it is a Number type field.

Hope to find a solution and resolve this problem! I really appreciate your help and time!

What did you try so far? Please show the code you developed already. — Knut Herrmann
I've tried smth like this: 'Call uidoc.FieldSetText ( "Difference", uidoc.FieldGetText( "TotalValue" ) - uidoc.FieldGetText( "Cost" )) — Florin M.
Do you really need the lines in separate documents? If not, there are easier ways to get this to work. — Knut Herrmann
You mean if the 3 fields are really need to be from another form? — Florin M.
Yes. Otherwise you could put the list in one field of your main form. — Knut Herrmann

Torsten Link Torsten Link · Accepted Answer · 2013-07-23T10:49:59

After every save you have to cylce through all entries belonging to "this" main document and recalculate the totals. I assume, that the "lines" are response- documents to the main document and that the Embedded view is categorized by the unid of the main document...

Dim ses as New NotesSession
Dim db as NotesDatabase
Dim viewEmbedded as NotesView
Dim viwNav as NotesViewNavigator
Dim ve as NotesViewEntry
Dim docLine as NotesDocument
Dim docMain as NotesDocument

Dim dblTotal as Double

Set db = ses.CurrentDatabase
Set docMain = ... 'somehow get the main document, therefor I would need your current code
dblTotal = docMain.TotalValue(0)
Set viewEmbedded = db.Getview( "vwTest" )
viewEmbedded.AutoUpdate = False
Set viwNav = viwEmbedded.CreateViewNavFromCategory( docMain.UniversalID )
Set ve = viwNav.getFirst()
While Not ve is Nothing
  Set docLine = ve.Document
  dblTotal = dblTotal - docLine.Cost(0)
  If dblTotal <> docLine.Difference(0) then
    docLine.Difference = dblTotal
    Call docLine.Save( true, true )
  End If
  Set ve = viwNav.getnextDocument(ve)
Wend

Why the loop? What if someone modifies the first line after a second one and a third one have been created? then the total for 2, 3 and all subsequent lines has to change.

This code was not typed in Designer and might contain typos. It does NOT contain any error- handling and can produce Replication / Save- Conflicts if not used carefully..

Hope that helps

Lotus Notes 7 - Auto-calculation for one column from an editable view - InViewEdit event

1 Answers