CIB merge technical documentation (EN): Use case example Serial Letter

CIB merge technical documentation (EN)

10. Use case examples

10.2. Use case example Serial Letter

General
Simple merge call from command line
Serial letter with 3 datasets as multi-RTF
Serial letter with dynamic table and multi-CSV
Cancelling the merge process via progess bar
Serial letter with XML data source and alias definitions in the RTF
Serial Letter with XML data source, alias names in XML files, usage of prefixes
Calling an XML job from command line
Calling an XML job from Java
Define NEXT behavior in case of missing variables

General

If the payment reminder from the previous example is to be sent not only to one customer, but to several, this can be done without changing the cover letter. Only the CSV file must be supplemented with the additional data of the other customers. The automatic mail merge functionality is easiest to understand if there is a mental loop around the text which forwards the control file (implicit loop). The following assumes that you are in the Mail Merge subdirectory.

The CSV file Adressen.csv contains 4 datasets:

Vorname;Name;Strasse;PLZ;Ort;Geschlecht;KundenNr;AuftragsNr;Betrag
Max;Müller;Teststr. 3;99999;Testort;M;0987392;232222-4;123118
Franz;Meier;Teststr. 4;12345;Musterdorf;M;9898989;111111-2;999
Maria;Huber;Teststr. 5;54321;Testheim;W;1234567;876564-1;500
Franziska;Bauer;Teststr. 6;77777;Neumusterdorf;W;6665554;981234-0;3333
Franziska;Bauer;Teststr. 6;77777;Neumusterdorf;W;6665554;981234-0;3333

Simple merge call from command line

The following simple command line call creates the desired serial letter (Serienbrief.bat):

cibmrg32.exe ..\Lueckentext\Anschreiben_in.rtf !Serienbrief_out.rtf
Adressen.csv

The cover letter from the gap text example serves as input, the output file is called Serienbrief_out.rtf and the extended CSV file with 4 data sets is used as usage. CIB merge automatically generates a mail merge in this merge process, which contains a payment reminder (separated by section breaks) for each data set from the CSV file. Changes in parameters or RTF are not necessary.

Serial letter with 3 datasets as multi-RTF

Now a mail merge letter is to be created, which does not contain all data sets, but only the first three. Furthermore the result should be a multi-RTF and not, as before, an RTF separated by section breaks. Furthermore, the information shall be provided, how many documents were created in this merge process.

For the sake of clarity, the merge process will again be controlled by a parameter file, which contains the input, output, and data file as well as the necessary parameters to achieve the desired result (Serialletter.bat):

cibmrg32.exe --parameterfile=Serialletter_par.par

Content of the parameter file Serialletter_par.par

Errors shall be logged

--logfile=!Serialletter_log.log

Involved files for the merge process

--inputfile=..\Lueckentext\Anschreiben_in.rtf
--outputfile=!Serialletter_out.rtf
--datafile=Adressen.csv

Creation of a multi-RTF

--serialletter=multi

Output of the amount of created documents

--statistics=!count.txt
-mDer Mischvorgang verwendet folgende Ein- und Ausgabedateien:
-m@
--merge=3

Explanations:

The --merge=3 parameter starts the merge process and uses all previously set parameters. The number 3 indicates that a mail merge is to be created using the first three data sets from the data source.

With --serialletter=multi the mail merge will be created as multi-RTF and not, as default, a document that contains the different data sets by section break.

Using the --statistics parameter, another file is created, which contains the number of generated documents. In this case the file count.txt has the content:

GeneratedDocumentCount=3

Serial letter with dynamic table and multi-CSV

The payment reminder is now to be extended by further information. For each customer, an overview of the open line items should be listed on a separate page.

The line items must be made available in a suitable form. The easiest way to do this is to use an additional CSV file Einzelposten.csv. In order to determine which line items belong to which customer, this CSV file contains for each line item the corresponding order number of the customer. The lines are sorted by these order numbers (both in the file Adressen.csv, as well as in the file Einzelposten.csv).

Now you must specify that several CSV files are to be loaded for this merge process. This is done by means of a multi-CSV file (multi.csv). It contains the names of all CSV files that are to be loaded in the current merge process. Using the fields in the header of the multi-CSV file, each CSV file is assigned an alias that can be used to access these CSV files in the document.

Extract from Adressen.csv:

Vorname;Name;Strasse;PLZ;Ort;Geschlecht;KundenNr;AuftragsNr;Betrag
Franz;Meier;Teststr. 4;12345;Musterdorf;M;9898989;111111-2;999
Max;Müller;Teststr. 3;99999;Testort;M;0987392;232222-4;123118
…

Extract from Einzelposten.csv:

KundeAuftragsNr;Bezeichnung;EinzelBetrag
111111-2;Artikel1;999
232222-4;Artikel1;18
232222-4;Artikel2;123100
…

multi.csv:

Kunden;Einzelposten
Adressen.csv;Einzelposten.csv

A dynamic table listing the individual items must be inserted in the cover letter.

A section break is inserted after the text of the cover letter. The new page contains a table listing all the associated line items. This is done via a loop.

Extract Einzelposten_in.rtf

…
The order {REF OrderNo } consists of the following single items:

Description	Amount

{ IF { = AND({ MERGEREC ?SingleItems};{ COMPARE { REF OrderNo } = { REF CustomerOrderNo } })} = 1

“{ REF Description }	{REF SingleAmount}

{ NEXT SingleItems }“ \* WHILE }
…

As long as a line item exists (check with { MERGEREC ?line item}) and the customer order no. corresponds to the customer order no. (COMPARE { REF order no. } = { REF CustomerOrderNo. }), the name and the amount of the line item is displayed in a table and in the line item.csv it is switched to the next data set ({ NEXT line item }). If the data for the comparison were purely numerical, the COMPARE could be omitted.

The implicit loop in the automatic mail merge functionality switches the control file on. If, as in this case, several control files are involved in the merge process, all control files would be forwarded. However, since the file Einzelposten.csv is switched in the RTF itself using the NEXT command, this is not desired here. To avoid this, an explicit loop is set around the letter and the serial letter function is switched off.

Extract from Einzelposten_in.rtf

{ IF {MERGEREC ?Customers } = 1“

…Here follows the complete cover letter…

{ NEXT Customers }{ IF {MERGEREC ?Customers } = 1“…Here section break…“}“ \*WHILE }

The loop goes through all customers from the file Addresses.csv and inserts a section break after each letter, if there is another customer.

The following call creates the desired form letter with dynamic table (Einzelposten.bat):

cibmrg32.exe --parameterfile=Einzelposten_par.par

Errors shall be logged

--logfile=!Einzelposten_log.log

Involved files for the merge process

--inputfile=Einzelposten_in.rtf
--outputfile=!Einzelposten_out.rtf
--datafile=multi.csv

The data file is a multi-CSV

--multidatafile
-mThe mixing process uses the following input and output files:
-m@

Switch off serial letter functionality

--merge=1

Explanations:

The RTF file Line Einzelposten_in.rtf serves as input. As described, this file contains two loops with which the control files are forwarded.

The --multidatafile parameter indicates that the file specified with the --datafile parameter is a multi-control file.

As already described, the control files are forwarded in the RTF itself. Any forwarding resulting from the automatic mail merge functionality should be suppressed. The --merge=1 parameter executes the merge process as a single merge run. The mail merge is created only with the help of RTF field commands.

Cancelling the merge process via progess bar

The RTF file Endlosschleife_in.rtf contains an endless loop due to a forgotten NEXT field. If the text programmer makes such an error, the merge process can only be aborted via the task manager. To avoid this, a progress dialog is shown below, which can be used to abort the merge process.

The following call starts the endless merge process with progress display (Endlosschleife.bat):

cibmrg32.exe --parameterfile=Endlosschleife_par.par

Errors shall be logged

--logfile=!Endlosschleife_log.log

Involved files for the merge process

--inputfile=Endlosschleife_in.rtf
--outputfile=!Endlosschleife_out.rtf
--datafile=multi.csv

The data file is a multi-CSV

--multidatafile
-mDer Mischvorgang verwendet folgende Ein- und Ausgabedateien:
-m@

Show progress bar

--dialog=top

Switch off serial letter functionality

--merge=1

Explanations:

Input RTF is Endlosschleife_in.rtf. This produces an endless loop during the merge process.

The --dialog=top parameter sets a progress bar in the foreground, which also contains a button to cancel the merge process.

Serial letter with XML data source and alias definitions in the RTF

In the following, the data will not be transferred as CSV files, but as XML. The data should be in a single file Daten.xml. No multi-CSV file shall be used anymore.

The following extract from Daten.xml shows a possible conversion of data from CSV to XML:

<?xml version="1.0"
encoding="ISO-8859-1" ?>
<root>
      <data>
            <Kunden>
                  <Kunde>
                        <Vorname>Franz</Vorname>
                        <Name>Meier</Name>
                        <Strasse>Teststr. 4</Strasse>
                        <PLZ>12345</PLZ>
                        <Ort>Musterdorf</Ort>
                        <Geschlecht>M</Geschlecht>
                        <KundenNr>9898989</KundenNr>
                        <AuftragsNr>111111-2</AuftragsNr>
                        <Betrag>999</Betrag>
                  </Kunde>
                  weitere Kunden ......
            </Kunden>
            <Posten>
                  <Einzelposten>
                        <KundeAuftragsNr>111111-2</KundeAuftragsNr>
                   <Bezeichnung>Artikel1</Bezeichnung>
                     <EinzelBetrag>999</EinzelBetrag>
                  </Einzelposten>
                  weitere Einzelposten....
            </Posten>
      </data>
</root>

The alias definitions are defined directly in the RTF input file NextDef_in.rtf using the { NEXT ”DEF:Aliasname;<filepath>” }. With this special variant of the NEXT switch a new control file can be assigned to a current alias name in the current document. This command automatically loads the first data set of this file.

In this case the following alias definitions are in the RTF:

{ NEXT ”DEF:customers;XML:{ REF XmlFilename };/root/data/customers/customer“ }

{ NEXT ”DEF:single_item;XML:{ REF XmlFilename };/root/data/items/single_item“ }

In order not to wire the name of the XML-file in the RTF, a variable XmlFilename is used here instead. This variable must be set externally via parameters. After the filename follows a semicolon separated XPath information to the respective data. /root/data/customer/customer returns all <customer>-node from the file XmlFilename.

The following call creates the desired serial letter with XML data supply (NextDef.bat):

cibmrg32.exe --parameterfile=NextDef_par.par

Errors shall be logged

--logfile=!NextDef_log.log

Involved files for the merge process

--inputfile=NextDef_in.rtf
--outputfile=!NextDef_out.rtf

Set the filename for the XML data supply

--set=XmlDateiname=Daten.xml
-mDer Mischvorgang verwendet folgende Ein- und Ausgabedateien:
-m@

Switch off serial letter functionality

--merge=1

Explanations:

Input RTF is NextDef_in.rtf and contains alias definitions for the XML data.

The --set parameter sets the value Daten.xml to the file XmlFilename. This allows to set the filename variably from outside.

Due to the alias definitions in the raw text, a multi-control file is no longer required. The parameters --datafile and --multidatafile can therefore be omitted.

Since the series is also created by explicit loops in the raw text, the parameter --merge=1 disables the serial letter function.

Serial Letter with XML data source, alias names in XML files, usage of prefixes

Now the alias definitions should no longer be in the raw text, but together with the data in the XML file. Furthermore, the uniqueness of the variables shall be guaranteed by usage of prefixes in the raw text. These prefixes should be prefixed to the variable name with the separator “.”.

To realize this, the Daten.xml is extended as follows. (Daten_mitAlias.xml)

<multi>
 <Kunden>XML:$(this);/root/data/Kunden/Kunde</Kunden>
 <Einzelposten>XML:$(this);/root/data/Posten/Einzelposten</Einzelposten>
</multi>

The construct XML:$(this) specifies that an alias definition refers to data contained in the same XML file where the alias definition is located.

The input file must be extended so that all variable names are preceded by the respective alias followed by a dot.

So { REF first name } becomes { REF customer.first name } etc.

If, for example, the variable amount is used for both line items and customers, the prefix ensures uniqueness. As always, the parameters can be defined via a parameter file. An alternative would be to provide the parameters and data together in a so-called XML job. This job can also be executed via command line. Execution variants using Java and C++ are also explained in the following.

The following call generates the desired serial letter with XML data supply (Prefix.bat):

cibmrg32.exe --parameterfile=Prefix_par.par

Content of the file Prefix_par.par:

Errors shall be logged

--logfile=!Prefix_log.log

Involved files for the merge process

--inputfile=Prefix_in.rtf
--outputfile=!Prefix_out.rtf

Path to XML file

--headerfile=XML:Daten_mitAlias.xml

XPath to access the alias definitions

--datafile=/root/multi
--multidatafile

Delimiter between alias and variable names (default is dot)

--prefix-delimiter
-mDer Mischvorgang verwendet folgende Ein- und Ausgabedateien:
-m@

Switch off serial letter functionality

--merge=1

Explanations:

Input RTF is Prefix_in.rtf, which was extended by alias names for the variables as described.

The --headerfile parameter sets the data source to use (Daten_mitAlias.xml) and --datafile the XPath to the alias names.

By adding the --multidatafile parameter, CIB merge interprets the alias definitions independently and provides the corresponding alias names.

This can eliminate the {next}-DEF field statements in the raw text.

The --prefix-delimiter parameter defines the dot as separator between alias and variable name. The raw text must be extended so that all variables are preceded by the corresponding alias followed by this separator.

The result is written to the file Prefix_out.rtf.

Calling an XML job from command line

If the data is available as XML anyway, it is possible to set the parameters in an XML as well. This way the data and the parameters can be placed in a single XML file. All necessary properties are set in a so-called merge step. This job XML (PrefixJob.xml) can then be called from the command line as follows (PrefixJob.bat):

cibrsh.exe -d PrefixJob.xml

Extract from the file PrefixJob.xml (Comments begin with )

<step name="merge" command="merge">
<!-- List of properties for this step  -->
<properties>
      <!-- data transferred to the XML -->
      <property name="--logfile">!PrefixJob_log.log</property>
      <property name="--inputfile">Prefix_in.rtf</property>
      <property name="--outputfile">!Prefix_out.rtf</property>
      <property name="-h">XML:$(inline)</property>
      <property name="--datafile">/root/multi</property>
      <property name="--multidatafile"/>
      <property name="--prefix-delimiter"/>
      <property name="-@">1</property
</properties>
</step>

The data, which was previously provided via the file Daten_mitAlias.xml, can be passed directly with in the PrefixJob.xml. This is specified with XML:$(inline) at the parameter -h. Here the usage of the short form -h must be used explicitly, because it is expected in exactly the same way. The same applies to -@.

The data from Daten_mitAlias.xml can be transferred directly to PrefixJob.xml by inserting them directly below the <root> node.

Calling an XML job from Java

If an XML job is used, it can also be executed via Java. For this usage the class JCibJobJob is used. In the following small Java application the file PrefixJob.xml is also executed:

Extract from MergeJob.java:

…
JCibJobJob t_Job = new JCibJobJob();
t_Job.initialize();
if (!t_Job.isInitialized())
{
            // Error while initializing
            System.err.println("Error while initializing");
            System.exit(1);
}
try
{
 t_Job.setProperty(ICibJobJob.PROPERTY_INPUTFILENAME,
"PrefixJob.xml");
            //Execute job
            t_Job.execute();
            //Error handling
            int t_Error = ((Integer)t_Job.getProperty(IComodJob.PROPERTY_ERROR)).intValue();
            if (t_Error != 0)
            {
            // Error while executing the job
            String t_Errortext =
(String)t_Job.getProperty(IComodJob.PROPERTY_ERRORTEXT);
            System.err.println("Error while executing": "+t_Error+" "+t_Errortext);
            }
}
finally
{
            t_Job.terminate();
}
…

Define NEXT behavior in case of missing variables

In the following, an XML node is missing in a data set. The Daten.xml file is adjusted so that the node <CustomerNo> is missing in the third customer data set (Daten_NextMode.xml). In the result would simply be nothing at the position where this variable is inserted. In the following a variable, which is contained in a data set, but not in the next one, is to be deleted. This should lead to an error and the mixing process should be aborted.

The following call generates the desired behavior (NextMode.bat):

cibmrg32.exe --parameterfile=NextMode_par.par

Content of the file NextMode_par.par:

Errors should be logged

--logfile=!NextMode_log.log

Involved files for the merge process

--inputfile=NextDef_in.rtf
--outputfile=!NextMode_out.rtf

Set the file name for the XML data supply

--set=XmlDateiname=Daten_NextMode.xml
-mDer Mischvorgang verwendet folgende Ein- und Ausgabedateien:
-m@

Set behavior for NEXT if variable does not exist

--next-mode=delete

Switch off serial letter functionality

--merge=1

Explanations:

The --next-mode parameter with the assignment "delete" causes the missing variable Customer No. to be deleted and thus no longer defined. This results in an error.

unknown variable:

name: customer no.

and the mixing process is aborted.