Problem in multiple regions mail merge

[mtassinari]

Hi,

I attached a .doc template on which I’m working, that represents an HSE department audit created by our software.

As you see, there are many merge regions, and also many merge fields outside any regions.

The mail merge operation is NOT done within a single call, but rather following this steps:

- get all outside fields’ values
- get “ispettori” and “ispettore” regions
- get “associazioni” and “associazione” regions
- get “categoria” and “domanda” regions
- get “matrici”, “verifica” and “criterio” regions
- get “audit” and “punti_verifica” regions
- get “grafici” region

It is done this way so that each method returns its own DataSet with required DataTable and DataRelation set, then the merging is done like this:

merge(doc, this.getVerbale());
merge(doc, this.getAssociazioni(this.getModuleFrom(), this.getParam("id_valutazione")));
merge(doc, this.getChecklist());
merge(doc, this.getMatrici());
merge(doc, this.getRisultato());
merge(doc, this.getGrafico(), "grafici");

The first and last call simply merge a ResultSet, the other are all DataSet with proper names and relation set.

Also, I have setup mailmerge like this:

MailMerge merge = doc.getMailMerge();
merge.setFieldMergingCallback(this);
merge.setCleanupOptions(MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS | MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS);

I am facing the following problems:

1. If I just remove the lines doing the actual merge, I get a document with all the merge fields and regions still in place, despite the cleanup options I set

2. If I restore the removed line to try to fill the document, it fails, but I seem to be unable to get any error nor stacktrace out of it, even if the code is made so that each error should generate an exception with full stack trace

Can you help me with this issue?

Thanks in advance.

[mtassinari]

Hi again,

I found out the issue, due to the fact that I had placed a TableEnd tag on the same line with a TableStart tag.

Now I have another problem: after the first mail merge, all unused fields/regions gets removed even if I would need them for successive mail merges.

How do I make it so that the cleanup happens only when I need it to happen? Can I manually call some cleanup function?

Thanks

EDIT: I also have the following problems:

1. the “ispettori” and “ispettore” regions are not merged even if I am sure that there is at least one row of data

2. the “matrici” region, which forms a 3 levels deep nested region together with “verifica” and “criterio”, is not filled correctly even if I am sure that there are many rows of data

Here is how I take the “ispettori” and “matrici” data and relate them together:

protected DataSet getIspettori() throws Exception {
	try {
		DataSet ds = new DataSet();


		ds.getTables().add(new DataTable(this.query(
			"SELECT id_valutazione, id_azienda, id_sede, revisione_documento, IF(t1.module_from = '98_52', 'Sopralluogo', 'Audit') as tipo" +
			" FROM lin_98_20_valutazioni as t1 " +
			" JOIN lin_98_20_ispettori as t2 USING(id_valutazione, id_azienda, id_sede, revisione_documento) " +
			" WHERE " + this.getWhereString() +
			" AND id_valutazione='" + this.getParam("id_valutazione") + "'" +
			" GROUP BY id_valutazione"
		), "ispettori"));


		ds.getTables().add(new DataTable(this.query(
			"SELECT " +
			" IF(flag_presente, '', t2.note) as note " +
			" t1.*, " +
			" t2.nome_utente as ispettore, " +
			" lin_98_20_ispettori as t1 " +
			" JOIN lin_97_2_1_user_sgs as t3 USING(id_user_sgs, id_azienda, id_sede, revisione_documento) " +
			" WHERE " + this.getWhereString() +
			" AND id_valutazione='" + this.getParam("id_valutazione") + "'" +
			" ORDER BY ispettore"
		), "ispettore"));


		ds.getRelations().add(new DataRelation(
			"ispettori-ispettore",
			"ispettori",
			"ispettore",
			new String[] {"id_valutazione", "id_azienda", "id_sede", "revisione_documento"},
			new String[] {"id_valutazione", "id_azienda", "id_sede", "revisione_documento"}
		));


		return ds;
	}
	catch (Exception e) {
		throw this.error("Impossibile caricare l'elenco degli ispettori", e);
	}
}


protected DataSet getMatrici() throws Exception {
	try {
		DataSet ds = new DataSet();


		ds.getTables().add(new DataTable(this.getRisultatoMatrici(), "matrici"));


		ds.getTables().add(new DataTable(this.getPuntiVerifica(), "verifica"));


		ds.getTables().add(new DataTable(this.query(
			"SELECT" +
			" t1.*," +
			" t2.punto_verifica as criterio," +
			" t3.a" +
			" FROM lin_98_20_punteggi as t1" +
			" JOIN lin_98_20_punti_verifica as t3 USING (id_punto_verifica, id_valutazione, id_azienda, id_sede, revisione_documento)" +
			" JOIN lin_98_20_modelli_punti_verifica as t2 ON (t1.id_criterio = t2.id_punto_verifica)" +
			" WHERE id_valutazione = '" + this.getParam("id_valutazione") + "'" +
			" AND " + this.getWhereString() +
			" ORDER BY criterio"
		), "criteri"));


		ds.getRelations().add(new DataRelation(
			"matrici-verifica",
			"matrici",
			"verifica",
			new String[] {"id_valutazione", "id_azienda", "id_sede", "revisione_documento"},
			new String[] {"id_valutazione", "id_azienda", "id_sede", "revisione_documento"}
		));


		ds.getRelations().add(new DataRelation(
			"verifica-criteri",
			"verifica",
			"criteri",
			new String[] {"id_punto_verifica", "id_valutazione", "id_azienda", "id_sede", "revisione_documento"},
			new String[] {"id_punto_verifica", "id_valutazione", "id_azienda", "id_sede", "revisione_documento"}
		));


		return ds;
	}
	catch (Exception e) {
		throw this.error("Impossibile caricare l'elenco dei punteggi delle matrici", e);
	}
}

Am I doing something wrong there?

[mtassinari]

Please let me add an explanation about why the template has the structure it has.

Take for example the “ispettori” and “ispettore” regions, why did I choose the nested region?

I did it so that, if the “ispettore” region is empty, the “ispettori” region will be empty too, and so removed by the cleanup options I put.

My logic is this: the DataTable for “ispettori” contains exactly one row, with the necessary fields to do the joining with “ispettore”, which will contain the actual rows.

This costs me an extra query to retrieve the outer row, which I need to make sure it is no more then one, hence the GROUP BY clause.

Do you think this is a good method to somehow automate empty regions removal? If not, how should I do it instead?

[tahir.manzoor]

Hi Matteo,

Please accept my apologies for late response.

Thanks for your inquiry.

mtassinari:
Now I have another problem: after the first mail merge, all unused fields/regions gets removed even
if I would need them for successive mail merges.
How do I make it so that the cleanup happens only when I need it to happen? Can I manually call
some cleanup function?

The behavior of Aspose.Words is correct in your case. I suggest you please call MailMerge.setCleanupOptions just before last call of MailMerge.executeWithRegions method in your code.

If you use following line of code before all calls of MailMerge.executeWithRegions, the cleanup operation removes empty paragrapahs, unused regions and unused fields.

doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_EMPTY_PARAGRAPHS |MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS |MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS);

Please read more about MailMergeCleanupOptions from here:
https://reference.aspose.com/words/java/com.aspose.words/mailmergecleanupoptions

*mtassinari:

1. the “ispettori” and “ispettore” regions are not merged even if I am sure that there is at least
   one row of data

2. the “matrici” region, which forms a 3 levels deep nested region together with “verifica” and
   “criterio”, is not filled correctly even if I am sure that there are many rows of data*

Could you please attach your modified input Word document here for testing? I will investigate the issue on my side and provide you more information. I am getting following error with your document.

*Two regions cannot start/end in the same paragraph or occupy the same table row.
mtassinari:
Take for example the “ispettori” and “ispettore” regions, why did I choose the nested region?

I did it so that, if the “ispettore” region is empty, the “ispettori” region will be empty too, and
so removed by the cleanup options I put.*

If there is one to one relationship between tables ispettori and ispettore then there is no need to create nested region. The MailMergeCleanupOptions will resolve this issue. Please call MailMerge.setCleanupOptions just before last call of MailMerge.executeWithRegions method in your code.

[mtassinari]

tahir.manzoor:
Could you please attach your modified input Word document here for testing? I will investigate the issue on my side and provide you more information. I am getting following error with your document.

Two regions cannot start/end in the same paragraph or occupy the same table row.

Attached the corrected document!

tahir.manzoor:
If there is one to one relationship between tables ispettori and ispettore then there is no need to create nested region. The MailMergeCleanupOptions will resolve this issue. Please call MailMerge.setCleanupOptions just before last call of MailMerge.executeWithRegions method in your code.

*Suppose I do not create the outer region called “ispettori”, will the table header be removed as well together with the inner region called “ispettore”?

From your samples, I would not say so, and I wanted to create an automatic way to remove empty tables and their surroundings, without having to create custom code for each specific case.*

[mtassinari]

I have done some more tests, and I attached a new .doc file as a reference of the problems I am facing.

First of all, I found out why the regions “ispettori” and “ispettore” weren’t getting filled, it was due to an error in the queries.

But I have other problems now:

1. after the region “ispettori” there is a page break, which is NOT present in the compiled document (note: I did NOT set the option to remove empty paragraphs)

2. the nested regions “categoria” and “domanda” are not filled correctly, in fact it filles only the part of the first category, the others are empty, even if the name of the category is correctly set

3. before the “NOTE CONCLUSIVE” part there should be another page break, it is present in the template but not in the output

Regarding the second problem, I have attached to .csv files (whose first rows are column names), which shows what is in the database for each of the two regions.

Please note that we are having big trouble with nested mail merge not working properly, as in this case, but this would be our main reason to upgrade to newest version, so please, help us understand how to solve it!

[tahir.manzoor]

Hi Matteo,

Thanks for your inquiry.

*mtassinari:

1. after the region “ispettori” there is a page break, which is NOT present in the compiled document (note: I did NOT set the option to remove empty paragraphs)
2. before the “NOTE CONCLUSIVE” part there should be another page break, it is present in the template but not in the output*

Perhaps, you are using an older version of Aspose.Words; as with Aspose.Words v13.4.0, I am unable to reproduce this problem on my side. I would suggest you please upgrade to the latest version of Aspose.Words i.e. v13.4.0 and let us know how it goes on your side. I hope, this will help.

I have attached the two test output documents with this post for your kind reference.

mtassinari:
2. the nested regions “categoria” and “domanda” are not filled correctly, in fact it filles only the part of the first category, the others are empty, even if the name of the category is correctly set

I am working over your query and will update you asap.

[mtassinari]

tahir.manzoor:
Perhaps, you are using an older version of Aspose.Words; as with Aspose.Words v13.4.0, I am unable to reproduce this problem on my side. I would suggest you please upgrade to the latest version of Aspose.Words i.e. v13.4.0 and let us know how it goes on your side. I hope, this will help.

I am already using latest Aspose.Words.

tahir.manzoor:
I am working over your query and will update you asap.

Thanks.

[tahir.manzoor]

Hi Matteo,

Thanks for your patience.

*mtassinari:

1. after the region “ispettori” there is a page break, which is NOT present in the compiled document (note: I did NOT set the option to remove empty paragraphs)
2. before the “NOTE CONCLUSIVE” part there should be another page break, it is present in the template but not in the output*

I have not found the issue reported in issue number 1 and 3. Please see the attached images and output ‘issue-1-3.docx’ for detail.

Please see the Issue-1.png, there is page break in output Docx. Similarly, there is page break before ‘NOTE CONCLUSIVE’ (see Issue-3.png).

mtassinari:
2. the nested regions “categoria” and “domanda” are not filled correctly, in fact it filles only the part of the first category, the others are empty, even if the name of the category is correctly set

I have tested this scenario by using following code snippet and have not found the shared issue in output. Please see the attached out.docx file.

Please make sure that you are using the latest version of Aspose.Words for Java 13.4.0. Please check the relationship set in following highlighted code snippet.

com.aspose.words.DataSet dataSet = new com.aspose.words.DataSet();
DataTable datatable = new DataTable(executeQuery("SELECT * from categoria"), "categoria");
dataSet.getTables().add(datatable);
DataTable datatable2 = new DataTable(executeQuery("SELECT * from domanda "), "domanda");
dataSet.getTables().add(datatable2);
dataSet.getRelations().add(new DataRelation("DataRelation", datatable.getTableName(), datatable2.getTableName(), new String[] {"id_categoria"}, new String[] {"id_categoria"}));
Document doc = new Document("c:\\verbale_it.doc");
doc.getMailMerge().executeWithRegions(dataSet);
doc.save("c:\\out.docx")

[mtassinari]

Yes, I am SURE that I am using latest version of Aspose.Words for Java.

I am also SURE that I created the two DataTable correctly for categoria and domanda, and I also setup the DataRelation correctly.

In the end I resorted implementing my own version of DataTable and DataSet, which are working correctly.

I must admit that I do not like this solution very much, I would have preferred using your classes, but at least this works.

EDIT:

Also, please, instead of showing me something you tested with some dummy data of yours, could you please use the template I attached some posts ago (verbale_it.doc) together with the two CSV files (categoria.csv and domanda.csv) which shows exactly what comes from our database?

If you want, I think you could strip it down to have only the two regions categoria and domanda, and then use my .csv files as data sources to perform the mail merge, perhaps this way you’ll finally be able to reproduce the issue I am facing.

Please note the following:
- the .csv files have both the columns’ names as first line, so you might want to remove it
- the relation between categoria and domanda must be defined with new String[] {“id_categoria”, “id_azienda”, “id_sede”, “revisione_documento”}, because those are the proper joining fields

[tahir.manzoor]

Hi Matteo,

Thanks for confirming that you are using the latest version of Aspose.Words for Java. I have tested your scenario by using shared CSV files. I have used the following relationship between three columns.

dataSet.getRelations().add(new DataRelation("DataRelation",
datatable.getTableName(), datatable2.getTableName(), new String[] { "id_categoria", "id_azienda", "id_sede", "revisione_documento" },
    new String[] { "id_categoria", "id_azienda", "id_sede", "revisione_documento" }));

Document doc = new Document("c:\\verbale_it.doc");

doc.getMailMerge().executeWithRegions(dataSet);

doc.save("c:\\out.docx");

Could you please spot the problem in attached out.docx file and share some more detail about your issue? I will investigate the issue on my side and provide you more information.

[mtassinari]

tahir.manzoor:
Could you please spot the problem in attached out.docx file and share some more detail about your issue? I will investigate the issue on my side and provide you more information.

In your attached document it would seem that the regions categoria and domanda are filled correctly using the standard DataSet class.

I have tried once again, though, and still I cannot get the standard classes to work, while it works if I use the same exact queries in an object implementing IMailMergeDataSource.

I really cannot understand what could be the problem in this case, and why your tests work and mine don’t.

I can think of many possible reasons, from the database connection to the environment (our java code runs from java servlets on tomcat 7), but since we have no time to investigate them all, I’ll simply resort using my classes, which I know to work properly.

Do you think it could be useful if I shared the code of such class?

[tahir.manzoor]

Hi Matteo,

Thanks for sharing the detail. Perhaps you are using ResultSet.TYPE_FORWARD_ONLY in Connection.createStatement method, please use ResultSet.TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE as shown in following code snippet. Hope this helps you.

Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
s.execute("SELECT * FROM table");
ResultSet resultSet = s.getResultSet();

The incorrect results you are getting for your scenario may be due to ResultSet of type TYPE_FORWARD_ONLY. This is a bug in Aspose.Words for Java. I logged this issue as WORDSJAVA-707 in our issue tracking system. I have linked this forum thread to the same issue and you will be notified via this forum thread once this issue is resolved.

[mtassinari]

I will try your solutions and see if it works.

[mtassinari]

mtassinari:
I will try your solutions and see if it works.

I have tried it and I can confirm that this was the cause of the problem, using your solution it all worked properly.

I think you should at least document this, 'cause it gave me some headaches while trying to solve the problem!

[tahir.manzoor]

Hi Matteo,

Thanks for your feedback. We logged this issue as WORDSJAVA-707 in our issue tracking system. We will update you via this forum thread once this issue is resolved.

We apologize for your inconvenience.

[mtassinari]

*tahir.manzoor:
The behavior of Aspose.Words is correct in your case. I suggest you please call MailMerge.setCleanupOptions just before last call of MailMerge.executeWithRegions method in your code.

If you use following line of code before all calls of MailMerge.executeWithRegions, the cleanup operation removes empty paragrapahs, unused regions and unused fields.

doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_EMPTY_PARAGRAPHS |MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS |MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS);

Please read more about MailMergeCleanupOptions from here:
https://reference.aspose.com/words/java/com.aspose.words/mailmergecleanupoptions**

I have followed your advice, and it all seemed to work, but know I have a problem.

My code looks like:

MailMerge.merge(doc, this.getValutazione());
MailMerge.merge(doc, this.getChecklist());
MailMerge.merge(doc, this.getInterventi());
MailMerge.merge(doc, this.getNote(), true);

Those are some utility method I created to perform mail merge. When it recieves also a boolean true, it executes:

if (isLast)
{
    cleanup(doc);
}

[… mail merge code …]

where the method is implemented as

public static Document cleanup(Document doc)
{
    doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS |
        MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS |
        MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS);

    return doc;
}

I have noticed that with the calls in those order, the unused regions are not removed, while they are if I call the merges in this way:

MailMerge.merge(doc, this.getValutazione());
MailMerge.merge(doc, this.getChecklist());
MailMerge.merge(doc, this.getNote());
MailMerge.merge(doc, this.getInterventi(), true);

Could it be due to the fact that the merge with this.getNote() does not perform an executeWithRegions() but only an execute() ?

Is there a way to perform cleanup even if last call is not a region merge call, or do I have to take care of the merge ordering so that, if I want a final cleanup, the last merge must be a region merge?

[tahir.manzoor]

Hi Matteo,

Thanks for your inquiry. In your case, you need to set CleanupOptions with each mail merge operation according to your requirements.

mtassinari:
Could it be due to the fact that the merge with this.getNote() does not perform an executeWithRegions() but only an execute() ?

No, this does not cause the issue. I suggest you please use CleanupOptions with each mail merge operation but not with all flag. E.g In following mail merge regions, if test2 filed is missing in datasource and you call the setCleanupOptions method just before the last call of ExecuteWithRegions method. This does not remove the test2 mail merge field.

Mail Merge with regions 1:
«TableStart:table1»«test1»«test2»«TableEnd:table1»
Mail Merge with regions 2:
«TableStart:table2»«test1»«test2»«TableEnd:table2»
Mail Merge with regions 3:
«TableStart:table3»«test1»«test2»«TableEnd:table3»

At this stage the result of above template will be as follow:

Mail Merge with regions 1:
«test2»
Mail Merge with regions 2:
«test2»
Mail Merge with regions 3:
«test2»

Now, if mail merge operation is executed once again with setCleanupOptions(REMOVE_UNUSED_FIELDS). This will remove the unused regions. So it depends how you want to use the setCleanupOptions method.
I have attached a test template document with this post for testing purpose. If you face any issue, please use this template to explain your issue for CleanupOptions. I will then provide you more information on this along with code.

[mtassinari]

I’m sorry but I did not really understand all this.

Could you please also make an example of how could/should I reimplement my cleanup() method, and when/how should I call it?

EDIT:

First you said

tahir.manzoor:
I suggest you please call MailMerge.setCleanupOptions just before last call of MailMerge.executeWithRegions method in your code.

and now you said

tahir.manzoor:
I suggest you please use CleanupOptions with each mail merge operation but not with all flag.

I find this a little bit confusing…

[tahir.manzoor]

Hi Matteo,

Thanks for your inquiry.

*Tahir Manzoor:
I suggest you please call MailMerge.setCleanupOptions just before last call of MailMerge.executeWithRegions method in your code.

In your scenario, calling the mail merge operation multiple times for a document, It is best approach to call MailMerge.setCleanupOptions method before the last call of mail merge operation. However, It also depends about your requirements. Please see the attached template document. There are three mail merge regions (table1, table2, table3). Please execute the following code snippet and check the output Docx files.

The first call of MailMerge.executeWithRegions with flag MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS will removes the remaining two regions (test2 and test3) and these region will not merge. Please see the attached After.First.MailMerge.docx.

// Data Table for region1
java.sql.ResultSet table1 = createCachedRowSet(new String[] { "test1" });
addRow(table1, new String[] { "test1 Value in region table1" });
com.aspose.words.DataTable dt1 = new com.aspose.words.DataTable(table1, "table1");
Document doc = new > Document(MyDir + "testMailMerge.docx");
// > call cleanup operation with first call of executeWithRegion
doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_EMPTY_PARAGRAPHS
    | MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS
    | MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS
    | MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS);

doc.getMailMerge().executeWithRegions(dt1);
doc.save(MyDir + "After.First.MailMerge.docx");
// Data Table for region3
java.sql.ResultSet table3 = createCachedRowSet(new String[] { "test1" });
addRow(table3, new String[] { "test1 Value in region table1" });
com.aspose.words.DataTable dt3 = new com.aspose.words.DataTable(table3, "table3");
doc.getMailMerge().executeWithRegions(dt3);
doc.save(MyDir + "Out.docx");

In such case, please do not use REMOVE_UNUSED_REGIONS in first call of mail merge. Please also execute the following code example to understand how Cleanup flags works.

// Data Table for region1
java.sql.ResultSet table1 = createCachedRowSet(new String[] { "test1" });
addRow(table1, new String[] { "test1  Value in region table1" });
com.aspose.words.DataTable dt1 = new com.aspose.words.DataTable(table1, "table1");
Document doc = new > Document(MyDir + "testMailMerge.docx");
doc.getMailMerge().executeWithRegions(dt1);
doc.save(MyDir + "Out1.docx");
//Call for simple mail merge
doc.getMailMerge().execute(new String[] { "test1" }, new > Object[] { "Simple  mail merge " });
doc.save(MyDir + "Out2.docx");
// Data Table for region3
java.sql.ResultSet table3 = createCachedRowSet(new String[] { "test1" });
addRow(table3, new String[] { "test1  Value in region table3" });
com.aspose.words.DataTable dt3 = new com.aspose.words.DataTable(table3, "table3");
// > call cleanup operation with first call of executeWithRegion
doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_EMPTY_PARAGRAPHS
    | MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS
    | MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS
    | MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS);

doc.getMailMerge().executeWithRegions(dt3);
doc.save(MyDir + "Out.docx");

Hope this helps you. Please let us know if you have any more queries.