MailMerge with relational data

I am a complete newb to mail merging, so forgive me if this is an obvious question.

I have a DataSet containing a couple of tables and a DataRelation on the two tables tying them together in a one to many relationship (it is actually a company table and a contact table with a company ID foreign key).

Now I need to be able to mail merge with this DataSet. I can see that there is a MergeWithRegions method off the MailMerge object that takes a DataSet, and I’ve followed the example in the documentation, but I cannot get any output in my document.

This leads me to believe that there could be a problem with the field names in the document itself. I’ve tried uniquely naming my columns in my DataSet and having those as field names in the document but I always only get the base document with the field names in place, not replaced on the merge.

Can anyone help?

So, if I have a table called Company and a column called Name, and a table called Contact and a column called Name (or these can be unique as well), what should I call my field names to ensure that when I run MergeWithRegions that merge is successful.

Thanks,

Martin

Hey Martin,

Thanks for your question, and no worries regarding its obviousness. You are right, you should certainly call the ExecuteWithRegions method (I’m sure you meant it by MergeWithRegions ) but your template should have been prepared appropriately before execution. The essential thing is that each repeatable region should be enclosed between two special merge fields named TableStart:MyTable and TableEnd:MyTable where MyTable is the name of the table to fetch data from. The merge fields inside the defined regions must refer to table columns as usual.

For more info, please see this topic:

https://docs.aspose.com/words/net/mail-merge-and-reporting/

Feel free to post your further questions here

Dmitry,

Many thanks, I’ve got it working in a sense, just not the sense I was expecting I guess.

For instance, if I have two companies, and each company has two contacts, I would expect four letters, assuming my template is a letter.

If I use the TableStart and TableEnd, I cannot see a way of looping over the Company table and getting the Contact data. Like I said before, I know nothing about MailMerge, so…

So, my further question would be how can I go about templating something like:

Dear <> at <>…

and have that produce 4 letters with the data relationship followed appropriately through the Company table to the Contact table.

Sorry, I know this is kind of a Word support question rather than specific to the component, but I’m at a loss looking this up.

In case you’re wondering why I cannot just open up the structure in to a nice flat structure rather than related tables, it is because my actual structure is a Company table and around five related tables, all with keys to company, and some with links between themselves. If this is simply not possible because of the limitations of the mail merge functionality in Word, then fair enough I guess.

Cheers,

Martin

Merging master-child related tables is slightly more complicated task, we’re going to simplify it later. Take a look at the Product Catalog demo supplied with the Aspose.Words package, it shows the idea. The main steps are:

  1. Create an empty destination Document (clone the source template and remove the contents).
  2. Loop through all rows of the master table.
  3. In each iteration, create a copy of the template.
  4. Execute simple mail merge process to fill the copied template with the data from the master row.
  5. Retrieve the child table.
  6. Execute mail merge with regions to fill the copied template with the data from the child table.
  7. Append the copy of the template to the destination document.
  8. Repeat steps 3-7 until all master rows are processed.

So please have a look at the code of the Product Catalog demo which illustrates all above steps. I’ll be happy to answer your further questions if you have any.

Dmitry,

Again, many thanks. I’ve taken a look and I’m not sure I will be able to get a good match functionality wise.

In essence, we have to provide a merge facility where a user can browse to their own merge document, and the idea has been to provide them with a list of field codes for the various items in the available data set.

Adding the TableStart/TableEnd functionality is a bit of a training issue.

With that in mind, would it just be better to explode open all the data in to a flat structure, and if so, is there an easy way to eliminate duplicate items from a merge?

Thanks,

Martin

Sorry I don’t fully understand what duplicate items you mean. If you don’t want to deal with the code handling master-child relations shown in the Product Catalog demo AND if you are able to put all the data into one table, the best way to produce reports is still using the merge regions. So if you have a resulting DataTable with the following structure:

Company Contact …

Company1 Contact1 …

Company1 Contact2 …

Company2 Contact1 …

Company2 Contact2 …

and if you run ExecuteWithRegions, it will create a copy of the region for each table row and fill it with the data from the corresponding row. No duplications make sense here.

You also mentioned the scenario with providing the user with a list of field codes, is it the same scenario with the Company/Contact related tables?

Dmitry,

I will try the with regions approach and see how I get on.

Essentially my data is along the lines of:

Company data

Contact data (many to 1 with Company)

Location data (many to 1 with Company)

Contact Location data (many to many with Location and Contact)

Project data (many to 1 with Company)

Project task data (many to 1 with Project)

Task data (many to 1 with Company)

As you can see it’s pretty complex! My thoughts originally were to provide a list of fields that could be used as merge fields in a document, so <>, <>, <>, that sort of thing. The possibility of duplicates only really arises because of the fact that locations exist within a company, as do contacts, but there can also be a relationship between a location and a contact. I’ve not followed it all through, so actually there may be no duplicate problem, but it strikes me there might be.

So, if a user was to load a template something like:

<>

<>

<>

<>

Dear <>,

Hiya, your Projects are:

<>

<>

Your tasks are:
<>

Thanks,
The Team

In this scenario, I would hope to produce letters containing the company name, and the location details for the contact (via Contact Location data, not via Company - Location - Contact which would produce too many records), and have multiple project task regions for the project, and multiple tasks. Of course if no Contact within a Company had any relationships with Location via the Contact Location data, I may not want to produce anything, or I may want to produce a letter for each contact at the primary location for the company.

Like I say, pretty complex. If this is just beyond the bounds of the functionality within Word itself, then fair enough, I’ll tell the team here. Likewise with Aspose.Words, as at least that way we can get to a point where something is available.

Is that any clearer?

Many thanks for you help so far.

Martin

Thanks for explanation Martin, it’s now clearer indeed. However, I still can’t see any better way to accomplish your task using Aspose.Words than combining a document from several preliminarily merged pieces - similar to the way demonstrated in the Product Catalog demo. It’s really not that trivial because you have more than two related tables - but is possible I’m sure. I will consult with my colleagues, maybe we will succeed to find a simpler solution for you.

Dmitry,

As ever, many thanks.

I am having some joy by using the Product Catalog sample and enumerating over rows and using those rows with Execute rather than ExecueWithRegions.

It may mean having to make any field code list, or any documentation that accompanies the application a little bit more in depth, but that’ll be fine.

If you do have any other thoughts, then I’d be glad to here them.

Cheers,

Martin

Dmitry,

I think I have a sort of solution using a combination of Execute, ExecuteWithRegions (based on DataViews for repeating data) and MailMerge.GetFieldNames().

Using the latter it is possible to see what data there is, and I can then decide how to get my data out of my DataSet, and then call Execute or ExecuteWithRegions as necessary. I’ve done it with some basic stuff, and I’m optimistic it’ll give me enough flexibility as the relationships between the tables is strict so only certain types of data would naturally go together anyway in a document following the same strict rules.

I’m goiing to plug away with this approach, but as I said if you do come up with any other approach I’d be interested to hear it as it’s going to be a fairly mammoth task to code my approach up!!

Cheers,

Martin

Martin,

After consulting with the rest of the team I concluded that the solution you’ve come up with by the moment is the best. I agree it might take a while to implement it, taking into account that you deal with a complex table relationship. However, meanwhile there’s no simple way to accomplish your task.

We are going to simplify the process of merging related tables later. We are planning to implement nested merge regions, it’s a high priority feature in our list. I am sure it will greatly simplify complex tasks like yours.

Thank you for understanding.

The issues you have found earlier (filed as 39) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.
(69)