Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Loop using Rest API

Good Morning All,

I have an issue while looping through my Accounts table.  It only has 30K rows of data, but does not know when to stop looping.  I thought checking for the length of the variable being returned would work, but it appears it doesn't.  Any ideas?

Thanks......

 

LET vURL = 'https://CRMqas.api.crm.dynamics.com/api/data/v9.0/accounts';

LET i = 1;

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=30;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=anonymous;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStorbeeName=My;queryHeaders=OData-MaxVersion%24.0%1OData-Version%24.0%1Authorization%2Bearer $(vToken);PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=ZCASARB;XPassword=GbTdcBA;";

DO while LEN('$(vURL)') > 0 
RestConnectorMasterTable:
SQL SELECT
"@odata.context",
"@odata.nextLink",
"__KEY_root",
(SELECT
"@odata.etag",
"accountid",
"__FK_value"
FROM
"value" FK "__FK_value")
FROM JSON (wrap on)
"root" PK "__KEY_root"
WITH CONNECTION (Url
"$(vURL)");


[Accounts]:
LOAD [@odata.etag],
[accountid],
[__FK_value] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value]);

STORE * FROM [RestConnectorMasterTable] into qvd\Accounts365.QVD
;

 
[root]:
LOAD [@odata.context],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

Let vURL = FieldValue('@odata.nextLink',1);

LET i=i+1;

Loop

DROP TABLE RestConnectorMasterTable;

Labels (1)
3 Replies
kjhertz
Partner - Creator
Partner - Creator

It seems you are autoconcatenating your RestConnectorMasterTable with each DO WHILE loop. This should mean the line "Let vURL = FieldValue('@odata.nextLink',1)" will always return the first URL loaded since FieldValue returns the value by load order (see Help for this function).

Try adding "Trace $(vURL)" after you update the variable to see if the variable vURL is set to what you would expect.

If you are constantly using the first loaded URL try using the Peek() function to return the last URL read.

Also beware of spaces within the vURL string. I would also adjust the exit condition to LEN(TRIM('$(vURL)'))>0 just to be sure 🙂

tmumaw
Specialist II
Specialist II
Author

I'm a rookie when it comes to using the REST API.  How can I get the next url in vurl?  CRM365 only allows 5000 rows per call.

Thanks

tmumaw
Specialist II
Specialist II
Author

nextURL is not getting populated.