Hi Timon,

As a workaround, if you increase "pagesize" value, does it retrieve more than 100 invoices ?

Hi Syed,

I can increase pagesize value to 200 which is maximum pagesize value and that works fine.

If I increase it more than 200 I get http 400 error .

-Timo 

Hi Timo

I have had similar requests in Zendesk. The issue seems to be mixing Nested Calls with the specific type of Pagination you use.

It was suggested by CData to do it in a different way.

Attempts to mix the built-in paging with nested api:calls will not work seamlessly. The driver will always start at the start of the <api:script method="GET"> element when entering the iteration of the next page, which will put it right outside of the outer api:call again.

Usually, cases like this one are best handled by leveraging the query slicer. That way, each endpoint can be handled by its own table, and the paging logic can be separated between the two tables in separate RSD files. With the two RSD files attached, a query like the should produce the desired results:

SELECT * FROM debtors WHERE administration_id IN (SELECT id FROM administrations)

If the customer absolutely needs the logic in one RSD file (i.e. so they can run a simple unfiltered SELECT , then that will require manual logic using rows@next. In that case, please let me know and I can try to customize the RSD file accordingly.

Does it make sense?

Essentially you add a filter field and uses a query table to get the id applied. I have sent an file to you on the Zendesk ticket.

Hi @timon has the issue been resolved? if so can you please help select a best answer above? otherwise please let us know if we can provide further assistance 

Hi @Thomas Lind  In the response from CData it is mentioned that they’ve attached 2 example RSD files that should allow the SELECT statement in a Query Table. Can you post those here? I’m curious to see how RSD of the debtors call will address the administration_id. 

@rogier.helmus 

It is essentially what is shared in this.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
  <api:info title="detailedInfo" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1" other:queryslicercolumn="nested_id">
    <attr name="ID" xs:type="string" readonly="false" other:xPath="/json/data/name" />
    <!-- You can modify the name, type, and column size here. -->
    <attr name="nested_id" xs:type="string" readonly="false" other:filter="{nested_id}" other:xPath="/json/data/nested_id" />
    <attr name="Name" xs:type="string" readonly="false" other:xPath="/json/data/name" />
    <attr name="Date" xs:type="datetime" readonly="false" other:xPath="/json/data/date" />
    <attr name="Number" xs:type="decimal" readonly="false" other:xPath="/json/data/number" />
  </api:info>

  <api:set attr="DataModel" value="DOCUMENT" />
  <api:set attr="URI" value="https://api.rest.com/v2/detailedInfo?nested={nested_id}" />
  <api:match pattern="*nested_id*" type="glob" value=""_query.criteria]">
    <api:else>
      <api:set attr="URI" value="https://api.rest.com/v2/detailedInfo?nested=001" />
    </api:else>
  </api:match>
  <api:set attr="JSONPath" value="$.data" />

  <api:set attr="EnablePaging"   value="TRUE"                           />
  <api:set attr="pagetokenpath"  value="/EnumerationResults/NextMarker" />
  <api:set attr="pagetokenparam" value="marker"                         />

  <!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:set attr="method" value="GET"/>
    <api:call op="jsonproviderGet">
      <api:push/>
    </api:call>
  </api:script>
</api:script>

This is the evolved version of the file I was sent, which contains some customer specific naming, so this is essentially the same.