Table layout & CFLOOP

[xsw1971]

I have created a gantt chart that shows resource utilization over time. I need to explain what I did before I ask the question, so please bear with me.

I loop from the report start to the report end and create a table cell for each day:

<table>
   <tr>
      <th>Asset</th>
      <cfloop index="i" from="#v_start#" to="#v_end#" step="1">
      <th>#DateFormat(i, 'mm/dd/yy')#</th>
   </cfloop>
</tr>

Then I run a query to get all the assets that the user wants to see (from a form). I loop through all the assets and for each one I run a query to find any reservations that fall within the report start/end dates. I want to show the user all the assets they asked for, so if no records are returned from the reservation query I create an empty row like I did for the top portion:

   <tr>
      <td><cfoutput>#q_assets.identifier#</cfoutput></td>
      <cfloop index="i" from="#v_start#" to="#v_end#" step="1">
      <td width="50">&nbsp;</td>
      </cfloop>
   </tr>

If there are reservations, I want to show the user graphically what days are scheduled. I loop through the reservation records, then I create the daily loop as before. Inside the daily loop, I loop through each day of the reservation. For each reservation day that matches the daily day I color the cell blue:

   <cfoutput query="q_reservation">
   <tr>
      <td>#q_reservation.identifier#</td>
      <cfloop index="i" from="#v_start#" to="#v_end#" step="1">
      <td 
         <cfloop index="z" from="#q_reservation.start#" to="#q_reservation.end#" step="1">
         <cfif DateFormat(z,"mm/dd/yyyy") EQ DateFormat(i,"mm/dd/yyyy")>bgcolor="navy" </cfif>
         </cfloop>
      >&nbsp;</td>
      </cfloop>
   </tr>
   </cfoutput>
</table>

Everything works perfectly. Now for my question: If the same asset has more than one reservation record in the given time period, it creates more than one row. How can I combine my loops so that all scheduled days for a single asset show up on the same row? I have already tried grouping my output (<cfoutput query="q_reservation" group="identifier_id") but that caused it to only display 1 of the multiple records.

I was thinking I should loop through the reservation days and create a list of days, then loop through the list within the daily loop. ACK! It's getting confusing and I'm worried about processing time. Any thoughts?

Thanks in advance!

-sg

 

[ecobb]

From just a (very)quick glance, it looks like you just need to change up your cfoutput tags. As you said, it's creating a new row for each record. Try this:

   [maroon]<cfoutput>[/maroon]
   <tr>
      <td>[maroon]<cfloop  query="q_reservation">#q_reservation.identifier#<br></cfloop>[/maroon]</td>
      <cfloop index="i" from="#v_start#" to="#v_end#" step="1">
      <td 
         <cfloop index="z" from="#q_reservation.start#" to="#q_reservation.end#" step="1">
         <cfif DateFormat(z,"mm/dd/yyyy") EQ DateFormat(i,"mm/dd/yyyy")>bgcolor="navy" </cfif>
         </cfloop>
      >&nbsp;</td>
      </cfloop>
   </tr>
   </cfoutput>
</table>

I may be way off, though...



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher