Multiple Transactions per letter

Kevin Lippiatt · May 27, 2004

I'm developing a professional accountancy system with a debt management facility and I want to set up a mail merge where each customer(account) gets one letter, each with mulitple transactions (any number of) and each transaction can have multiple instalments (any number of or none at all)(see example below) Surely this must be a common request for mail merging
I don't see how I can create a mail merge or a data source document i.e. .csv file that has varying numbers of fields for each customer.
I have tried to generate .csv files where all the transaction and instalment data is in one mail merge field and is seperated by newline characters, but this approach doesn't yield very professional looking results
Currently my system also has the option to generate .xml files that have the appropriate structure (see below) but Word mail merge does not support .xml input - even in Word 200
<LETTER><ACCOUNT><REF_ID> 3009</REF_ID><NAME>Tennant Brown plc</NAME><TRANSACTION><REF_ID> 3009</REF_ID><SEQ_ID>MS000000013740</SEQ_ID><TRAN_DATE>15/09/2002</TRAN_DATE><DESN>Tennant Browm plc</DESN><GROSS_VAL>4700</GROSS_VAL><INSTALMENT><REF_ID> 3009</REF_ID><SEQ_ID>MS000000013740</SEQ_ID><ORIG_VALUE>1000</ORIG_VALUE><FIVAL>1000</FIVAL></INSTALMENT><INSTALMENT><REF_ID> 3009</REF_ID><SEQ_ID>MS000000013740</SEQ_ID><ORIG_VALUE>1000</ORIG_VALUE><FIVAL>1000</FIVAL></INSTALMENT><INSTALMENT><REF_ID> 3009</REF_ID><SEQ_ID>MS000000013740</SEQ_ID><ORIG_VALUE>700</ORIG_VALUE><FIVAL>700</FIVAL></INSTALMENT></TRANSACTION></ACCOUNT><ACCOUNT><REF_ID> 3010</REF_ID><NAME>V & P Midlands Ltd</NAME><TRANSACTION><REF_ID> 3010</REF_ID><SEQ_ID>MS000000013471</SEQ_ID><TRAN_DATE>20/08/2002</TRAN_DATE><DESN>V & P Midlands Ltd</DESN><GROSS_VAL>4700</GROSS_VAL></TRANSACTION><TRANSACTION><REF_ID> 3010</REF_ID><SEQ_ID>MS000000013472</SEQ_ID><TRAN_DATE></TRAN_DATE><DESN>V & P Midlands Ltd</DESN><GROSS_VAL>411.25</GROSS_VAL></TRANSACTION></ACCOUNT></LETTER>

Peter Jamieson · May 27, 2004

It is a common request, but the unfortunate reality is that Word is not
currently designed for this.

If you happen to have Access availale as a tool, there is a lot to be said
for importing your data into Access (which should be able to cope with your
xml file, at least if you do some transformations first) and using Access's
reporting facilities to produce output. But even that will probably only be
straightforward if you want to print the letters, not e-mail them etc.

If you have to use Word, you can consider
a. using the customer table (or whatever it is) as the data source for the
merge, and a DATABASE field to list the transactions for that customer, e.g.
if you have an alphanumeric (text) customer ID field called CustomerID, you
might have something along the lines of

Dear { MERGEFIELD CustomerName }

Your transactions:

{ DATABASE \d "file containing the transaction data"
\c "connection info."
\s "SELECT * FROM [file or table name] WHERE [Customer ID] = '{ MERGEFIELD
CustomerID }' }

You can insert a DATABASE field by enabling the Database toolbar and using
the Insert Database button, specifying a filter condition to give you a
WHERE clause, and checking the "Insert as field" option. Assuming that
works, you can then modify the WHERE clause by inserting the mergefield (the
{} have to be the special field braces you can insert using ctrl-F9).

However, that method gives you very little control over the layout of the
resulting table (you can use some table layout switches in the DATABASE
field, but that's about it) and the data in it - you don't get decimal tabs,
for example.

b. you can use VBA. I do not know whether that option is realistic for you,
but possible approaches include:
i) "roll your own" VBA that reads the data and constructs each document
from scratch, perhaps using boilerplate text that you get from a template or
skeleton of some kind. How to read the data depends on the format of your
data - you might use ADO to get data from Access, SQL Server, Oracle etc. or
certain types of text file, or perhaps use Microsoft's msxml parser to get
XML data.
ii) use the Word Mailmerge events - do a merge with one record in the
data source for each customer, and construct the transaction list for each
customer prior to merging each record.

As for reading the XML, I was also a bit taken aback when all the XML
support arrived but there was no real support for its use as a data
interchange format. You could consider
a.importing into Access or Excel, if you have either of those
b. trying to use the new XML options in the { INCLUDEFILE } field to create
a table in a Word document which you can then use as a data source for a
merge. I haven't really got around to trying that yet...

--
Peter Jamieson

Kevin Lippiatt said:
I'm developing a professional accountancy system with a debt management

facility and I want to set up a mail merge where each customer(account) gets
one letter, each with mulitple transactions (any number of) and each
transaction can have multiple instalments (any number of or none at all)(see
example below) Surely this must be a common request for mail merging.

I don't see how I can create a mail merge or a data source document i.e.

..csv file that has varying numbers of fields for each customer.

I have tried to generate .csv files where all the transaction and

instalment data is in one mail merge field and is seperated by newline
characters, but this approach doesn't yield very professional looking
results.

Currently my system also has the option to generate .xml files that have

the appropriate structure (see below) but Word mail merge does not support
..xml input - even in Word 2003

<LETTER><ACCOUNT><REF_ID> 3009</REF_ID><NAME>Tennant Brown

plc said:
<DESN>Tennant Browm

plc</DESN><GROSS_VAL>4700</GROSS_VAL><INSTALMENT><REF_ID>
3009</REF_ID><SEQ_ID>MS000000013740</SEQ_ID><ORIG_VALUE>1000</ORIG_VALUE><FI
VAL>1000</FIVAL></INSTALMENT><INSTALMENT><REF_ID>
3009</REF_ID><SEQ_ID>MS000000013740</SEQ_ID><ORIG_VALUE>1000</ORIG_VALUE><FI
VAL>1000</FIVAL></INSTALMENT><INSTALMENT><REF_ID>
3009</REF_ID><SEQ_ID>MS000000013740</SEQ_ID><ORIG_VALUE>700</ORIG_VALUE><FIV
AL>700</FIVAL></INSTALMENT></TRANSACTION></ACCOUNT><ACCOUNT><REF_ID>

3010 said:
<DESN>V & P Midlands

Ltd</DESN><GROSS_VAL>4700</GROSS_VAL></TRANSACTION><TRANSACTION><REF_ID>
3010</REF_ID><SEQ_ID>MS000000013472</SEQ_ID><TRAN_DATE></TRAN_DATE><DESN>V
& P Midlands
Ltd</DESN><GROSS_VAL>411.25</GROSS_VAL></TRANSACTION></ACCOUNT></LETTER>