Integra for Notes Banner
  QuickReports Integra4Notes Integra Family of Products



How to export data from response and parent documents

Data in Notes documents is frequently held in response documents but some additional data related to the response document may also be required to be exported from the response document's parent document. This FAQ explains how this is achieved using Integra for Notes.

Firstly, create a profile linked to the Notes form which is the response documents' form. Set up the profile as if only the fields from the response documents are being exported.

On the profile, create a computed field (type formula) which looks up the fields from the parent document of the given response document. This is best done using the @GetDocField function.

The @GetDocField function returns the contents of a specific field on a document the Unique ID of which has been provided. In this case it is the parent's Document Unique ID, which is contained in the $Ref field on the response document.

As a result, for instance to obtain the contents of the field called CompanyName stored on the parent of a response document, the computed field on the Integra profile would need to be set to the following formula:

@GetDocField($Ref; "CompanyName")

Because the @GetDocField function was only introduced in a later release of the Notes R5 client, this function may not work properly in all versions of Notes R5. If this function does not work, then create a view of Document Unique ID's in the database and use the @DBLookup function to look-up the value stored in the $Ref field within that view and then return the value of the required field from that parent document.

OK, but what if I want the user to select the main document and then export the responses?

This is the scenario. I have a Notes database with purchase orders. Each purchase order has a heading document with e.g. details of the supplier and n numbers of line items (stored as Notes response documents). The user selects the purchase order header document (or opens it on screen) and runs Integra to create a well formatted purchase order in Excel.

The requirement here is that though the user selects the parent document, Integra should export the response documents. The Callback Script (advanced tab in the Integra profile) code sample below shows how to "tricks" Integra at runtime to work on a different document collection then the user actually selected. Of course you still can refer to fields of the parent document (e.g. the suppliers address) using the above described method.

A few points to be noted to make this example work:
  • Though the user selects a single main document (purchase order header), the Excel export profile needs to be set to Export Type = "Report". Mainly because otherwise Integra will not create a correct Excel template format when using the Edit Template button first time.
  • Please refer to the profile creator manual to learn about the difference between Single Cell tags/fields and Column tags/fields. In this example the fields contained within the purchase order header document are likely to be exported to Excel single cells, whereas the fields of the purchase order line items are to be exported to row/columns in the resulting Excel spreadsheet.
  • As long we do not use the sorting and grouping feature, Integra is exporting document collections using an intermediete array using its own sorting algorythm. As we replace the document collection, but not the internal array, it is required that we use the sorting and grouping feature in the Integra profile.

Click here if you want to export the response documents of multiple parent documents.

' THIS CODE SAMPLE WORKS ONLY WITH INTEGRA FOR NOTES 4.1 BUILD 32 OR LATER !
Const CB_INITIALISE = 3
Const CB_BFOREXPORT = 10
Const CB_BFORREADNOTES = 6
Const CB_BFORWRITECOM = 1
Const CB_AFTERWRITECOM = 9
Const CB_BFORCOMACTION = 5
Const CB_BFORNOTESACTION = 2
Const CB_TERMINATE = 4

select case stats.cbstatus
case CB_INITIALISE
case CB_BFOREXPORT
' to make sure Integra captures the currently opened UI document
' in case the user calls Integra from an opened document rather then from a view
' we need to change the export type to Single Document
export.record.structure.TempType = "Single Document"

case CB_BFORREADNOTES
' Use the list of response documents of the selected document
' for export rather then the selected document itself

if not stats.fstcall then
' if not first exported document then continue as normal
if stats.expdoc.hasitem("$Conflict") Then
' don't export conflict documents
export.continue = false
elseif not stats.expdoc.form(0) = "POLineItem" then
' any condition required if not all response documents are to be exported
' you can remove the elseif construct if you are certain that there are no
' other type of response documents then the ones you want to export
export.continue = false 
end if

elseif not stats.expdoc.form(0) = "POHeader" then
' any condition required to ensure the user selected the purchase order header
' (main) document and if not we abort the export
export.continue = false

elseif stats.doccol.count > 0 then
'  we need switch the export type back to Report as we most likely 
' have to export more than one document
export.record.structure.TempType = "Report"

' check if main documented has responses and if so  
if stats.expdoc.responses.count = 0 then
export.continue = false
else
' change exported document collection to main document's responses
set stats.doccol = stats.expdoc.responses

' and re-assign first document to be exported
set stats.expdoc = stats.doccol.getfirstdocument()

' as we already passed Integra's document validation, we need to do it again
do while not stats.ExpDoc is nothing
if stats.ExpDoc.IsValid and not stats.ExpDoc.IsDeleted then
exit do
end if

set stats.expdoc = stats.doccol.getnextdocument(stats.ExpDoc)
loop

stats.keynum = stats.doccol.count
end if

else
' no main document selected; abort
export.continue = false
end if 

case CB_BFORWRITECOM
case CB_AFTERWRITECOM
case CB_BFORCOMACTION
case CB_BFORNOTESACTION
case CB_TERMINATE
end select

.




FAQ# 0059
Bookmark and Share

 
QUICK LINKS
 
 

 

  © Shireburn Software Ltd. 2008 - 2024