Loop using Rest API

[tmumaw]

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;

[kjhertz]

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]

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]

nextURL is not getting populated.

[marksouzacosta]

As an addition to this topic, I'm excited to share my latest tutorial on integrating Asana with Qlik Cloud using REST APIs!

In this step-by-step guide, you'll learn:
- How to use Asana REST APIs with Postman
- Creating REST Data Connections in Qlik Cloud
- Extracting data from Asana and saving as Parquet files
- Enhancing connections with custom parameters and pagination

Check out the full video on YouTube:
Integrate Asana with Qlik Cloud Using REST APIs: A Step-by-Step Guide
https://youtu.be/lFwar30BNkQ?si=UZoM_x9L5amdJSnf

Regards,

Mark Costa