Passing a value to the next Mail Merge record

Topic

New Reply

I’m trying to use Word’s Mail Merge functionality to automatically construct a document from a data source. Conceptually, it’s a lot like designing a Report in Access. (For anyone interested, it’s actually a D&D spellbook, reordered in 1st Edition style.)

A lot of things are quite doable, using the “Directory” layout. But I’m struggling to get some conditional functionality working.

Conditional Heading

The main thing is, I want some records to be preceded by a heading, based on the fact that a merge field’s value (the spell level) has incremented from one record to the next. Essentially, I’m trying to pass information from one record to the next.

Initially, I tried creating a bookmark that is compared to the new value of the merge field, before being set to that value. Obviously, this could generate an error in the first field (since it hasn’t been set yet), but in fact it just doesn’t seem to work at all — at least, not in the merge simulation. It’s possible that’s because I need to put it at the start of the merge template (for testing reasons, I put it at the end). Running the real Merge, the match always fails, probably indicating that the bookmark does not retain its value between records.

Is there a way to get this working?

Obviously, the fall-back option is to modify the data. That seems inelegant, but probably necessary.

Blank Lines Interacting with Styles

The other issue is a bit weird. Some of the merge fields can be blank; some of these need to appear on their own line, but only if they contain data (spell duration, etc). I’ve tried both obvious techniques, but they’re a little inconsistent:  ending a paragraph inside an IF field, and putting the paragraph mark inside the merge field’s \f switch.

Because I want to control the style of each line independently, the merge field (or IF field) has two different styles: one before and one after the embedded paragraph mark. Sometimes this works fine, but sometimes I get a very small, blank line, or weird paragraph spacing.

Is there a better way to do this? Or any tips that might get me more consistent behaviour?

(Incidentally, what I’ve described is really only possible by showing all field codes using Alt-F9 — in case you were wondering.)

 

Thanks in advance for any help!

Reply | Quote

Replies

Your Conditional Heading description suggests you’re doing a catalogue/directory mailmerge. In that case, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
https://www.msofficeforums.com/mail-merge/38721-microsoft-word-catalogue-directory-mailmerge-tutorial.html
or:
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

Depending on what you’re trying to achieve, the field coding for this can be complex. However, since the tutorial document includes working field codes for all of its examples, most of the hard work has already been done for you – you should be able to do little more than copy/paste the relevant field codes into your own mailmerge main document, substitute/insert  your own field names and adjust the formatting to get the results you desire. For some worked examples, see the attachments to the posts at:
https://www.msofficeforums.com/mail-merge/9180-mail-merge-duplicate-names-but-different-dollar.html#post23345
https://www.msofficeforums.com/mail-merge/11436-access-word-creating-list-multiple-records.html#post30327

Another option would be to use a DATABASE field in a normal ‘letter’ mailmerge main document and a macro to drive the process. An outline of this approach can be found at:
https://answers.microsoft.com/en-us/office/forum/office_2010-word/many-to-one-email-merge-using-tables/8bce1798-fbe8-41f9-a121-1996c14dca5d
Conversely, if you’re using a relational database or, Excel workbook with a separate table with just a single instance of each of the grouping criteria, a DATABASE field in a normal ‘letter’ mailmerge main document could be used without the need for a macro. An outline of this approach can be found at:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_winother-mso_2010/mail-merge-to-a-word-table-on-a-single-page/4edb4654-27e0-47d2-bd5f-8642e46fa103
For some working examples, see:
https://www.msofficeforums.com/mail-merge/37844-mail-merge-using-one-excel-file-multiple.html
https://www.msofficeforums.com/mail-merge/45044-using-mailmerge-include-grouped-information-letter-2.html#post151706
https://www.excelforum.com/excel-general/1273421-merge-excel-list-into-word-receipt.html#post5110813
(the second of these uses a macro to apply some additional formatting).

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at https://www.gmayor.com/ManyToOne.htm ; or
Doug Robbins at https://onedrive.live.com/?cid=5AEDCB43615E886B&id=5AEDCB43615E886B!566

As for the Styles issue, you can’t control the format of text inserted via the \b or \f MERGEFIELD switches – you’d need to use IF fields for that. For example:

{IF{MERGEFIELD Item_A}<> “” “Start of Item A content {MERGEFIELD Item_A} End of Item A content¶
“}{IF{MERGEFIELD Item_B}<> “” “Start of Item B content {MERGEFIELD Item_B} End of Item B content¶
“}{IF{MERGEFIELD Item_C}<> “” “Start of Item C content {MERGEFIELD Item_C} End of Item C content¶
“}Content following mergefields

where each ¶ is a real paragraph break with the required Style applied. Note, too, how the final mergefield is attached to the start of the paragraph following the series.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks, macropod.

I think you could have shortened your reply based on the information I provided, but maybe I wasn’t as clear as I thought I was.

What I’ve understood from your reply is that there probably isn’t any way to pass values from one record to the next. But it’s not really going to be an issue:  I need to merge some manual data with the API query, anyway, so creating an extra column manually is a much easier solution.

As far as Styles go, I think the options I identified probably are best practice. I have decided to add 4 point lines as spacers, as it allows me to keep the data block on one page while allowing the subsequent description text to roll over to the next page.

Now my only issue seems to be that the MERGEREC field ignores the SKIPIF field. Luckily, I can get around this, as I only need to identify the first record, which I can do based on the new column I’m adding.

Reply | Quote

In this era of twitter, my best advice is to never complain to someone giving you an answer with a detailed answer.

Susan Bradley Patch Lady/Prudent patcher

1 user thanked author for this post.

PStepanas

Reply | Quote

I think I’ve figured out that the answer to my original question is “No”, and  I think I know why.

As far as I can tell, when you run mail merge, all fields are converted to text (or locked in or whatever the correct terminology is). Consequently, the output of a mail merge will not contain any fields at all.

This makes perfect sense, in the context of an actual mail merge — you want all the merge fields to be converted to text — but it limits what you can do when you’re trying to use mail merge for automated document building, as I am.

Please do correct me, if I’ve misunderstood.

As for the style issue (my second question), I think you just have to occasionally reapply the styles with all fields displayed (Ctrl-Alt-F9). In other words, there’s no better solution than what I’m already doing.

(I figured I’d post this in case anyone stumbles upon this thread with similar questions.)

Reply | Quote

Clearly, you haven’t paid much attention to either the techniques employed in my tutorial or to the advice I gave on the field coding. Having expressed your “I think you could have shortened your reply based on the information I provided” attitude, you’re on your own…

Reply | Quote