Grouping & Sorting in Coldfusion

[ndeeley]

Hi there,

I'm replicating the look of an Access form in Coldfusion

In Access I can create group headers to sort my data, so all the jobs under a particular site appear under that site name - which appears only once.

I'm using a cfloop query to get all my results in CF so my site name is repeated constantly. - like so

Site: MYSITE1
Job1

Site:MYSITE1
Job2


How do I get it to appear only once, as a header for all the jobs underneath it? Obviously the loop will need to go on and output the jobs under the next site, with that only having one header as well. like so...

Site: MYSITE1
Job1
Job2
Job3

Site: MYSITE2
Job1
Job2

Thanks for your help!

[acoder]

Can you post the cfloop code.

[ndeeley]

Hi Acoder,

I certainly can:

<tr>
			<cfloop query="ManagerJobsIn">
			<cfoutput>
			<tr>
			<td colspan="14" style="color: red; font-weight: 600;">#ClientFK#</td>
			</td>	

			<tr>
			<td colspan="14" style="color: red; font-weight: 600;">#SiteFK#</td>
			</td>	
			
			<tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#">
			<td width="20%">#AssetClassFK#</td>
			<td>#ManufacturerFK#</td>
			<td>#ModelFK#</td>
			<td>#PowerRating#</td>
			<td>#ClientWONO#</td>
			<td>#WorkshopWONO#</td>
			<td>#dateFormat(WoRecdate,'dd/mm/yyyy')#</td>
			<td>#dateFormat(QuotePDate,'dd/mm/yyyy')#</td>
			<td>#numberFormat(Price, "(______,.00")#</td>
			<td>#dateFormat(QuoteACDate, 'dd/mm/yyyy')#</td>
			<td>#ResponseLevelFK#</td>
			<td>#dateFormat(ActualCompDate, 'dd/mm/yyyy')#</td>
			<td>#AssignedToFK#</td>
			</tr>

		
			</cfoutput>
			</cfloop>
			</table>

Cheers
Neil

[acoder]

Assuming the sites are sorted and grouped in the query, e.g.

Site    Job
1       1
1       2
1       3
2       1
2       2

you can use a variable to store the current site, e.g. MYSITE1. Then compare with the SiteFK value. If it's equal, don't add a header. If it's a new site, add a header and set that variable to this new value.

[ndeeley]

Acoder,

That's great - I'll give it a go.

Cheers
Neil

[jKara]

You can also use cfoutput's group attribute. Just order your results by "Site" first. Then use nested cfoutput tags and group by "Site" as well.

<cfquery ..>
       SELECT  Site, Job,  ... other columns
       FROM     Thetable
       ORDER BY Site, Job, ... other columns
</cfquery>

<cfoutput query="yourQuery" group="Site">
          #Site#<br>
          <cfoutput>
               #Job#<br>
          </cfoutput>
</cfoutput>

[acoder]

Nice tip. Thanks for sharing!