| |
|
|
Combine data from header and response documents into a Word export
Many Lotus Notes applications manage information which contains data in both header (parent) documents and child/response documents. Examples could be a purchase order application where the header details of the purchase order indicating the client name and address, date, reference etc. are maintained in a parent document and the details of each line item in the purchase order is contained within a series of Notes response documents.
This FAQ explains what is required to allow Integra for Notes to pick up the data from the parent document as well as from the child response documents and export all these documents to one MS-Word document for better printing and distribution of the purchase order details. A scenario of a purchase order example is used within this FAQ. The term PO in the explanation below is short for Purchase Order.
A Notes database contains purchase order header documents and order line item documents containing the individual items being ordered which are saved as responses to the header document. The goal is to create a profile that creates an MS- Word purchase order document containing in its header, information about the customer, the PO number, the date, etc (this information comes from the PO header document in Notes) and a table containing all the line items of the PO with columns like quantity, item description, unit price, line total (this information comes from the line item response documents in Notes).
Whenever creating a response document, Lotus Notes automatically creates a field within the response document called $Ref which contains the UniqueDocId of the parent document that the response document relates to.
To support this approach of using parent and child documents, the purchase order database needs to contain a hidden view in the Notes database showing all response documents sorted by the UniqueDocId of the parents document (i.e. $Ref field). For the example below we assume the availability of this view and that it is called ResponsesDocsByRef.
On creating an Integra profile, the user is to point to the PO database and select the form used for the PO header. The fields coming from the PO header form in Notes are selected from the header form fields and added to the Integra Selected Field List as with any Integra profile.
If computed fields are also required, these are also created as any other computed field is created and added to the Selected Field List from where the formula for the computed field can be set.
The fields coming from the PO line item documents (i.e. the response documents) which are required in the Word document are also to be added as fields of type formula and added to the Selected Field List in the Integra profile.
The value of each of these fields is to be defined in such a way that the contents is obtained through a formula. As an example, if the response documents each contain a field called Qty then a computed field is added to the profile called Qty and the formula to evaluate its value is to be set through a normal Notes formula. This will obtain multi-value data where the number of data elements is equal to the number of line item response documents for the selected purchase order header.
The example below is based on picking up a field called Qty from each of the response documents related to a chosen purchase order header document. The Qty field obtained by Integra will contain a multi-value Notes field. Equally, other multi-value fields would be obtained containing a number of descriptions, unit cost etc.
For the example below we assume the availability of a view called ResponsesDocsByRef which lists all the response documents sorted by the $Ref field.
REM "We need to obtain the name of the current database name and server.";
REM "This technique is used because if we use the standard @Subset(@Dbname;1)" ;
REM "approach, we will obtain the name of the Integra database not the PO database" ;
srv := @getenvironment("IntegraCurrentSrv");
db := @getenvironment("IntegraCurrentDB");
REM "Lookup the Quantity field from the view showing the all response docs by the parents unique id";
res := @dblookup("":"";srv:db;"ResponsesDocsByRef";@Text($Ref);"Quantity");
@if(@iserror(res);@Return("");res);
REM "In case of numeric fields we need to convert to text";
qty := @Text(res;"F,2");
REM "As a last step we need to implode the multi-value return into a single value string using a carriage return as separator
@Implode(qty;@NewLine)
For some columns, for instance the one showing the description, the text length per value in the mutli-value result might be too long to be displayed in a single row inside the Word table. In this case you need to limit the length of the text to a length appropiate for the column width. Replace the last line
@Implode(qty;@NewLine)
as follows
@Implode(@Left(qty;20);@NewLine)
The value 20 is an example and has to be adjusted according to the width of the Word table column
Please note that the Notes Implode function running on the multi-value field to obtain a list of qty or descriptions each separated by a carriage return will result in a column of data presented to MS-Word with each description or qty being shown on a different line.
If the description field and the quantity field (and of course all the other required fields from the response documents) are each contained within a row of a table in MS-Word, then the MS-Word table will expand to accommodate the appropriate number of lines of text.
The sample Word document shows an example of the field positioning in the Integra PO Sample. Note that the fields called Item, Description, Qty, UnitCost, Value are all fields taken from the response documents and will obtain Notes multi-value data which will be expanded to one entry per line which is then shown in the table.
The fields Total, Tax and Grandtotal would either be on the header document or are calculated fields on the profile. For instance, the calculation of the tax could be done as a computed field in the Integra profile or might be stored in the Notes PO header document.
Related FAQ's:
|
. |
|
FAQ# 0011
|
|
|
| |