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.
ZKM128Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
Instead of < you would use   lt   or lte   (less than, less than or equal)

>  is gt  or  >=  is gte

You don't need the  age > 25 in the second part because it will be if not caught by the first part..

<cfif Age lt 25>$100<cfelseif Age lt 55>$120</cfif>
ZKM128Author Commented:
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?
gdemariaCommented:
You are allowed to use CFIF statements within CFMAIL, can you paste your exact code, there must be a little type-o or something specific to the situation
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

ZKM128Author Commented:
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

gdemariaCommented:
I took your code and changed the datasource, created a table and ran it without an error.   I scoped your variables by adding the query name, that's not a requirement but it's good form and it may resolve a problem if other parts of your code use the same variable name with a different scope.

If that doesn't help, I would say that some other part of your code is causing the issue.  Try deleting a section and running it again, if no error, delete another part and try until you narrow down on which block of code has the error.

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

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

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

</cfmail>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZKM128Author Commented:
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 < & <=/
_agx_Commented:
> <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.
ZKM128Author Commented:
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 :-)
gdemariaCommented:
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                             ??
_agx_Commented:
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+
ZKM128Author Commented:
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_Commented:
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 :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.