My dataset has three simple tables. The top table has a single field primary key,
The detail table has a compound primary key consisting of two fields.
The subdetail table has a three field primary key.
I am merging this with a word document that has three corresponding nested regions.
Table ‘top’ and ‘detail’ are merged correctly, but table ‘subdetail’ is not:
All ‘subdetail’ in the dataset records appear under every detail record.
I’v also tried this with single column primary keys only. Then the merge works fine.
Is there a problem with keys spanning multiple fields?
Hello
Thanks for your request. It seems the problem occurs because you are creating wrong relations between tables. Please try using the following code with your template:
// Open template.
Document doc = new Document("C:\\Temp\\hierarchie.docx");
// Execute mail merge with regions.
doc.MailMerge.ExecuteWithRegions(GetDatas());
// Save the result.
doc.Save("C:\\Temp\\out.docx");
///
/// Returns dummy datasource, which contaion two tables with parent-child relationships.
///
private static DataSet GetDatas()
{
DataSet ds = new DataSet();
// Create two tables and add them into the data set.
DataTable parent = new DataTable("Top");
parent.Columns.Add("topnummer");
parent.Columns.Add("omschrijving");
parent.Rows.Add(1, "TopTest1");
parent.Rows.Add(2, "TopTest2");
DataTable child = new DataTable("Detail");
child.Columns.Add("detailnummer");
child.Columns.Add("relatedtopnummer"); // this column is needed to create relationshoip between tables.
child.Columns.Add("omschrijving");
child.Rows.Add(1, 1, "DetailTest1");
child.Rows.Add(2, 1, "DetailTest2");
child.Rows.Add(3, 2, "DetailTest3");
child.Rows.Add(4, 2, "DetailTest4");
DataTable childDetail = new DataTable("SubDetail");
childDetail.Columns.Add("subdetailnummer");
childDetail.Columns.Add("relateddetailnummer"); // this column is needed to create relationshoip between tables.
childDetail.Columns.Add("omschrijving");
childDetail.Rows.Add(1, 1, "SubDetailTest1");
childDetail.Rows.Add(2, 1, "SubDetailTest2");
childDetail.Rows.Add(3, 2, "SubDetailTest3");
childDetail.Rows.Add(4, 2, "SubDetailTest4");
childDetail.Rows.Add(5, 3, "SubDetailTest5");
childDetail.Rows.Add(6, 4, "SubDetailTest6");
// Add tables into the data set and add relationship.
ds.Tables.Add(parent);
ds.Tables.Add(child);
ds.Tables.Add(childDetail);
ds.Relations.Add(parent.Columns["topnummer"], child.Columns["relatedtopnummer"]);
ds.Relations.Add(child.Columns["detailnummer"], childDetail.Columns["relateddetailnummer"]);
return ds;
}
Best regards,
The DataSet that is returned from the GetDatas() method has relations spanning single fields, and I have found this to work correctly.
The problem I ran into occurs when a relation spans multiple fields. Surely such a relation is perfectly legitimate in a DataSet. In the application for which I am considering to use Aspose.Word we do not have autonumer or guid primary keys a lot. Our data has real life primary keys, that sometimes span three or four fields.
Say for instance that the Detail table has a two-field key. Say that its primary key includes the ‘relatedtopnummer’ field.
Then, the relation between table ‘SubDetail’ and table ‘Detail’ would we added to the dataset something like this:
ds.Relations.Add(new[] { child.Columns["topnummer"], child.Columns["detailnummer"] },
new[] { childDetail.Columns["topnummer"], childDetail.Columns["detailnummer"] });
In my original code, I did this in a strongly typed dataset, and I had explicit primary keys too.
But apparently it boils down to support for relations spanning multiple fields. It would appear that the MergeWithRegions feature does not support this…
Hello
Thank you for additional information. Most likely, there is something wrong with relationship inside your data source. I have tested with the following code and all works as expected:
Document doc = new Document("C:\\Temp\\in.doc");
doc.MailMerge.ExecuteWithRegions(GetNestedData());
doc.Save("C:\\Temp\\out.doc");
private static DataSet GetNestedData()
{
DataSet ds = new DataSet();
DataTable master = new DataTable("master");
master.Columns.Add("id1");
master.Columns.Add("MasterName");
master.PrimaryKey = new DataColumn[]
{
master.Columns["id1"]
};
DataTable parent = new DataTable("parent");
parent.Columns.Add("id1");
parent.Columns.Add("id2");
parent.Columns.Add("ParentName");
parent.PrimaryKey = new DataColumn[]
{
parent.Columns["id1"], parent.Columns["id2"]
};
DataTable child = new DataTable("child");
child.Columns.Add("id1");
child.Columns.Add("id2");
child.Columns.Add("id3");
child.Columns.Add("ChildName");
child.PrimaryKey = new DataColumn[]
{
child.Columns["id1"], child.Columns["id2"], child.Columns["id3"]
};
ds.Tables.Add(master);
ds.Tables.Add(parent);
ds.Tables.Add(child);
ds.Relations.Add(new DataColumn[]
{
master.Columns["id1"]
}, new DataColumn[]
{
parent.Columns["id1"]
});
ds.Relations.Add(new DataColumn[]
{
parent.Columns["id1"], parent.Columns["id2"]
},
new DataColumn[]
{
child.Columns["id1"], child.Columns["id2"]
});
Random rnd = new Random();
for (int i = 0; i <3; i++)
{
master.Rows.Add(new object[]
{
i,
string.Format("Master_{0}", i)
});
int parentCount = rnd.Next(2, 5);
for (int j = 0; j <parentCount; j++)
{
parent.Rows.Add(new object[]
{
i,
j,
string.Format("Name_{0}_{1}", i, j)
});
int childCount = rnd.Next(2, 5);
for (int k = 0; k <childCount; k++)
child.Rows.Add(new object[]
{
i,
j,
k,
string.Format("Child_{0}_{1}_{2}", i, j, k)
});
}
}
return ds;
}
Best regards,
Thanks very much for the work you put in. I just tried your code, and found it to work correctly.
I have been testing using strongly typed datasets, so I have relied on generated code for the fields, primary keys and relations. I don’t see anything wrong with the dataset design, but I will try and find out why it doesn’t work as expected. There must be an explanation…
Thanks again.
Hildo den Breejen
Found the problem! Merge field casing!
The casing of my innermost region’s StartTable and EndTable tags in my Word file was different from the casing of the actual tablename in the dataset.
Aspose.Word still finds the table, merges the rows of that table, but did not apply the relation. That’s why all rows of this table were merged repeatedly.
So, as it turns out, the data is retrieved from the dataset case-insensitively, but the relation is handled case-sensitively.
I would have preferred this to work consistently. Perhaps doing everyting case-insensitively would be the right thing to do.
I myself would not mind everything being done case-sensitively, and get an exception when the exact table is not found.
Hello
Thank you for reporting this problem to us and for the additional information. I managed to reproduce the problem on my side. Your request has been linked to the appropriate issue. You will be notified as soon as it is fixed.
Best regards,
The issues you have found earlier (filed as WORDSNET-4901) have been fixed in this .NET update and this Java update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.