Avatar of ZKM128
ZKM128
 asked on

Using <CFIF inside <cfmail <cfoutput

Hello,

I am sending notification emails to members in an organisation using <cfmail> in ColdFusion.
The cfm page is reading member records in the database and sending emails to members using the database field values FirstName, LastName, emailAddress, Age etc.
The email message that members receive should show different fees/prices depending on their age.
So, basically what I need to do is, read member's age from database records, calculate appropriate fees and display on the email message generated by ColdFusion.
I am trying to use <cfif> to determine/set correct fees but I don't know the correct syntax.

<cfquery name="qryMembers" datasource="cfdsnMembers">
SELECT FirstName, LastName, emailAddress, Age FROM MembersTable
</cfquery>

<cfmail query = "qryMembers" from="Rainbow Club <info@rainbowclub.com>" to="#FirstName# #LastName# <#emailAddress#>" Subject="Half yearly membership fee" type="html">

<p>Dear #FirstName# #LastName#,</p>
<p>The amount of your half yearly membership fee is: 

<cfif #Age# < 25>$100<cfelse  25 <= #Age# < 55>$120</cfif>

<p>

</cfmail>

Open in new window


The code shown above causes error.
Please show me the correct way of writing <cfif> conditional codes inside the <cfmail> tag.

Thank you.
ColdFusion Language

Avatar of undefined
Last Comment
_agx_

8/22/2022 - Mon
SOLUTION
gdemaria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ZKM128

ASKER
I am still getting error "Invalid CFML construct found "...

This line <cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif> is still causting the error.

Am I allowed to type <cfif> tag in normal way inside the <cfmail> tag? Or is there another way to type the <cfif    and <cfelseif tags inside <cfmail> tag?
SOLUTION
gdemaria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ZKM128

ASKER
When I delete the code <cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif> and send the email, the emails are sent OK.
So, I think <cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif> is definitely causing the error.

Here's the code I have used to send test emails:
<cfquery name="qryMembers" datasource="cfdsnMembers">
SELECT FirstName, LastName, emailAddress, Age FROM MembersTable
</cfquery>

<cfmail query="qryMembers" from="info@rainbowclub.com" to="#emailAddress#" subject="CFMail CFIF conditional statement testing" type="html">

<p>Dear #FirstName# #LastName#,</p>
<p>Your age is 
<cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif>
</p>

</cfmail>

Open in new window

ASKER CERTIFIED SOLUTION
gdemaria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ZKM128

ASKER
I'm sorry, I stupidly forgot to change the data type of the 'Age' field in my testing database table  to number/integer (int) format and left it as string (nvarcha) format and that was causing the error.
Thank you very much for your help and for letting me know to use lt & lte instead of < & <=/
Your help has saved me hundreds of hours of internet surfing.
fblack61
_agx_

> <cfif qryMembers.age lt 25>$100<cfelseif qryMembers.age lt 55>$120</cfif>

So membership should be free for those 55 and over? :)  Just confirming you do not need a default "cfesle" case.
ZKM128

ASKER
We will be offering different membership fee for members aged more than 55 but at the moment the membership fee hasn't been decided yet. I will need to modify the codes a bit later. Thank you very much for your help experts :-)
gdemaria

Just to add to the topic of best practices, I (and likely agx as well) would suggest keeping your fees inside of a database table instead of hard coding,   It will make it a lot easier to manage if you use the fee in different places and if they ever change.

Table structure...

FromAge   ToAge     Fee
      0               25         $100
    26               55         $120
    56                             ??
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
_agx_

Yep.  Then you can use a simple JOIN to get the current "Fee".  If you want to leave the upper limit of the 55+ null, something like:

SELECT m.FirstName, m.LastName, m.emailAddress
FROM   MembersTable m LEFT JOIN MembershipFees fee
      ON m.Age BETWEEN fee.FromAge AND fee.ToAgee
           OR m.Age >= fee.FromAge and fee.ToAge IS NULL

Though if the ranges are under 25, and under 55, the ranges should be:

FromAge   ToAge     Fee
      0               24         $100                <=== under 25
    25               54         $120                <=== 25 and over, but under 55 years of age
    55              (null)         ??                 <=== 55+
ZKM128

ASKER
Yes, I will try to keep the fees inside the database table instead of hard coding. I was doing most of the mathematical calculations by hard coding on the web pages since I have decided to change my back-end database from MS Access to MS SQL, due to the complexity and lack of knowledge about SQL Server.
Thank you so much for your help experts :-)
_agx_

Well fortunately MS Access syntax is very similar to SQL Server's (for the most part). So it's less of a learning curve than if you switched to MySQL or Oracle.  

One thing, I just noticed a logic error in the sample query above.  Since it matches on "IS NULL", it should use an INNER JOIN instead of OUTER (or LEFT JOIN), ie

          FROM   MembersTable m INNER JOIN MembershipFees fee

Edit:  Anyway, glad to help :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy