cfoutput query group

[trojnfn]

I have the following table, with the cfoutput, query, and group. The displayed output is below.

<center>
<table border="1" width="760">
<cfoutput query="qry" group="user_employee_number">
<tr>
<td valign="top" colspan="2"><b>#ucase(user_employee_number)#</b></td>
</tr>
<cfoutput>
<tr>
<td><a href="next_page.cfm?ref_no=#qry.ref_no#&line_item=#qry.line_item#">Reference No:#qry.ref_no#</a></td>
<td>line item #qry.line_item#</td>
</tr>
</cfoutput>
</cfoutput>
</table>
</center>

123456
Reference No: 99999 line item 1
Reference No: 99999 line item 2
345678
Reference No:00001 line item 1
912311
Reference No:10934 line item 1
Reference No:10934 line item 2
Reference No:10934 line item 3

What do I need to change so that the Reference No only shows up once, instead of occuring the same amount of times of the line item ?

[Newsgroup_User]

Just add another <cfoutput...> layer with the desired group parameter.

<cfoutput ... group="user_employee_number">
...
<cfoutput group="ref_no">
...
<cfoutput>
...
</cfoutput>
...
</cfoutput>
...
</cfoutput>

[trojnfn]

Is this where I add the other group tag ? It does not
seem to work, still giving me duplications

<center>
> <table border="1" width="760">
> <cfoutput query="qry" group="user_ employee_
number">
> <tr>
> <td valign="top"
> colspan="2"> <b>#ucase( user_employee_ number)#<
/b></td>
> </tr>
<cfoutput group="ref_no" > <------
> <cfoutput>
> <tr>
> <td><a
> href="next_page. cfm?ref_no= #qry.ref_ no#&line_
item=#qry. line_item# ">Reference
> No:#qry.ref_ no#</a></ td>
> <td>line item #qry.line_item# </td>
> </tr>
> </cfoutput>
> </cfoutput>
</cfoutput> <-----
> </table>
> </center>

[Newsgroup_User]

trojnfn wrote:
> Is this where I add the other group tag ? It does not
> seem to work, still giving me duplications
> ...
> <cfoutput group="ref_no" > <------
> > <cfoutput>
...
> > </cfoutput>
> > </cfoutput>
> </cfoutput> <-----

Yes that is where you put the other group tag, but you then need to
output the ref_no field between this tag and the inner-most
<cfoutput...> tag.

The way this works is this:

<cfoutput ... group="user_employee_number">
... <!-- Content here will be output once per value change in the
user_employee_number field -->
<cfoutput group="ref_no">
... <!-- Content here will be output once per value change in the
ref_no field -->
<cfoutput>
... <!-- Content here will be output once per row in the record set -->
</cfoutput>
... <!-- Content here will be output once per value change in the
ref_no field -->
</cfoutput>
... <!-- Content here will be output once per value change in the
user_employee_number field -->
</cfoutput>

Why your behavior did not change was because you where still outputting
the ref_no field inside the inner most <cfoutput...> clause. Thus it is
output once for every row.

[trojnfn]

This is what I have now and it displays the output like this, on one row, instead of multiple rows :

ref_no line item-1 line item-2...etc

instead of this :

ref_no line item-1
line item-2..etc.

But at least the ref_no only shows up once, so I guess it is close enougth. I think it has something to do with the <tr> placement. Depending on where I put it within the <cfoutput> it gives me a different result. Where should it be placed to get the output above ?


<center>
<table border="0" width="760">
<cfoutput query="qry" group="user_employee_number">
<tr>
<td valign="top" colspan="2"><br><b>#ucase(user_employee_number)#</b></td>
</tr>
<cfoutput group="ref_no">
<tr>
<td><ahref="next_page.cfm?ref_no=#qry.ref_no#&line_item=#qry.line_item#">Ref No:#qry.ref_no#</a></td>
<cfoutput>
<td>Line Item #qry.line_item#</td>
</cfoutput>
</tr>
</cfoutput>
</cfoutput>
</table>
</center>

[Dan_Bracuk]

I've never actually done this, but I think you can have nested groups.

<cfoutput group="g1">
g1 group data
<cfoutput group = "g2"
g2 group data
<cfoutput>
more data
</cfoutput>
</cfoutput>
</cfoutput>

[Newsgroup_User]

you can have nested groups, but since it does not seem to solve your
problem i suspect it is your query which is to blame. it probably
returns multiple ref_no records to start with...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Dan_Bracuk]

The reason it didn't solve your problem is that you are not displaying any data between the 2nd and 3rd cfoutput tags.

[Prasanth_Kumar_S]

Hi,
The problem may be in the query. Are you using order by in the query?

- Prasanth

[Dan_Bracuk]

Instead of this,
ref_no line item-1
line item-2..etc.

Why not this?
ref_no line
item-1
line item-2..etc.

It would look a lot better and be easier to code as well.