Table API pagination issue

[gilmagali]

Hi 

having issues with table API pagination

i have 1157 files in my attachment table.

my first query is : /api/now/v1/table/sys_attachment?sysparm_query=sys_created_on>=1970-01-01+00:00:00^ORDERBYsys_created_on&sysparm_limit=1200&sysparm_offset=0

as you can see it will return all files create after/on  1970-01-01+00:00:00 order by sys_created_on.

in this query the results are correct im getting ALL attachments.

but when quering the same query with paging: 

/api/now/v1/table/sys_attachment?sysparm_query=sys_created_on>=1970-01-01+00:00:00^ORDERBYsys_created_on&sysparm_limit=100&sysparm_offset=0

/api/now/v1/table/sys_attachment?sysparm_query=sys_created_on>=1970-01-01+00:00:00^ORDERBYsys_created_on&sysparm_limit=100&sysparm_offset=100

/api/now/v1/table/sys_attachment?sysparm_query=sys_created_on>=1970-01-01+00:00:00^ORDERBYsys_created_on&sysparm_limit=100&sysparm_offset=200

.

.

.

/api/now/v1/table/sys_attachment?sysparm_query=sys_created_on>=1970-01-01+00:00:00^ORDERBYsys_created_on&sysparm_limit=100&sysparm_offset=1100

and aggregate all files and compare them to the first response im getting that there are some missing files and some duplicates.

* the issues is that the response are different and not that i can remove the create on this is just an example where we are using created on on query the pagination result are incorrect.

thanks

[Tony Chatfield1]

Hi, unfortunately the details provided don't make it clear as to how you are setting the offset.
You post also states you are querying for all attachments created after 1970-01-01, but your code snippets show created 'ON', also querying for attachments created after 1970-01-01 seems unnecessary as this will always result in all attachments. Perhaps you can update this thread with your code so that the forum can better understand and (reproduce) the issue you are encountering.

[gilmagali]

thanks i updated the post hope its more clear now

[Tony Chatfield1]

Hi, taking another look at your post
I would expect a date\time based query to reflect the (encoded) query that you would have to run directly against the target table in order to return the same results; and for a date\time field this would be something like this

'sys_created_on>javascript:gs.dateGenerate('1970-01-01','00:00:00')^ORDERBYsys_created_on'

• You can validate this by running similar query on a table, right clicking the breadcrumb filter and selecting copy query

Spot testing using a PDI and Postman, this seems to return consistent expected results.

/api/now/table/sys_attachment?sysparm_query=sys_created_on>javascript:gs.dateGenerate('1970-01-01','00:00:00')^ORDERBYsys_created_on&sysparm_limit=10&sysparm_offset=500