Solved

record per page

Posted on 2014-04-29
24
264 Views
Last Modified: 2014-05-10
Hi,

I can't get it to display number of record per page based on the parent list

so every 20 parent issues should be listed on each page.

right now it count the parent and the child records up to 20.  but I just want it to display based on the parent only.

Thank you,

      
<CFIF isDefined("QSearchResult") and #QSearchResult.RecordCount# gt 0>

<cfparam name="url.page" default="1">
<cfset page_links_shown = 5>
<cfset records_per_page = 20>

<cfset total_pages = ceiling(QSearchResult.RecordCount / records_per_page)>

<cfif val(url.page) lt 1>
  <cfset url.page = 1>
<cfelseif val(url.page) gt total_pages>
  <cfset url.page = total_pages>
</cfif>
<cfset start_record = ((url.page - 1) * records_per_page ) + 1>

<table style="width:100%;border: solid 1px #dddddd;" id="ResultListTbl">
<tr style="background-color:#d0d2d3;font-weight:bold;">
 <th style="width:100px;padding-left:5px;">Issue ID <img id="SortByIssueID" style="cursor:hand;" src="images/ArrowDowndefault.png" alt="Sort by issue ID" onclick="FlipImage(1);" /></th>
 <th style="width:150px;">Ticket# <img id="SortByTicketNumber" style="cursor:hand;" src="images/ArrowDowndefault.png" alt="Sort by ticket number" onclick="FlipImage(2);" /></th>
 <th style="width:210px;">Category <img id="SortByCategory" style="cursor:hand;" src="images/ArrowDowndefault.png" alt="Sort by category" onclick="FlipImage(3);" /></th>
 <th style="width:350px;">Title <img id="SortByTitle" style="cursor:hand;" src="images/ArrowDowndefault.png" alt="Sort by title" onclick="FlipImage(4);" /></th>
 <th style="width:80px;">Status <img id="SortByStatus" style="cursor:hand;" src="images/ArrowDowndefault.png" alt="Sort by status" onclick="FlipImage(5);" /></th>
 <th style="width:110px;">Date <img id="SortByDate" style="cursor:hand;" src="images/ArrowDowndefault.png" alt="Sort by date" onclick="FlipImage(6);" /></th>
  <th style="width:110px;">&nbsp;</th>
</tr>

<cfset previousParentIssueID = QSearchResult.ParentIssueID> 
<cfoutput query="QSearchResult" startrow="#start_record#" maxrows="#records_per_page#">
 <cfif val(QSearchResult.ParentIssueID)> 
 <!-- //Child Row -->
 <tr class="bg2row">
   <td style="padding-left:20px;">#QSearchResult.ParentIssueID#(#QSearchResult.issueID#)</td>
 <td>#QSearchResult.ticketNumber# <cfif #QSearchResult.TicketNumberTxt# neq "">#QSearchResult.TicketNumberTxt#</cfif></td>
 <td>#QSearchResult.Category#</td>
 <td>#QSearchResult.TitleOfIssue#</td>
 <td>#QSearchResult.Status#</td>
  <td>#DateFormat(QSearchResult.DateCreated, "m/d/yyyy")#</td>
    <td><a href="LogEntry.cfm?TechType=#QSearchResult.CategoryTypeID#&ID=#QSearchResult.issueID#"><img src="images/edit.png" alt="Edit Log" /></a>
    <img src="images/view.png" alt="View Log" onClick="ViewDialog(900,600,#QSearchResult.CategoryTypeID#,#QSearchResult.issueID#);" />
    </td>
    </tr>  
 <cfelse>

 <tr class="bgrow">
	 <td style="font-weight:bold;padding-left:5px;">#QSearchResult.issueID#</td>
	 <td style="font-weight:bold;">#QSearchResult.TicketNumber# <cfif #QSearchResult.TicketNumberTxt# neq "">#QSearchResult.TicketNumberTxt#</cfif></td>
	 <td style="font-weight:bold;">#QSearchResult.Category#</td>
	 <td style="font-weight:bold;">#QSearchResult.TitleOfIssue#</td>
	 <td style="font-weight:bold;">#QSearchResult.Status#</td>
	  <td style="font-weight:bold;">#DateFormat(QSearchResult.DateCreated, "m/d/yyyy")#</td>
	  <td style="color:dark blue;">
      
      <a href="LogEntry.cfm?TechType=#QSearchResult.CategoryTypeID#&ID=#QSearchResult.issueID#"><img src="images/edit.png" alt="Edit Log" /></a>
	  <img src="images/view.png" alt="View Log" onClick="ViewDialog(900,600,#QSearchResult.CategoryTypeID#,#QSearchResult.issueID#);" />
       <cfif not val(previousParentIssueID)>
      <a href="LogEntry.cfm?TechType=#QSearchResult.CategoryTypeID#&IDP=#QSearchResult.issueID#"><img src="images/add.png" alt="Add Log" /></a>
      </cfif>
	  </td>
  </tr>
</cfif>


</cfoutput>

</table> 

Open in new window

0
Comment
Question by:lulu50
  • 15
  • 5
  • 4
24 Comments
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40030488
Hi Lulu,

Here is one of the very easy tutorials for pagination:

http://tutorial20.easycfm.com/

http://tutorial47.easycfm.com/ - This is quite old don't use it

http://tutorial290.easycfm.com/

http://tutorial310.easycfm.com/

http://tutorial375.easycfm.com/ -  This  is Good enough

http://tutorial530.easycfm.com/

Now to your code you are missing the next/previous links which should be there to navigate to other records, add those, follow the above one tutorials and you will be get going

Cheers
0
 

Author Comment

by:lulu50
ID: 40030610
I am still confused and not sure how to do it.

I have the paging the next and previous, but I just didn't post all my code.

 <cfoutput>
<hr>
<cfif url.page EQ 1>
   &laquo; Prev
<cfelse>
   <a href="index.cfm?page=#url.page-1#">&laquo; Prev</a>
</cfif>
&nbsp;|&nbsp;
<cfloop index="pp" from="1" to="#total_pages#">
  <cfif pp eq url.page>
   <strong>#pp#</strong> <!--- current page ---->
  <cfelse>
   <a href="index.cfm?page=#pp#">#pp#</a>
  </cfif>&nbsp;|&nbsp;
</cfloop>
<cfif url.page lt total_pages>
   <a href="index.cfm?page=#url.page+1#">Next &raquo;</a>
<cfelse>
   Next &raquo;
</cfif>
<hr>
</cfoutput>

Open in new window

0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40031304
how you differentiate between parent and child in the code

for example: for parent suppose you kept it as: 0 and for childrens you kept it as 1

<cfset previousParentIssueID = QSearchResult.ParentIssueID>
 <cfif val(QSearchResult.ParentIssueID) IS 0>

Parent Values
<cfelse>
 Children values
</cfif>
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:lulu50
ID: 40031742
<cfif val(QSearchResult.ParentIssueID)>
 <!-- //Child Row -->


 <cfif not val(QSearchResult.ParentIssueID)>
 <!-- //Parent Row -->
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40031797
do the cfdump of the above two values what you are seeing
<cfif val(QSearchResult.ParentIssueID)>
 <!-- //Child Row -->


 <cfif not val(QSearchResult.ParentIssueID)>
 <!-- //Parent Row --> 

Open in new window

0
 

Author Comment

by:lulu50
ID: 40034549
I see the parentIssueID
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40034571
So if you see parentIssueID, probably the query should should only records which are parent in case the example like 0 for parent and 1 for child

Just show me a query result so i can try in editor and see what exactly it comes out and what you need
0
 

Author Comment

by:lulu50
ID: 40037261
CATEGORY     CATEGORYTYPEID      DATECREATED     ISSUEID      PARENTISSUEID    STATUS
Technology Impacts     1          2014-05-02 00:00:00.0  711               [empty string]      Open
Technology Impacts     1          2014-05-02 00:00:00.0  712                  711                     Open  
Other Call Drivers         4          2014-05-02 00:00:00.0  710               [empty string]      Open  
Staffing Issues               2         2014-05-02 00:00:00.0  704                [empty string]      Open  
Staffing Issues               2          2014-05-02 00:00:00.0  707               [empty string]      Open
0
 

Author Comment

by:lulu50
ID: 40042422
Any help on this?
0
 

Author Comment

by:lulu50
ID: 40044241
gdemaria,

do you remember when you posted this for me.

http://www.experts-exchange.com/Programming/Languages/Scripting/Cold_Fusion_Markup_Language/Q_28386881.html


by: gdemariaPosted on 2014-03-12 at 07:23:30ID: 39924393

Rank: Ace









 A couple questions -

when counting the 10 records to show on a page.   Is that ten comprised of parent and child issues together?    Or are we counting only 10 parent issues and showing any number of child issues that may happen to belong?

I assume it is the first, that the total number of records will be 10.  But when you go to page two, you have to be aware that you may be showing a child issue with no parent on the same page.    For example, if you have a parent issue with 5 child issues, the parent issue could show as record 8 on the first page, then show 1 child issue on the first page, then on the second page would show just 3 child issues with no parent.   That is ok?

How many total records might there be?   For example, there may be about 100 total records which would be 10 pages of 10 records each?   Or are we talking thousands?    If the number if fairly small, we can do it the easy way.
0
 

Author Comment

by:lulu50
ID: 40044249
What I am looking for is to have 10 records show on a page comprised of parent only.

but not ten comprised of parent and child issues together.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 40045310
There are a couple ways to do this.   Perhaps the easiest is to change the main query on your page to fetch only the parent records, that way you can control the number of them.  
Then inside the loop when you are displaying each parent record, put in a query to fetch the children for that parent and loop to display each one.   (that would be an inner loop of the outside parent loop).

Does that make sense?
0
 

Author Comment

by:lulu50
ID: 40047464
gdemaria,

Yes, it make sense.  Let me try it and I'll let you know
0
 

Author Comment

by:lulu50
ID: 40047581
I did the first step, filtered only the parent

but, I don't know how to do the next step. Fetch the children for the parent.

This is going to get only the child record

<cfset ResultIssueIDs = "">

<cfloop index="cc" list="#valueList(QSearchResult.IssueID)#">
 <cfif listFind(ResultIssueIDs,cc) eq 0> 
     <cfset ResultIssueIDs = listAppend(ResultIssueIDs,cc)>
 </cfif> 
</cfloop> 


<CFIF isDefined("QSearchResult") and #QSearchResult.RecordCount# gt 0>
 	<cfquery name = "QISSIDs"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="cce">
		 SELECT case when L.ParentIssueID is null then L.IssueId
         else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
         , L.UserID
		 , L.ParentIssueID
		 , T.TicketNumber
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
         , L.CategoryTypeID
         , C.Category
         , L.TicketNumberTxt
   FROM LogEntry L
     LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
     LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
     LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID
   	 where L.ParentIssueID in  (#ResultIssueIDs#)
   	 order by IssueID
	</cfquery>
    
    
   <cfdump var="#QISSIDs#" /> 
<cfoutput>SQL: #cce.SQL#</cfoutput> 

Open in new window



this is where I need to fetch the children for the parent. 

<cfoutput query="QSearchResult" startrow="#start_record#" maxrows="#records_per_page#">

 <cfif (isDefined("QISSIDs") and #QISSIDs.RecordCount# gt 0) and val(QSearchResult.IssueID) eq val(QISSIDs.ParentIssueID)> 

 <!-- //Child Row -->
 <tr class="bg2row">
   <td style="padding-left:20px;">#QISSIDs.ParentIssueID#(#QISSIDs.issueID#)</td>
 <td>#QISSIDs.ticketNumber# <cfif #QISSIDs.TicketNumberTxt# neq "">#QISSIDs.TicketNumberTxt#</cfif></td>
 <td>#QISSIDs.Category#</td>
 <td>#QISSIDs.TitleOfIssue#</td>
 <td>#QISSIDs.Status#</td>
  <td>#DateFormat(QISSIDs.DateCreated, "m/d/yyyy")#</td>
    <td>
     <cfif (isdefined('QUser') and #QUser.RecordCount# gt 0) and (right(QUser.EId, len(QUser.EId)-1) eq QISSIDs.UserID or right(QUser.LeaderBadgeID, len(QUser.LeaderBadgeID)-1) eq QISSIDs.UserID)>
    <a href="LogEntry.cfm?TechType=#QISSIDs.CategoryTypeID#&ID=#QISSIDs.issueID#"><img src="images/edit.png" alt="Edit Log" /></a>
    </cfif>
    <img src="images/view.png" alt="View Log" onClick="ViewDialog(900,600,#QISSIDs.CategoryTypeID#,#QISSIDs.issueID#);" />
    </td>
    </tr>  
 <cfelse>
<!--- <cfset start_record = #start_record# - 1>
<cfdump var="#start_record#"> --->

 <!-- //Parent Row -->
 <tr class="bgrow">
	 <td style="font-weight:bold;padding-left:5px;">#QSearchResult.issueID#</td>
	 <td style="font-weight:bold;">#QSearchResult.TicketNumber# <cfif #QSearchResult.TicketNumberTxt# neq "">#QSearchResult.TicketNumberTxt#</cfif></td>
	 <td style="font-weight:bold;">#QSearchResult.Category#</td>
	 <td style="font-weight:bold;">#QSearchResult.TitleOfIssue#</td>
	 <td style="font-weight:bold;">#QSearchResult.Status#</td>
	  <td style="font-weight:bold;">#DateFormat(QSearchResult.DateCreated, "m/d/yyyy")#</td>
	  <td style="color:dark blue;">
         
           <cfif (isdefined('QUser') and #QUser.RecordCount# gt 0) and (right(QUser.EId, len(QUser.EId)-1) eq QSearchResult.UserID or right(QUser.LeaderBadgeID, len(QUser.LeaderBadgeID)-1) eq QSearchResult.UserID)>
      <a href="LogEntry.cfm?TechType=#QSearchResult.CategoryTypeID#&ID=#QSearchResult.issueID#"><img src="images/edit.png" alt="Edit Log" /></a>
      </cfif>
	  <img src="images/view.png" alt="View Log" onClick="ViewDialog(900,600,#QSearchResult.CategoryTypeID#,#QSearchResult.issueID#);" />
       <cfif not val(QSearchResult.ParentIssueID)>
      <a href="LogEntry.cfm?TechType=#QSearchResult.CategoryTypeID#&IDP=#QSearchResult.issueID#"><img src="images/add.png" alt="Add Log" /></a>
      </cfif>
	  </td>
  </tr>
</cfif>

</cfoutput>

Open in new window

0
 

Author Comment

by:lulu50
ID: 40047680
gdemaria,

I got it to work!!!!!!

I first got all the parents then I fetch the children for the parent.

It works!!!!!

Thank you so much.
0
 

Author Comment

by:lulu50
ID: 40053472
I've requested that this question be closed as follows:

Accepted answer: 0 points for lulu50's comment #a40047464

for the following reason:

Thanks,
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40047771
Glad it worked!

Didn't you mean to accept my suggestion as the solution?    EE says you are accepting your own comment...
0
 

Author Comment

by:lulu50
ID: 40047970
oh, I think I did something wrong
0
 

Author Comment

by:lulu50
ID: 40047978
I want to accept gdemaria solution.

EE keep on changing the design on me.

So, I don't know to what I have clicked.

Please, EE fix this for me.
0
 

Author Comment

by:lulu50
ID: 40047983
No I don't want to accept my own comment.

Please, someone fix this.

Thanks,
Lulu
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40048138
I know, the new design is not good AGAIN!
They need to have clean boxes and / or headers around each post.    It's so "clean" that's MESSY!!

EE please see previous comment and un-assign points for reassignment. ..
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40049453
My 10 points, Even the Design is not too good, I am having hard time reading it
0
 

Author Comment

by:lulu50
ID: 40053449
EE please assign 500 points to gdemaria for giving me the correct solution to my question.  

Thanks,
Lulu
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40053473
Not sure if the "object" button is working, trying again..
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 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