Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Need a little help. I need to loop through a table in Microsoft CRM365. Does anyone have an example of how to do this? The loop, I think I can figure out the rest.
Thanks....
Pagination is probably what you are looking for:
With "auto-pagination" the REST connector will "loop" repeatedly to send multiple requests to get the results.
With manual pagination you can do the looping yourself and modify the URL and or the POST content of the REST SQL statement by using the WITH CONNECTION clause.
I have never used the REST connector before. So this is a first time for me. Here is what I have so far. Any help would be appreciated.
Connect String
RestConnectorMasterTable:
SQL SELECT
"token_type",
"expires_in",
"ext_expires_in",
"expires_on",
"not_before",
"resource",
"access_token"
FROM JSON (wrap on) "root";
[root]:
LOAD [token_type],
[expires_in],
[ext_expires_in],
[expires_on],
[not_before],
[resource],
[access_token]
RESIDENT RestConnectorMasterTable;
LET vToken = peek('access_token');
DROP TABLE RestConnectorMasterTable;
LET accountsURL = 'https://daveyqas.api.crm.dynamics.com/api/data/v9.0/accounts';
CUSTOM CONNECT TO
RestConnectorMasterTable:
SQL SELECT
"@odata.context",
"__KEY_root",
(SELECT
"@odata.etag",
"paymenttermscode",
"davey_saptext",
"mcala_lastemaildate",
"address1_line1",
"mcala_lastactivitytype",
"merged",
"davey_accountgroup",
"_modifiedby_value",
"territorycode",
"emailaddress1",
"davey_donotvalidateaddress",
"exchangerate",
"davey_createnewprimarycontact",
"davey_cpbusinesspartner",
"mcala_lastactivitydate",
"name",
"accountcategorycode",
"_owningbusinessunit_value",
"_primarycontactid_value",
"davey_source",
"davey_stateprovince",
"address1_composite",
"davey_accountbalance_base",
"donotpostalmail",
"accountratingcode",
"marketingonly",
"davey_noupdatetobillingscheduleflag",
"donotphone",
"preferredcontactmethodcode",
"_ownerid_value",
"address1_telephone1",
"description",
"davey_clientrelationshipfolder",
"customersizecode",
"davey_lastpaymentamount",
"openrevenue_date",
"address2_addresstypecode",
"businesstypecode",
"donotemail",
"address2_shippingmethodcode",
"timezoneruleversionnumber",
"address1_addressid",
"davey_renewalallowed",
"address2_freighttermscode",
"statuscode",
"createdon",
"versionnumber",
"address1_stateorprovince",
"davey_renewalmonth",
"davey_baddebt",
"davey_country",
"donotsendmm",
"donotfax",
"davey_clientsince",
"donotbulkpostalmail",
"davey_addressaccepted",
"address1_country",
"davey_taxexempted",
"followemail",
"davey_evergreenstatus",
"address1_line2",
"modifiedon",
"creditonhold",
"_davey_salesarborist_value",
"davey_bill_sched_block",
"_transactioncurrencyid_value",
"accountid",
"davey_addressunvalidated",
"donotbulkemail",
"accountclassificationcode",
"davey_accountname2",
"davey_daveyemployeeflag",
"davey_clientclass",
"mca_sapaccountid",
"shippingmethodcode",
"mcala_nextapptdate",
"mcala_nextactivitytype",
"_createdby_value",
"address1_city",
"davey_sortzip",
"mcala_nextactivitydate",
"davey_industrycode",
"davey_renewaldeliverypreference",
"davey_blockedforsales",
"telephone1",
"davey_ranrenewalyear",
"_owningteam_value",
"participatesinworkflow",
"davey_lastpaymentamount_base",
"statecode",
"overriddencreatedon",
"address2_addressid",
"address1_postalcode",
"davey_accountbalance",
"davey_lastpaymentdate",
"opendeals_date",
"davey_sortterm",
"aging60",
"mca_datesenttosap",
"address2_upszone",
"sharesoutstanding",
"address2_stateorprovince",
"address1_longitude",
"importsequencenumber",
"address2_longitude",
"sic",
"address2_telephone2",
"entityimage",
"address2_composite",
"entityimage_timestamp",
"traversedpath",
"primarytwitterid",
"marketcap_base",
"davey_servicecontactpreference",
"davey_propertycreatememo",
"openrevenue",
"address1_line3",
"fax",
"stageid",
"_preferredsystemuserid_value",
"customertypecode",
"numberofemployees",
"emailaddress2",
"processid",
"address2_primarycontactname",
"address2_utcoffset",
"davey_callfirst",
"address2_city",
"stockexchange",
"creditlimit_base",
"address1_latitude",
"timespentbymeonemailandmeetings",
"address2_name",
"utcconversiontimezonecode",
"davey_marketingchannel",
"_originatingleadid_value",
"address2_postofficebox",
"telephone2",
"address2_latitude",
"revenue",
"entityimageid",
"address2_fax",
"address2_telephone1",
"address1_shippingmethodcode",
"lastusedincampaign",
"address1_telephone3",
"entityimage_url",
"marketcap",
"address2_county",
"address1_freighttermscode",
"openrevenue_base",
"address1_primarycontactname",
"davey_arboristsrenewalnotes",
"mcala_lasttaskdate",
"tickersymbol",
"_davey_propertyid_value",
"_parentaccountid_value",
"creditlimit",
"industrycode",
"accountnumber",
"davey_crmaudit",
"address2_telephone3",
"aging90",
"aging90_base",
"_preferredequipmentid_value",
"_defaultpricelevelid_value",
"_slainvokedid_value",
"address1_telephone2",
"address2_line1",
"address1_postofficebox",
"openrevenue_state",
"address1_utcoffset",
"ownershipcode",
"davey_primarycontactfullname",
"_territoryid_value",
"davey_specialcustomer",
"_slaid_value",
"revenue_base",
"_owninguser_value",
"davey_acquisitioncode",
"_modifiedbyexternalparty_value",
"opendeals_state",
"davey_jobsitetext",
"onholdtime",
"aging30_base",
"mcala_nextphonecalldate",
"_preferredserviceid_value",
"aging60_base",
"davey_referredbynote",
"davey_referredon",
"address1_name",
"_modifiedonbehalfby_value",
"yominame",
"address1_addresstypecode",
"address1_county",
"_createdonbehalfby_value",
"address2_line2",
"opendeals",
"ftpsiteurl",
"address2_country",
"aging30",
"_davey_previousaccount_value",
"davey_laststatusupdatedate",
"address1_fax",
"davey_marketingsubchannel",
"davey_accounttype",
"primarysatoriid",
"mcala_nexttaskdate",
"preferredappointmenttimecode",
"preferredappointmentdaycode",
"_createdbyexternalparty_value",
"_masterid_value",
"emailaddress3",
"address1_upszone",
"_davey_referredbyid_value",
"mcala_lastphonecalldate",
"davey_acquiredfrom",
"mcala_lastapptdate",
"address2_postalcode",
"websiteurl",
"lastonholdtime",
"telephone3",
"address2_line3",
"__FK_value"
FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root";
[Account]:
LOAD [@odata.etag],
[paymenttermscode],
[davey_saptext],
[mcala_lastemaildate],
[address1_line1],
[mcala_lastactivitytype],
[merged],
[davey_accountgroup],
[_modifiedby_value],
[territorycode],
[emailaddress1],
[davey_donotvalidateaddress],
[exchangerate],
[davey_createnewprimarycontact],
[davey_cpbusinesspartner],
[mcala_lastactivitydate],
[name],
[accountcategorycode],
[_owningbusinessunit_value],
[_primarycontactid_value],
[davey_source],
[davey_stateprovince],
[address1_composite],
[davey_accountbalance_base],
[donotpostalmail],
[accountratingcode],
[marketingonly],
[davey_noupdatetobillingscheduleflag],
[donotphone],
[preferredcontactmethodcode],
[_ownerid_value],
[address1_telephone1],
[description],
[davey_clientrelationshipfolder],
[customersizecode],
[davey_lastpaymentamount],
[openrevenue_date],
[address2_addresstypecode],
[businesstypecode],
[donotemail],
[address2_shippingmethodcode],
[timezoneruleversionnumber],
[address1_addressid],
[davey_renewalallowed],
[address2_freighttermscode],
[statuscode],
[createdon],
[versionnumber],
[address1_stateorprovince],
[davey_renewalmonth],
[davey_baddebt],
[davey_country],
[donotsendmm],
[donotfax],
[davey_clientsince],
[donotbulkpostalmail],
[davey_addressaccepted],
[address1_country],
[davey_taxexempted],
[followemail],
[davey_evergreenstatus],
[address1_line2],
[modifiedon],
[creditonhold],
[_davey_salesarborist_value],
[davey_bill_sched_block],
[_transactioncurrencyid_value],
[accountid],
[davey_addressunvalidated],
[donotbulkemail],
[accountclassificationcode],
[davey_accountname2],
[davey_daveyemployeeflag],
[davey_clientclass],
[mca_sapaccountid],
[shippingmethodcode],
[mcala_nextapptdate],
[mcala_nextactivitytype],
[_createdby_value],
[address1_city],
[davey_sortzip],
[mcala_nextactivitydate],
[davey_industrycode],
[davey_renewaldeliverypreference],
[davey_blockedforsales],
[telephone1],
[davey_ranrenewalyear],
[_owningteam_value],
[participatesinworkflow],
[davey_lastpaymentamount_base],
[statecode],
[overriddencreatedon],
[address2_addressid],
[address1_postalcode],
[davey_accountbalance],
[davey_lastpaymentdate],
[opendeals_date],
[davey_sortterm],
[aging60],
[mca_datesenttosap],
[address2_upszone],
[sharesoutstanding],
[address2_stateorprovince],
[address1_longitude],
[importsequencenumber],
[address2_longitude],
[sic],
[address2_telephone2],
[entityimage],
[address2_composite],
[entityimage_timestamp],
[traversedpath],
[primarytwitterid],
[marketcap_base],
[davey_servicecontactpreference],
[davey_propertycreatememo],
[openrevenue],
[address1_line3],
[fax],
[stageid],
[_preferredsystemuserid_value],
[customertypecode],
[numberofemployees],
[emailaddress2],
[processid],
[address2_primarycontactname],
[address2_utcoffset],
[davey_callfirst],
[address2_city],
[stockexchange],
[creditlimit_base],
[address1_latitude],
[timespentbymeonemailandmeetings],
[address2_name],
[utcconversiontimezonecode],
[davey_marketingchannel],
[_originatingleadid_value],
[address2_postofficebox],
[telephone2],
[address2_latitude],
[revenue],
[entityimageid],
[address2_fax],
[address2_telephone1],
[address1_shippingmethodcode],
[lastusedincampaign],
[address1_telephone3],
[entityimage_url],
[marketcap],
[address2_county],
[address1_freighttermscode],
[openrevenue_base],
[address1_primarycontactname],
[davey_arboristsrenewalnotes],
[mcala_lasttaskdate],
[tickersymbol],
[_davey_propertyid_value],
[_parentaccountid_value],
[creditlimit],
[industrycode],
[accountnumber],
[davey_crmaudit],
[address2_telephone3],
[aging90],
[aging90_base],
[_preferredequipmentid_value],
[_defaultpricelevelid_value],
[_slainvokedid_value],
[address1_telephone2],
[address2_line1],
[address1_postofficebox],
[openrevenue_state],
[address1_utcoffset],
[ownershipcode],
[davey_primarycontactfullname],
[_territoryid_value],
[davey_specialcustomer],
[_slaid_value],
[revenue_base],
[_owninguser_value],
[davey_acquisitioncode],
[_modifiedbyexternalparty_value],
[opendeals_state],
[davey_jobsitetext],
[onholdtime],
[aging30_base],
[mcala_nextphonecalldate],
[_preferredserviceid_value],
[aging60_base],
[davey_referredbynote],
[davey_referredon],
[address1_name],
[_modifiedonbehalfby_value],
[yominame],
[address1_addresstypecode],
[address1_county],
[_createdonbehalfby_value],
[address2_line2],
[opendeals],
[ftpsiteurl],
[address2_country],
[aging30],
[_davey_previousaccount_value],
[davey_laststatusupdatedate],
[address1_fax],
[davey_marketingsubchannel],
[davey_accounttype],
[primarysatoriid],
[mcala_nexttaskdate],
[preferredappointmenttimecode],
[preferredappointmentdaycode],
[_createdbyexternalparty_value],
[_masterid_value],
[emailaddress3],
[address1_upszone],
[_davey_referredbyid_value],
[mcala_lastphonecalldate],
[davey_acquiredfrom],
[mcala_lastapptdate],
[address2_postalcode],
[websiteurl],
[lastonholdtime],
[telephone3],
[address2_line3],
[__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]);
DROP TABLEs RestConnectorMasterTable, Account;
Exit SCRIPT;
Hi Thom,
You need to put your Do While Loop below the CUSTOM CONNECT statement. Qlikview does not allow Custom Connect statements simulateneously.
So, you just have to change the URL with each loop counter like this
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); Loop DROP TABLE RestConnectorMasterTable;