HCL Notes Trick: Retrieve Details Rows From Embedded View

 

HCL Notes doesn’t provide a direct way of working with a master-details scenario, may be because it’s not a relational database system where Primary-Foreign-Key relationship entails the designer platform to provide some efficient mechanism to entertain this particular scenario. So we need to devise some alternate methodology to achieve it.

So in order to populate some fields in a parent document from a selected detail document from the embedded view, we need to write this code in Queryopendocument() event of the view. Note that we use the CaretNoteID property of the selected document from the embedded view to get the actual document (docDtl). CaretNoteID is the note ID of the document currently highlighted (that is, at the caret location) in a view.

To get the parent document (docParent) handle from within the embedded view we access ws.CurrentDocument.Document object (where ws is the Workspace instance), and then we’re able to populate the fields in the parent doc.

Let’s assume a well know scenario of Purchase Order request in a business. To cover it, we typically require two tables – one is a master table and the other one is the details table. In our example we’re considering the details table which holds the details of the products being purchased mentioned in the Purchase order master table. Following is the little but pretty useful code which retrieves the Purchase Order detail row which has been double-clicked by the user from the embedded view.

 

Sub Queryopendocument(Source As Notesuiview, Continue As Variant)
	Dim ws As New NotesUIWorkspace
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim docParent As NotesDocument      ''Parent document
	Dim docDtl As NotesDocument         ''Detail document selected by the user.

	Set db = session.CurrentDatabase
	Set docDtl = db.GetDocumentByID(Source.CaretNoteID)
	Set docParent= ws.CurrentDocument.Document

	Call docParent.ReplaceItemValue("ProductId", docDtl.ProductId(0))
	Call docParent.ReplaceItemValue("Quantity", docDtl.Quantity(0))
	Call docParent.ReplaceItemValue("UnitPrice", docDtl.UnitPrice(0))
	Call docParent.ReplaceItemValue("TaxRate", docDtl.TaxRate(0))
	Call docParent.ReplaceItemValue("Discount", docDtl.Discount)
	Call docParent.ReplaceItemValue("DeliveryDate", docDtl.DliveryDate)

	Continue = False
End Sub