valuelist question

[trojnfn]

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 ?

[Newsgroup_User]

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>

[Dan_Bracuk]

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)#">