Saving Data Using REST API

The SaveToDB add-in allows you to save data using OData and the DBGate REST API out of the box.

Users can also configure saving changes for other REST API services using the connection wizard.

Developers can set up the REST API in a database, enabling users to connect and save data without additional configuration steps.

In this case, users must connect to a database instead of directly connecting to a data service.

Developers can utilize the xls.objects table or Query List views.

xls.objects Table Format

Here’s the format for the xls.objects table:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
	<schema>	<name>	HTTP	<select command>	<insert command>	<update command>	<delete command>

Query List View Format

Here’s the format for the Query List view:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_PROCEDURE	UPDATE_PROCEDURE	DELETE_PROCEDURE	PROCEDURE_TYPE
	<schema>	<name>	HTTP	<select command>	<insert command>	<update command>	<delete command>

Sample Configuration for ZohoCRM

Here’s a sample configuration for ZohoCRM:

TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
ZohoCRM	Accounts	HTTP	https://www.zohoapis.com/crm/v2/Accounts	POST {$url} {"data":[{@json_row_values}]}	PUT {$url}/{id} {"data":[{@json_row_values}]}	DELETE {$url}/{id}
ZohoCRM	Contacts	HTTP	https://www.zohoapis.com/crm/v2/Contacts	POST {$url} {"data":[{@json_row_values}]}	PUT {$url}/{id} {"data":[{@json_row_values}]}	DELETE {$url}/{id}
ZohoCRM	Leads	HTTP	https://www.zohoapis.com/crm/v2/Leads	POST {$url} {"data":[{@json_row_values}]}	PUT {$url}/{id} {"data":[{@json_row_values}]}	DELETE {$url}/{id}

This sample includes three configured HTTP objects in the ZohoCRM schema: Accounts, Contacts, and Leads.

The TABLE_CODE field contains the URLs used to load data. For more details, see Getting Data from REST API.

Other fields specify the formats for insert, update, and delete HTTP commands.

HTTP Command Grammar

The formal grammar for HTTP commands is as follows:

HTTP-command = method SP absolute-URI [ SP "HTTP/1.1"] *( NEWLINE header-name ":" header-value ) [ 2*NEWLINE message-body ]

method = "POST" | "PUT" | "PATCH" | "MERGE" | "DELETE"

NEWLINE = CR | LF | CRLF

The URL can include parameters enclosed in curly brackets, such as {id} in the sample.

The add-in populates these parameters from table columns and selects URL parameters.

Additionally, you can use the built-in {url} parameter, which is populated with the select URL (without parameters).

The body can contain parameters prefixed with the @ character.

You can specify parameters for all properties manually, or use built-in parameters like @json_row_values and @form_row_values, which include all row values.

The add-in supports both application/json and application/x-www-form-urlencoded body formats, automatically detecting the format based on the body definition.