[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

record per page

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
lulu50
Asked:
lulu50
  • 15
  • 5
  • 4
1 Solution
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
 
lulu50Author Commented:
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
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
lulu50Author Commented:
<cfif val(QSearchResult.ParentIssueID)>
 <!-- //Child Row -->


 <cfif not val(QSearchResult.ParentIssueID)>
 <!-- //Parent Row -->
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
 
lulu50Author Commented:
I see the parentIssueID
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
 
lulu50Author Commented:
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
 
lulu50Author Commented:
Any help on this?
0
 
lulu50Author Commented:
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
 
lulu50Author Commented:
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
 
gdemariaCommented:
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
 
lulu50Author Commented:
gdemaria,

Yes, it make sense.  Let me try it and I'll let you know
0
 
lulu50Author Commented:
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
 
lulu50Author Commented:
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
 
lulu50Author Commented:
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
 
gdemariaCommented:
Glad it worked!

Didn't you mean to accept my suggestion as the solution?    EE says you are accepting your own comment...
0
 
lulu50Author Commented:
oh, I think I did something wrong
0
 
lulu50Author Commented:
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
 
lulu50Author Commented:
No I don't want to accept my own comment.

Please, someone fix this.

Thanks,
Lulu
0
 
gdemariaCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
My 10 points, Even the Design is not too good, I am having hard time reading it
0
 
lulu50Author Commented:
EE please assign 500 points to gdemaria for giving me the correct solution to my question.  

Thanks,
Lulu
0
 
gdemariaCommented:
Not sure if the "object" button is working, trying again..
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now