How to use pagination widget with grid widget

[LY_10838955]

Hi Everyone,

I have a grid data of 1000's row now I want split data into different pages using pagination widget please 

help me to write logic and implementation as I am new to thingworx so not much idea on it

Thanks and Regards,

Lavkush.

[LY_10691079]

Hi, 

I have a SQL service which is giving an infotable output and I have bind this infotable to a grid and now I want to paginate this grid data so please recommend how can I do this 

Thing Name: TestThing
DataShape: TestDS
service: SQLData (this is to fetch data from postgres )

now I want to implement pagination to the grid data so please recommend/implement the solution

Regards,

Lav

[Catalina]

Hello,

It was brought to our attention that these were duplicate questions. We have merged the two, so all the relevant information is in one place.

Thank you for your participation in the Community!

Best regards,

[Rocko]

Have you looked into the help center yet?

https://support.ptc.com/help/thingworx/platform/r9.5/en/#page/ThingWorx/Help/Mashup_Builder/Widgets/Grid_UsingTheGridWithaPaginationWidget.html

[LY_10691079]

Hi @Rocko 
I tried looking in to this but still not sure how to create a service which will fulfill my requirement
I have a SQL service:

select * from Product_Table;

Can you give me a basic Idea that how can I include this pageNumer and pageSize to this service so that it can fetch the actual 
results in the mashup that will be appreciated

Thanks,

Lav. 

[Rocko]

In general you have two options. Getting the all of the data from the db into an infotable and show it only pagewise to the users. This is shown here: https://support.ptc.com/help/thingworx/platform/r9.5/en/#page/ThingWorx/Help/Mashup_Builder/Widgets/CreatingAPaginationService.html#wwID0EN3MDB

You would have no more DB queries when the user browses through the pages as TWX already got all the data. The disadvantage is that you pull all data ahead, even if it might never get rendered.

The alternative is to put the paging information into the query, using LIMIT and OFFSET keywords, e.g.

SELECT vehicle_number, dept_id, type FROM employee
ORDER BY employee.id DESC LIMIT 20 OFFSET 20;

which will get you entries 20 to 40. In that case you will have to add [[]] placeholders into the query (see https://www.ptc.com/en/support/article/CS413368) The advantage is that this way you only pull the data that is being displayed, the disadvantage is that when the table changes between the paging queries it might show updated data. Plus you will have to add some logic to determine how many rows there are in total, so the pagination widget knows how many pages to display.

[LY_10838955]

Hi @Rocko 

Thanks for Your reply,

Although I want to use first method only for now.

But still I am not able to create the exact service even though I tried a lot and still trying 

to figure out the service creation but I am getting the error 

so if you don't mind can you please create the service as I am stuck here for the day

The Approach which I am used is:

Service 1 (SQL Service) : SQL service to get the data from DB 
service 1: 
select * from Test_Table;

Service 2 (Javascript) :  JavaScript service to store the result and paginate the result but could not  succeeded yet.
service 2 is below:

// Define page size and page number
var pageSize = parseInt(pageSize) || 19;
var pageNumber = parseInt(pageNumber) || 1;

// Retrieve data from the service
var dataFromService = Things["Test_Database_Thing"].Test_Query();

// Check if dataFromService is an InfoTable and has rows
if (dataFromService && dataFromService.rows) {
// Extract rows from InfoTable
var dataArray = [];
var rows = dataFromService.rows;

// Use InfoTableFunctions to convert InfoTable to an array
dataArray = Resources["InfoTableFunctions"].GetTableRowsAsArray({
infoTable: dataFromService
});

var totalRecords = dataArray.length;
var startIndex = (pageNumber - 1) * pageSize;
var endIndex = startIndex + pageSize;

// Adjust indices to be within bounds
if (startIndex >= totalRecords) {
startIndex = totalRecords;
}
if (endIndex > totalRecords) {
endIndex = totalRecords;
}

// Slice array to get paginated data
var paginatedData = dataArray.slice(startIndex, endIndex);

// Define the shape of the InfoTable based on the original data shape
var dataShape = dataFromService.dataShape;

// Create a new InfoTable with the specified data shape
var resultTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
dataShape: dataShape
});

// Add rows to the new InfoTable
for (var i = 0; i < paginatedData.length; i++) {
resultTable.addRow(paginatedData[i]);
}

// Set the output
output = resultTable;
} else {
throw new Error("Expected InfoTable from Test_Query service.");
}

If possible, please provide the whole implementation Thanks in Advance!

Thanks and Regards,

Lav

[Rocko]

My recommendation would be to implement the example from the help, learn from that and then transfer the solution to your case.

Anyhow, attached is a basic sample, check mashup PaginationDemo.