I can’t figure our how I am supposed to get a detail row whan I have multiple Orders with Items.
Scenario:
1.I have 2 orders each having 2 order lines (say Table called Items).
2.I load it from xml into a .Net DataSet and end up with two tables: Orders and Items.
Orders table has 2 rows and Items has 4. They are joined on a foreign key. Data is all consistent.
3. I use a word template like: ( I skipped all TableStart mergefields here):
Order Date: xxx
Oreder Total: xxx
Item price quantity Total for Item
-----------------------------------
Coke $1 2 $2
4 seasons pizza $10 1 $10
- Now Mailmerge.ExecuteWithRegions(myDataSet) works CORRECTLY for both orders.
PROBLEM:
IFieldMergingCallback.FieldMerging(FieldMergingArgs e) is called 4 times with tableName == ‘Items’ - which is what I expect.
Problem is that FieldMergingArgs.RecordIndex passed in is repeated:
I get
0, 1 and then 0,1 again.
if I try using Items DataTable.Rows to get the data I need from the rows, I actually need
FieldMergingArgs.RecordIndex to be
0,1 for first order and
2,3 for second.
The reason I need this is that I need to have the whole Items row, not just the field value (I know I can get this from FieldMergingArgs.Value). I think it is quit a sensible thing to ask for and I really can’t make my application to work without haveing access to the DataRow.
IMHO an object member like FieldMergingArgs.RowInstance would solve it for me and everybody (it can be a custom object or a DataRow depending on type of datasourse used)- I guess a change like this (adding a new property) is not going to break any code and would be easily done in a minor release?
Is there any other way to get the correct Items Row in ths scenario?
Hope I am explaining this right.
William W.
Hello.
Thanks for your request. Could you please attach you input and output documents here for testing?
I will check them and provide some feedback.
Best regards,
Hi
Thanks for your request. I think, the following article should answer some of your questions:
https://docs.aspose.com/words/net/nested-mail-merge-with-regions/
Best regards,
Attached are data (XML) and simple template.
If you use a merheField handler then you will see the recordindex return
as 0,1 for first order and 0,1 for second. This means I can’t get the
Item row I am on when I am trying to do my custom merging logic for a
particular row:
i.e. I can’t get row by using:
myDS.Tables[“Items”].Rows[e.RecordIndex]
For second Order line above returns Items from first order.
Thanks
alexey.noskov:
Thanks for your request. I think, the following article should answer some of your questions:
https://docs.aspose.com/words/net/nested-mail-merge-with-regions/*
No this does not help - there is no working example there on how to get detail Row using e.RecordIndex in the case described in my Post.
Note that MailMerge.ExecuteWithRegions does work correctly even in the case of 2 orders with different Iteems. This is not the problem.
I also get CORRECT value from e.FieldValue field.
The problem is - How do I get a column from Merge row which does not show on my template but I need it for my custom logic.
Simple examples would be:
1)Say I might have a bool column like “Item.TodaysSpecial” and want to use Bold font for column Item.Name when customer ordered “Today’s Special Pizza” - how do I get value of my TodaysSpecial column for second order?
2)Or using current data: say I want Item.Name to appear bold when Item.Price >5.00.
Again for second + order(s) using myDS.Tables["Items].Rows[e.RecordIndex] will return wrong row - it will be row from the very first row, so myDS.Tables["Items].Rows[e.RecordIndex]["Price"] is wrong price.
---------------------
Now I do not know myOrder_Id at this point - they only way for me to get to it would be to save it when a column from Order (master) row is being merged by using e.RecordIndex when an order column is merged.
BUT this is chicken and egg problem - what if I have 3 level master-detail1-detail2 and order is detail1? I.e. I have tables OrdersByDay - Order-Items and genereate a report for several OrderByDays’ rows (i.e. for a week)?
Anyway, I don’t think that filtering my Items table DataRows on Order_id (if I can get it) using a DataView and then indexing into result using e.RecordIndex is guaranteed to give me the correct row as order after DataView filtering is probably undefined?
May be doing a DataView with filter and ordering on Item_Id will work- I guess ExecuteWithRegions merges in order of Item_ids?
-----
The longer I think about it the more confident I am about my original suggestion:
an new object member of FieldMergingArgs like:
class FieldMergingArgs
{
....
public obj FieldMergingArgs.RowInstance get
{}
...}
would solve it for me and everybody (it can be anything - custom object or a DataRow - depending on type of datasourse used).
I guess a change like this (adding a new property) is not going to break any existing code and would be easily done in a minor release?
And obviously Aspose will have no trouble of setting this value before call to FieldMerging method.
Hi
Thank you for additional information. But it is not quite clear for me why you need this complex custom logic if to fill your template it would be enough to execute mail merge with regions. Please see the following code:
// Read XML into DataSet
DataSet ds = new DataSet();
ds.ReadXml(@"Test001\CustomerData.xml");
// open template and execute mail merge with regions.
Document doc = new Document(@"Test001\Orders.doc");
doc.MailMerge.ExecuteWithRegions(ds);
doc.Save(@"Test001\out.doc");
I attached the output document produced by this code. I used your template and your data source for testing.
If the output does not looks as you expected, could you please attach the desired output here?
Best regards,
Hi William,
Thanks for your inquiry.
The behaviour you are observing is not a bug, this is just how the mail merge engine works. The RecordIndex corresponds to the current index of the data relative to its parent, not the index of the DataTable from your data source. This means if there is a parent table then the record index for each child table will restart with each parent row.
We will look into providing a property in FieldMergingArgs so you can retrieve the full data of the current record being merged. In the mean time you can still achieve what you are looking for, please see the code examples below. There are two techniques to choose from.
The first technique simply remembers the paragraph that you want to work with for when the column you are looking for appears in the output. Please note for this to work there must be a TodaysSpecial field found in the document. However this field is only used for implementing your logic and will not be merge with any data. See args.Text ="";
public class HandleFieldMerging : IFieldMergingCallback
{
Paragraph mNameParagraph;
public void FieldMerging(FieldMergingArgs args)
{
if (args.TableName == "Item")
{
if (args.FieldName == "Name")
mNameParagraph = args.Field.Start.ParentParagraph;
if (args.FieldName == "TodaysSpecial")
{
foreach(Run run in mNameParagraph.Runs)
run.Font.Bold = true;
args.Text = "";
}
}
}
public void ImageFieldMerging(ImageFieldMergingArgs args)
{
// Do Nothing
}
}
The second technique attempts to find the data row by searching through the relations of the DataSet. You need to pass your datasource to this handler when you first create it.
public class HandleFieldMergingField: IFieldMergingCallback
{
public DataSet mDataSet;
private Hashtable tableIndices = new Hashtable();
public HandleFieldMergingField(DataSet dataSet)
{
mDataSet = dataSet;
}
public void FieldMerging(FieldMergingArgs args)
{
// This method needs to be called with each field merged.
UpdateCurrentRecords(args);
// This should get the correct DataRow from the current record.
DataRow row = GetDataRow(args);
}
public void ImageFieldMerging(ImageFieldMergingArgs args)
{
// Do Nothing
}
///
/// Stores the current index for all table names during mail merge.
///
private void UpdateCurrentRecords(FieldMergingArgs args)
{
if (tableIndices.ContainsKey(args.TableName))
tableIndices.Remove(args.TableName);
tableIndices.Add(args.TableName, args.RecordIndex);
}
///
/// Retrieves the current row from the data source of the current record.
///
private DataRow GetDataRow(FieldMergingArgs args)
{
DataTable currentTable = mDataSet.Tables[args.TableName];
// If there are no parent relations then return the current row.
if (currentTable.ParentRelations.Count == 0)
return mDataSet.Tables[args.TableName].Rows[args.RecordIndex];
// Find all parent relations starting from this table.
ArrayList relations = new ArrayList();
while (currentTable.ParentRelations.Count != 0)
{
DataRelation parentRelation = mDataSet.Tables[args.TableName].ParentRelations[0];
relations.Insert(0, parentRelation);
currentTable = parentRelation.ParentTable;
}
// Search down the relations using the current index of each table.
DataTable parentTable = ((DataRelation) relations[0]).ParentTable;
DataRow currentRow = parentTable.Rows[(int) tableIndices[parentTable.TableName]];
foreach(DataRelation relation in relations)
currentRow = currentRow.GetChildRows(relation)[(int) tableIndices[relation.ChildTable.TableName]];
return currentRow;
}
}
If we can help with anything else, please feel free to ask.
Thanks,
Thanks for yuor help Adam,
1.I need the whole row because my customer requires it. I am writing a program allowing complicated logic on merge which requires use of any column from my dataset even if it is not a MergeField. These are customer requirements as they want their template to be as flexible as possible.
3.Yes the merged result is correct in my simple example. To demonstrate my problem I gave you some examples of what I need to implement in my previous post. I also need to be able to do it for any dataset and any table. So bottom line is - I need the details correct details row. Hope this answers your first question.
2.Re: workarounds:
2.1.First approach does not help me as I can’t have all the columns as mergeFields (and I need all columns at all times).
2.2.Second approach - need to have a better look and test.
Thanks for your help.
William
Looked at your second technique:
1)It works for 2 level mater<-Detail structure (Order<-Items).
2)I think it breaks for 3+ level structure (Master<-Detail1<-Detail2).
I think RecordIndex of second level (Detail1) will return wrong master (Detail1) for 3rd level row (Detail2)
Not sure if there is a way to fix this technique to work for any mumber of levels. I suspect there may be…
3)When do you think the extra property or such can be added? My Project Manager is on my Back :-(.
Thanks again
You have great product there guys,
Long Live Aspose!
William.
Hi
Thank you for additional information. I also would like to propose the third possible approach that you can use. Maybe in your case you can use IMailMergeDataSource:
https://reference.aspose.com/words/net/aspose.words.mailmerging/imailmergedatasource/
In IMailMergeDataSource in GetValue method, you can get access to the whole object that represents the current row in the data source.
Best regards,
Thanks Alexey.
I think I like this method best.
I guess I will need to:
-
implement my own (DataSet based) tree IMailMergeDataSource which will support root table and multiple child tables; I will need to provide a kind of Stack to keep current row on each level of my dataset tree as merging progresses;
I will need to implement the MyDataSource.GetChildDataSource(string tableName) method to get child datasources (MyDataSources) created dynamically. I assume that Aspose will call this method once for each master row - i.e. for each row in order table in my Order<-Items example.
-
in the MyDataSource class then I can have a public method getCurrentObject to get the current row; no parameters will be required.
-
I can then pass MyDataSource to my FieldMerging handler class in constructor;
-
when FieldMerging handler method is called by Aspose (from Mailmerge.ExecuteWithRegions) I can then call my method MyDataSource.GetCurrentObject() to get the currently merged row for the currenl level (which can be Items level).
Is all above basically correct?
An example of how to implement such a generic multi-level MyDataSource based on a tree-like DataSet with one root table would help - do you think such an example exists / can be provided by Aspose support?
Thanks a lot for an excellent idea - I think this is going to work.
William.
Hi
Thanks for your request. . I created a simple code example for you. Hope it could be useful:
[Test]
public void Test001()
{
// Create a dummy datasource.
List <Order> orders = new List <Order> ();
for (int i = 0; i <10; i++)
{
Order order = new Order(DateTime.Now, i, "test address", "test", "blabla", "11-11-11", i + 10);
for (int j = 0; j <5; j++)
{
OrderItem item = new OrderItem(string.Format("item{0}-{1}", i, j), i, j, i * j);
order.Item.Add(item);
}
orders.Add(order);
}
// open template
Document doc = new Document(@"Test001\in.dotx");
// For each order recor we execute a simpel mail merge.
foreach(Order order in orders)
{
// Clone tempalte.
Document temp = (Document) doc.Clone(true);
// Create a subdatasource.
List <Order> subData = new List <Order> ();
subData.Add(order);
MailMergeDataSource orderDs = new MailMergeDataSource(subData, "Order");
// Execute simpel mail merge
temp.MailMerge.Execute(orderDs);
// Now create anothe datasource and execute mail merge with regions.
MailMergeDataSource itemDs = new MailMergeDataSource(order.Item, "Item");
temp.MailMerge.ExecuteWithRegions(itemDs);
// Save the document.
temp.Save(string.Format(@"Test001\out_{0}.docx", order.Number));
}
// Save output.
doc.Save(@"Test001\out.doc");
}
private class Order
{
public Order(DateTime date, int number, string address, string suburb, string city, string phonenumber, int total)
{
mDate = date;
mTotal = total;
mPhonenumber = phonenumber;
mCity = city;
mSuburb = suburb;
mAddress = address;
mNumber = number;
}
private DateTime mDate;
private int mNumber;
private string mAddress;
private string mSuburb;
private string mCity;
private string mPhonenumber;
private int mTotal;
private List<OrderItem> mItem = new List<OrderItem>();
public int Total
{
get
{
return mTotal;
}
}
public string Phonenumber
{
get
{
return mPhonenumber;
}
}
public string City
{
get
{
return mCity;
}
}
public string Suburb
{
get
{
return mSuburb;
}
}
public string Address
{
get
{
return mAddress;
}
}
public int Number
{
get
{
return mNumber;
}
}
public DateTime Date
{
get
{
return mDate;
}
}
public List<OrderItem> Item
{
get
{
return mItem;
}
}
}
private class OrderItem
{
public OrderItem(string name, int price, int quantity, int itemTotal)
{
mName = name;
mItemTotal = itemTotal;
mQuantity = quantity;
mPrice = price;
}
public int ItemTotal
{
get
{
return mItemTotal;
}
}
public int Quantity
{
get
{
return mQuantity;
}
}
public int Price
{
get
{
return mPrice;
}
}
public string Name
{
get
{
return mName;
}
}
private string mName;
private int mPrice;
private int mQuantity;
private int mItemTotal;
}
///
/// A custom mail merge data source that you implement to allow Aspose.Words
/// to mail merge data from LINQ query results into Microsoft Word documents.
///
public class MailMergeDataSource: IMailMergeDataSource
{
///
/// Creates new instance of a custom mail merge data source
///
/// Data returned from a LINQ query
/// Name of the data source is only used when you perform mail merge with regions.
/// If you would like to use simple mail merge then use constructor with one parameter.
public MailMergeDataSource(IEnumerable data, string tableName)
{
mEnumerator = data.GetEnumerator();
// Name of the data source is needed when you perform mail merge with regions
mTableName = tableName;
}
///
/// Aspose.Words call this to get a value for every data field.
///
public bool GetValue(string fieldName, out object fieldValue)
{
fieldValue = GetFieldValue(fieldName);
return fieldValue != null;
}
public IMailMergeDataSource GetChildDataSource(string tableName)
{
IEnumerable childData = GetFieldValue(tableName) as IEnumerable;
if (childData != null)
return new MailMergeDataSource(childData, tableName);
return null;
}
///
/// Moves to the next record in the collection.
///
public bool MoveNext()
{
// Move enumerator to next record
bool hasNexeRecord = mEnumerator.MoveNext();
if (hasNexeRecord)
{
mCurrentObject = mEnumerator.Current;
}
return hasNexeRecord;
}
///
/// The name of the data source. Used by Aspose.Words only when executing mail merge with repeatable regions.
///
public string TableName
{
get
{
return mTableName;
}
}
private object GetFieldValue(string fieldName)
{
// Get type of current record
Type curentRecordType = mCurrentObject.GetType();
// Use reflection to get property by name and its value
PropertyInfo property = curentRecordType.GetProperty(fieldName);
if (property != null)
{
object value = property.GetValue(mCurrentObject, null);
if (value is byte[] && ((byte[]) value).Length == 0)
value = null;
return value;
}
else
{
// A field with this name was not found,
// return false to the Aspose.Words mail merge engine.
return null;
}
}
private IEnumerator mEnumerator;
private object mCurrentObject;
private string mTableName = string.Empty;
}
Best regards,
Thanks for this example.
This approach pretty much worked for me.
Long Live Aspose!