I have one form with part, location and original inventory balance. I have another form with transactions (date, inventory in, inventory out). I have to somewhere, somehow show the total remaining inventory after each transaction (ib + ii - io). I have an embedded view in the transaction form. I can’t make it happen! I don’t know java and have little script knowledge. I tried searching but the idea’s don’t work. Could anyone help a beginner?Thanks!!
Subject: Help with view, totals, balances please
Let me make sure I have this right… you have a document with the original balance and then you have an embedded view in another document that has the inventory transactions on that same part? - and you want to then show what the current balance is, correct?
Subject: RE: Help with view, totals, balances please
That’s correct! I have to show them what is left in inventory after each transaction by taking the original inventory balance and adding in the (added to inventory) and subtracting the (removed from inventory). Thank you for replying!
Subject: RE: Help with view, totals, balances please
Add a computed field with a formula to lookup all the inventory added:
list:=@dblookup(“”:“NoCache”;“”;“viewname”;“partnum”;colnum)
where colnum is the column number of inventory added. Add another computed field to get the list of inventory numbers going out.
If the inventory move numbers are in the same column of your transaction view, then you’ll only need one computed field. But, make sure the inventory going out numbers are negative numbers.
Then, you need to add up the values, so continue the formula by summing the list result:
@sum(list)
This number can then be added to your original inventory balance to get your current balance. You’ll probably need another lookup field to get the original balance.
(if you have two columns, and therefore two computed fields, then you just need to add the increase in inventory and subtract the decrease value)
I hope this makes sense…
Subject: RE: Help with view, totals, balances please
I had this in the form:
FIELD add := add;
r:=@Dblookup(“”:“”; “”; “Adjustments”;3 );
@SetField (“add”;@Sum(r));
And I had the same for subtract, which worked good, but I don’t know how to get the Original Inventory field and then subtract the 2 fields to get a balance. Is it close?
Subject: RE: Help with view, totals, balances please
Yes, close…
your dblookup formula doesn’t look right, the syntax is:
@DbLookup( class : cache ; server : database ; view ; key ; columnNumber)
It looks like your missing a parameter - it’s the part number that will be used as the key in the lookup. (btw the part number must be the first column in the view and it must be sorted)
As for the original inventory, those documents must be in a view as well, with the inventory balance in one of the columns. Then, just create another dblookup field to get the original balance similar to how we got the transaction balances.
Subject: RE: Help with view, totals, balances please
Well I was doing well until I deleted my test data and tried to start again. Now I get "field add does not exist), so I assume I need to do a @Ifisnew command, and I tried, but the data still comes up blank! Now I’m not getting any results at all from the dblookup in any column. When it did work, the only issue I had was the data would not refresh until I exited & re-entered the database.
Subject: RE: Help with view, totals, balances please
Ok, this is where I am now. I can get the calculation to work but I can’t get it to display on my main form. If an inventory movement has not been made, and therefore an entry in my dblookup table does not exist, I get a field not found in index or view not indexed message. I guess you can’t display a dblookup value in a view either. I have a button that will invoke the calculation and display it using another form, but I don’t like that. Also, the balance of movements will not update unless I close & re-open the database.Help!
Subject: Lets backup a bit…
You have one form with the original inventory balance, and this form is in a view. You have other forms with transaction entries, and these are in another view. Now, you have a “main” form where you wish to display the original inventory, movements and then finally the current balance. Is this right?
You shouldn’t have to close and reopen the db to see a result. Also, you shouldn’t have to use dblookup in a view.
To get around the error of ‘entry not found in index’, use @if(@iserror(@dblookup(…));“”;@dblookup(…)) for the lookup fields.
Subject: Thank you for your help
Finally got a second to thank you for all your help. It’s working ok, needs a little bit more tweaking, but I have learned a ton thru this ordeal. Thank you for your patience and clarity in explaining!
Subject: Deliverable and Shipments Solution
I realize this is an old post but it came up with I did a search for a project I am working on.
Robin, I wrote something similar that addresses your needs if you are in the mood to do some easy LotusScript coding.
We have Contracts with Clients, every Contract has Deliverables and a due date for the Deliverable. It may take 5 Shipments to satisfy a Deliverable so we want to reflect that. We also want to show if the Shipment was on time for on-time performance tracking.
I created a Deliverable form which has Due Date, Revised Due Date. Then a response Shipment doc that inherits the Actual Due Due (if Revised is not blank…) If the Qty Due on the Deliverable is 10 and the shipment was for 5, the Deliverable reflects that there are still 5 outstanding.
I show this in an embedded view on the Contract (main doc).
Everytime a Shipment doc is saved (QueryClose), it goes out and finds all Shipments for the Deliverable, adds up the amounts, wipes out the balance on the Deliverable and rewrites it. This keeps the Deliverable always correct. If there is any doubt, all the user needs to do is open any Shipment and save it - boom, it recalculates automatically.
Sub Queryclose(Source As Notesuidocument, Continue As Variant)
'************************************************************************
'Get the Parent Deliverable, find all the Shipment Responses.
'Loop through the Shipments, accumulate the totals.
'Remove the previous values from the Parent, update with the new calculations
'If the Deliverable is now satisfied (outstanding balance <= 0), then status the Deliverable as Complete
'************************************************************************
Dim docParent As NotesDocument
Dim strKey As String
Dim rDocColl As NotesDocumentCollection 'Response document(s)
Dim rdoc As NotesDocument 'Response document(s)
Dim rView As NotesView 'View containing all of the Response documents sorted by $REF ascending
Dim intCount As Integer
Dim intTemp As Integer
Dim intShipQty As Integer
Dim intDeliverableQty As Integer
Dim strMessage As String
Dim strHistory As String
'************************************************************************
'Only run this update to the Parent if the document is in Edit Mode
'Only Admin can be in edit mode
'************************************************************************
If Not Source.EditMode = True Then
Goto EndProcess
End If
Call InitializeVariables
intCount = 0
intTemp = 0
intShipQty = 0
intDeliverableQty = 0
'Get the $Ref of the current Shipment
strKey = Source.FieldGetText("ParentDocID")
Set rView = db.GetView("($REFIDSHIPMENTS)")
'Set up the document collection of Responses, loop through and perform calculations
Set rdocColl = rview.GetAllDocumentsByKey(strKey) 'Find all by the Key
Set rDoc = rdocColl.GetFirstDocument
While Not rDoc Is Nothing
intCount = intCount + 1 'Update the counter of how many shipments have been made
intTemp = rdoc.DeliverableQty_NO(0)
intShipQty = intShipQty+ intTemp
Set rDoc = rDocColl.GetNextDocument(rDoc) 'Get the next response doc
Wend
'Get the Parent (Deliverable) document
Set docParent = db.GetDocumentByUNID(strKey)
If docParent Is Nothing Then
strMessage = "The Deliverable that needs to be updated was not found. Sorry, but you cannot continue."
Messagebox strMessage , (0 + 16 + 0 + 0),"Processing Halted"
Goto EndProcess
End If
'Done looping throught the responses
strCount = Cstr(intCount)
'Parent doc values
intDeliverableQty = docParent.DeliverableQty_NO(0) 'Deliverable commitment to customer
Call docParent.RemoveItem("DeliverableTotalShipments_NO") 'How many shipments
Set item = docParent.ReplaceItemValue( "DeliverableTotalShipments_NO", intCount )
Call docParent.RemoveItem("DeliverableQtyShp_NO") 'Quantity Shipped (accum)
Set item = docParent.ReplaceItemValue( "DeliverableQtyShp_NO", intShipQty )
Call docParent.RemoveItem("DeliverableQtyOut_NO") 'Oustanding Balance
intOutstandingBalance = (intDeliverableQty - intShipQty)
Set item = docParent.ReplaceItemValue( "DeliverableQtyOut_NO", intOutstandingBalance )
If intOutstandingBalance <= 0 Then
docParent.Status_TX = "Complete"
Else
docParent.Status_TX = "Pending"
End If
If Source.IsNewDoc Then
strHistory = "Shipment quantity " + doc.DeliverableQty_NO(0) + " added"
Else
strHistory = "Shipment quantity changed"
End If
'*********************************************************************************************
'Specify the action taken on the Primary Key for the doc cycle
'Update cycle history (from DBObjects script lib)
'*********************************************************************************************
GstrFormat = "Ddd mm/dd/yyyy hh:mm:ss AM/PM"
Call SetCycleValues( docParent, strHistory )
'Save the Parent
Call docParent.Save(True, True)
strMessage = "The Deliverable doc was updated and saved." & Chr(10) & "The Deliverable status is now " & docParent.Status_TX(0)
Messagebox strMessage, (0 + 64 + 0 + 0),"Processing Complete"
Goto EndProcess
EndProcess:
'Allow the closing of the Shipment document to Proceed
Continue = True
End Sub
Subject: Thanks, but could not make it work - here is what i ended with
Have a db with a main document with a total and response documents that contain transactions. Need to read all responses and post the total to the main document. I could not make the $REF view method work, so i used .Responses property of the main document instead - i think it is simpler also.
Just in case someone has similar difficulty - this works nicely for me:
Sub Queryclose(Source As Notesuidocument, Continue As Variant)
'************************************************************************
'Get the Parent doc ID , find all the Responses. Loop through and accumulate the total.
'Remove the previous values from the Parent, update with the new calculated total.
'************************************************************************
Dim s As New NotesSession
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim db As NotesDatabase
Dim docParent As NotesDocument
Dim strKey As String
Dim rDocColl As NotesDocumentCollection
Dim rdoc As NotesDocument
Dim curTotalAmt As Currency
Dim strMessage As String
'************************************************************************
'Only run this update to the Parent if the document is in Edit Mode
'************************************************************************
If Not Source.EditMode = True Then
Goto EndProcess
End If
Set db = s.CurrentDatabase
curTotalAmt = 0
'Get the ParentDocumentUNID of the current document (response document)
Set uidoc = workspace.CurrentDocument
Set rdoc = uidoc.Document
strKey = rdoc.ParentDocumentUNID
'Get the Parent document
Set docParent = db.GetDocumentByUNID(strKey)
If docParent Is Nothing Then
strMessage = "The Project document that needs to be updated was not found. Sorry, but you cannot continue."
Messagebox strMessage , (0 + 16 + 0 + 0),"Processing Halted"
Goto EndProcess
End If
Set rdocColl = docParent.Responses
Set rDoc = rdocColl.GetFirstDocument
While Not rDoc Is Nothing
curTotalAmt = curTotalAmt+ rdoc.CPDollars(0)
Set rDoc = rDocColl.GetNextDocument(rDoc) 'Get the next response doc
Wend
'Done looping throught the responses
Call docParent.RemoveItem("CPTotal")
Set item = docParent.ReplaceItemValue( "CPTotal", curTotalAmt )
'Save the Parent
Call docParent.Save(True, True)
Goto EndProcess
EndProcess:
'Allow the closing of the document to Proceed
Continue = True
End Sub
Subject: Oops - posted to quick, need one change
It just bombs if i use data type Currency - need to comment two lines out:
'Dim curTotalAmt As Currency
'curTotalAmt = 0
and let it decide it should be a variant - or dim it that way if you like. Don’t know why it does not like Currency - seems like it should, but is only useful for creating .nsd files!