Solved

record per page

Posted on 2014-04-29
24
257 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 15

Expert Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
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 15

Expert Comment

by:myselfrandhawa
Comment Utility
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
 

Author Comment

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


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

Expert Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
I see the parentIssueID
0
 
LVL 15

Expert Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
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
Comment Utility
Any help on this?
0
 

Author Comment

by:lulu50
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:lulu50
Comment Utility
gdemaria,

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

Author Comment

by:lulu50
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
oh, I think I did something wrong
0
 

Author Comment

by:lulu50
Comment Utility
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
Comment Utility
No I don't want to accept my own comment.

Please, someone fix this.

Thanks,
Lulu
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
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 15

Expert Comment

by:myselfrandhawa
Comment Utility
My 10 points, Even the Design is not too good, I am having hard time reading it
0
 

Author Comment

by:lulu50
Comment Utility
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
Comment Utility
Not sure if the "object" button is working, trying again..
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now