I'm trying to create an e-mail report for some course administrators. I've
got things figured out so that I can output the results, and I'm getting the
correct results... but I only want each administrator to get a single
e-mail... my current output would have them getting one for each student.
*** *** ***
My query (generates the proper results):
<cfquery name="users" datasource="#DSN#">
SELECT users.id AS users_id,
attendee.id AS attendee_id,
users.*,
attendee.*,
courses.*,
organizations.*
FROM users,
attendee,
courses,
organizations
WHERE users.attendee = 'Yes'
AND users.id = attendee.users_id
AND ((users.designation = 'R.T.') OR (users.designation =
'Pharm.D.'))
AND ((attendee.writtentest1 > 70) OR (attendee.writtentest2
> 70) OR (attendee.writtentest3 > 70))
AND ((attendee.skilltest1 = 'Yes') OR (attendee.skilltest2 =
'Yes') OR (attendee.skilltest3 = 'Yes'))
AND ((attendee.assignrec1 = 'Yes') AND (attendee.assignrec2
= 'Yes') AND (attendee.assignrec3 = 'Yes') AND (attendee.pretest = 'Yes'))
AND attendee.assigneddate = courses.id
AND organizations.id = courses.org
</cfquery>
*** *** ***
This loops correctly through the admin responsible for that 'user'...
<cfloop query="users">
<cfquery name="ceu_needed" datasource="#DSN#">
SELECT org_admin.users_id,
org_admin.org_id,
users.id,
users.email
FROM org_admin
INNER JOIN users ON org_admin.users_id = users.id
WHERE org_admin.org_id = #users.org#
</cfquery>
<cfoutput>
#ceu_needed.org_id# - #ceu_needed.email#
</cfoutput>
<br /><br />
<cfoutput>
#users.lname#, #users.fname#
<br />#users.address#
<br />#users.city#, #users.prov# #users.country# #users.pcode#
<br /><br />
</cfoutput>
</cfloop>
*** *** ***
How can I translate the above query into a CFMAIL Tag so that each admin
only gets 1 e-mail?
<cfmail TO="#ceu_needed.email#"
FROM="[EMAIL PROTECTED]"
SUBJECT="CEU Notice"
SERVER="mail.domain.org"
TYPE="html"
QUERY="users">
The following individuals meet the requirements CEU Notice requirements,
and do not have a CEU date noted in their user record:
<cfoutput>
#lname#, #fname#
<br />#address#
<br />#city#, #prov# #country# #pcode#
</cfoutput>
</cfmail>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
