asked on

Merging two queries for double-dynamic mail merge

Using CFDOCUMENT, I'm trying to creating a PDF thank-you letter to donors. Simply inserting the donor data into a message created within the CFDOCUMENT is no problem, but I'm trying to allow the client to modify the template dynamically as well. So basically we need to output the dynamic letter, and within the letter, output the dynamic donor data based on a URL.sort.

I've tried inserting #queryname.field# in the text of the dynamic letter (inside cfoutputs, of course), but the final PDF renders with the pound signs and not with the dynamic donor data.

Here is the CFDOCUMENT page I've been working with. Both queries work perfectly on their own. Any thoughts? Thanks!

<cfif isDefined("URL.sort")>
<cfparam name="URL.sort" default="1">
<cfquery datasource="#dsn#" name="qGetDonor">
      SELECT *
      FROM donors
      WHERE intTransID = #URL.sort#
</cfquery>
</cfif>

<cfquery datasource="#dsn#" name="qGetLetter">
      SELECT lettermessage
      FROM donorinfo
      WHERE ID = 1
</cfquery>

<cfdocument format="pdf">
<cfoutput>
#qGetLetter.lettermessage#
</cfoutput>
</cfdocument>

ASKER CERTIFIED SOLUTION

g127404

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ondeckstudio

ASKER

Those were the two options we'd considered as well, but were hoping for something more direct.

However we did implement the second idea and it has worked perfectly, so it's what we're sticking with. The code is copied down below:

<cfset stringtoreplace = qGetLetter.lettermessage>
<cfset find = "">
<cfset replace = "">

<cfset find = ListAppend(find, "InsertFirstName,InsertPaymentAmount,Insertdonorcity")>
<cfset replace = ListAppend(replace, qGetDonor.donorFirstName)>
<cfset replace = ListAppend(replace, DollarFormat(qGetDonor.paymentAmount))>
<cfset replace = ListAppend(replace, qGetDonor.donorCity)>

<cfoutput>
#Replacelist(stringtoreplace, find, replace)#
</cfoutput>

g127404

Thanks for the points, as well as posting the code you're using. That solution you're using is simple and works so stick with it. =-)