Nested Mail Merge

[Karit Olsen]

Hi

I am evaluating your DocIO framework. Everything works fine for me with the MailMerge.Execute-command. But I really have problems with the MailMerge.ExecuteNestedGroup-command.

I have 4 tables in my database:

1. Person (PersonID, FirstName, LastName, AddressID_FK)
2. Address (AddressID, StreetName, StreetNumber, Zip_FK)
3. ZipTown (Zip, Town)
4. Payments (Date, Discription, Amount, PersonID_FK)

I have a 1:1 relationship between 1 and 2, and between 2 and 3.
I have a 1:N relationship between 1 and 4

I would like to perform a Nested Mail Merge. On the top of the document I would like to have the informations (Name and Address) of the person, and underneath I would like to have a table with the Payments information, related to the person.

var conn = new SqlConnection(my db connection details);
conn.Open();

ArrayList commands = new ArrayList();

DictionaryEntry entry = new DictionaryEntry("Person", "Select * From Person, Address, ZipTown Where Person.AddressID_FK = Address.AddressID AND Zip_FK = Zip");
commands.Add(entry);

// To retrive payment details for each person
entry = new DictionaryEntry("Payments", "SELECT Date, Discription, Amount FROM Payments, Person WHERE Payments.PersonID_FK = %Person.PersonID%");
commands.Add(entry);

//Execute Mail merge
doc.MailMerge.ExecuteNestedGroup(conn, commands);
conn.Close();
doc.Save("Example.docx", FormatType.Docx);

I think everything is correct so far... (?).

But it doesn't work. And I begin thinking, that my error is in the template docx-file. In your documentation you write something about TableStart and TableEnd and BeginGroup and EndGroup. I don't understand how to use/implement these. Do you have a sample of such a template document?

Kind regards, Karit

[Karit Olsen]

Well, I found a lot of small errors in my code.

1. I forgot the 'true' in the MailMerge.ExecuteNestedGroup(conn, commands, true)-call (I use SqlConnection).
2. When I add BeginGroup:GroupName, that matches the DictionaryEntry key, and the respective EndGroup:GroupName MergeFields, it works. I get data to show.

That's a first!

But I still have problems showing the data in only one table for each person. I think it is a question of where to or how to add the BeginGroup/EndGroup MergeFields or maybe I need to use the TableStart/TableEnd?
Also, I would like to have a headline on each column in the table of Payments for each Person.

Also I would like to know: Is it possible to format the date in a MergeField programmatically?

Kind regards, Karit :)

[Karit Olsen]

Well, after one more day trying, I found out, that these two lines of code are doing the job:

doc.MailMerge.ClearFields = true;
doc.MailMerge.RemoveEmptyParagraphs = true;

They have to be added before the
doc.MailMerge.ExecuteNestedGroup(table);

Another thing, I found out is, that if you want to use Aggregates in your MergeFields, you have to use Alias in your Select. E.g. "Select Sum(Amount) as Sum from Payments....". Now you can use 'Sum' as a MergeField.

Greatings, Karit :)

[Gunasekaran T]

Hi Karit,

Thank you for your interest in Syncfusion products.

To perform nested mail merge in a region the group start and end merge fields should be BeginGroup and EndGroup respectively. In a table the group start and end merge fields should be TableStart and TableEnd respectively. We have prepared the sample to meet your requirement to perform nested mail merge (BeginGroup/EndGroup and TableStart/TableEnd group merge fields) using DocIO. Please find the sample for the same from the attachment. Try running the sample and let us know if this helps you.

Please let us know if you have any other questions.

Regards,
Gunasekaran




Sample_9dfa00a3.zip

[Karit Olsen]

Thank you very much for your sample. I can't run it because of a server-version mismatch, but I can open your files and that is the most important.

If I take a look at your template-file, I can see, that you place your 'TableStart:Payments' and 'TableEnd:Payments' MergeFields INSIDE the table. If I do it, I get the Exception: "Group "Payments" is missing in the source document."

I have to place the 'TableStart:Payments' outside the table.

That means: I have to add a table only containing the headlines of the tablecolumns (Date, Discription, Amount).
Then I place the 'TableStart:Payments' MergeField in the line AFTER that first table.
Then i place another table containing the Payments-MergeFields like 'Date', 'Discription' and 'Amount'.
Then I place the 'TableEnd:Payments'-MergeField in the line AFTER that SECOND table.

I have to place these two lines in my C# document, to avoid having 2 tables in my resulting MailMerge document:

doc.MailMerge.ClearFields = true; doc.MailMerge.RemoveEmptyParagraphs = true;

I use Word 2010 and your newest release (10.1.0.44).

Do you have any idea, why it doesn't work for me to place the TableStart:.. and TableEnd:.. MergeFields inside the tablecells? It looks so awkward in my template-file... (attached).

I also tried out to implement a picture in the MailMerge. It was hard to find Documentation on your website on that topic. But for all the others, who have had the same problem and are reading this post: The MergeField fieldname for the picture has to start with 'Image:' followed by the columname of the picture in your db.

Greatings, Karit :)



Image_3fd103d.zip

[Gunasekaran T]

Hi Karit,

Thank you for your update.

Could you please provide us the sample or code snippet to reproduce the mentioned exception "Group "Payments" is missing in the source document" along with the input word document and data used for mail merging on our side for further analysis? So that we can analyze further on the exact cause of the exception and provide you with the appropriate solution.

Please let us know if you have any other questions.

Regards,
Gunasekaran

[tiemo]

sure