1 (edited by thezimguy 2018-08-07 17:18:07)

Topic: [SOLVED] TableGrid with Pagination and Offset

Please how do I create pagination for tableGrid using buttons as next, previous, 1,2,3..., end etc
So that on form load. First 500 records will be loaded and when I click the next button the next 500 records will load without the first 500

@thezimguy

Re: [SOLVED] TableGrid with Pagination and Offset

Hello.


Please download latest beta version, I have added new property dbOffSet for the TableGrid
https://www.dropbox.com/s/wutj7mnux7f3a … a.zip?dl=0


Script:

procedure Form1_OnShow (Sender: TObject; Action: string);
begin
    Form1.bSearch.Click;
end;

procedure Form1_bSearch_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbOffSet := 0;
end;

procedure Form1_bNext_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbOffSet := Form1.GridEmployees.dbOffSet + 500;
    Form1.GridEmployees.dbUpdate;
end;

procedure Form1_bPrev_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbOffSet := Form1.GridEmployees.dbOffSet - 500;
    if Form1.GridEmployees.dbOffSet < 0 then Form1.GridEmployees.dbOffSet := 0;
    Form1.GridEmployees.dbUpdate;
end;

begin
    Form1.GridEmployees.dbLimit := 500;
end.

Project with the example:

Post's attachments

Attachment icon TableGrid pagination.zip 345.21 kb, 816 downloads since 2018-07-30 

Dmitry.

3 (edited by thezimguy 2018-07-31 10:51:34)

Re: [SOLVED] TableGrid with Pagination and Offset

Thank you so much boss
I'm really grateful
I will try it and communicate back

You are the best
Dmitry/MVD


It works like magic. You have really made it easy to load more data in the tablegrid

Guys check this new feature out. You will really love it

Don't forget to download the 4.6 beta] version Dmitry uploaded

Post's attachments

Attachment icon TableGrid pagination.png 14.61 kb, 266 downloads since 2018-07-31 

@thezimguy

4 (edited by thezimguy 2018-08-03 04:44:40)

Re: [SOLVED] TableGrid with Pagination and Offset

With the help of Dmitry
I have been able to add the pagination offset in a combo box so you can select the number of rows to display in the table Grid.
I'm really enjoying MVD.

Check this out

procedure Form1_OnShow (Sender: TObject; Action: string);
begin
    Form1.cbPages.ItemIndex:=4;////this will show the first 100 records
    Form1.GridEmployees.dbLimit := StrToInt((Form1.cbPages.Text));
    Form1.bSearch.Click;
end;

procedure Form1_bSearch_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbLimit := StrToInt((Form1.cbPages.Text));
    Form1.GridEmployees.dbOffSet := 0;
end;             

procedure Form1_bNext_OnClick (Sender: TObject; var Cancel: boolean);
begin                                   
    Form1.GridEmployees.dbOffSet := Form1.GridEmployees.dbOffSet + StrToInt((Form1.cbPages.Text));
    Form1.GridEmployees.dbLimit := StrToInt((Form1.cbPages.Text));
    Form1.GridEmployees.dbUpdate;
end;

procedure Form1_bPrev_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbOffSet := Form1.GridEmployees.dbOffSet - StrToInt((Form1.cbPages.Text));
    Form1.GridEmployees.dbLimit := StrToInt((Form1.cbPages.Text));
    if Form1.GridEmployees.dbOffSet < 0 then Form1.GridEmployees.dbOffSet := 0;
    Form1.GridEmployees.dbUpdate;
end;

procedure Form1_cbPages_OnChange(Sender: TObject; var Cancel: boolean);
begin
    if (Form1.cbPages.ItemIndex =0) then
    begin
        Form1.cbPages.ItemIndex := 1;
    end;
end;
begin
    //Form1.GridEmployees.dbLimit := 500;
end.
Post's attachments

Attachment icon TableGrid pagination.zip 346.34 kb, 650 downloads since 2018-07-31 

@thezimguy

Re: [SOLVED] TableGrid with Pagination and Offset

This is the preview

Post's attachments

Attachment icon TableGrid pagination2.png 18.23 kb, 315 downloads since 2018-07-31 

@thezimguy

6 (edited by thezimguy 2018-12-31 10:01:32)

Re: [SOLVED] TableGrid with Pagination and Offset

Hello Dmitry,

procedure Form1_OnShow (Sender: string; Action: string);
begin
    Form1.TableGrid1.dbLimit := 10;
end;

procedure Form1_TableGrid1_OnChange(Sender: TObject; ACol, ARow: Integer);
var
    i : integer;
    rowFrom : integer = 0;
    rowTo : integer = 0;
    rowDBTotal : integer = 0;
begin
    rowFrom := Form1.TableGrid1.dbOffSet + 1;//rTo := Form1.TableGrid1.dbOffSet + Form1.TableGrid1.dbLimit;
    rowTo := Form1.TableGrid1.dbOffSet + Form1.TableGrid1.RowCount;
    //rowDBTotal := Form1.TableGrid1.dbTotalRows;//Total rows from database and not from tableGrid(not RowCount)

    Form1.lblRows.Caption := IntToStr(rowFrom) + ' to ' + IntToStr(rowTo) + ' of ' +  IntToStr(SQLExecute('SELECT COUNT(ID) FROM employees'));
    //so that instead of the above I will just do the following
    Form1.lblRows.Caption := IntToStr(rowFrom) + ' to ' + IntToStr(rowTo) + ' of ' +  IntToStr(rowDBTotal);
end;

Instead of

Form1.lblRows.Caption := IntToStr(rowFrom) + ' to ' + IntToStr(rowTo) + ' of ' +  IntToStr(SQLExecute('SELECT COUNT(ID) FROM employees'));

It will be

Form1.lblRows.Caption := IntToStr(rowFrom) + ' to ' + IntToStr(rowTo) + ' of ' +  IntToStr(rowDBTotal);

THE REASON FOR THIS IS I DONT WANT TO REQUERY THE DATABASE SINCE THE TABLEGRID HAS ALREADY QUERIED THE DATABASE ON UPDATE.
I mean that on tableGrid update or tableGrid load should come with the total rows from that query which should be attached to the tableGrid.
This will really help for the navigation through the table to be simplier and fast and will also reduce the number of queries to the database.
I hope to hear from you.
Regards

@thezimguy

Re: [SOLVED] TableGrid with Pagination and Offset

Any response?
Regards

@thezimguy

8 (edited by thezimguy 2019-02-05 06:13:00)

Re: [SOLVED] TableGrid with Pagination and Offset

Hello
I want to get the total rows in a table and also the total affect rows from a select Statement without coding.
I wish this can be attached to the tableGrid during the select of the Grid.
Example

Form1.tableGrid1.dbTotalRowsFromDatabase // this will count the total rows in the table of the database not tableGrid
Form1.tableGrid1.dbAffectRows// this will count the total affected rows in the database from the select Statement embed in the tableGrid.
Thank you in advance
@thezimguy

Re: [SOLVED] TableGrid with Pagination and Offset

Hi,
Not sure if I quite understand what you want to do, but does the attached help at all? 
It shows
1) the current row in the tablegrid
2) the number of rows selected into the grid
3) the total number of rows in the table
Regards,
Derek.

Post's attachments

Attachment icon thezimguyrows.zip 339.65 kb, 634 downloads since 2019-02-05 

10 (edited by thezimguy 2019-02-11 00:18:20)

Re: [SOLVED] TableGrid with Pagination and Offset

Thank you Derek for your quick response.
But what I'm requesting is a feature request or an update on the tableGrid with features like

Form1.tableGrid1.dbTotalRowsFromDatabase // this will count the total rows in the table of the database not tableGrid
Form1.tableGrid1.dbAffectRows// this will count the total affected rows in the database from the select Statement embed in the tableGrid.

I have been testing MVD for practice and testing purposes and wish to make some recommendations.
This becomes very handy when one uses the limit and page offset which will not list all data from the database.
This will also reduce the number of queries sent to the database since the tableGrid would have already queried the database for that data.
Thank you.

@thezimguy

Re: [SOLVED] TableGrid with Pagination and Offset

Any update on this? I'm on the same page with thezimguy.
I need to show the filtered record but I don't know what function to use.
It is also tedious work to query all filters just to show the total number of filtered record.
I have here a json on one of my PHP project.
Filtering the 4804 record, then paginating the filtered record (199) by 10.
I want to know if there's an existing function to use to get the recordsFiltered on the grid.
Thanks in advance.

Post's attachments

Attachment icon del.png 2.5 kb, 212 downloads since 2019-05-02 

brian

12

Re: [SOLVED] TableGrid with Pagination and Offset

I realise this is an old post, but, the solution provided uses a database table to store the pagination increments.

If you don't want to have an extra table, you can just add items to the combobox as a string.

So the code could be changed to:


    Form1.comboBox1.ItemIndex:=4;////this will show the first 100 records
    Form1.GridEmployees.dbLimit := StrToInt((Form1.combobox1.Text));
    Form1.bSearch.Click;
    form1.ComboBox1.Items.Clear;
    form1.ComboBox1.Items.Add('10');
    form1.ComboBox1.Items.Add('20');
     form1.ComboBox1.Items.Add('100');
    form1.combobox1.Items.Add('500')

I found this topic while searching to see if there was a way to paginate a gridtable by clickable page numbers at the bottom or top of the table, but it doesn't seem possible with MVD

On a clear disk you can seek forever