TableAccess APIs
Retrieve all records of a table
Overview
Allows you to retrieve all records of a specific table. The response gives you a set of records.
Also see paging APIs to specify a page size to enable you to iterate through large sums of data.
Entry point
GET /api/tableaccess/{tablename}
Authorisation
Use the bearer token obtained from GetBearerToken for authorisation.
Path Parameters
Enter the name of the table you wish to query in {tablename} as a string:
Name | Mandatory | Description | Default value | Example |
---|---|---|---|---|
Table | Yes | Table name | Booking |
Resource URL
https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/booking
Request Body
There will be no Request Body.
Example Response
If successful, returns the following response:
200 OK
Success
A set of records:
[
{
"booking_guid": "7473d3cd-3d26-4d19-aa66-003259e4d02e",
"booking_time": 8.0,
"booking_loading": 100.0,
"booking_start": "2022-02-18T00:00:00+00:00",
"booking_end": "2022-02-19T23:59:59+00:00",
"booking_resource_guid": "a72b5a7b-f291-46e3-befd-32f2a4eec40b",
"booking_job_guid": "8e3b0c7a-54bc-4614-87db-5dd6fe9791a0",
"booking_nonwork": null,
"booking_fixedtime": 0,
"booking_notes": null,
"booking_bookingtype_guid": "3ea83722-e35a-43ac-8630-dc1a2d0fa10e",
"booking_workactivity_guid": null,
"booking_hours_per_day": 8.0,
"booking_totalcost": 0.0,
"booking_totalrevenue": 0.0,
"booking_diary_hours": 8.0,
"booking_diary_days": 1,
"booking_createdon": "2022-02-11T05:23:53.9066667+00:00",
"booking_updatedon": "2022-02-16T12:30:07.35+00:00",
"booking_updatedby_resource_guid": null,
"booking_createdby_resource_guid": "a72b5a7b-f291-46e3-befd-32f2a4eec40b",
"booking_chargemode": 0,
"booking_overridden_chargerate_guid": null,
"booking_revenueperhour": null,
"booking_costperhour": null,
"booking_startday": "2022-02-18T00:00:00",
"booking_endday": "2022-02-19T00:00:00",
"booking_fte_hours": 8.0,
"booking_fte_days": 1
},
{
"booking_guid": "a80032af-e42e-45b7-a6fa-0052e5a82f0f",
"booking_time": 8.0,
"booking_loading": 100.0,
"booking_start": "2022-02-18T00:00:00+00:00",
"booking_end": "2022-02-18T23:59:59+00:00",
"booking_resource_guid": "feb00825-767c-47d6-8c94-836c2bd3d558",
"booking_job_guid": "8e3b0c7a-54bc-4614-87db-5dd6fe9791a0",
"booking_nonwork": null,
"booking_fixedtime": 0,
"booking_notes": null,
"booking_bookingtype_guid": "3ea83722-e35a-43ac-8630-dc1a2d0fa10e",
"booking_workactivity_guid": null,
"booking_hours_per_day": 8.0,
"booking_totalcost": 0.0,
"booking_totalrevenue": 0.0,
"booking_diary_hours": 8.0,
"booking_diary_days": 1,
"booking_createdon": "2022-01-28T09:45:03.62+00:00",
"booking_updatedon": "2022-02-16T12:30:07.35+00:00",
"booking_updatedby_resource_guid": null,
"booking_createdby_resource_guid": "da0d0d98-c196-4493-bffe-724ccd7ee3ac",
"booking_chargemode": 0,
"booking_overridden_chargerate_guid": null,
"booking_revenueperhour": null,
"booking_costperhour": null,
"booking_startday": "2022-02-18T00:00:00",
"booking_endday": "2022-02-18T00:00:00",
"booking_fte_hours": 8.0,
"booking_fte_days": 1
}
]
If unsuccessful, returns one of the following response codes as a string:
- 400 - Bad request
- 401 - Unauthorised
- 403 - Forbidden
- 404 - Not found
- 409 - Conflict
Insert a single record into a table
Overview
Allows you to insert a single record into a specific table.
Entry point
POST /api/tableaccess/{table}
Authorisation
Use the bearer token obtained from GetBearerToken for authorisation.
Path Parameters
Enter the name of the table in {tablename} as a string:
Name | Mandatory | Description | Default value | Example |
---|---|---|---|---|
Table | Yes | Table name | Booking |
Request URL
https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/Booking
Example Request
Specify the data you wish to insert. In the Booking table, 'booking_guid' is automatically generated and does not need to be specified. The other fields specified in the example below, except for 'Booking_notes' form a part of the primary key and therefore must be specified at the minimum.
{
"booking_loading": 100.0,
"booking_start": "2022-02-10T00:00:00+00:00",
"booking_end": "2022-02-12T23:59:59+00:00",
"booking_resource_guid": "c85de5ba-8346-4168-a9a7-1f35062aea07",
"booking_job_guid": "18916398-6bd8-4b84-899a-d3470183df61",
"booking_notes": "example of insert"
}
Response
If successful, returns the following response:
- 200 OK
- Success
else returns one of the following response codes as a string:
- 400 - Bad request
- 401 - Unauthorised
- 403 - Forbidden
- 404 - Not found
- 409 - Conflict
Update a field of a single record
Overview
Allows you to update a single record of a specific table based on the request body. The response is a boolean value (true/false) depending on success/failure of the update.
Entry point
PATCH api/tableaccess/[table]
Authorisation
Use the bearer token obtained from GetBearerToken for authorisation.
Path Parameters
Enter the name of the table you wish to update:
Name | Mandatory | Description | Default value | Example |
---|---|---|---|---|
Table | Yes | Table name | Booking |
Query Parameter
Enter the ID of the record you wish to update in the request body.
Resource URL
https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/booking?id=7473d3cd-3d26-4d19-aa66-003259e4d02e
Example Request
The following Request body will translate to the following query:
update booking
set booking_time = 9
where booking_guid = '7473d3cd-3d26-4d19-aa66-003259e4d02e'
{
"booking_time": "9"
}
Response
If successful, returns the following response:
- 200 OK
- Success
else returns one of the following response codes as a string:
- 400 - Bad request
- 401 - Unauthorised
- 403 - Forbidden
- 404 - Not found
- 409 - Conflict
Update all fields of a single record
Overview
Allows you to update all fields of a single record of a specific table based on the request body. The response is a boolean value (true/false) depending on success/failure of the update.
Entry point
PUT api/tableaccess/[table]
Authorisation
Use the bearer token obtained from GetBearerToken for authorisation.
Path Parameters
Enter the name of the table you wish to update:
Name | Mandatory | Description | Default value | Example |
---|---|---|---|---|
Table | Yes | Table name | Booking |
Query Parameter
Enter the IDs of the record you wish to update in the request body.
Resource URL
https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/booking?id=7473d3cd-3d26-4d19-aa66-003259e4d02e
Example Request
The following Request body will translate to the following query:
Update booking set booking_time = 9, ... where booking_guid = '7473d3cd-3d26-4d19-aa66-003259e4d02e' { "booking_time": "9", ... }
Response
If successful, returns the following response:
- 200 OK
- Success
else returns one of the following response codes as a string:
- 400 - Bad request
- 401 - Unauthorised
- 403 - Forbidden
- 404 - Not found
- 409 - Conflict
Retrieve a single record from a table
Overview
Allows you to retrieve a single record from a specific table. The response returns all the fields of the record.
Entry point
GET api/tableaccess/[table]/[ID]
Authorisation
Use the bearer token obtained from GetBearerToken for authorisation.
Path Parameters
Enter the name of the table and ID of the record you wish to query:
Name | Mandatory | Description | Default value | Example |
---|---|---|---|---|
Table | Yes | Table name | Booking | |
Id | Yes | Id of the record to retrieve | 7473d3cd-3d26-4d19-aa66-003259e4d02e |
Resource URL
https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/booking/7473d3cd-3d26-4d19-aa66-003259e4d02e
Request body
Nothing needs to be specified in the Request body.
Response
If successful, returns the following response:
- 200 OK
- Success
- The record:
{
"booking_guid": "7473d3cd-3d26-4d19-aa66-003259e4d02e",
"booking_time": 8.0,
"booking_loading": 100.0,
"booking_start": "2022-02-18T00:00:00+00:00",
"booking_end": "2022-02-19T23:59:59+00:00",
"booking_resource_guid": "a72b5a7b-f291-46e3-befd-32f2a4eec40b",
"booking_job_guid": "8e3b0c7a-54bc-4614-87db-5dd6fe9791a0",
...
}
If unsuccessful, returns one of the following response codes as a string:
- 400 - Bad request
- 401 - Unauthorised
- 403 - Forbidden
- 404 - Not found
- 409 - Conflict
Delete a single record
Overview
Allows you to delete a single record of a specific table based on the supplied ID. The response is a boolean value (true/false) depending on success/failure of the deletion.
Entry point
DELETE /api/tableaccess/{table}/{id}
Authorisation
Use the bearer token obtained from GetBearerToken for authorisation.
Path Parameters
Enter the name of the table and the ID of the record you wish to delete.
Name | Mandatory | Description | Default value | Example |
---|---|---|---|---|
Table | Yes | Table name | Booking | |
Id | Yes | Id of the record to delete | 7473d3cd-3d26-4d19-aa66-003259e4d02e |
Resource URL
https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/booking/7473d3cd-3d26-4d19-aa66-003259e4d02e
Request body
Nothing needs to be specified in the Request body.
Response
If successful, returns the following:
- 200 OK
- Success
else returns one of the following response codes as a string:
- 400 - Bad request
- 401 - Unauthorised
- 403 - Forbidden
- 404 - Not found
- 409 - Conflict