Merge with Regions Help

Aspose.Words Product Family
1

I’m attaching a sample output file and my template so you can see me issue. I have a template with a static area of merge fields at the top of the document. I then have 2 areas (callbacks and relationships) both wrapped in mergefield with start and end areas. My issue is, the callbacks and relationships areas should only load for the records which are responsible for each person. Each area is loading all records in the table for each person. In the example I attached, each person has one relationship and one callback, but you can see it put them both on each.

Dim Query As String = "SELECT CASE WHEN ContactTxt = ‘’ THEN ‘-’ ELSE ContactTxt END AS ContactTxt, CASE WHEN TitleTxt = ‘’ THEN ‘-’ ELSE TitleTxt END AS TitleTxt, CASE WHEN CompanyTxt = ‘’ THEN ‘-’ ELSE CompanyTxt END AS CompanyTxt, CASE WHEN ContactKeyYNTxt = ‘’ THEN ‘-’ ELSE ContactKeyYNTxt END AS ContactKeyYNTxt, CASE WHEN ContactCategoryTxt = ‘’ THEN ‘-’ ELSE ContactCategoryTxt END AS ContactCategoryTxt, case when len(ltrim(rtrim(REPLACE(REPLACE(PhoneDirectTxt,’-’,’’),’.’,’’))))=‘10’ then ‘’+SUBSTRING(REPLACE(REPLACE(PhoneDirectTxt,’-’,’’),’.’,’’),1,3)+’.’+’’+SUBSTRING(REPLACE(REPLACE(PhoneDirectTxt,’-’,’’),’.’,’’),4,3)+’.’+SUBSTRING(REPLACE(REPLACE(PhoneDirectTxt,’-’,’’),’.’,’’),7,4) else CASE WHEN PhoneDirectTxt = ‘’ THEN ‘-’ ELSE PhoneDirectTxt END end AS PhoneDirectTxt, PhoneDirectExtTxt, case when len(ltrim(rtrim(REPLACE(REPLACE(FaxDirectTxt,’-’,’’),’.’,’’))))=‘10’ then ‘’+SUBSTRING(REPLACE(REPLACE(FaxDirectTxt,’-’,’’),’.’,’’),1,3)+’.’+’’+SUBSTRING(REPLACE(REPLACE(FaxDirectTxt,’-’,’’),’.’,’’),4,3)+’.’+SUBSTRING(REPLACE(REPLACE(FaxDirectTxt,’-’,’’),’.’,’’),7,4) else CASE WHEN FaxDirectTxt = ‘’ THEN ‘-’ ELSE FaxDirectTxt END end AS FaxDirectTxt, case when len(ltrim(rtrim(REPLACE(REPLACE(PhoneGeneralTxt,’-’,’’),’.’,’’))))=‘10’ then ‘’+SUBSTRING(REPLACE(REPLACE(PhoneGeneralTxt,’-’,’’),’.’,’’),1,3)+’.’+’’+SUBSTRING(REPLACE(REPLACE(PhoneGeneralTxt,’-’,’’),’.’,’’),4,3)+’.’+SUBSTRING(REPLACE(REPLACE(PhoneGeneralTxt,’-’,’’),’.’,’’),7,4) else CASE WHEN PhoneGeneralTxt = ‘’ THEN ‘-’ ELSE PhoneGeneralTxt END end AS PhoneGeneralTxt, case when len(ltrim(rtrim(REPLACE(REPLACE(FaxGeneralTxt,’-’,’’),’.’,’’))))=‘10’ then ‘’+SUBSTRING(REPLACE(REPLACE(FaxGeneralTxt,’-’,’’),’.’,’’),1,3)+’.’+’’+SUBSTRING(REPLACE(REPLACE(FaxGeneralTxt,’-’,’’),’.’,’’),4,3)+’.’+SUBSTRING(REPLACE(REPLACE(FaxGeneralTxt,’-’,’’),’.’,’’),7,4) else CASE WHEN FaxGeneralTxt = ‘’ THEN ‘-’ ELSE FaxGeneralTxt END end AS FaxGeneralTxt, case when len(ltrim(rtrim(REPLACE(REPLACE(PhoneMobileTxt,’-’,’’),’.’,’’))))=‘10’ then ‘’+SUBSTRING(REPLACE(REPLACE(PhoneMobileTxt,’-’,’’),’.’,’’),1,3)+’.’+’’+SUBSTRING(REPLACE(REPLACE(PhoneMobileTxt,’-’,’’),’.’,’’),4,3)+’.’+SUBSTRING(REPLACE(REPLACE(PhoneMobileTxt,’-’,’’),’.’,’’),7,4) else CASE WHEN PhoneMobileTxt = ‘’ THEN ‘-’ ELSE PhoneMobileTxt END end AS PhoneMobileTxt, case when len(ltrim(rtrim(REPLACE(REPLACE(PhoneHomeTxt,’-’,’’),’.’,’’))))=‘10’ then ‘’+SUBSTRING(REPLACE(REPLACE(PhoneHomeTxt,’-’,’’),’.’,’’),1,3)+’.’+’’+SUBSTRING(REPLACE(REPLACE(PhoneHomeTxt,’-’,’’),’.’,’’),4,3)+’.’+SUBSTRING(REPLACE(REPLACE(PhoneHomeTxt,’-’,’’),’.’,’’),7,4) else CASE WHEN PhoneHomeTxt = ‘’ THEN ‘-’ ELSE PhoneHomeTxt END end AS PhoneHomeTxt, case when len(ltrim(rtrim(REPLACE(REPLACE(FaxHomeTxt,’-’,’’),’.’,’’))))=‘10’ then ‘’+SUBSTRING(REPLACE(REPLACE(FaxHomeTxt,’-’,’’),’.’,’’),1,3)+’.’+’’+SUBSTRING(REPLACE(REPLACE(FaxHomeTxt,’-’,’’),’.’,’’),4,3)+’.’+SUBSTRING(REPLACE(REPLACE(FaxHomeTxt,’-’,’’),’.’,’’),7,4) else CASE WHEN FaxHomeTxt = ‘’ THEN ‘-’ ELSE FaxHomeTxt END end AS FaxHomeTxt, CASE WHEN EmailURL = ‘’ THEN ‘-’ ELSE EmailURL END AS EmailURL, CASE WHEN REPLACE(WebURL,‘http://’,’’) = ‘’ THEN ‘-’ ELSE REPLACE(WebURL,‘http://’,’’) END AS WebURL, CASE WHEN Address1Txt > ‘’ THEN Address1Txt ELSE ‘-’ END AS Address1Txt, CASE WHEN Address2Txt > ‘’ THEN CHAR(10) + Address2Txt ELSE ‘’ END AS Address2Txt, CASE WHEN CityTxt > ‘’ THEN CHAR(10) + CityTxt ELSE ‘’ END AS CityTxt, CASE WHEN StateCode > ‘’ THEN StateCode ELSE ’ ’ END AS StateCode, CASE WHEN PostalCodeTxt > ‘’ THEN PostalCodeTxt ELSE ’ ’ END AS PostalCodeTxt, CASE WHEN CountryCode > ‘’ THEN CHAR(10) + CountryCode ELSE ‘’ END AS CountryCode, CASE WHEN ShippingAddress1Txt > ‘’ THEN ShippingAddress1Txt ELSE ‘-’ END AS ShippingAddress1Txt, CASE WHEN ShippingAddress2Txt > ‘’ THEN CHAR(10) + ShippingAddress2Txt ELSE ’ ’ END AS ShippingAddress2Txt, CASE WHEN ShippingCityTxt > ‘’ THEN CHAR(10) + ShippingCityTxt ELSE ’ ’ END AS ShippingCityTxt, CASE WHEN ShippingStateCode > ‘’ THEN ShippingStateCode ELSE ’ ’ END AS ShippingStateCode, CASE WHEN ShippingPostalCodeTxt > ‘’ THEN ShippingPostalCodeTxt ELSE ’ ’ END AS ShippingPostalCodeTxt, CASE WHEN ShippingCountryCode > ‘’ THEN CHAR(10) + ShippingCountryCode ELSE ’ ’ END AS ShippingCountryCode, ContactProfileMemo FROM ViewContact INNER JOIN ExportDetail ON ExportDetail.ExportPrimaryRecordID = ViewContact.ContactID WHERE ExportDetail.ExportID=’" & Request.QueryString("ID") & "’"

If qExportTemplateID = "24" Then
    Dim doc As New Document("d:\hart11\templates\contactbasicprofile.doc")
    Dim adapterTemplate1 As New SQLDataAdapter(Query, myConnection)
    Dim dtTemplate1 As New DataTable()
    adapterTemplate1.Fill(dtTemplate1)
    doc.MailMerge.Execute(dtTemplate1)
    doc.Save(SavePath)

ElseIf qExportTemplateID = "29" Then
    Dim doc As New Document("d:\hart11\templates\contactdetailedprofile.doc")

    'Callbacks
    Dim adapterTemplate2 As New SQLDataAdapter("SELECT ActivityTxt, ActivityMemo, ActivityDueDate, ResponsibleContactTxt FROM ViewActivity INNER JOIN ExportDetail ON ExportDetail.ExportPrimaryRecordID = ViewActivity.PrimaryRecordID WHERE ActivityStatusTxt <> ‘Terminated’ AND PrimaryRecordIDActiveYNTxt<>‘No’ AND (ActivityTypeTxt = ‘Callback’ OR ActivityTypeTxt = ‘Task’) AND ActivityActive <> ‘No’ AND ExportDetail.ExportID=’" & Request.QueryString("ID") & "’", myConnection)
    Dim dtTemplate2 As New DataTable()
    adapterTemplate2.Fill(dtTemplate2)
    dtTemplate2.TableName = "Callbacks"
    doc.MailMerge.ExecuteWithRegions(dtTemplate2)

    'Relationships
    Dim adapterTemplate3 As New SQLDataAdapter("SELECT Contact2ContactRelationshipTxt, InternalRelatedTo, InternalCompanyTxt, ContactContact2Memo FROM ViewRelationship INNER JOIN ExportDetail ON ExportDetail.ExportPrimaryRecordID = ViewRelationship.ContactID WHERE ContactContact2ActiveYNTxt=‘Yes’ AND ExportDetail.ExportID=’" & Request.QueryString("ID") & "’", myConnection)
    Dim dtTemplate3 As New DataTable()
    adapterTemplate3.Fill(dtTemplate3)
    dtTemplate3.TableName = "Relationships"
    doc.MailMerge.ExecuteWithRegions(dtTemplate3)
    Dim adapterTemplate1 As New SQLDataAdapter(Query, myConnection)
    Dim dtTemplate1 As New DataTable()
    adapterTemplate1.Fill(dtTemplate1)
    doc.MailMerge.Execute(dtTemplate1)
    doc.Save(SavePath)
2

Hi Ryan,
Thanks for your inquiry.
Could you please try debuging your code and check the contents of the dtTemplate2 and dtTemplate3 DataTables at runtime? I believe they most likely contain both entries, which points to a problem with the queries you are making to the database. If this is not the case could you please post your datasource that you are using for testing here?
You may also want to look into nested mail merge as described here. This could help you with what you are looking to do.
Thanks,

3

Yes, my datasets are loading all data for all records. That example you provided me used XML and XML relationships. I’m not using an XML datasource. Where can I define my relationships for each dataset? If I change the dataset to only get one, then it will miss the others. Do I have to loop through the table manually and run the region merge each time?

4

I really cannot figure this out so I decided to just generate an XML file and populate from that. How do I deal with inserting a picture from an XML file?

5

Hi

Thanks for your inquiry.

  1. No, you do not need to loop through the table manually and execute mail merge for each record. You should just use DataSet as data source for mail merge.
  2. Please follow the link to learn how to insert images during mail merge:

https://docs.aspose.com/words/net/types-of-mail-merge-operations/
You can use the same technique to insert images from XML.
Best regards,

6

Hi Ryan,
Thanks for this additional information.
Setting up a relation in XML and from a database is the same thing as both are being loaded into a DataSet object. When loading from XML the relations were automatically inferred from the structure, when loading from a database you may need to manually set your data relations.
Please see this post here which shows an example of how to set multiple relations between a table in a DataSet.
If you have any further troubles, please feel free to ask.
Thanks,

7

Thankyou for that link. I was almost done using string writer and spitting all my table info into XML format to then mimick your XML example. That post enabled me to do the following which is working just fine… Your support is what pushed me over the edge and I have after 30 days of playing with the product made the purchase. I love the product and the support response time! 19xx well spent IMO…

Dim doc As New Document("d:\hart11\templates\contactdetailedprofile.doc")
Dim mergeData As New DataSet()
Dim adapterTemplate1 As New SQLDataAdapter(Query, myConnection)
Dim dtTemplate1 As New DataTable()
adapterTemplate1.Fill(dtTemplate1)
dtTemplate1.TableName = "DetailedProfile"
mergeData.Tables.Add(dtTemplate1)
Dim adapterTemplate2 As New SQLDataAdapter("SELECT ActivityTxt, ActivityMemo, CONVERT(varchar,ActivityDueDate,101) AS ActivityDueDate, ResponsibleContactTxt, PrimaryRecordID FROM ViewActivity INNER JOIN ExportDetail ON ExportDetail.ExportPrimaryRecordID = ViewActivity.PrimaryRecordID WHERE ActivityStatusTxt <> ‘Terminated’ AND PrimaryRecordIDActiveYNTxt<>‘No’ AND (ActivityTypeTxt = ‘Callback’ OR ActivityTypeTxt = ‘Task’) AND ActivityActive <> ‘No’ AND ExportDetail.ExportID=’" & Request.QueryString("ID") & "’", myConnection)
Dim dtTemplate2 As New DataTable()
adapterTemplate2.Fill(dtTemplate2)
dtTemplate2.TableName = "Callbacks"
mergeData.Tables.Add(dtTemplate2)
Dim adapterTemplate3 As New SQLDataAdapter("SELECT ContactID, Contact2ContactRelationshipTxt, InternalRelatedTo, InternalCompanyTxt, ContactContact2Memo FROM ViewRelationship INNER JOIN ExportDetail ON ExportDetail.ExportPrimaryRecordID = ViewRelationship.ContactID WHERE ContactContact2ActiveYNTxt=‘Yes’ AND ExportDetail.ExportID=’" & Request.QueryString("ID") & "’", myConnection)
Dim dtTemplate3 As New DataTable()
adapterTemplate3.Fill(dtTemplate3)
dtTemplate3.TableName = "Relationships"
mergeData.Tables.Add(dtTemplate3)
Dim adapterTemplate4 As New SQLDataAdapter("SELECT ContactID, CategoryID, CategoryTxt FROM ViewGroups INNER JOIN ExportDetail ON ExportDetail.ExportPrimaryRecordID = ViewGroups.ContactID WHERE ExportDetail.ExportID=’" & Request.QueryString("ID") & "’ ORDER BY ContactCategoryID DESC", myConnection)
Dim dtTemplate4 As New DataTable()
adapterTemplate4.Fill(dtTemplate4)
dtTemplate4.TableName = "Groups"
mergeData.Tables.Add(dtTemplate4)
Dim adapterTemplate5 As New SQLDataAdapter("SELECT PrimaryRecordID, CommunicationSubjectTxt, CommunicationCreatedContactTxt, CONVERT(varchar,CommunicationCreatedDate,101) AS CommunicationCreatedDate2, REPLACE(CommunicationMemo,’’, CHAR(13)) AS CommunicationMemo FROM ViewCommunication INNER JOIN ExportDetail ON ExportDetail.ExportPrimaryRecordID = ViewCommunication.PrimaryRecordID WHERE CommunicationActiveYNTxt <> ‘No’ AND PrimaryRecordID<>‘0’ AND ExportDetail.ExportID=’" & Request.QueryString("ID") & "’ order by CommunicationCreatedDate desc", myConnection)
Dim dtTemplate5 As New DataTable()
adapterTemplate5.Fill(dtTemplate5)
dtTemplate5.TableName = "Communications"
mergeData.Tables.Add(dtTemplate5)
mergeData.Relations.Add(dtTemplate1.Columns("ContactID"), dtTemplate2.Columns("PrimaryRecordID"))
mergeData.Relations.Add(dtTemplate1.Columns("ContactID"), dtTemplate3.Columns("ContactID"))
mergeData.Relations.Add(dtTemplate1.Columns("ContactID"), dtTemplate4.Columns("ContactID"))
mergeData.Relations.Add(dtTemplate1.Columns("ContactID"), dtTemplate5.Columns("PrimaryRecordID"))
doc.MailMerge.ExecuteWithRegions(mergeData)
doc.Save(SavePath)
8

Hi Ryan,
Thanks for that postive feedback regarding Aspose.
If you have any further inquiries, please feel free to ask.
Thanks,