ColdFusion sort problem: in a sorted list of records, white and gray background colors do not consistently alternate

My question in brief: if I have several documents that have the same DocumentPublicationDate, or the same DocumentTitle, then how can I get ColdFusion to sort them with alternating background colors?

Details:

1. Here's my current problem, which I have been reviewing for much of today. gdemaria, _agx_, Brichsoft, and other experts have helped me with this application to display CEP documents. Currently I am trying to group records in a readable, user-friendly list using alternating white and gray backgrounds. But my pages do not consistenly display orderly, alternating background colors.

2. I will give an example. Take a look at this page:

http://ebwebwork.com/cep/press/

You will see that, instead of the expected alternating background colors, that (at the beginning) there are two white backgrounds slumped together. Then the alternating background colors proceed as expected.

3. I believe I know why the problem occurs. The default sort order for these pages is:

D.DocumentPublicationDate DESC, D.DocumentTitle, D.DocumentID, F.FileName

You will note that, on this page, three records all have the same DocumentPublicationDate: June 30, 2010. So, when ColdFusion tries to sort by DocumentPublicationDate, it must "sort" three records with the same DocumentPublicationDate. This causes the conflation of background colors -- I am fairly confident of this.

4. In a similar way, when I sort documents (or records) by Title, then documents that start with "School" get lumped together, causing further conflation of background colors.

5. Hmmm. How can I fix this? I need to tell ColdFusion to distinguish among database records that often have the same characteristics: same publication date; or same first word in their titles.

6. I organize records using the CFOUTPUT GROUP attribute and the CurrentRow MOD 2 IS 1 function:

  <cfif CurrentRow MOD 2 IS 1>
        <cfset bgcolor="##ffffff">
        <cfelse>
        <cfset bgcolor="##f7f5f5">
  </cfif>

... that is straightforward.

7. Here is the query that I use to query the database for documents that have files marked "isPressRelease" ... if a document has a file that is a press release, then the press release file shows up on the Press Releases page:

<!--- query getDocumentsandFiles: select columns from tables tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F --->
<!--- to display CEP Document Title, Author, Abstract, Publication Date; and CEP Files associated with those documents; and download links to those files --->

<cfquery name="getDocumentsandFiles" datasource="#ds#">
SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , T.DocumentTopicID
     , T.DocumentTopic
     , H.DocumentID
     , H.DocumentTopicID
     , F.DocumentID
     , F.FileID
     , F.FileName
     , F.FileLinkText
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.IsPressRelease
FROM tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F
WHERE D.DocumentID = H.DocumentID
AND D.DocumentID = F.DocumentID
AND F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC, D.DocumentTitle, D.DocumentID, F.FileName
</cfquery>

My question: if I have several documents that have the same DocumentPublicationDate, or the same DocumentTitle, then how can I get ColdFusion to sort them with alternating background colors?

I hope this question is lucid. Thank you for your help.

Eric
LVL 3
Eric BourlandAsked:
Who is Participating?
 
gdemariaCommented:
That means you needs some work on your query it's got a lose join somewhere

FROM tbl_CEP_Documents D
   , tbl_CEP_Document_Topic T
   , tbl_Document_Has_Topic H
   , tbl_CEP_files F
WHERE D.DocumentID = H.DocumentID
AND   D.DocumentID = F.DocumentID
AND   F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC, D.DocumentTitle, D.DocumentID, F.FileName


Looking at this, I see the _Topic table included in the from clause, but there are no join conditions for this table (nothing in the where clause).

Btw, why aren't you using the standard format for joining?


FROM tbl_CEP_Documents D
 inner join  tbl_CEP_Document_Topic T on t. ??????
 inner join  tbl_Document_Has_Topic H on D.DocumentID = H.DocumentID
 left join tbl_CEP_files F on D.DocumentID = F.DocumentID and F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC
              , D.DocumentTitle
              , D.DocumentID
              , F.FileName

0
 
JohnHowlettCommented:
Might be an idea to post the full page code....
0
 
gdemariaCommented:
Hey Eric, I agree with John.  You don't need all the line-by-line description, just the code

The problem may be that you have your CFIF statement

<cfif CurrentRow MOD 2 IS 1>
        <cfset bgcolor="##ffffff">
        <cfelse>
        <cfset bgcolor="##f7f5f5">
  </cfif>

Outside of your group <cfoutput> and they need to be inside that 2nd cfoutput to ensure they are run every time
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Enroll Today
 
dagaz_deCommented:
I would use it this way:

<tr bgcolor="###IIF(getDocumentsandFiles.currentrow MOD 2, DE('ffffff'), DE('f7f5f5'))#">
0
 
gdemariaCommented:
IIF and DE are very convenient functions, but are 'expensive'

The simple CFIF statement runs much faster, so if there are many rows displayed, you may see a difference in load time..
0
 
Eric BourlandAuthor Commented:
Good morning! Thank you John, gdemaria, and dagaz for these comments.

I did try moving around the CurrentRow MOD code within the application. There are three CFOUTPUTS working, and I tried the CurrentRow MOD code inside of each.

<cfif CurrentRow MOD 2 IS 1>
        <cfset bgcolor="##ffffff">
        <cfelse>
        <cfset bgcolor="##f7f5f5">
  </cfif>

I have moved around the code and tried all possible permutations. At this point I am wondering if I need to adjust my query; or use a new strategy altogether. I have a feeling this must be a common problem, and that someone out there has devised a solution.

I attach the code below; it is simple. There is a query that requests CEP DocumentTitles and files associated with each DocumentTitle. Then there is a series of three CFOUTPUTS that are required to display the correct order Documents and the files -- in this case, only Press Releases -- associated with the documents.

(In my scheme, a "document" is an overarching publication that comprises several PDF files: Full Report, Press Release, Appendix, other files. This page displays a list of all Press Releases of all CEP documents sorted by date DESC.)

I am curious to learn if there is a solution to this problem. I could tell the client to "live with it" but of course I would much rather not do that; I feel compelled to make this application as near perfect as I can.

Thank you again for your help.

Eric
<!--- query getDocumentsandFiles: select columns from tables tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F --->
<!--- to display CEP Document Title, Author, Abstract, Publication Date; and CEP Files associated with those documents; and download links to those files --->

<cfquery name="getDocumentsandFiles" datasource="#ds#">
SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , T.DocumentTopicID
     , T.DocumentTopic
     , H.DocumentID
     , H.DocumentTopicID
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileLinkText
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.IsPressRelease
FROM tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F
WHERE D.DocumentID = H.DocumentID
AND D.DocumentID = F.DocumentID
AND F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC, D.DocumentTitle, D.DocumentID, F.FileName
</cfquery>


<h1>Press Releases</h1>


<!--- begin output from query getDocumentsandFiles; list Document Title, Publication Date; and CEP 'Press Release' Files associated with those documents; and download links to those files --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">


  <cfif CurrentRow MOD 2 IS 1>
        <cfset bgcolor="##ffffff">
        <cfelse>
        <cfset bgcolor="##f7f5f5">
  </cfif>
  
  
     <!--- set bgcolor --->
<div style="background-color:#bgcolor#;">

<!--- set padding --->
<div style="padding:5px;">

<h3>#DocumentTitle#</h3>
<p class="documentText">
<em>Published:</em> <span class="black">#DateFormat(DocumentPublicationDate, "mmmm d, yyyy")#</span><br />




<!--- output group on DocumentID --->
<cfoutput group="DocumentTitle">




<!--- output of query filename --->
 <cfoutput group="FileName">
 

 

 <a href="/cep/cfcontent_file.cfm?Attachment=#FileName#&FileType=#FileExtension#">#FileLinkText#</a>
(#FileExtension#, #FileSize# bytes)<br />


<!--- /output of query filename --->
</cfoutput>
</p>


</div>
<!--- /set padding --->



</div>
<!--- /set bgcolor --->

<!--- /output group on DocumentID --->
    </cfoutput>
    
<!--- /output from query getDocumentsandFiles --->
</cfoutput>

Open in new window

0
 
gdemariaCommented:
Hey Eric,  You have an extra group for documentTitle, you have it in two cfoutput group= statements, I removed one..
<!--- query getDocumentsandFiles: select columns from tables tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F --->
<!--- to display CEP Document Title, Author, Abstract, Publication Date; and CEP Files associated with those documents; and download links to those files --->

<cfquery name="getDocumentsandFiles" datasource="#ds#">
SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , T.DocumentTopicID
     , T.DocumentTopic
     , H.DocumentID
     , H.DocumentTopicID
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileLinkText
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.IsPressRelease
FROM tbl_CEP_Documents D, tbl_CEP_Document_Topic T, tbl_Document_Has_Topic H, tbl_CEP_files F
WHERE D.DocumentID = H.DocumentID
AND D.DocumentID = F.DocumentID
AND F.IsPressRelease = 1
ORDER BY D.DocumentPublicationDate DESC, D.DocumentTitle, D.DocumentID, F.FileName
</cfquery>


<h1>Press Releases</h1>


<!--- begin output from query getDocumentsandFiles; list Document Title, Publication Date; and CEP 'Press Release' Files associated with those documents; and download links to those files --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">
  <cfif getDocumentsandFiles.currentRow MOD 2 IS 1>
    <cfset bgcolor="##ffffff">
  <cfelse>
    <cfset bgcolor="##f7f5f5">
  </cfif>
  
<!--- set bgcolor --->
<div style="background-color:#bgcolor#;padding:5px;">

  <h3>#DocumentTitle#</h3>
  <p class="documentText">
  <em>Published:</em> <span class="black">#DateFormat(DocumentPublicationDate, "mmmm d, yyyy")#</span><br />
  <!--- output of query filename --->
  <cfoutput group="FileName">
  <a href="/cep/cfcontent_file.cfm?Attachment=#FileName#&FileType=#FileExtension#">#FileLinkText#</a>
  (#FileExtension#, #FileSize# bytes)<br />
  </cfoutput> <!--- /output of query filename --->
  </p>

</div> <!--- /set bgcolor and padding --->
<!--- /output from query getDocumentsandFiles --->
</cfoutput>

Open in new window

0
 
Eric BourlandAuthor Commented:
gdemaria,

I was wondering about the extra group for DocumentTitle. I used your simpler code.

That is a good idea. Thank you for pointing out the extra group.

http://ebwebwork.com/cep/press/

Hm. The background colors still do not alternate -- any idea what else I can do?

I realized I forgot to apply a scope to my variables in the CFOUTPUTs -- I went ahead and did that too. EB
<h1>Press Releases</h1>


<!--- begin output from query getDocumentsandFiles; list Document Title, Publication Date; and CEP 'Press Release' Files associated with those documents; and download links to those files --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">

  <cfif CurrentRow MOD 2 IS 1>
        <cfset bgcolor="##ffffff">
        <cfelse>
        <cfset bgcolor="##f7f5f5">
  </cfif>

  
 <!--- set set bgcolor and padding --->  
<div style="background-color:#bgcolor#;padding:5px;">  
  
  <h3>#getDocumentsandFiles.DocumentTitle#</h3>  
  <p class="documentText">  

  <em>Published:</em> <span class="black">#DateFormat(getDocumentsandFiles.DocumentPublicationDate, "mmmm d, yyyy")#</span><br />  
  
  <!--- output of query filename --->  
  <cfoutput group="FileName">  
  <a href="/cep/cfcontent_file.cfm?Attachment=#FileName#&FileType=#FileExtension#">#getDocumentsandFiles.FileLinkText#</a>  
  (#getDocumentsandFiles.FileExtension#, #getDocumentsandFiles.FileSize# bytes)<br />  
  </cfoutput>
  
  <!--- /output of query filename --->  
  </p>  
  
</div>
<!--- /set bgcolor and padding --->  

<!--- /output of query getDocumentsandFiles --->  
</cfoutput>

Open in new window

0
 
gdemariaCommented:
I just realized that you have a group on your 2nd cfoutput statement for filename, I removed that.  Seems to me you are only grouping on the documentTitle

I also changed your <CFIF statement a tad to include the query name and first test for 0 instead of 1 (not that that would matter much)

  <cfif getDocumentsandFiles.CurrentRow MOD 2 eq 0>

<h1>Press Releases</h1>


<!--- begin output from query getDocumentsandFiles; list Document Title, Publication Date; and CEP 'Press Release' Files associated with those documents; and download links to those files --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">

  <cfif getDocumentsandFiles.CurrentRow MOD 2 eq 0>
    <cfset bgcolor="##ffffff">
  <cfelse>
    <cfset bgcolor="##f7f5f5">
  </cfif>

  
 <!--- set set bgcolor and padding --->  
<div style="background-color:#bgcolor#;padding:5px;">  
  
  <h3>#getDocumentsandFiles.DocumentTitle#</h3>  
  <p class="documentText">  

  <em>Published:</em> <span class="black">#DateFormat(getDocumentsandFiles.DocumentPublicationDate, "mmmm d, yyyy")#</span><br />  
  
  <!--- output of query filename --->  
  <cfoutput>
  <a href="/cep/cfcontent_file.cfm?Attachment=#FileName#&FileType=#FileExtension#">#getDocumentsandFiles.FileLinkText#</a>  
  (#getDocumentsandFiles.FileExtension#, #getDocumentsandFiles.FileSize# bytes)<br />  
  </cfoutput>
  
  <!--- /output of query filename --->  
  </p>  
  
</div>
<!--- /set bgcolor and padding --->  

<!--- /output of query getDocumentsandFiles --->  
</cfoutput>

Open in new window

0
 
Eric BourlandAuthor Commented:
The trouble is, is I do not group on filename, then I get a lot of repetition of files as you can see here:

http://ebwebwork.com/cep/press/
0
 
tconsidineCommented:
The problem here is that "currentrow" does not refer to the "group number", but refers to the record number of the first row in the group of records that is starting.  Therefore, if you contrived your data to have only odd numbers in each group, it would seem that you had fixed it.

The proper solution is to toggle (alternate) your color without reference to the query, every time you fall through the outer cfoutput loop

 <cfif bgColor neq "##ffffff">
    <cfset bgcolor="##ffffff">
  <cfelse>
    <cfset bgcolor="##f7f5f5">
  </cfif>
0
 
Eric BourlandAuthor Commented:
I don't think I need the _topic table in this query at all.

I understand now -- I was being too simplistic in my selection of tables.

I am trying out your idea.
0
 
gdemariaCommented:
tconsidine is correct, Eric,  changing your CFIF structure to that example will take care of the alternating rows.

<cfif bgColor neq "##ffffff">
    <cfset bgcolor="##ffffff">
  <cfelse>
    <cfset bgcolor="##f7f5f5">
  </cfif>

The only addition is that you need to add a starting value for the bgcolor towards the top of your page, not inside the cfoutput statement...

   <cfset bgcolor="##ffffff">



You still have a problem with the join, etc.   but that is separate from the issue of the alternating colors


0
 
Eric BourlandAuthor Commented:
tconsidine -- I understand! I did that, and it did solve the problem.

gdemaria,

I removed the tbl_CEP_Document_Topic T from query getDocumentsandFiles -- not needed in this query. We also do not need to worry about table tbl_Document_Has_Topic; we do not need to invoke these two tables simply to display a list of Press Releases:

tbl_CEP_Document_Topic
tbl_Document_Has_Topic

.... so I reviewed JOINs in my Forta book. We need a simple inner join b/c we just need to test for equality: D.DocumentID = F.DocumentID. This code worked:

<cfquery name="getDocumentsandFiles" datasource="#ds#">
SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , F.DocumentID
     , F.FileID
     , F.FileName
     , F.FileLinkText
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.IsPressRelease
      FROM tbl_CEP_Documents D
      INNER JOIN tbl_CEP_files F
      ON D.DocumentID = F.DocumentID
      AND F.IsPressRelease = 1
      ORDER BY D.DocumentPublicationDate DESC
              , D.DocumentTitle
              , D.DocumentID
              , F.FileName

</cfquery>

The result is here: http://ebwebwork.com/cep/press/

I need to run out to a meeting but will follow up here when I get back.

Thank you very much! Hope you are well.

Eric
0
 
gdemariaCommented:
Eric,

Just to verify, you do not want a document to show unless it has a press release, is that true?   in other words, if no press releases, then do not show the document.

If you want the document to show even if it has no press releases, then change the INNER join to a LEFT join.

0
 
Eric BourlandAuthor Commented:
>>>Just to verify, you do not want a document to show unless it has a press release, is that true?

Correct.

It was a LEFT, and I was getting some strange results. But the INNER seems to do the trick.
0
 
Eric BourlandAuthor Commented:
What I learned: toggle bgcolor without reference to the query to ensure truly alternating rows; and a better solution for the JOINs I use in my query.

Thank you tconsidine and gdemaria! I really appreciate your time.

Eric B
0
 
gdemariaCommented:
>  toggle bgcolor without reference to the query to ensure truly alternating rows

just for educational purposes, the reason this does not work in this case is because of the group-by.   The query will return records like this..

1 -  Title A  - File 1
2 -  Title A  - File 2
3 -  Title B  - File 1
4 -  Title B  - File 2
5 -  Title C  - File 1

The first column above is the record number,  if you group by title, then record <CFIF condition will only be reached by record 1 and record 3 and record 5, which all have the same result when MOD by 2.   Records 2 and 4 are fetched inside the second cfouput which displays the files.   So the group by cfoutput does not loop records 1,2,3,4... as a  regular cfoutput does.




0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here