This article is aimed at web services developers and assumes a working knowledge of REST, JSON, SQL and HireTrack NX.
Although HireTrack NX doesn't have a published API per se, it is still possible to get data in and out of HireTrack NX using third party web services.
This is done by the Web Services calling upon HireTrack's QBE facility to GET or POST data.
One of the most powerful areas of HireTrack NX is the QBE facility. This allows the user or a developer to design and test queries that can either retrieve data from the HireTrack NX database, or update/append data. However, it does require an understanding of how databases work and the structure of the HireTrack NX database, so it may not be suitable for all.
Note: We recommend that any QBE that updates the HireTrack NX database be written in consultation with the HireTrack NX support team as there may be unforeseen side effects to writing certain data.
The QBE designer is launched from the Integrations button and further information on how to build and publish QBE's can be found here: www.navigator.co.uk/manuals/client/online/qbe_getting_started.htm
There are currently 5 different types of Integration QBE.
- Acc. - These are QBE's used by the accounts integration plug in and can be ignored here
- Poll - These are QBE's used by the WebService to GET data from HireTrack NX. No parameters are supplied by the WebService e.g Get All Jobs Modified This Week
- Search - These are QBE's used by the WebService to GET data from HireTrack NX. Search parameters are supplied by the WebService e.g Get Company Address WHERE Company Name is "Navigator".
- Push - These are QBE's used by the WebService to POST data to HireTrack NX.
- Push MD - These are QBE's used by the WebService to POST Master-Detail data to HireTrack NX. e.g an Invoice header (Master) and one or more Invoice lines (Detail)
- Trigger - This is a QBE that can be called by a remote WebService in response to HireTrack NX calling a WebHook.
Before your WebService can communicate with HireTrack NX, the HireTrack NX integration service needs to be installed. This article https://navigator.zendesk.com/hc/en-us/articles/360018171693-Installing-the-Zapier-Integration explains how to install and configure it (there's no need for a Zapier account and the section titled "Configuring Zapier" can be ignored) .
Once the service has been installed there are a number of header variables that must be set before any WebService call can happen. If you're not au fait with HireTrack NX, then you may need talk to the HireTrack Admin to find this information.
-
API Server Address: This is the address the web service will use to connect to your HireTrack NX Integration Server - in our example zapier.navigator.co.uk
-
API Server Port: This is the port the web service will use to connect to your HireTrack NX Integration Server. The default is 10055.
These make up the endpoint URL e.g https://zapier.navigator.co.uk:10055 -
HireTrack NX Server's IP Address: This is the address that the HireTrack NX Integration Server will use to connect to your HireTrack NX Database Server. This is usually an internal address on your network. Header name: ipaddress
-
HireTrack NX Server's Port: This is the port that the HireTrack NX Integration Server will use to connect to your HireTrack NX Database Server. The default is 16000. Header name: port
-
Alias: This is the database alias on the HireTrack NX Database Server. Header name: alias
-
Authenticate Against: There are two options here; Alias or Server. If alias is selected then the integration service will use a regular HireTrack NX Client username & password to connect. If server is selected then your webservice will use a username & password configured on the database server. Header name: target
-
Username: The username the web service will use to connect. This will either be a server or Client username depending on the Autheigate against setting. Header name: username
-
Password: The password the web service will use to connect that pairs with the appropriate Username. Header name: password.
All integration QBEs can be accessed via the api_v1 collection. The endpoints are detailed below.
TEST CONNECTION - Tests that a connection can be made successfully to the HireTrack NX database server
GET Test Connection
https://zapier.navigator.co.uk:10055/api_v1/TestConnection
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
curl --location --request GET 'https://zapier.navigator.co.uk:10055/api_v1/TestConnection' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: LiveData' \
--header 'username: administrator' \
--header 'password: NexusDB'
GET OUTPUT QBEs - Gets a list of QBE's that can be called. The query_type parameter specifies which QBE types are required.
- Poll QBE's: query_type=5
- Search QBE's: query_type=6
- Push QBE's: query_type=7
- Push MD QBE's: query_type=8
- Trigger QBE's: query_type=9
GET Output QBEs
https://zapier.navigator.co.uk:10055/api_v1/GetOutputQBEs?query_type=5
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
curl --location --request GET 'https://zapier.navigator.co.uk:10055/api_v1/GetOutputQBEs?query_type=5' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: LiveData' \
--header 'username: administrator' \
--header 'password: NexusDB'
The response is a JSON array. Each id value refers to the internal QBE reference and the description is the human readable QBE name.
GET QBE Results - Runs POLL QBEs and returns the results.
See https://navigator.zendesk.com/hc/en-us/articles/360022034394-Using-Zapier-Part-1-Pulling-Information-in-to-Zapier-Polling
The qbe_id parameter specified which QBE will be run. e.g to return the results of the Get Jobs Changes QBE use the QBE # 56.
GET Test Connection
https://zapier.navigator.co.uk:10055/api_v1/GetQBEresults?qbe_id=56
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
curl --location --request GET 'https://zapier.navigator.co.uk:10055/api_v1/GetQBEresults?qbe_id=56' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: LiveData' \
--header 'username: administrator' \
--header 'password: NexusDB'
The response is a JSON array. The data returned depends on the QBE. In this example the QBE is selecting (and thus returning) 4 fields.
GET SEARCH RESULTS - Runs Search QBEs and returns the results.
See https://navigator.zendesk.com/hc/en-us/articles/360022253854-Using-Zapier-Part-2-Pulling-Information-in-to-Zapier-Exploring-Zapier-Searches-and-Master-Detail-via-a-Zap-to-Xero
The qbe_id parameter specified which QBE will be run. e.g to return the results of the Get Jobs Changes QBE use the QBE # 56. Additionally the search terms should also be set as parameters.
GET Test Connection
https://zapier.navigator.co.uk:10055/api_v1/GetSearchResults?qbe_id=66&Invoice_count=1
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
curl --location --request GET 'https://zapier.navigator.co.uk:10055/api_v1/GetSearchResults?qbe_id=66&Invoice_count=1' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: LiveData' \
--header 'username: administrator' \
--header 'password: NexusDB'
POST POST DATA - Runs a PUSH QBEs updates the database using the passed parameters and optionally returns any the results.
See https://navigator.zendesk.com/hc/en-us/articles/360022349454-Using-Zapier-Part-3-Exploring-WebHooks-and-Callbacks-via-a-Zap-to-request-mileage-information-from-Google-
The qbe_id parameter specified which QBE will be run. e.g to return the results of the Get Jobs Changes QBE use the QBE # 58. Additionally the data values should also be set as parameters.
POST Post Data
https://zapier.navigator.co.uk:10055/api_v1/PostData?qbe_id=58&Mileage=125&RecordNo=1
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
curl --location --request POST'https://zapier.navigator.co.uk:10055/api_v1/GetSearchResults?qbe_id=66&Invoice_count=1' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: LiveData' \
--header 'username: administrator' \
--header 'password: NexusDB'
POST POST MASTER DETAIL - Runs a PUSH MD QBE to update the database using the passed parameters and optionally returns any the results.
Unlike all the other GET & POST calls this one uses a different methodology. Two QBE's are required to write Master Detail data, one fo the Master and one for each instance of the detail. The two QBE's get called using their API Key value, rather than their QBE #.
Additionally the data to post is sent using a JSON representation as part of the BODY of the message.
POST Post Data
https://zapier.navigator.co.uk:10055/api_v1/PostMasterDetail?master_id={A4B56360-485E-417F-B3AD-CEC9708172C9}&detail_id={9732EA50-354A-4E22-BA8C-88A0BF3FB86E}
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
PARAMS
master_id {A4B56360-485E-417F-B3AD-CEC9708172C9}
detail_id {9732EA50-354A-4E22-BA8C-88A0BF3FB86E}
BODY Raw
{
"Title": "A Title",
"User": 1,
"Site": 1,
"Currency": 0,
"PriceScheme": 1,
"client_name": "Navigator Systems Ltd",
"client_id": 1,
"details":
[
{
"Qty": 1,
"Eqtype": 1,
"price_each": 12.50
}
]
}
curl --location --request POST 'https://zapier.navigator.co.uk:10055/api_v1/PostMasterDetail?master_id={A4B56360-485E-417F-B3AD-CEC9708172C9}&detail_id={9732EA50-354A-4E22-BA8C-88A0BF3FB86E}' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: Zapier-Data' \
--header 'username: administrator' \
--header 'password: NexusDB' \
--data-raw '{
"Title": "A Title",
"User": 1,
"Site": 1,
"Currency": 0,
"PriceScheme": 1,
"client_name": "Navigator Systems Ltd",
"client_id": 1,
"details":
[
{
"Qty": 1,
"Eqtype": 1,
"price_each": 12.50
}
]
}'
CallBacks
After a PUSH or PUSH MD POST call, it may be that the HireTrack NX user interface needs to call a refresh in order to display this updated data.
This is done via the callback setting. In this example "We're updating the Transport Detail, so if it's displaying the record we've updated, refresh it".
To use CallBacks the Web Service needs to tell HireTrack NX which record has been updated. It does this via the field named RecordID. Select the appropriate field and alias it as RecordID in the final SELECT statement. See https://navigator.zendesk.com/hc/en-us/articles/360022349454-Using-Zapier-Part-3-Exploring-WebHooks-and-Callbacks-via-a-Zap-to-request-mileage-information-from-Google-
Note: On the initial release CallBacks are just provided for Transport. CallBacks for additional areas will be added in the future. If there's an area you specifically require, please post a request to the support team and they will log it for the developers attention.
WebHooks
WebHooks allow HireTrack NX to call the remote web service, passing to it a dataset using a JSON structure.
To initiate the WebHook call the matching API key needs to be written to the Trigger_logs table. This is normally done by a custom database trigger in response to a database change. e.g When the user adds a new transport record, a database trigger writes an entry to the triggers_log table comprising of the APIKey and the appropriate data record key (record_ID).
The QBE with a matching APIKey is then automatically run (the record_ID field can be JOINed if required) and the results of this QBE are then converted to JSON and POSTed as to the address stored as the WebHook.
HTTPClient.Request.ContentType := 'application/json';
HTTPClient.Request.UserAgent :=
'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.139 Safari/537.36';
Helper Functions.
There are three helper calls available
GetMetaData, GetFieldData & GetParameters
GET METADATA - Returns the fieldname and the field type expected in the results of running the query. The qbe_id parameter specified which QBE will be run. e.g to return the results of the Get Jobs Changes QBE use the QBE # 56.
GET Test Connection
https://zapier.navigator.co.uk:10055/api_v1/GetMetaData?qbe_id=56
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
curl --location --request GET 'https://zapier.navigator.co.uk:10055/api_v1/GetMetaData?qbe_id=56' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: LiveData' \
--header 'username: administrator' \
--header 'password: NexusDB'
Here the QBE is expected to return four fields
- Fieldname id of type int (integer)
- Fieldname Overview of type unicode (string)
- Fieldname Details of type text (multiline string)
- Fieldname Date of type datetime
GET FIELD DATA - Returns the fieldname and the field type and size expected in the results of running the query. Unlike GET METADATA, GET FIELD DATA will return the native fieldtypes of the underlying database.
The qbe_id parameter specified which QBE will be run. e.g to return the results of the Get Jobs Changes QBE use the QBE # 56.
GET Test Connection
https://zapier.navigator.co.uk:10055/api_v1/GetFieldData?qbe_id=56
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
curl --location --request GET 'https://zapier.navigator.co.uk:10055/api_v1/GetFieldData?qbe_id=56' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: LiveData' \
--header 'username: administrator' \
--header 'password: NexusDB'
Here the QBE is expected to return four fields
- Fieldname id of type ftLongWord
- Fieldname Overview of type ftstring, length 255 characters
- Fieldname Details of type ftMemo (unrestricted number of characters)
- Fieldname Date of type ftDateTime
GET PARAMETERS - Returns the parameter name and the type expected by the QBE. The qbe_id parameter specified which QBE will be run. e.g to return the parameters for the Search Invoice QBE use the QBE # 66.
GET Paramters
https://zapier.navigator.co.uk:10055/api_v1/GetParameters?qbe_id=66
HEADERS
target server
ipaddress 127.0.0.1
port 16000
alias LiveData
username administrator
password NexusDB
curl --location --request GET 'https://zapier.navigator.co.uk:10055/api_v1/GetParameters?qbe_id=66' \
--header 'target: server' \
--header 'ipaddress: 127.0.0.1' \
--header 'port: 16000' \
--header 'alias: LiveData' \
--header 'username: administrator' \
--header 'password: NexusDB'
Here the QBE is expects one additional parameter
- Invoice_count of type int (integer)
The additional information returned is label - something that could be displayed to and end user if input is required, key - the parameter name, required - whether the parameter is mandatory (they always are) and type, here an integer.
This QBE would therefore expect to be called thus
https://zapier.navigator.co.uk:10055/api_v1/GetSearchResults?qbe_id=66&Invoice_count=1
Comments
0 comments
Please sign in to leave a comment.