Reactive Web Application : Paginate a table with an AdvancedSQL Source.

Roel Bernardo

Solved

Question

Hi OutSys Devs out here,

I just wanted to know how to paginate your table when your data source is a AdvancedSQL in Reactive Web App.

26 Jan 2020

Marcelo Ferreira

Solution

Hi Roel,

You need to handle the pagination by yourself like this:

You also need to do another query with count to use that count on Navigation widget. and you need to handle the sort a bit differently.

I added an example on attachment for you to check.

Regards,

Marcelo

TableAndAdvancedSql.oml

26 Jan 2020

4 replies

Last reply 03 Jun

Show thread

Hide thread

Shuet Ling Tai

Hi after I added

ORDER BY @TableSort
OFFSET @StartIndex ROWS
FETCH NEXT @MaxRecords ROWS ONLY;

I am having the below query

12 Jan 2024

MUHAMMAD SYAZANI BIN MOHD FAUDZI

Hi Marcelo Ferreira,

Can you help me with this issue?

Before this, I tried your solution which is working fine but with the latest update, I think this issue is occurring.

04 Mar 2024

Mohamed Shahat

Replying to Shuet Ling Tai's comment on 12 Jan 2024 05:36:58

i have the same error, did you find the solution?

08 Jan 2025

Grzegorz Plichta

Replying to Mohamed Shahat's comment on 08 Jan 2025 08:43:45

Hi, I was looking for solution regarding pagination, turns out this part of code:
ORDER BY @TableSort

OFFSET @StartIndex ROWS

FETCH NEXT @MaxRecords ROWS ONLY;
Works great! But I was having problem after deploying my changes and testing them in SQL editor. Turns out problem was with my input values and also with my WHERE statements.
Later on I added nested query with group by (because of where condition they were returned doubled), turns out the best option is to use above part of query in main sql query even if nested one returns ids to search in main query.

So if you have error try to comment your query conditions one by one to find problem :)
Also use button "Test SQL" with provided values ( I have spend 2 hours checking why I don't get any records to return :)

SQL_query_structure.png

SQL_query_example1.png

03 Jun

Mangesh Phanse

I am using same solution for rest api. We can send single output parameter as response so not getting how to pass second out put parameter total count.

Restapicannothavetwooutout.png

25 Mar 2021

Mangesh Phanse

https://www.outsystems.com/forums/discussion/56822/reactive-web-application-paginate-a-table-with-an-advancedsql-source/#Post214417

Hi Marcelo ,

I modified your resolution with introduction of Structure. This helped me to use in REST API

[Attaching modified OML File. I worked on the oml you shared.]

1 - Introduced structure matching employees named -EmployeeStructure

Another structure Employee Dataset has list of EmployeeStructure and Count variable

2 - In Data action changed the input parameter to EmployeeDataset of type EmployeeDataset

3- Changed the Advanced SQL assignment with EmployeeList and Count with Employee count

Same changed I have implemented in my REST Api action as below. It helped me to implement pagination.

JSON Returned by REST API action is

{
  "Count": 0,
  "RecordList": [
    {
      "Id": 1234567891234567,
      "EmployeeId": 1234567891234567,
      "RequestId": 1234567891234567,
      "CreatedDate": "2014-12-31",
      "CompletedDate": "2014-12-31",
      "StatusId": 0,
      "BonusAmount": 0.1,
      "BonusThreshold": 0,
      "HalfBonusThreshold": 0,
      "ThresholdUnitPrice": 0.1,
      "HalfThresholUnitprice": "string",
      "RequestName": "string",
      "EmployeeFirstName": "string",
      "EmployeeLastName": "string",
      "StatusLabel": "string"
    }
  ]
}

TableAndAdvancedSqlWithStructure.oml

26 Mar 2021

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines