Queries & Reports API: Insert or update a record with auditing

API endpoints for queries, reports, and record operations

Insert or update a record with auditing

[Warning:]	This service is to be used by experienced developers ONLY. Note that some referential integrity is enforced at the application level not the database level, so updating arbitrary field(s) can have unforeseen consequences. Please contact Catch-e Support if in any doubt.
Upsert should almost never be used to create new records of any sort.
New records should be created by using (in this order):
1)	The Catch-e APP (safest and most robust validations),
2)	Import Table Data (in the UI),
3)	Dedicated Token Authenticated API’s. A list of available services can be found here: https://api.catch-e.com/docs/#
If none of that is suitable:
4)	Submit a Change Request to ask Catch-e to undertake new API development for the record type you are wanting to create.
Only after exhausting all of these options:
5)	Ask Catch-e for advice on whether upsert can be considered.

Use this API to insert a record or update fields values for a record in a table and includes record locks and audit record creation.

This API can be run for authorised tables. Run getTablesAuthorisedForImport) to get a list of these tables.

Audit records created in these updates are marked as external.

Multiple records can be updated within the JSON payload provided they belong to the same table.
All records in the JSON payload are individually locked during the update.

Supplementary SWAGGER documentation is available here: api.catch-e.com/docs/#/Import%20%2F%20Export/upsert

Dependent Records

You can use a UUID placeholder in the import files to manage data where there are child records that have a key field dependency,
E.g. .
Visit the Dependent Records page for the details of how to use this feature.

Permissions

To run this API, the nominated 'web-services' role needs to be given permission.

If you are not actively using the API, leave the permission off for better security.

Go to Roles / Apis and check on Record:Upsert.

HTTP Method

Use the HTTP Method 'POST' for consuming this web service.

URL Examples

The table you are updating forms part of the URL path. E.g. api.staging.catch-e.com/gb/record/upsert/table_name

https://api.catch-e.com/gb/record/upsert/{table_name}

Parameters

Key	Format	Value	Notes	Mandatory
Path
table_name	string	fm_clients	Target table to update fields or create new records.	Yes
Query
timezone	string		Set a timezone to parse the timestamps.	No
table_name	string		If yes, records must contains a unique key.	No
Header
Accept	string	'application/problem+json' or 'application/vnd.catch-e-api.v1+json'		Yes
Audit-User-Id	string		Pass this if you want to record this user in the audit records as the creator of these records. This key will only work if you add the permission Audit:UserIdOverride to the authenticated user's role. This permission should only be given to the 'web-services' role if required.	No
Content-Type	string	'application/json' or 'application/json'		Yes

Body (JSON payload)

JSON Field	Format	Notes	Mandatory
contract_id	Int	The unique id for the table being updated. The field here and below must all belong to the table_name entered in the URL. If you want to insert a new record, leave the unique id blank.	Yes
colour		One or more fields can be updated here. List each field separately.	Yes

API Examples

This example is for two contracts being updated within the same request, the first has updates one field, the second updates two fields.

https://api.catch-e.com/gb/record/upsert/fm_contracts

[ { "contract_id": 100000, "colour": "Blue" }, { "contract_id": 100001, "colour": "White", "radio_pin": "777" }]

This example is to create a new contract (insert record). It will create a new Contract and set the colour to blue. All other fields will use default values or will be blank.

https://api.catch-e.com/gb/record/upsert/fm_contracts

[ { "contract_id": "", "colour": "Blue" }]

204 - No Content

(BLANK) — The request was successful request. No response is required (BLANK)

400 - Bad Request

"detail": "JSON decoding error: Underflow or the modes mismatch" — The JSON content is mis-configured e.g. a missing right brace (curly bracket '}') will trigger this error. "title": "Bad Request",

403 - Forbidden

"detail": "Forbidden" — You do not have permissions for this request.

422 - Unprocessable Entity

"detail": "Failed Validation" — The table used in the API uses a Unique Key. The required fields that make up the Unique Key have not all been passed. See Table Data for documented Unique Key tables and their required fields. "records": { "missingMandatoryColumns": "Missing mandatory columns: supplierid, cardnumber"
"detail": "Failed Validation" — The table used in the API does not have a singularis not authorised. See getTablesAuthorisedForImport for available tables. "table_name": { "noRecordFound": "Table is not authorised for import or is invalid"

423 - Locked

"detail": "Locked" — The record that the update was being run for was locked. Try again later. "Record already locked by user: {user_login}"

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Locked", "status": 423, "detail": "Locked"}

| If one of the records in the JSON Body is already being edited, the entire batch will fail and show this message. | |

Web service Gb Queries exportQuery

Execute a query known by queryid and return the resultset as XML.This web service executes a query stored in the table qtqueries, and can include queries with derived fields.

Note: If you don't need to use any derived fields in your query, use the Print a report query API.
Contact Catch-e Support if you need assistance with the set-up.

Input Fields

	Query string key	Format	Notes	Mandatory
1	query_id	integer	Unique identifier for a query	yes
2	output_format	string	Request a specific output format, options include 'xml' and 'text'. Defaults to 'xml'.	no
3	limit	integer	Add a LIMIT clause to the query	no

Warning: The limit clause will only work with simple queries, e.g. it won't work with queries involving UNIONs. If in doubt, please contact Catch-e Support.

Input URL Example

https://yourname.catch-e.net.au/services/gb/queries/exportQuery?query_id=100001

Output Fields

	XML element	Format	Notes	Mandatory
1	mixed	XML	Mixed column data	yes

Output Example

100000

Holden
…

100001

Ford
…

Error Codes

Error Codes
CATCHEERRORBADQUERY
CATCHEERRORBADQUERY_TYPE
CATCHEERRORUNKNOWNQUERY
CATCHEERRORTOOMANY_REQUESTS
403 Forbidden

Error Output

CATCHEERRORUNKNOWNQUERY

Troubleshooting

Tip: Visit the main Troubleshooting page for a list of all the available problem-solving tips.

403 Error

If you are using the exportQuery function in your web site, Catch-e will need to whitelist the IP address you are using so this function is allowed.

Web service Gb Queries query

Warning: This web service has been deprecated and is no longer supported by Catch-e.
You must use Print a report query instead and contact Catch-e Support if you need assistance with the set-up.

Execute a specified query, and return the resultset as Text(csv), XML, or Office(xls).
The query can include Derived Fields.
Multiple queries#multiple-queries-url-example) are supported with the resultset limited to XML only.

URL Encoding Tool

SQL queries are to be encoded according to rfc3986 - example see http://www.php.net/manual/en/function.rawurlencode.php

For your convenience there is an encode / decode tool located at https://yourname.catch-e.net.au/utils/urlencode.phpo

Input Fields

	Query string key	Format	Notes	For output_format	Mandatory
1	query	rfc3986 encoded	See encode / decode page for further details		yes
2	output_format	string	Request a specific output format, options include 'xml', 'text', and office(xls). Defaults to 'xml'.		no
3	columnheadingsflag	string	If you set this to 'yes', column headings will be included in the output.	text	no
4	separated_by	string	You can change the default field separator character(s) with this, e.g. using {tab} will create a Tab separated file.	text	no
5	textdelimitedby	string	This sets the character used to delimit fields containing special characters. Special characters include double quotes, newlines, and the delimiter character itself. The 'none' option will add no delimiters, and is useful when the query itself adds the delimiters e.g. creating a custom interface file to specification.	text	no
6	singletontagsflag	string	If you set this to 'yes', tags with empty values will use the singleton style format e.g. as opposed to .	xml	no
7	indent_size	string	You can set the indentation (whitespaces) used, this must be an integer between 0 and 9. The default is 2.	xml	no
8	newline_flag	string	If you set this to 'yes', all lines will end in a newline character. This improves readability when viewing or printing the XML from non-specialized text editors.	xml	no
9	limit	integer	Add a LIMIT clause to the query		no

Input URL Example 1

SELECT contract_id FROM fm_contracts LIMIT 10

To run the above query you would use:

https://yourname.catch-e.net.au/services/gb/queries/query?login=######&password=######&query=SELECT%20contract_id%20FROM%20fm_contracts%20LIMIT%2010

Input URL Example 2

SELECT t1.attachment_id, t1.table_name, t1.record_id, COUNT(t2.attachment_id) AS number_of_attachmentsFROM gb_attachments AS t1LEFT JOIN gb_attachments AS t2 ON t2.table_name = t1.table_name AND t2.record_id = t1.record_id AND t2.status_flag != 'deleted'WHERE t1.status_flag != 'deleted'GROUP BY t1.attachment_idHAVING number_of_attachments > 0

To run the above query you would use:

https://yourname.catch-e.net.au/services/gb/queries/query?login=######&password=######&query=SELECT%20t1.attachment_id%2C%20t1.table_name%2C%20t1.record_id%2C%20COUNT%28t2.attachment_id%29%20AS%20number_of_attachments%0AFROM%20gb_attachments%20AS%20t1%0ALEFT%20JOIN%20gb_attachments%20AS%20t2%20ON%20t2.table_name%20%3D%20t1.table_name%20AND%20t2.record_id%20%3D%20t1.record_id%20AND%20t2.status_flag%20%21%3D%20%27deleted%27%0AWHERE%20t1.status_flag%20%21%3D%20%27deleted%27%0AGROUP%20BY%20t1.attachment_id%0AHAVING%20number_of_attachments%20%3E%200

Output Fields

	XML element	Format	Notes	Mandatory
1	mixed	Text(csv) / XML / Office(xls)	Mixed column data	yes

Output Example

100000

Holden
…

100001

Ford
…

Output Example When Query Returns Empty Resultset

1 0

Multiple Queries URL Example

Each query must be separated by a semicolon (;) followed by a neIf Nothing Foundwline character.

2 Queries:https://yourname.catch-e.net.au/services/gb/queries/query?login=######&password=######&query=SELECT%20contract_id%20FROM%20fm_contracts%20LIMIT%202%3B%0ASELECT%20vmrs_code%20FROM%20fm_vmrs_codes%20LIMIT%202%3B

3 Queries:https://yourname.catch-e.net.au/services/gb/queries/query?login=######&password=######&query=SELECT%20contract_id%20FROM%20fm_contracts%20LIMIT%2010;%0D%0ASELECT%20quote_id%20FROM%20qt_quotes%20LIMIT%2010;%0D%0ASELECT%20%2A%20FROM%20fm_clients%20LIMIT%201

Multiple Query Output Example (2 Queries)

100000 100001 4WDC A/F

Error Codes

	Error Codes
1	CATCHEERRORBADQUERY
2	CATCHEERRORBADQUERY_TYPE
3	CATCHEERRORMISSINGQUERY
4	CATCHEERRORTOOMANY_REQUESTS

Error Output

CATCHEERRORMISSINGQUERY

GetCountries

Allows you to extract the list of Countries for use in other APIs that use "country_id:" in their payload. These include CreateDrivers), updateDriversList) or updateDriver).

The use of "country_id:" is optional.

Permissions

If you want to use this API in your system, enable Countries:Get.

Go to System Roles and select the 'web_services' role
Navigate to the Roles / APIs tab
Find the Countries:Get permission and check it's check box

HTTP Method

Use the HTTP Method 'GET' for consuming this web service.

URL Examples

For a list of Countries:

https://api.demo.catch-e.com/gb/countries

Input Fields (Body)

Key	Format	Notes	Mandatory
name	string	This is the name of the Country you require information for. This field supports the wildcard % operator (See Wildcard % operator notes below).	No
isocode2	string	ISO 3166-1 alpha-2 code. This field supports the wildcard % operator (See Wildcard % operator notes below).	No
isocode3	string	ISO 3166-1 alpha-3 code. This field supports the wildcard % operator (See Wildcard % operator notes below).	No
page	integer($int32)	If entered, will return data from the entered page number, based on the page_size entered. Default value is 1.	No
page_size	integer($int32)	If entered, will return the nominated amount of Countries per page. Default value is 25. i.e:If 6 is entered, 6 countries will appear per page	No

Wildcard % operator notes:

If you are using "Postman" app, with the wildcard operator, the search string of either %ab% or %ba%, will give this response:

"field_name": {"isEmpty": "Value is required and can't be empty"}.

If you need to search either %ab% or %ba%, please use %25ab%25 or %25ba%25 as required to return results as expected.

Input Fields (JSON)

https://api.catch-e.com/gb/countries[ { "name": "Australia", "iso_code_2": "", "iso_code_3": "", "page": "1" "page_size":"25" }]

Successful Response Example

{ "_links": { "self": { "href": "https://api.staging.catch-e.com/gb/countries/?name=Australia&page=1&page_size=25" }, "first": { "href": "https://api.staging.catch-e.com/gb/countries/?name=Australia&page_size=25" }, "last": { "href": "https://api.staging.catch-e.com/gb/countries/?name=Australia&page=1&page_size=25" } }, "_embedded": { "gb_countries": [ { "country_id": "7015503456999836108", "name": "Australia", "iso_code_2": "AU", "iso_code_3": "AUS", "_links": { "self": { "href": "https://api.staging.catch-e.com/gb/countries/7015503456999836108" } } } ] }, "page_count": 1, "page_size": 25, "total_items": 1, "page": 1}

Error Response Details

Validation Messages	Comments
401 - Unauthorized

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unauthorized", "status": 401, "detail": "Unauthorized"}

| You have not authenticated before running this API or The token_timeout of the current session has passed. You need to authenticate again. |
| 403 - Forbidden | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Forbidden", "status": 403, "detail": "Forbidden"}

| You do not have permissions for this request. Go to System Roles and enter 'web_services' Navigate to the Roles / APIs tab to make sure the permission you need to run this API is checked. |
| 422 - Unprocessable Entity | |
| | No validations are in place due to the wildcard option. |