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:

Expand|Select|Wrap|Line Numbers

 
<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.

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 
<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!

Grouping & Sorting in Coldfusion

re: Grouping & Sorting in Coldfusion

re: Grouping & Sorting in Coldfusion

re: Grouping & Sorting in Coldfusion

re: Grouping & Sorting in Coldfusion

re: Grouping & Sorting in Coldfusion

re: Grouping & Sorting in Coldfusion

Similar ColdFusion bytes

/bytes/development

/bytes/it

/bytes/about