REST API Can't find 'Total field path ' Error

DrB1 — Wed, 12 Jan 2022 23:02:28 GMT

I need rescued - I have tried so many things and cannot seem to get all my data (4775 records). With some settings I get the 429 error so I am trying to add headers to adjust to the api provider's requirements. Now it appears to want to run, but can't find the total records field. Here's the script (basically getting every field offered) and the error message I'm getting now. I'm trying to use the offset pagination required by the provider. Can someone help me, please??

RestConnectorMasterTable:
SQL SELECT
"Date",
"Transfer-Encoding",
"Connection",
"Server",
"X-Frame-Options",
"X-XSS-Protection",
"X-Content-Type-Options",
"X-Download-Options",
"X-Permitted-Cross-Domain-Policies",
"Referrer-Policy",
"X-PCO-API-Auth-Method",
"X-PCO-API-Request-Rate-Count",
"X-PCO-API-Request-Rate-Limit",
"X-PCO-API-Request-Rate-Period",
"X-PCO-API-Processor",
"Access-Control-Allow-Credentials",
"Access-Control-Expose-Headers",
"Access-Control-Allow-Headers",
"Access-Control-Allow-Methods",
"Access-Control-Allow-Origin",
"Vary",
"X-PCO-API-Processed-As-Version",
"ETag",
"Cache-Control",
"X-Request-ID",
"X-Runtime",
"Strict-Transport-Security",
"Content-Type",
"__KEY__response_header",
(SELECT
"__KEY_root",
"__FK_root",
(SELECT
"self",
"next",
"__FK_links"
FROM "links" FK "__FK_links"),
(SELECT
"type" AS "type_u0",
"id" AS "id_u0",
"__KEY_data",
"__FK_data",
(SELECT
"accounting_administrator",
"anniversary",
"avatar",
"birthdate",
"can_create_forms",
"child",
"created_at",
"demographic_avatar_url",
"directory_status",
"first_name",
"gender",
"given_name",
"grade",
"graduation_year",
"inactivated_at",
"last_name",
"medical_notes",
"membership",
"middle_name",
"name",
"nickname",
"passed_background_check",
"people_permissions",
"remote_id",
"school_type",
"site_administrator",
"status",
"updated_at",
"__FK_attributes"
FROM "attributes" FK "__FK_attributes"),
(SELECT
"__KEY_relationships",
"__FK_relationships",
(SELECT
"data",
"__KEY_primary_campus",
"__FK_primary_campus",
(SELECT
"type",
"id",
"__FK_data_u0"
FROM "data" FK "__FK_data_u0")
FROM "primary_campus" PK "__KEY_primary_campus" FK "__FK_primary_campus")
FROM "relationships" PK "__KEY_relationships" FK "__FK_relationships"),
(SELECT
"self" AS "self_u0",
"__FK_links_u0"
FROM "links" FK "__FK_links_u0")
FROM "data" PK "__KEY_data" FK "__FK_data"),
(SELECT
"@Value",
"__FK_included"
FROM "included" FK "__FK_included" ArrayValueAlias "@Value"),
(SELECT
"total_count",
"count",
"__KEY_meta",
"__FK_meta",
(SELECT
"offset",
"__FK_next"
FROM "next" FK "__FK_next"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_can_order_by"
FROM "can_order_by" FK "__FK_can_order_by" ArrayValueAlias "@Value_u0"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_can_query_by"
FROM "can_query_by" FK "__FK_can_query_by" ArrayValueAlias "@Value_u1"),
(SELECT
"@Value" AS "@Value_u2",
"__FK_can_include"
FROM "can_include" FK "__FK_can_include" ArrayValueAlias "@Value_u2"),
(SELECT
"@Value" AS "@Value_u3",
"__FK_can_filter"
FROM "can_filter" FK "__FK_can_filter" ArrayValueAlias "@Value_u3"),
(SELECT
"id" AS "id_u1",
"type" AS "type_u1",
"__FK_parent"
FROM "parent" FK "__FK_parent")
FROM "meta" PK "__KEY_meta" FK "__FK_meta")
FROM (wrap on) "root" PK "__KEY_root" FK "__FK_root")
FROM JSON "_response_header" PK "__KEY__response_header";

[links]:
LOAD [self],
[next],
[__FK_links] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_links]);

[attributes]:
LOAD [accounting_administrator],
[anniversary],
[avatar],
[birthdate],
[can_create_forms],
[child],
[created_at],
[demographic_avatar_url],
[directory_status],
[first_name],
[gender],
[given_name],
[grade],
[graduation_year],
[inactivated_at],
[last_name],
[medical_notes],
[membership],
[middle_name],
[name],
[nickname],
[passed_background_check],
[people_permissions],
[remote_id],
[school_type],
[site_administrator],
[status],
[updated_at],
[__FK_attributes] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_attributes]);

[data]:
LOAD [type],
[id],
[__FK_data_u0] AS [__KEY_primary_campus]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data_u0]);

[primary_campus]:
LOAD [data],
[__KEY_primary_campus],
[__FK_primary_campus] AS [__KEY_relationships]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_primary_campus]);

[relationships]:
LOAD [__KEY_relationships],
[__FK_relationships] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_relationships]);

[links_u0]:
LOAD [self_u0] AS [self_u0],
[__FK_links_u0] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_links_u0]);

[data_u0]:
LOAD [type_u0] AS [type_u0],
[id_u0] AS [id_u0],
[__KEY_data],
[__FK_data] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data]);

[included]:
LOAD [@Value],
[__FK_included] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_included]);

[next]:
LOAD [offset],
[__FK_next] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_next]);

[can_order_by]:
LOAD [@Value_u0] AS [@Value_u0],
[__FK_can_order_by] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_can_order_by]);

[can_query_by]:
LOAD [@Value_u1] AS [@Value_u1],
[__FK_can_query_by] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_can_query_by]);

[can_include]:
LOAD [@Value_u2] AS [@Value_u2],
[__FK_can_include] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_can_include]);

[can_filter]:
LOAD [@Value_u3] AS [@Value_u3],
[__FK_can_filter] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_can_filter]);

[parent]:
LOAD [id_u1] AS [id_u1],
[type_u1] AS [type_u1],
[__FK_parent] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_parent]);

[meta]:
LOAD [total_count],
[count],
[__KEY_meta],
[__FK_meta] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_meta]);

[root]:
LOAD [__KEY_root],
[__FK_root] AS [__KEY__response_header]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_root]);

[_response_header]:
LOAD [Date],
[Transfer-Encoding],
[Connection],
[Server],
[X-Frame-Options],
[X-XSS-Protection],
[X-Content-Type-Options],
[X-Download-Options],
[X-Permitted-Cross-Domain-Policies],
[Referrer-Policy],
[X-PCO-API-Auth-Method],
[X-PCO-API-Request-Rate-Count],
[X-PCO-API-Request-Rate-Limit],
[X-PCO-API-Request-Rate-Period],
[X-PCO-API-Processor],
[Access-Control-Allow-Credentials],
[Access-Control-Expose-Headers],
[Access-Control-Allow-Headers],
[Access-Control-Allow-Methods],
[Access-Control-Allow-Origin],
[Vary],
[X-PCO-API-Processed-As-Version],
[ETag],
[Cache-Control],
[X-Request-ID],
[X-Runtime],
[Strict-Transport-Security],
[Content-Type],
[__KEY__response_header]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY__response_header]);

DROP TABLE RestConnectorMasterTable;

Re: REST API Can't find 'Total field path ' Error

Levi_Turner — Thu, 13 Jan 2022 15:45:19 GMT

I haven't seen this myself but my best guess is the pagination settings are off. What do you have in the pagination settings in the connector config and can you provide an example response from the API for how it provides the next URL? For example in Qlik SaaS it would be like this:

"links": { "self": { "href": "https://<myTenant>.us.qlikcloud.com/api/v1/items" }, "next": { "href": "https://<myTenant>.us.qlikcloud.com/api/v1/items?next=FgAAAAdfaWQAXqrcirVtPQABnKexAA" } }

Re: REST API Can't find 'Total field path ' Error

DrB1 — Thu, 13 Jan 2022 16:42:30 GMT

@Levi_Turner - thanks! Here's what the provider api explorer shows and my pagination settings screenshots:

"root":{

4 items

"links":{

2 items

"self":

"https://api.provider.com/people/v2/people"

"next":

"https://api.provider.com/people/v2/people?offset=25"

}

"data":[

25 items

0:{

...

}

5 items

1:{

...

}

5 items

2:{

...

}

5 items

3:{

...

}

5 items

4:{

...

}

5 items

5:{

...

}

5 items

6:{

...

}

5 items

7:{

...

}

5 items

8:{

...

}

5 items

9:{

...

}

5 items

10:{

...

}

5 items

11:{

...

}

5 items

12:{

...

}

5 items

13:{

...

}

5 items

14:{

...

}

5 items

15:{

...

}

5 items

16:{

...

}

5 items

17:{

...

}

5 items

18:{

...

}

5 items

19:{

...

}

5 items

20:{

...

}

5 items

21:{

...

}

5 items

22:{

...

}

5 items

23:{

...

}

5 items

24:{

...

}

5 items

]

"included":[]

0 items

"meta":{

8 items

"total_count":

4775

"count":

"next":{

...

}

1 item

"can_order_by":[

...

]

20 items

"can_query_by":[

...

]

27 items

"can_include":[

...

]

15 items

"can_filter":[

...

]

3 items

"parent":{

...

}

2 items

}

Re: REST API Can't find 'Total field path ' Error

Levi_Turner — Thu, 13 Jan 2022 19:43:52 GMT

Does root/links/next + Next URL in pagination work for you?

Re: REST API Can't find 'Total field path ' Error

DrB1 — Fri, 14 Jan 2022 04:52:01 GMT

@Levi_Turner it absolutely does!!! Thank you so much!!

Re: REST API Can't find 'Total field path ' Error

DrB1 — Tue, 18 Jan 2022 16:26:56 GMT

@Levi_Turner - turns out that my responses are sporadic because I hit a 429 error. I believe it is due to needed headers for rate limiting. I've tried adding 3 of them in the Query Headers section of the Rest connector, matching what I see in the Response Headers. Am I missing something? Is this how you slow down the calls so you don't surpass the limits?

topic Re: REST API Can't find 'Total field path ' Error in Integration, Extension & APIs

REST API Can't find 'Total field path ' Error

Re: REST API Can't find 'Total field path ' Error

Re: REST API Can't find 'Total field path ' Error

Re: REST API Can't find 'Total field path ' Error

Re: REST API Can't find 'Total field path ' Error

Re: REST API Can't find 'Total field path ' Error