Dynamic REST connection to Service Now

[Ilhui]

I'm trying to unify all the REST connections created in QlikSense to ServiceNow since I have to create a new connection every time I pull out data from a new table. This will make difficult to manage all my connections in the future and because of that I'm trying to have only one connection that will override specific parameters depending on the required table by using the "WITH CONNECTION" function.

I tried passing the parameters like what's on the code below but after loading the data there's nothing in the table:

LET vURL = "https://mydomain.com/.../.../.../nameofthetable?";

RestConnectorMasterTable:
SQL SELECT 
	...my fields to query from Service NOW...
FROM JSON (wrap off) "result" PK "__KEY_result"
WITH CONNECTION (
URL "$(vURL)",
QUERY "timeout" "900",
QUERY "method" "GET",
QUERY "httpProtocol" "1.1",
QUERY "isKeepAlive" "true",
QUERY "bodyEncoding" "UTF-8",
QUERY "sendExpect100Continue" "true",
QUERY "autoDetectResponseType" "true",
QUERY "queryParameters" "sysparm_query%2u_stateINin_progress,Closed,New%1sysparm_fields%2sys_created_by,%1sysparm_display_value%2true",
QUERY "addMissingQueryParametersToFinalRequest" "false",
QUERY "PaginationType" "Offset",
QUERY "OffsetStartField" "sysparm_offset",
QUERY "IsOffsetStartFieldHeader" "false",
QUERY "OffsetStartFieldValue" "0",
QUERY "OffsetCountFieldName" "sysparm_limit",
QUERY "IsOffsetCountFieldHeader" "false",
QUERY "OffsetCountValue" "10000",
QUERY "OffsetTotalPath" "X-Total-Count",
QUERY "IsOffsetTotalPathHeader" "true",
QUERY "allowResponseHeaders" "false",
QUERY "allowHttpsOnly" "true",
QUERY "useProxy" "false",
QUERY "proxyBypassOnLocal" "false",
QUERY "proxyUseDefaultCredentials" "true"
);

I got no error but I don't know if that's the correct syntax to load data or even if this is the right approach I should be using.

I have seen some other people posted similar questions to this but none of them explained in detail how did they solved it.

Can anyone help me on this:

References:

1.- https://community.qlik.com/t5/New-to-QlikView/Dynamically-load-and-fill-variables-from-a-table-t... 

2.- https://community.qlik.com/t5/Qlik-Sense-App-Development/REST-Connector-WITH-CONNECTION-Syntax/t... 

3.- https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Lo... 

4.- https://community.qlik.com/t5/Qlik-Sense-Data-Connectivity/Passing-a-parameter-to-REST-connector... 

5.- https://community.qlik.com/t5/Qlik-Sense-Data-Connectivity/REST-API-Connector-Multi-table/td-p/1... 

[Ilhui · Accepted solution]

I got the solution in stackoverflow and wanted to post it in here in case someone else runs on the same situation.

The WITH CONNECTION part can alter some parameters of the connection but not all of them. The http parameters can be changed there like: the url, headers, query parameters and the body. You cant change the rest like: the method (get or post), timeout, http protocol etc. These properties are connection specific and they have to be updated on connection level (when manually editing an connection or updating it via Repository API)

Have a look at the documentation about the WITH CONNECTION keyword

btw the QUERY command will just add a query parameter in the url before sending the request. And thats why you are not getting any data. For example

let vURL = 'http://example.com'

RestConnectorMasterTable:
SQL SELECT 
    ...my fields to query from Service NOW...
FROM JSON (wrap off) "result" PK "__KEY_result"
WITH CONNECTION (
URL "$(vURL)",
QUERY "type" "XML",
QUERY "subtype" "text"
);

will alter the url before sending it and the actual url will be http://example.com?type=XML&subtype=text

you can (should?) split each query param like this: QUERY "definition" "Gavel Sub-State Duration", QUERY "sysparm_fields" "id,sys_id,start,end,value,definition", QUERY "1sysparm_display_value" "false"

Here's the link for more  reference: https://stackoverflow.com/questions/63175850/dynamic-rest-connection-to-service-now-in-qlik-sense