Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

cfmail question please help 1

Status
Not open for further replies.
alsjka

alsjka

Technical User
Jan 21, 2003
114
US
hi all,
i have a problrem. i need to send an email to employees what they have purchased and has been recieved. how would i go about quering the data grouping the items by the employee that has order and then sending an email to them.

this is what i would do..

select itemnum, description, datereceived, catalognum
from invy
where datereceived = '04/28/03'
and catalognum is not null

then i would goto a cfmail loop?
<cfloop query=&quot;query here&quot;>
<cfmail from='name@here' from = 'someone@here'>
#catalognum# the following items have been received to you stockroom please pick them up.

#itemnum#-#description#-#datereceived#
</cfmail>

</cfloop>

but when i run this it just loops through each itemnum and eamil each individual item to the employee..i do not get how you get all the items received on that day and group the items by employee then email?

can someone please help me.
tia.
JKA!
 
Sort by date Sort by votes
JKA~
First off, you need to run your query to get your results before you run cfmail.

So it would look something like this:
<cfquery datasource=&quot;#application.dsn#&quot; name=&quot;YOURQUERY&quot;>
select itemnum, description, datereceived, catalognum
from invy
where datereceived = '04/28/03'
and catalognum is not null
</cfquery>

<cfmail
to = &quot;recipient&quot;
from = &quot;sender&quot;
type = &quot;html&quot;
query = &quot;YOURQUERY&quot;
server = &quot;servername&quot;>

#catalognum# the following items have been received to you stockroom please pick them up.

#itemnum#-#description#-#datereceived#

</cfmail>

 
Upvote 0 Downvote
This is what I understand your query is returning. something like this...

itemnum, description, datereceived, catalognum

1 abc date1 100
2 def date2 200
3 xyz date3 100
4 xlm date4 300
5 opq date5 100
6 lmn date6 300


Now you are sending 6 emails and what you want is actually send only three emails
to 100 with items 1,3,5
to 200 with item 2
to 300 with item 4,6

Am I correct??
 
Upvote 0 Downvote
This is my code if it helps.. it works fine i get all of the items ordered by each person grouped together fine but it only outputs the same name over and over. how do i over come that? I need to send an email to each name(catalognum).

<body>
<cfquery name=&quot;get_emp&quot; datasource=&quot;mp2sql&quot;>
SELECT DISTINCT PURREQ.CATALOGNUM, PURREQ.ITEMNUM, PURREQ.PONUM,purreq.DESCRIPTIONONPO
FROM POHEADER, PURREQ, REQRECVO
WHERE POHEADER.PONUM = PURREQ.PONUM AND POHEADER.PONUM = REQRECVO.PONUM AND ((REQRECVO.DATERECEIVED>={ts '2003-04-28 00:00:00'}))
and PURREQ.CATALOGNUM != 'none'
and PURREQ.CATALOGNUM != ''
and PURREQ.CATALOGNUM is not null
GROUP BY PURREQ.CATALOGNUM, PURREQ.ITEMNUM, PURREQ.PONUM,purreq.DESCRIPTIONONPO
</cfquery>
<cfset name_length = len(#get_emp.catalognum#)>
<cfset last_name = removechars(#get_emp.catalognum#,name_length,1)>
<cfset first_int = right(#get_emp.catalognum#,1)>

<cfmail to=#first_int##last_name# from=&quot;StockRoom@&quot;
query=&quot;get_emp&quot; group=&quot;catalognum&quot; subject=&quot;Received Stock Items&quot; type=&quot;HTML&quot;>

Hello #first_int#, #last_name#:<br><br>



The following items have been recieved to your stockroom and are available to be picked up at your earliest conveinence.
<br><br>


Thank You<br><br>
<br>



Item Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item Description<br>

----------------------------------------------------<br>

<cfoutput>

<table>
<tr><td>#Get_emp.itemnum# - #Get_emp.DESCRIPTIONONPO#</td></tr>
<tr><td>
</table>

</cfoutput>
<br>
<br>
<br>

PLEASE DO NOT REPLY TO THIS EMAIL<br>

</cfmail>

</body>
</html>

Thanks for your time again.
 
Upvote 0 Downvote
by default (#get_emp.catalognum#) points to the first record from your query since that's what you are loading in your variables before sending the email you are sending everything to just that person.

what you need to do is build the firstname lastname string while doing the CFMAIL tag, that way you'll step through each of the records.....

Have a CFLOOP go through the query and inside that loop use the CFMAIL with the first and lastnames already in a variable.


sorry i can't give the exact code right now... working on something else.. :)
 
Upvote 0 Downvote
building the firstname lastname string while doing the CFMAIL tag works great to go through each name but placing a loop around the query just emails multiple emails out and not to the right names.? so i guess i do not follow you that well. this is how i have my code now..


<body>


<cfquery name=&quot;get_emp&quot; datasource=&quot;mp2sql&quot;>
SELECT DISTINCT PURREQ.CATALOGNUM, PURREQ.ITEMNUM, PURREQ.DESCRIPTIONONPO, REQRECVO.DATERECEIVED
FROM PURREQ, REQRECVO,poheader
WHERE PURREQ.PONUM = REQRECVO.PONUM
and REQRECVO.DATERECEIVED>='4/29/03'
and (PURREQ.CATALOGNUM != 'none' and PURREQ.CATALOGNUM != '' and PURREQ.CATALOGNUM is not null)
and (poheader.status = 'All Received' or poheader.status = 'receiving')
GROUP BY PURREQ.CATALOGNUM, PURREQ.ITEMNUM, PURREQ.DESCRIPTIONONPO, REQRECVO.DATERECEIVED,poheader.status
</cfquery>

<cfmail to=&quot;&quot; from=&quot;&quot;
query=&quot;get_emp&quot; group=&quot;catalognum&quot; subject=&quot;Received Stock Items&quot; type=&quot;HTML&quot;>

<cfset name_length = len(#get_emp.catalognum#)>
<cfset last_name = removechars(#get_emp.catalognum#,name_length,1)>
<cfset first_int = right(#get_emp.catalognum#,1)>


Hello #first_int#, #last_name# :<br><br>



The following item(s) have been received to your stockroom on #get_emp.datereceived# and are available to be picked up at your earliest conveinence.
<br><br>


Thank You<br><br>
<br>



Item Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item Description<br>

---------------------------------------------------------------------<br>


<cfoutput>
<table>
<tr><td>#Get_emp.itemnum# - #Get_emp.DESCRIPTIONONPO#</td></tr>
<tr><td>
</table>
</cfoutput>


<br>
<br>
<br>

PLEASE DO NOT REPLY TO THIS EMAIL<br>

</cfmail>

</body>
</html>
 
Upvote 0 Downvote
OK different aproach.. Sorry I couldn't be more specific I was working on some other thing at the moment.
Leave the Cfquery intact and after that follow this pseudocode....(you'll figure it out).

<CFSET LSTPROCESSED=&quot;&quot;> just a holding variable .
<CFLOOP query=&quot;get_emp&quot;>
<CFIF Listfind(LSTPROCESSED,#get_emp.catalognum#) eq &quot;0&quot;>
<cfset name_length = len(#get_emp.catalognum#)>
<cfset last_name = removechars #get_emp.catalognum#,name_length,1)>
<cfset first_int = right(#get_emp.catalognum#,1)>
<cfquery name=&quot;get_items&quot; dbtype=&quot;query&quot;>
Select itemnum, DESCRIPTIONONPO from get_emp
where catolognum=#get_emp.catalognum#
</cfquery>
<cfmail to=#first_int##last_name# from=&quot;StockRoom@&quot;
subject=&quot;Received Stock Items&quot; type=&quot;HTML&quot;>

Hello #first_int#, #last_name# :<br><br>



The following item(s) have been received to your stockroom on #get_emp.datereceived# and are available to be picked up at your earliest conveinence.
<br><br>


Thank You<br><br>
<br>



<table>
<tr>
<th>Item</th>
<th>Description</th>
</tr>
<cfoutput query=&quot;get_items&quot;>
<tr><td>#itemnum#</td>
<td>#Get_emp.DESCRIPTIONONPO#</td>
</tr>
</cfoutput>
</table>
<br>
<br>
<br>

PLEASE DO NOT REPLY TO THIS EMAIL<br>

</cfmail>
<CFSET LSTPROCESSED=ListAppend(LSTPROCESSED,#get_emp.catolognum#)>
</cfif>
</cfloop>


Here's the logic...
Create a variable to hold all the catalognum already processed.
Now you have a loop that will go through all your get_emp query.
First thing you do is check if the current catalognum has been already processed, if not you process the email for that person if yes you just jump to the next record.ListFind is a CFML function returns 0(zero) if it doesn't find what you are looking for.

Now inside the CFLOOP and while pointing to the record you build the firstname+lastname that will go to the CFMAIL.
The second thing you build is a small query pulling only the items and their descriptions from the big query you had initially based on the current catalognum.
Done!
from there you just trigger the CFMAIL with the TO parameter set to the &quot;firstname+lastname&quot; combination and inside you create a small loop from your second query. That will show all the items selected for that catalognum

Once the email is sent you add or append the catalognum to your list of already processed emails.ListAppend is also a CFML function.

This will repeat for all the records in your get_emp query but will process each catalognum only once thanks to the LSTPROCESSED list that has been created.


Now there's another way creating a custom function and passing the return value to the TO parameter of the CFMAIL with a combination of the Grouping feature, but I haven't investigated that yet.
 
Upvote 0 Downvote
thanks, i figured out a way another way i will post the code when at work. thanks for giving me other ideas.
will post tommorow.

jka.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sirnose1
  • Locked
  • Question
CFIF for CFmail
Replies
0
Views
668
sirnose1
sirnose1
LyndonOHRC
  • Locked
  • Question
Need help using ImageCrop()
Replies
1
Views
238
LyndonOHRC
LyndonOHRC
MDEJAKE
  • Locked
  • Question
autosuggest (cfc and jquery) help
Replies
0
Views
278
MDEJAKE
MDEJAKE
cfuser10
  • Locked
  • Question
Cfselect Question
Replies
1
Views
225
cfuser10
cfuser10
jdhilljr
  • Locked
  • Question
Coldfusion mysql question
Replies
1
Views
240
jdhilljr
jdhilljr

Part and Inventory Search

Sponsor

Back
Top