Table API Pagination - Incomplete Pages

Zylo
Tera Contributor

‎04-12-2022 02:35 PM

Hello,

We are experiencing inaccurate pagination behavior when using the Table API. Below are two requests made:

/api/now/table/sys_user?sysparm_limit=1000&sysparm_offset=5000&sysparm_query=ORDERBYuser_name

/api/now/table/sys_user?sysparm_limit=2000&sysparm_offset=6000&sysparm_query=ORDERBYuser_name


Both requests used the same credentials. The first request received a total of 993 records back and the second received 1970 records back. The total amount of records in sys_user is known to be greater than 6000 and so for both of these limit and offset combinations we are expecting the maximum, i.e. 1000 for the first request and 2000 for the second. However we are receiving less than that. Also we are receiving less than the limit when the offset does not move us to the last page. There are no ACLs or other restrictions in place to prevent the user from seeing all records. Are there any settings that can be adjusted to correct this behavior and return all records as requested?

Any suggestions and questions are welcome, thanks!

Labels:
0 Helpfuls
  • 1,737 Views
3 REPLIES 3

Filipe Cruz
Kilo Sage
Kilo Sage

‎04-12-2022 03:27 PM

Hello Zylo,

Can this be a timeout in the request?
What happens if you change the limit to 500? or even 100? is the issue still present?

Best regards,

Filipe Cruz

0 Helpfuls

Hitoshi Ozawa
Giga Sage
Giga Sage

‎04-12-2022 06:27 PM

Hi Zylo,

Try with less values to see if the problem is really with pagination.

For example, to see if the following will return 10 records.

/api/now/table/sys_user?sysparm_limit=10&sysparm_offset=10&sysparm_query=ORDERBYuser_name

/api/now/table/sys_user?sysparm_limit=10&sysparm_offset=20&sysparm_query=ORDERBYuser_name

/api/now/table/sys_user?sysparm_limit=10&sysparm_offset=1000&sysparm_query=ORDERBYuser_name

/api/now/table/sys_user?sysparm_limit=10&sysparm_offset=1010&sysparm_query=ORDERBYuser_name

If 10 records are returned, Check if there is any error. The problem may with size reaching the max size or with timeout.

Check the properties defined in the following page.

https://docs.servicenow.com/bundle/sandiego-application-development/page/integrate/custom-web-servic...

https://docs.servicenow.com/bundle/sandiego-application-development/page/integrate/web-services/reference/r_HTTPConnectionManagementProps.html

Finally, there may be a possibility that there is some invalid data in the record. Try to find which record was not returned and try to just return that record.

 

Zylo
Tera Contributor
In response to Hitoshi Ozawa

‎04-13-2022 11:03 AM

Some additional information, we were previously using 200 for the limit size and encountered this same behavior. It was also tried with a limit of 100,000 and all the expected records were returned. That is why we think it may be an issue with pagination. I will try to send a request with a limit of 10 and see how it behaves.

Also regarding the timeout, wouldn't a timeout result in no records being returned? Or would the API possibly return records with non 200 status codes?

0 Helpfuls