CODEWITHSUNDEEP
excelcoldfusioncoldfusion-10cfspreadsheet
Artisdesigns
Artisdesigns

Reputation: 13

CFspreadsheet looping

I have a question regarding cfspreadsheet....So I'm using cfspreadshseet to create excel spreadsheets for reporting purposes. My page allows a user to select whatever columns from the database to include in the report. So here is an example:

The spreadsheet could look like this:

First Name---Last Name---Organization---Address---City---State---Zip---Concern

Joe Smith Sample 12 main denver co 80513 concerns go here

My question is this, if Joe has more than 1 concern I get multiple rows with joe's info...is there a way I can loop the concerns and only have 1 row for joe?

Thanks,

Steve

Upvotes: 0

Views: 698

Answers (3)

Simon Fermor
Simon Fermor

Reputation: 116

Assuming you want separate lines for each comment, something like this would work:

<cfset current_id = "">
<cfloop query = "my_query">
    <cfset next_id = user_id>
    <!--- or whatever else forms the primary key --->
    <cfif next_id neq current_id>
        <cfset current_id = next_id>
        <cfset SpreadsheetAddRow(my_spreadsheet, "#first_name#,#last_name#,etc, #comment#">
    <cfelse>
        <cfset SpreadsheetAddRow(my_spreadsheet, ",,#comment#">
    </cfif>
</cfloop>

This is based on the information provided. If you have a unique ID the group attribute would work better.

Upvotes: 0

Leigh
Leigh

Reputation: 28873

Using the "group" feature of cfoutput is perfectly fine for this task. Just to throw out another possibility, you could also generate the list within your database query.

For example, MySQL has the GROUP_CONCAT function. I do not know the structure of your tables, but say you have two tables User and UserConcern, you could use GROUP_CONCAT like so to concatenate the "Concern" values into a single string:

SQLFiddle

SELECT
     u.UserID
     , u.FirstName
     , ... other columns
     , GROUP_CONCAT( uc.Concern ) AS ConcernList
FROM UserTable u INNER JOIN UserConcern uc
         ON uc.UserID = u.UserID
GROUP BY 
     u.UserID
     , u.FirstName
     , ... other columns

For SQL Server, a standard trick is to use XML Path:

SQLFiddle

SELECT
     u.UserID
     , u.FirstName
     , ... other columns
     , STUFF( ( SELECT ',' + uc.Concern
                FROM  UserConcern uc
                WHERE uc.UserID = u.UserID
                ORDER BY uc.Concern
                FOR XML PATH('')
               ) 
              , 1, 1, ''
          ) AS ConcernList
FROM UserTable u
GROUP BY 
     u.UserID
     , u.FirstName
     , ... other columns

Then simply generate your spreadsheet as usual.

Upvotes: 2

Regular Jo
Regular Jo

Reputation: 5500

You need a unique row ID to do this safest, the outer group working with lastname, or something, could cause conflict. UserID is a placeholder variable. Make sure to replace it with an accurate ID name. Of course, a few of these variable names are just guessed.

<cfoutput query ="thequery" group="UserID">
  <cfset cList="">
  <cfoutput group="concern">
    <cfset cList=ListAppend(cList,Concern)>
  </cfoutput>
  <cfset temp = spreadsheetAddRow(my_spreadsheet,"'#fn#','#ln#',...,'#cList#'">
</cfoutput>

Upvotes: 2

Related Questions