Dear community,
I am struggling with the Rest Connector at the moment.
I want to connect to a SharePoint list that has more than 100 entries. Therefore, the response is split into multiple pages.
The connection itself is working, so if I specify "No Pagination" I get 100 entries as a result.
Because I was not sure how to set the Pagination of SharePoint in QV Connector, I read the maximum ID from the result set and use this in a loop to connect to the next set:
Example:
1. First request: "url=https://{our page}/_api/Web/Lists(guid'{...}')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d0"
2. Next request (max ID = 112): "url=https://{our page}/_api/Web/Lists(guid'{...}')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d112"
And if I check this manually in Chrome, I can see that the next result entry I get is ID = 113.
But if I put this in a loop in QV, the first connection is set and the results are loaded in a table. Then the maximum ID is read into a variable, which is still working (var = 112).
But after the next connection, which uses the url from 2. above (I checked this in Debug mode - the url is correct), I still get the same result set as in the first query and my maximum ID again is 112... I tried this also with PaginationMode=Custom (see below), but to no avail.
So, can someone tell me if I miss some logic here? Here is the full code (shortened and censored):
SET vNextPage = https://{page}/_api/Web/Lists(guid'{...}')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d;
LET vLastId = 0;
SET ErrorMode = 0;
myList:
LOAD 0 as %KEY_myListId AutoGenerate 0;
Do
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vNextPage)$(vLastId);timeout=30;method=GET;httpProtocol=1.1;isKeepAlive=1;bodyEncoding=UTF-8;sendExpect100Continue=1;autoDetectResponseType=1;checkResponseTypeOnTestConnection=1;keyGenerationStrategy=0;authSchema=ntlm;skipServerCertificateValidation=0;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;addMissingQueryParametersToFinalRequest=0;PaginationType=Custom;allowResponseHeaders=0;allowHttpsOnly=0;XUserId={...};XPassword={...};";
RestConnectorMasterTable:
SQL SELECT
"__KEY_feed",
(SELECT
(SELECT
FROM "category"),
(SELECT
FROM "link"),
(SELECT
FROM "author"),
(SELECT
(SELECT
(SELECT
FROM "FileSystemObjectType"),
(SELECT
"@Content" AS "@Content_u0",
"__FK_Id"
FROM "Id" FK "__FK_Id" ContentFieldAlias "@Content_u0"),
(SELECT
"@Content" AS "@Content_u1",
"__FK_Type"
FROM "Type" FK "__FK_Type" ContentFieldAlias "@Content_u1"),
(SELECT
"@Content" AS "@Content_u2",
"__FK_Description"
FROM "Description" FK "__FK_Description" ContentFieldAlias "@Content_u2"),
{...}
FROM "properties" PK "__KEY_properties" FK "__FK_properties")
FROM "content" PK "__KEY_content" FK "__FK_content")
FROM "entry" PK "__KEY_entry" FK "__FK_entry")
FROM XML "feed" PK "__KEY_feed";
[Id]:
LOAD [@Content_u0],
[__FK_Id] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Id]);
maxID:
LOAD max(@Content_u0) as fMaxID RESIDENT Id;
LET vLastId = Peek('fMaxID', 0, 'maxID');
DROP TABLE maxID;
[Type]:
LOAD [@Content_u1],
[__FK_Type] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Type]);
[Description]:
LOAD [@Content_u2],
[__FK_Description] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Description]);
{...}
DROP TABLE RestConnectorMasterTable;
tmpTable:
LOAD [@Content_u0] as [%KEY_myListId], [__KEY_properties] RESIDENT Id;
LEFT JOIN LOAD [@Content_u1] as [Type], [__KEY_properties] RESIDENT Type;
LEFT JOIN LOAD [@Content_u2] as [Description], [__KEY_properties] RESIDENT Description;
{...}
Concatenate(myList)
LOAD * RESIDENT tmpTable;
DROP TABLE tmpTable;
DROP TABLE Id;
DROP TABLE Type;
DROP TABLE Description;
{...}
EXIT DO WHEN ISNULL(vLastId);
LOOP WHILE NOT IS NULL(vLastId)
SET ErrorMode = 1;
If anyone can tell me how to do it without a loop, directly within the pagination setup of the Connector, that would be even more awesome 🙂
Thank you all very much and hope someone can help me here.
Cheers, Ben
