How to Display Number Of Records Into Pages

[Beginner]

Hi everyone,
I want to display records in page form that is 10 records in a page. If number of records is more than 10 a next button will be enabled and on click of next it will display the next 10 records and so on.
I have done the coding as below and it is working fine. But every time next is pressed form is submitted and data has to be fetched from database and displayed is there a way of doing this without form submit so that performance is better.

Any help would be appreciated.


Here is a part of the Code:

define variable pv_strtdt as date initial today no-undo.
define variable pv_enddt as date initial today no-undo.

define temp-table tmp_drso_mstr
field tmp_mstr_nbr like drsom_nbr
field tmp_mstr_due like drsom_due
field tmp_mstr_type like drsom_type
field tmp_mstr_value like drsom_value
field tmp_mstr_tons like drsom_tons
index tmp_mstr_nbr is primary tmp_mstr_nbr
index tmp_mstr_due tmp_mstr_due
index tmp_mstr_type tmp_mstr_type.

for each drso_mstr no-lock
where drsom_customer = pvg_mfgid and
drsom_due >= pv_strtdt and
drsom_due <= pv_enddt and
drsom_pro = no break by drsom_type:
accumulate drsom_value (total by drsom_type)
drsom_ton (total by drsom_type).
pv_lncnt = pv_lncnt + 1.
create tmp_drso_mstr.
assign
tmp_drso_mstr.tmp_mstr_nbr = drsom_nbr
tmp_drso_mstr.tmp_mstr_due = drsom_due
tmp_drso_mstr.tmp_mstr_type = drsom_type
tmp_drso_mstr.tmp_mstr_value = drsom_value
tmp_drso_mstr.tmp_mstr_tons = drsom_tons.
end. /* for each drso_mstr no-lock */

<table border="1" cellspacing="0" cellpadding="0">
<tr>
<td class="drsvconthead">
&nbsp;Order Number&nbsp;
</td>
<td class="drsvconthead">
&nbsp;Due date&nbsp;
</td>
<td class="drsvconthead">
&nbsp;Order Type&nbsp;
</td>
<td class="drsvconthead">
&nbsp;Value (Rs)&nbsp;
</td>
<td class="drsvconthead">
&nbsp;Tons&nbsp;
</td>
</tr>
<%
pv_pgcnt = 0.
loopdispg:
for each tmp_drso_mstr: /* this logic is for displaying the required page */
if pv_pgcnt ge (pv_lnstart + 9) then leave.
pv_pgcnt = pv_pgcnt + 1.
if pv_pgcnt lt pv_lnstart then next loopdispg.
if pv_lncol then do:
assign
pv_rowcol = "drsvcontdata2"
pv_lncol = no.
end. /* if pv_lncol then do: */
else do:
assign
pv_rowcol = "drsvcontdata1"
pv_lncol = yes.
end. /* else do of if pv_lncol */


%>
<tr class="`pv_rowcol`">
<td><a href="javascript:drsodetview('`tmp_drso_mstr.tmp_mstr_nbr`')">
`tmp_drso_mstr.tmp_mstr_nbr`
</a>
</td>
<td>
`tmp_drso_mstr.tmp_mstr_due`
</td>
<td>
`tmp_drso_mstr.tmp_mstr_type`
</td>
<td align='right'>
`string(tmp_drso_mstr.tmp_mstr_value, ">>,>>>,>>9.99")`
</td>
<td align='right'>
`string(tmp_drso_mstr.tmp_mstr_tons, ">>,>>9.99")`
</td>
</tr>
<%
end. /* for each tmp_drso_mstr: */
if pv_lnstart lt 10 then
pv_prevbut = "disabled".
if (pv_pgcnt ) ge pv_lncnt then
pv_nextbut = "disabled".
%>
<tr>
<td colspan="5" class="drsvcontfoot">
<input `pv_prevbut` class="drsopgbutton" type="button" value="<< Prev" onClick="page_prev();">
Displaying `pv_lnstart` to `pv_pgcnt ` of `pv_lncnt`
<input `pv_nextbut` class="drsopgbutton" type="button" value="Next >>" onClick="page_next();">
</td>
</tr>
</table>

 

[lee.bourne]

Hi,

If you want to do away with the form submit you'll need to start looking into Ajax. This way using javascript you can make a call in the background to retrieve the next 10 records as an xml file. You'll need to write a Progress CGI Wrapper app to return the records in xml format for that. To make the Ajax side of things easier you might want to look at http://www.prototypejs.org/.

Alternatively you could look at the Yahoo YUI toolkit which will handle all the pagination for you. http://developer.yahoo.com/yui/datatable/

Good luck,

Lee

 

[Beginner]

Thanks a lot I will try it out

 

[nevaeh.aaric]

When I sort dates from my sql database, the records sort all in one time.I would like to sort only 20/25 records and after to clik a button and sort other 20/25 records and so on.

 

[webguy]

Depending on the number of records its usually always better to use a server side request for paging versus all these client side javascript table paging methods. It would be nice if progress included a paging class with webspeed but again this is Progress and for some reason they like to be in a vaccum.

This is a very concise clear video of doing it with PHP. Some of this can translate over to progress although progress doesnt have some of these native built in methods like limit. But it gives a general idea.

[video=youtube;wC0uc_TkdR0]http://www.youtube.com/watch?v=wC0uc_TkdR0[/video]

 

[Cecil]

Hi there I watch the youTube video and whilst watching it I wrote the Pagination.html WebSpeed code.

I know the ABL does not have a Limit factor within a query but under 10.2A it does have a batch size attribute on temp tables.

So using a combintation of dynamic Buffer, temp-table ,Query, Data-source and ProDataSet you can say the to the ABL "Hey! Go and get me page 'X' from a query and only return 'Y'" number of records".

In the past I use to count the number temp-table records created and then break out of the loop once the limit was achived and pass back-n-forth the rowid of the last known record fetch record. Also I would have methods for "first", "previous","next" and "last" which got a little be complicated sometimes. However this should now be made simple with this code.

I only wrote the code this morning and I have not included any error checking or error handling. To run it just simple change the global define xcDBTable to a table on your database and execute it in the normal manor from the web browser.

Hope this helps you out.

View attachment Pagination.zip

 

[webguy]

Nice Cecil,

One question. I dont really use prodata sets that much. Why are you creating a handle for a prodata set and a dynamic query and a dynamic temp table? Seems like a lot of layers. Why not just do the dynamic query with the PRESELECT so you can get the total number of records and then feed that to a tamp table?. I would think that might be faster or less resource intensive or maybe Im wrong?

By the way that kind of effort deserves some paypal action. Very kind of you to provide that code. Kind of shows me a bit about prodatasets which I dont use at all.

 

[MaximMonin]

The reason: to use method Prodataset:Fill to insert all records to temp-table.

Right now I do a project where there are a lot transfers from one db to anoother. So i learned prodataset recently and I say it gives some bonuses:
Example: Exporting 5 tables to 1 xml file:

define temp-table t_accplan  NO-UNDO LIKE-SEQUENTIAL acc-plan.
define temp-table t_accounts NO-UNDO LIKE-SEQUENTIAL accounts.
define temp-table t_antype   NO-UNDO LIKE-SEQUENTIAL AnType.
define temp-table t_accobj   NO-UNDO LIKE-SEQUENTIAL Apnd-count-obj.
define temp-table t_currency NO-UNDO LIKE-SEQUENTIAL currency.

DEFINE DATASET accdata for t_accplan, t_accounts, t_antype, t_accobj, t_currency.
define variable hDS as handle.

DEFINE DATA-SOURCE ds_accplan  FOR acc-plan.
DEFINE DATA-SOURCE ds_accounts FOR accounts.
DEFINE DATA-SOURCE ds_antype   FOR AnType.
DEFINE DATA-SOURCE ds_accobj   FOR Apnd-count-obj.
DEFINE DATA-SOURCE ds_currency FOR currency.

BUFFER t_accplan:ATTACH-DATA-SOURCE (DATA-SOURCE ds_accplan:HANDLE ).
BUFFER t_accounts:ATTACH-DATA-SOURCE(DATA-SOURCE ds_accounts:HANDLE).
BUFFER t_antype:ATTACH-DATA-SOURCE  (DATA-SOURCE ds_antype:HANDLE  ).
BUFFER t_accobj:ATTACH-DATA-SOURCE  (DATA-SOURCE ds_accobj:HANDLE  ).
BUFFER t_currency:ATTACH-DATA-SOURCE(DATA-SOURCE ds_currency:HANDLE  ).

hDS = DATASET accdata:HANDLE.

hDS:FILL ().
run src/transfer/XmlWriteDataset.p (FileName, DATASET-HANDLE hDS).

On the other side I just read this file and get 5 dymanic temp-tables created and filled with data by using only 1 operator XML-READ.

 

[Cecil]

Nice Cecil,

One question. I dont really use prodata sets that much. Why are you creating a handle for a prodata set and a dynamic query and a dynamic temp table? Seems like a lot of layers. Why not just do the dynamic query with the PRESELECT so you can get the total number of records and then feed that to a tamp table?. I would think that might be faster or less resource intensive or maybe Im wrong?

By the way that kind of effort deserves some paypal action. Very kind of you to provide that code. Kind of shows me a bit about prodatasets which I dont use at all.

Ok, First thing I should explain it that I don't use ProDataSet's that much my self and the main reason I'm using so many dynamics objects was to make this code portable to make it work on any database schema. I did not want to create sample code which needed to be compiled against the Progress Sports database because not every developer has development has that environment setup (I know that I don't..).

What inspired me about writing this sample code is firstly I'm expanding my own knowledge about new methodology's in the ABL and the secondly it was the Buffer BATCH-SIZE attribute. I have never used this attribute before and I want to see how I could use it. Also this code is no way perfect and I bet someone in the Progress community could improve on it.

The Pagination.html seamed like the perfect scenario in which to use this newish attribute, to take theory into practice. But BATCH-SIZE can only be used in combination with ProDataSet. Once I started using a ProDataSet I then continued on to using a DataSource object, and again it's the first time I've used this object. Hopefully you can see how my development continue rolled into using Dynamic Queries and Dynamic-Temp Tables.

One of the trade-offs to using dynamic objects is performance and resources, yes I do believe using dynamic objects can/does have a performance issue but it is at an acceptable level. If using dynamic object was so bad for performance, no developer would ever use them.

Hey enjoy the code, it was kind of fun to do.

 

[webguy]

Ok, First thing I should explain it that I don't use ProDataSet's that much my self and the main reason I'm using so many dynamics objects was to make this code portable to make it work on any database schema. I did not want to create sample code which needed to be compiled against the Progress Sports database because not every developer has development has that environment setup (I know that I don't..).

What inspired me about writing this sample code is firstly I'm expanding my own knowledge about new methodology's in the ABL and the secondly it was the Buffer BATCH-SIZE attribute. I have never used this attribute before and I want to see how I could use it. Also this code is no way perfect and I bet someone in the Progress community could improve on it.

The Pagination.html seamed like the perfect scenario in which to use this newish attribute, to take theory into practice. But BATCH-SIZE can only be used in combination with ProDataSet. Once I started using a ProDataSet I then continued on to using a DataSource object, and again it's the first time I've used this object. Hopefully you can see how my development continue rolled into using Dynamic Queries and Dynamic-Temp Tables.

One of the trade-offs to using dynamic objects is performance and resources, yes I do believe using dynamic objects can/does have a performance issue but it is at an acceptable level. If using dynamic object was so bad for performance, no developer would ever use them.

Hey enjoy the code, it was kind of fun to do.

Makes total sense Cecil. Works like a reusable object/class.