?
Solved

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

Posted on 2010-08-26
18
2 solutions
Medium Priority
?
469 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Eric Bourland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
18 Comments
 
LVL 4

Expert Comment

by:JohnHowlett
ID: 33539898
Might be an idea to post the full page code....
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 33542087
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
 
LVL 3

Expert Comment

by:dagaz_de
ID: 33542398
I would use it this way:

<tr bgcolor="###IIF(getDocumentsandFiles.currentrow MOD 2, DE('ffffff'), DE('f7f5f5'))#">
0
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Read More
 
LVL 39

Expert Comment

by:gdemaria
ID: 33542486
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33543620
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 33543732
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33544374
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 33544473
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33544774
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 1000 total points
ID: 33544834
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
 
LVL 1

Assisted Solution

by:tconsidine
tconsidine earned 1000 total points
ID: 33545043
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33545051
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 33545159
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33545394
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 33545904
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 33546871
>>>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
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 33546930
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 33547787
>  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

Featured Post

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Show Me the Guide
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Math to Detect and Slow Down Spamming Technique
Article by: Gurpreet Singh
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Eventful.com OAuth in Coldfusion
Article by: ColdfusionAdmins
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
How to export Office 365 mailboxes to PST using eDiscovery
Video by: CodeTwo
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Why Use Three Nodes in a Database Cluster?
Video by: Percona
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Advertise Here
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here