Merging using Parent-child relationships in reverse?

Aspose.Words Product Family
1

Hi

I just wanted to find out if it would be possible to almost do a reverse mailmerge based on information that is in the child table and not starting with the parent table.

What I would like to display in the final document is (or to replace the Code field with a Symbol in the 2nd column below):

Passenger 1 ZAR 2000
USD 50
Passenger 2 ZAR 2000
USD 50
Passenger 3 ZAR 3000
USD 100
Passenger 4 ZAR 4000
USD 200

I have a QuotationPassengers table that has Primary Key: QuotationPassengerId and a column for the name.

I also have a Currencies table made up as follows (this is to be used as a reference table for the table below, but it is also the “Parent” table?):
Parent Table : Currencies
Primary Key: CurrencyId
Other columns: Code, Symbol

The data could look as follows:

<span style=“mso-bidi-font-family:“Times New Roman”” lang=“EN-US”>CurrencyId Code Symbol
1 ZAR R
2 USD $
3 EUR €

I have a table PassengerTotals
Primary Keys: QuotationPassengerId, CurrencyId (I have created a relationship between the QuotationPassengerId and the QuotationPassengers table, and created a separate relationship between CurrencyId and the Currencies table)
Other Columns: Amount
Data eg:

QuotationPassengerId CurrencyId Amount
1 1 2000
2 1 2000
3 1 3000
4 1 4000
1 2 50
2 2 50
3 2 100
4 2 200

I’ve currently managed to correctly show the Passenger Name and amounts field by using the following:
«TableStart:QuotationPassengers»

«Name»
«TableStart:PassengerTotals» «Amount»«TableEnd:PassengerTotals»

«TableEnd:QuotationPassengers»

As you can see I’ve put a nested table inside 1 column to be able to just show Passenger 1 once.
My problem at the moment is that I can’t see how to display the Code or Symbol fields into the above.
I’ve tried adding the TableStart:Currencies, field Code and TableEnd:Currencies to the above nested table, but that just gives a list of all the Currencies, ie, next to the ZAR amount of 2000, I would see the all the currencies ZAR, USD, EUR listed as the code.

Is there actually any way to implement this so that it is dynamic as possible and I am able to switch between the Code and Symbol field easily?

Thank you.

Kind regards,
Kamla

2

Hi Kamla,

Thanks for your query. It would be great if you please share your template document along with the SQL script of database tables.

3

Hi Tahir

Please find attached a sample project that I have created.

The template does not have the field for the code/symbol filled in since I can’t figure out how to do that.

Thank you.

Regards,
Kamla

4

Hi Kamla,

Thanks for sharing the details. I have modified your template document and have attached it with this post. I have also attached the output file with this post. Please use the modified template.doc file. Hope this helps you.

Please let us know if you have any more queries.

5

Hi Tahir

I took a look at the output file you posted.

I think you missed the basic fact that I do not want to show an EUR row if there is no amount attached to it. This was just a sample of the currencies I have in my DB, add about 10 more to the list and you’ll see that the display gets even worse.

In order for the EUR row to show, please copy this line of code in to add a EUR amount to Passenger 2. In terms of what I am trying to achieve, the only passenger that should display an EUR row is Passenger 2. Passenger 1 & Passenger 3 should only show ZAR and USD rows.

dsReport.PassengerTotals.AddPassengerTotalsRow((DSReport.QuotationPassengersRow)dsReport.QuotationPassengers[1], (DSReport.CurrenciesRow)dsReport.Currencies[2], 30);

Please can you take another look and let me know if there is anyway to achieve what I am looking for?

Thank you.

Kind regards,
Kamla

6

Hi Kamla,

Thanks for sharing the information. I have modified your code and template document. Please find the code and template document in attachment. Hope this helps you. Please let us know if you have any more queries.

class AsposeMailMerge : IFieldMergingCallback
{
    DataTable Currencies = new DataTable();

    public AsposeMailMerge(DataTable dt)
    {
        Currencies = dt;
    }

    void IFieldMergingCallback.FieldMerging(FieldMergingArgs args)
    {
        if (args.FieldName == "CurrencyId")
        {
            DocumentBuilder builder = new DocumentBuilder(args.Document);
            builder.MoveToMergeField(args.FieldName);
            DataRow[] dr = Currencies.Select("CurrencyId = " + args.FieldValue.ToString());
            builder.Write(dr[0]["Code"].ToString());
        }
    }
            
    /// 
    /// This is called when mail merge engine encounters Image:XXX merge field in the document.
    /// You have a chance to return an Image object, file name or a stream that contains the image.
    /// 
    void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs e)
    {
    }
}
7

Hi there,

Thanks for your inquiry.

You can still merge this data directly using mail merge and without using any merge handler. You just need to rework the relations between the different data tables.

I have made some changes to your project and attached it to this post. The output should now be correct. Note that I only did a quick rework of the relations so the code just uses direct ids. You may wish to properly implement these relations.

Thanks,

8

Hi Adam

Thank you very much, that is working exceptionally well.

I’ve come across another query in relation to this though, is there any way to sort the records according to the currency code?

Ie. at the moment, for all the passengers, the currencies display in the reverse order.

I can’t really change the fill method in my real application for PassengerTotals to order it correctly since that is handled by a separate function and is based on a loop through a number of other tables which does not have access to the Currencies table, so this would need to be done somehow after Currencies is filled?

Any assistance would be appreciated.

Thank you and keep well.

Kind regards,
Kamla

9

Hi Kamla,

Please accept my apologies for late response. Please add the records in Currencies table as shown below.

dsReport.Currencies.AddCurrenciesRow(0, "EUR", "€");
dsReport.Currencies.AddCurrenciesRow(1, "USD", "$");
dsReport.Currencies.AddCurrenciesRow(2, "ZAR", "R");

You can also sort the data by using DataTable.DataView.Sort property.

dsReport.Currencies.AddCurrenciesRow(0, "ZAR", "R");
dsReport.Currencies.AddCurrenciesRow(1, "USD", "$");
dsReport.Currencies.AddCurrenciesRow(2, "EUR", "€");
dsReport.Currencies.DefaultView.Sort = "code";
DataTable currencies = dsReport.Currencies.DefaultView.ToTable();
dsReport.Currencies.Rows.Clear();
foreach (DataRow row in currencies.Rows)
{
    dsReport.Currencies.ImportRow(row);
}

Hope this answers your query. Please let us know if you have any more queries.