My table contains a supplier number and email address. I can have multiple
email addresses for each suppliernumber. I want to group all the emails
together into one string so cfmail can send to the entire string instead of
individually, so I try to use valuelist to do this. Seems to work, but my
question is how do I separate each list by the supplier number ? I tried to use
group by in the queyr and it does not work.

So if supplier 1 has 3 records (email) and supplier 2 has 5 records (email),
my oput should be two records only, for supplier 1 with eamail 1,2,3, etc. and
supplier 2 with email 1, 2,3, 4, 5. etc.

How can I do this ?

trojnfn wrote:
>
> How can I do this ?
>

Well that is going to take a couple of steps.

If you look at valueList() you will see that it returns a list of all
the values from a column in a record set.

You currently have a record set that contains all the emails for all the
suppliers. The emails are grouped by supplier, but the record set still
contains all of them.

Obviously somewhere in between you need to break this single record set
into multiple groups of records. One group for each supplier with all
that suppliers e-mails.

I can think of three different ideas off the top of my head. Each with
different pros and cons.

Repeatedly query the database for each supplier.

Query the database for all the suppliers and use Query of Query
functionality to break up this large record set into smaller record sets.

Query the database for all the suppliers, use <cfoutput...> with its
group parameter to loop over this record set. Building the email list
as it loops, and then outputting an email after all the emails for a
single supplier are listed.

<cfoutput query="myQuery" group="supplier">
<cfset emailList = "">
<cfoutput>
<cfset emailList = ListAppend(emailList,myQuery.supplier_email)>
</cfoutput>
<cfmail to="#emailList#"...>
</cfmail>
</cfoutput>

Try something like this:

<cfoutput query="q1" group = "suppliernumber">
<cfquery name = "q2" dbtype = "query">
select email
from q1
where suppliernumber = #suppliernumber#

<cfmail to = "#valuelist(q2.email)#">