Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Im trying to request SaleId for this url
https://server."company".com/masterkey/agency/sale/<saleid>/saleOrder
So that I can get country and city of destination of the SaleOrder.
I want to make a loop so that every call I make, it will return the information of the saleOrder im calling.
If there is someone that could help me that would be great!!!!!
I have this already in the script
LIB CONNECT TO 'Sale';
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"sort",
"order",
"max",
"offset",
"totalCount",
"count",
"__FK_meta"
FROM "meta" FK "__FK_meta"),
(SELECT
"q",
"courseType",
"status",
"interestLevel",
"referrer",
"paymentStatus",
"enrollmentStatus",
"seller",
"client",
"saleGroup",
"isEnabled",
"studentName",
"saleOrderStartDate",
"courseName",
"customStatus",
"channel",
"manager",
"__KEY_query",
"__FK_query",
(SELECT
"@Value",
"__FK_distributor"
FROM "distributor" FK "__FK_distributor" ArrayValueAlias "@Value")
FROM "query" PK "__KEY_query" FK "__FK_query"),
(SELECT
"id" AS "id_u5",
"version",
"pid",
"externalId",
"name" AS "name_u5",
"status" AS "status_u0",
"courseType" AS "courseType_u0",
"dateCreated" AS "dateCreated_u0",
"lastUpdated",
"referrer" AS "referrer_u0",
"interestLevel" AS "interestLevel_u0",
"campaign",
"formURL",
"customStatus" AS "customStatus_u0",
"saleGroup" AS "saleGroup_u0",
"isEnabled" AS "isEnabled_u0",
"manager" AS "manager_u0",
"discardReason",
"enrollmentFieldApproved",
"enrollmentFieldCount",
"paymentDueDate",
"paymentStatus" AS "paymentStatus_u0",
"enrollmentStatus" AS "enrollmentStatus_u0",
"__KEY_resourceList",
"__FK_resourceList",
(SELECT
"id",
"name",
"email",
"firstName",
"lastName",
"birthday",
"gender",
"__KEY_client",
"__FK_client",
(SELECT
"number",
"type",
"__FK_phone"
FROM "phone" FK "__FK_phone")
FROM "client" PK "__KEY_client" FK "__FK_client"),
(SELECT
"name" AS "name_u0",
"nationality",
"birthday" AS "birthday_u0",
"occupation",
"firstName" AS "firstName_u0",
"lastName" AS "lastName_u0",
"email" AS "email_u0",
"__FK_student"
FROM "student" FK "__FK_student"),
(SELECT
"id" AS "id_u0",
"name" AS "name_u1",
"email" AS "email_u1",
"iconUri",
"__FK_seller"
FROM "seller" FK "__FK_seller"),
(SELECT
"id" AS "id_u1",
"name" AS "name_u2",
"__FK_distributor_u0"
FROM "distributor" FK "__FK_distributor_u0"),
(SELECT
"departureDate",
"destination",
"interest",
"budget",
"channel" AS "channel_u0",
"comments",
"dateCreated",
"reporter",
"__FK_lead"
FROM "lead" FK "__FK_lead"),
(SELECT
"id" AS "id_u2",
"name" AS "name_u3",
"__FK_customStatus"
FROM "customStatus" FK "__FK_customStatus"),
(SELECT
"id" AS "id_u3",
"start",
"__FK_saleOrder"
FROM "saleOrder" FK "__FK_saleOrder"),
(SELECT
"id" AS "id_u4",
"name" AS "name_u4",
"__FK_saleGroup"
FROM "saleGroup" FK "__FK_saleGroup")
FROM "resourceList" PK "__KEY_resourceList" FK "__FK_resourceList")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (Url "$(vURL)");
[client.sale]:
LOAD [id] as id.sale,
[name] as name.sale,
[email] as email.sale,
[firstName] as [firstName.sale],
[lastName] as [lastName.sale],
[birthday] as [birthday.sale],
[gender] as [gender.sale],
[__KEY_client] as [__KEY_client.sale],
[__FK_client] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_client]);
store [client.sale] into [lib://QVD/client.sale.qvd] (qvd);
drop table [client.sale];
[student.sale]:
LOAD [name_u0] as [name_u0.sale],
[nationality] as [nationality.sale],
[birthday_u0] as [birthday_u0.sale],
[occupation] as [occupation.sale],
[firstName_u0] as [firstName_u0.sale],
[lastName_u0] as [lastName_u0.sale],
[email_u0] as [email_u0.sale],
[__FK_student] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_student]);
store [student.sale] into [lib://QVD/student.sale.qvd] (qvd);
drop table [student.sale];
[seller.sale]:
LOAD [id_u0] as [id_u0.sale],
[name_u1] as [name_u1.sale],
[email_u1] as [email_u1.sale],
[iconUri] as [iconUri.sale],
[__FK_seller] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_seller]);
store [seller.sale] into [lib://QVD/seller.sale.qvd] (qvd);
drop table [seller.sale];
[distributor_u0.sale]:
LOAD [id_u1] as [id_u1.sale],
[name_u2] as [name_u2.sale],
[__FK_distributor_u0] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_distributor_u0]);
store [distributor_u0.sale] into [lib://QVD/distributor_u0.sale.qvd] (qvd);
drop table [distributor_u0.sale];
[lead.sale]:
LOAD [departureDate] as [departureDate.sale],
[destination] as [destination.sale],
[interest] as [interest.sale],
[budget] as [budget.sale],
[channel_u0] as [channel_u0.sale],
[comments] as [comments.sale],
[dateCreated] as [dateCreated.sale],
[reporter] as [reporter.sale],
[__FK_lead] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_lead]);
store [lead.sale] into [lib://QVD/lead.sale.qvd] (qvd);
drop table [lead.sale];
[customStatus.sale]:
LOAD [id_u2] as [id_u2.sale],
[name_u3] as [name_u3.sale],
[__FK_customStatus] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customStatus]);
store [customStatus.sale] into [lib://QVD/customStatus.sale.qvd] (qvd);
drop table [customStatus.sale];
[saleOrder.sale]:
LOAD [id_u3] as [id_u3.sale],
[start] as [start.sale],
[__FK_saleOrder] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_saleOrder]);
store [saleOrder.sale] into [lib://QVD/saleOrder.sale.qvd] (qvd);
drop table [saleOrder.sale];
[saleGroup.sale]:
LOAD [id_u5] as [id_u5.sale],
[name_u5] as [name_u5.sale],
[__FK_saleGroup] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_saleGroup]);
store [saleGroup.sale] into [lib://QVD/saleGroup.sale.qvd] (qvd);
drop table [saleGroup.sale];
[resourceList.sale]:
LOAD [id_u4] as [id_u4.sale],
[version] as [version.sale],
[pid] as [pid.sale],
[externalId] as [externalId.sale],
[name_u4] as [name_u4.sale],
[status_u0] as [status_u0.sale],
[courseType_u0] as [courseType_u0.sale],
[dateCreated_u0] as [dateCreated_u0.sale],
[lastUpdated] as [lastUpdated.sale],
[referrer_u0] as [referrer_u0.sale],
[interestLevel_u0] as [interestLevel_u0.sale],
[campaign] as [campaign.sale],
[formURL] as [formURL.sale],
[customStatus_u0] as [customStatus_u0.sale],
[saleGroup_u0] as [saleGroup_u0.sale],
[isEnabled_u0] as [isEnabled_u0.sale],
[manager_u0] as [manager_u0.sale],
[discardReason] as [discardReason.sale],
[enrollmentFieldApproved] as [enrollmentFieldApproved.sale],
[enrollmentFieldCount] as [enrollmentFieldCount.sale],
[paymentDueDate] as [paymentDueDate.sale],
[paymentStatus_u0] as [paymentStatus_u0.sale],
[enrollmentStatus_u0] as [enrollmentStatus_u0.sale],
[__KEY_resourceList] as [__KEY_resourceList.sale],
[__FK_resourceList] AS [__KEY_root.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_resourceList]);
store [resourceList.sale] into [lib://QVD/resourceList.sale.qvd] (qvd);
drop table [resourceList.sale];
DROP TABLE RestConnectorMasterTable;
We do this all the time with REST connectors. If you have a table that has your SaleIds in it called "tableHoldingYourSaleIDs" this is how this would work. Note, you should accumulate all your data into single tables and write the QVDs out once unless you are going to name them differently - otherwise you're only going to get the last saleid.
LIB CONNECT TO 'Sale';
for i=1 to NoOfRows('tableHoldingYourSaleIDs')
let vSaleId=FieldValue('SaleId',$(i));
let vURL='https://server."company".com/masterkey/agency/sale/$(vSaleId)/saleOrder'
#do your stuff here
WITH CONNECTION (Url "$(vURL)");
#accumulate it into tables.
next
store [seller.sale] into [lib://QVD/seller.sale.qvd] (qvd); #unless you write out the QVDs with the saleID in it - which you can do with the variable.
drop table [seller.sale];
#more QVDs here.