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