Mail Merge - List clients with different sets of products

[Echevca]

I need to create a mail merge that will have a set of rules but not sure how to put the logic together. I am familiar setting mail merges but just the regular/basic ones.


I have clients with different set of products. For simplicity purposes lets pretend the products are called A, B, C, D. Not all clients have all four products. Client #1, for example only has products A and B; Client#2 only has product B and Client #3 only has products C and D.

My Excel file will look like this:


Client Product A Product B Product C Product D
Client 1 1% 2%
Client 2 2%
Client 3 3% 4%

I want to put a letter together that reference the % increase each product will incur without having to list all four products as a template and avoid spaces between the lines when there is no product. For example. the letter for carriers #1 will say that they are getting an increase for products A and B while the letter for Carrier #2 will say they are getting an increase for product. B....they will not see the other products listed since they don't have a % marked in the Excel file.


Is this feasible using the mail merge rules? If so, how do I put it together?


Your help will be greatly appreciated!

[gmayor]

On the face of it from your description, this is a simple merge using conditional fields e.g.

{ IF { MERGEFIELD "Product A" } <> "" "Product A : { MERGEFIELD "Product A" }¶" }{ IF { MERGEFIELD "Product B" } <> "" "Product B : { MERGEFIELD "Product B" }¶" }{ IF { MERGEFIELD "Product C" } <> "" "Product C : { MERGEFIELD "Product C" }¶" }{ IF { MERGEFIELD "Product D" } <> "" "Product D : { MERGEFIELD "Product D" }¶" }

(the field bracket pairs are inserted with CTRL+F9). Press the enter key at ¶.

If there is a value in 'Product A' the field will produce 'Product A : 1%' followed by a paragraph break, otherwise it will do nothing. Similarly the other fields. Modify as required.

The only minor proviso relates to the percentage value in the data source. If necessary use a formatting switch to give the correct result - see http://www.gmayor.com/formatting_word_fields.htm

[Echevca]

Thank you, thank you, thank you for you quick response during the weekend. I was having computer problems so I was unable to thank you right away.

It seemed to have worked with the products that had values listed one after the other on one line. I ventured into adding a ¶ after each product mergefield so that the products appear one over the other, but in doing so I get an empty row that I don't need/want...how do I get rid of those blank rows or how do I get the results to list without empty product lines?

This is the edited version for Products A-C taking your formula and adding the extra ¶

{ IF { MERGEFIELD "Product A" } <> "" "Product A : { MERGEFIELD "Product A" }¶" }¶{ IF { MERGEFIELD "Product B" } <> "" "Product B : { MERGEFIELD "Product B" }¶" }¶{ IF { MERGEFIELD "Product C" } <> "" "Product C : { MERGEFIELD "Product C" }¶" }¶

So right now I get the following look:


Product A #%

Product C #%
Product D #%


I want to eliminate that extra empty row that pertains to Product B (which didn't populate because it didn't have a value....which is what I wanted/needed)

Thanks in advance!

[Echevca]

In addition, how do I insert a table ONLY when one of the products has information. So if Product D has a % in the data source then insert a table that will have three columns headers : Product, Current Price, New Price.

This table will only show when Product D has a % otherwise it will be "invisible".

The table will have 11 rows. Each of those "cells" will be conditioned "show" when Product D has a % in the data source.

Product Current Price New Price
Fee 1 $1.00 $2.00
Fee 2 $2.00 $3.00
Fee 3 $3.00 $4.00

Thanks again!!!!!

[macropod]

Originally Posted by Echevca

I have clients with different set of products. For simplicity purposes lets pretend the products are called A, B, C, D. Not all clients have all four products. Client #1, for example only has products A and B; Client#2 only has product B and Client #3 only has products C and D.

My Excel file will look like this:


Client Product A Product B Product C Product D
Client 1 1% 2%
Client 2 2%
Client 3 3% 4%

I want to put a letter together that reference the % increase each product will incur without having to list all four products as a template and avoid spaces between the lines when there is no product. For example. the letter for carriers #1 will say that they are getting an increase for products A and B while the letter for Carrier #2 will say they are getting an increase for product. B....they will not see the other products listed since they don't have a % marked in the Excel file.

Assuming you want lists like:

Client 1
Product A : %
Product C : %
Product D : %

Client 2
Product B : %
Product D : %

Client 3
Product A : %
Product B : %
Product C : %
Product D : %

you don't need IF tests! Try:
{MERGEFIELD Product_A \b "Product A : "}{MERGEFIELD Product_B \b "¶
Product B : "}{MERGEFIELD Product_C \b "¶
Product C : "}{MERGEFIELD Product_D \b "¶
Product D : "}¶
where ¶ is an actual paragraph break or line break.

To conditionally output a whole table row, though, you would need IF tests but, unless you want to make things very complicated (so as to account for every possible combination), your output will generate a series of one-row tables. The one-row table approach uses fields like:
|Product|Current Price|New Price|
{IF{MERGEFIELD Product_A}<> "|{MERGEFIELD Product_A}|{MERGEFIELD Current_Price_A}|{MERGEFIELD New_Price_A}|¶
"}{IF{MERGEFIELD Product_B}<> "|{MERGEFIELD Product_B}|{MERGEFIELD Current_Price_B}|{MERGEFIELD New_Price_B}|¶
"}
etc., where the | characters represent table cell borders.