Objects

Objects is a section that defines the objects and their fields in the connector. It contains object configurations.

Object configurations contain the following settings:

For example,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
"Objects": [{
        "Name": "Tickets",
        "Url": "/tickets?include=requester,tags",
        "ResultPath": "tickets",
        "Columns": [{
                "Name": "Id",
                "APIPath": "id",
                "DbType": "Int64",
                "Primary": true,
                "ReturningResultJPath": "ticket.id",
                "Createable": false,
                "Updateable": false
            },
            {
                "Name": "GroupId",
                "APIPath": "group_id",
                "DbType": "Int64",
                "FilterOperations": [{
                        "Operation": "Equals",
                        "ParameterName": "group_id",
                        "ParameterType": "UrlParameter"
                    },
                    {
                        "Operation": "LessThan",
                        "ParameterName": "group_id",
                        "ParameterType": "UrlParameter"
                    },
                    {
                        "Operation": "GreaterThan",
                        "ParameterName": "group_id",
                        "ParameterType": "UrlParameter"
                    },
                    {
                        "Operation": "LessThanOrEquals",
                        "ParameterName": "group_id",
                        "ParameterType": "UrlParameter"
                    },
                    {
                        "Operation": "GreaterThanOrEquals",
                        "ParameterName": "group_id",
                        "ParameterType": "UrlParameter"
                    }
                ]
            }, {
                "Name": "DepartmentId",
                "APIPath": "department_id",
                "DbType": "Int64"
            }, {
                "Name": "Status",
                "APIPath": "status",
                "DbType": "String",
                "NativeDbType": "Int32",
                "Required": true,
                "Enum": true,
                "EnumValues": [{
                        "Name": "Open",
                        "Value": "2"
                    },
                    {
                        "Name": "Pending",
                        "Value": "3"
                    },
                    {
                        "Name": "Resolved",
                        "Value": "4"
                    },
                    {
                        "Name": "Closed",
                        "Value": "5"
                    }
                ],
                "FilterOperations": [{
                        "Operation": "Equals",
                        "ParameterName": "status",
                        "ParameterType": "UrlParameter"
                    },
                    {
                        "Operation": "LessThan",
                        "ParameterName": "status",
                        "ParameterType": "UrlParameter"
                    },
                    {
                        "Operation": "GreaterThan",
                        "ParameterName": "status",
                        "ParameterType": "UrlParameter"
                    },
                    {
                        "Operation": "LessThanOrEquals",
                        "ParameterName": "status",
                        "ParameterType": "UrlParameter"
                    },
                    {
                        "Operation": "GreaterThanOrEquals",
                        "ParameterName": "status",
                        "ParameterType": "UrlParameter"
                    }
                ]
            }
        ]
        "ParentRelations": [{
            "Field": "DepartmentId",
            "RelatedTable": "Departments",
            "RelatedField": "Id"
        }],
        "RetrieveSingleOperation": {
            "Url": "/tickets/<ticket_id>?include=tags,requester,problem,assets",
            "ResultPath": "ticket",
            "Arguments": [{
                "ColumnName": "Id",
                "ParameterName": "ticket_id",
                "ParameterType": "UrlPath"
            }]
        },
        "InsertOperation": {
            "Url": "/tickets",
            "Method": "POST",
            "ReturningStrategy": {
                "Type": "Row"
            },
            "InputType": "JsonBody"
        },
        "UpdateOperation": {
            "Url": "/tickets/<ticket_id>",
            "Arguments": [{
                "ColumnName": "Id",
                "ParameterName": "ticket_id",
                "ParameterType": "UrlPath"
            }],
            "Method": "PUT",
            "ReturningStrategy": {
                "Type": "Row"
            },
            "InputType": "JsonBody"
        },
        "DeleteOperation": {
            "Url": "/tickets/<ticket_id>",
            "Arguments": [{
                "ColumnName": "Id",
                "ParameterName": "ticket_id",
                "ParameterType": "UrlPath"
            }],
            "Method": "DELETE"
        }
    }
   

General Settings

Name

The name of the object.

URL

The object endpoint URL. It can be an absolute or relative URL. An absolute URL is a full URL, such as https://api.example.com/accounts. Relative URL, for example, /customers, is set relatively to the base URL.

For example, if the base URL is https://api.example.com and the relative is /customers, then the object’s URL is formed of base and relative URL conjunction: https://api.example.com/customers.

If the URL value starts with the ‘/’ character, Skyvia treats it as a relative URL and appends it to the BaseUrl.

Method

A web request method, for example, GET, POST, PUT. Default method is GET.

ResultPath

A path to a response field containing the endpoint call’s result. More details are available in ProviderConfiguration ResultPath.

BodyPattern

A setting that describes the object body format. For more details, refer to ProviderConfiguration BodyPattern setting.

FieldsParameterSupported

This setting defines whether the source object supports specifying the fields list as an endpoint URL parameter for getting data. Default value is false. For more details, refer to the FieldsParameterSupported setting in ProviderConfiguration section.

FieldsParameterName

This setting determines the name of the web request URL parameter, which contains the list of fields. For more details, see the FieldsParameterName

Headers

The headers of the web request for reading the object. Specify Headers as a JSON array of objects with the Key and Value properties. These headers are merged with the respective constant headers, specified in ProviderConfiguration Headers section.

PagingStrategy

This section defines the specific paging strategy for an object. Add this section if it differs from the ProviderConfiguration PagingStrategy

ErrorHandling

Determines the specific object behavior in case of errors. Add this section if the behavior differs from the general settings provided in ProviderConfiguration ErrorHandling section.

ConstantParameters

The constant parameters of the web request for reading the object. These constant parameters are merged with the respective constant parameters, specified in ProviderConfiguration ConstantParameters section.

Columns

This section contain column configurations, which define connector object fields:

Name

The field name the user will see when querying the object data. For example, {"Name":"Amount"}.

APIPath

The name of the respective property in a JSON object corresponding to a record. If the property is nested, then the APIPath specifies its path in the format <parent property name>.<property name>. The nesting level is unlimited.

For example, the data source returns a record in the following format:

1
2
3
4
5
6
7
8
9
10
{
    address: {
        country: "USA",
        city: "New York",
        contactInfo: {
            phone: "1111",
            fax: "1111"
        }
    }
}

The corresponding columns in the corresponding object configuration should look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
{
    "Name": "AddressCountry",
    "APIPath": "address.country",
    "DbType":"String",
    "Createable":true,
    "Updateable":true
},
{
    "Name": "AddressCity",
    "APIPath": "address.city",
    "DbType":"String",
    "Createable":true,
    "Updateable":true
},
{
    "Name": "AddressPhone",
    "APIPath": "address.contactInfo.phone",
    "DbType":"String",
    "Createable":true,
    "Updateable":true
},
{
    "Name": "AddressFax",
    "APIPath": "address.contactInfo.fax",
    "DbType":"String",
    "Createable":true,
    "Updateable":true
}

If a field belongs to a foreign key with AggregateParent = true in ParentRelations section, APIPath must be specified related to the parent object web response, not to the current object. For more details, see the example.

You can use the <@=parent> setting in the APIPath to access the object on a level higher than the current object.

It allows displaying the nested arrays as separate objects, having some fields from their parent objects, for example, their parent IDs.

For example, the data source has the CustomerList object storing customers, and customer records have the Addresses JSON array field, storing customer addresses. We want to represent customer addresses as a separate object and have the relation to the corresponding customers in them by the customer ID field (which we label as CustomerID).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
{
    "Name": "CustomerAddresses",
    "Url": "/customer",
    "ResultPath": "CustomerList.Addresses",
    "Columns": [{
                "Name": "ID",
                "APIPath": "ID",
                "DbType": "Guid",
                "Createable": false,
                "Updateable": false
            },
            {
                "Name": "CustomerID",
                "APIPath": "<@=parent>.ID",
                "DbType": "Guid",
                "Createable": false,
                "Updateable": false
            }

If the dot character is a part of the name in JSON, and not the separator in the hierarchy, then you need to use quotation with the {} characters, for example:

1
2
3
4
{
    "Name": "Amount",
    "APIPath": "{my.amount}"
}

If the path to the corresponding property in JSON is more complex, for example, if it is inside of a JSON array, or API names in nesting include spaces/other special characters, you need to specify the JPath setting instead of APIName. JPath setting supports more advanced JSON path notation.

DMLAPIName

This setting is used when the data source API uses different property names/paths for DML operations and for selecting data. In this case, it specifies the corresponding API name of the field for CREATE/UPDATE operations.

For example,

1
2
3
4
5
6
7
"Columns": [
    {
        "Name": "OrderId",
        "APIPath": "order_id",
        "DbType": "String",
        "DMLAPIName": "tracking.order_id"
    }

Use the same notation for this setting as for the APIPath setting.

InsertAPIName

Use this setting when the data source API uses different property names/paths for CREATE and for UPDATE operations. In this case, it specifies the corresponding API name of the field for CREATE operations.

Use the same notation for this setting as for the APIPath setting.

For example, in our Stripe connector the BankAccounts object Country field is configured like this:

1
2
3
4
5
6
7
8
9
{
    "Name": "Country",
    "APIPath": "country",
    "InsertAPIName": "source.country",
    "DbType": "String",
    "Length": 2,
    "Required": true,
    "Updateable": false
}

UpdateAPIName

Use this setting when the data source API uses different property names/paths for CREATE and for UPDATE operations. In this case, it specifies the corresponding API name of the field for UPDATE operations.

Use the same notation for this setting as for the APIPath setting.

For example, in our Stripe connector the Plans object ProductId field is configured like this:

1
2
3
4
5
6
7
{
    "Name": "ProductId",
    "APIPath": "product",
    "InsertAPIName": "product.id",
    "UpdateAPIName": "product",
    "DbType": "String"
}

JPath

JSON path to search for a property with a field value in web response. This is the alternative setting for APIPath in more complex cases.

If a field is defined via JPath, it automatically becomes Required = false, Createable = false and Updateable = false.

If a field belongs to a foreign key with AggregateParent = true in ParentRelations section, JPath must be specified related to the parent object web response, not to the current object.

JSON path is an expression which queries and gets data from JSON structure in the specified JSON structure elements. You must follow JSON Path syntax to specify it right.

You need to use escaping for fields with special characters in their names. Names with dots, spaces, or other special characters must be quoted with square brackets.

Data sources may sometimes return arrays, which include elements with a space character in their names. For example,

1
2
3
4
5
6
7
8
9
10
11
{
    "fields": [
        {
            "first name": [{
                "value": "John",
                "label": "main",
                "is_primary": true
            }]
        }   
    ]
}

You can set JPath in the following format to get such field values.

1
"JPath": "fields.['first name'][0].value"

SingleResultJPath

Specifies the JSON path to search for a property with a field value (relative to ResultPath. Use this setting when the data source API uses a different property name/path for the operation of retrieving a single record by ID (RetrieveSingleOperation).

You need to use escaping for fields with special characters in their names. Names with dots, spaces, or other special characters must be quoted with square brackets.

ReturningResultJPath

Specifies the JSON path to search for a property with a field value (relative to ResultPath.

Use this setting when:

• ReturningStrategy is defined for an object or for the connector globally.
• The field path in the INSERT/UPDATE operation result differs from the path in APIPath or JPath.

You need to use escaping for fields with special characters in their names. Names with dots, spaces, or other special characters must be quoted with square brackets.

Primary

Defines whether the field is the primary key or not. It accepts true and false values. Default value is false.

Set Primary = true for every composite key field to define the composite primary key.

We recommend specifying the primary key fields at the beginning of object configuration.

DbType

Defines the field data type. Default data type is String.

1
2
3
4
5
{
    "Name": "FirstName",
    "APIPath": "first_name",
    "DbType": "String"
}

The list of possible data types is the following:

SubType

Specifies the data type for complex JSON object or JSON array fields (having DbType set to JsonArray or JsonObject).

For JsonObject fields, it can be a name or complex type. For JsonArray fields, it can be the name of a complex type or the name of a simple data type, and it specifies the type of array elements.

If the SubType is not specified, the JSON field content is passed as is without further processing and mapping to a specific sub type fields structure.

Length

Number of characters for text type fields. Default values are: 38 for Guid, int.Maxvalue for JSON Object or JSON Array, 255 for other text types.

Precision

Max number of significant digits for a numeric field. For example,

1
2
3
4
5
6
7
{
    "Name": "Amount",
    "APIPath": "amount",
    "DbType": "Decimal",
    "Precision": 15,
    "Scale": 5
}

Scale

Number of characters after comma for numeric fields.

1
2
3
4
5
6
7
{
    "Name": "Amount",
    "APIPath": "amount",
    "DbType": "Decimal",
    "Precision": 15,
    "Scale": 5
}

TimeStampStoreMode

Use this setting to redefine the TimeStampStoreMode from the ProviderConfiguration section for the specific field.

For example,

1
2
3
4
5
6
{
    "Name": "AvailableOn",
    "APIPath": "available_on",
    "DbType": "DateTime",
    "TimeStampStoreMode": "SecondsSinceEpoch"
},

DateTimeFormat

This setting defines how to process the DateTime data. If you omit it, it inherits the DateTimeFormat value from the ProviderConfiguration section.

DateFormat

This setting defines how to process the Date data. If you omit it, it inherits the DateFormat value from the ProviderConfiguration section.

TimeFormat

This setting defines how to process the Time data. If you omit it, it inherits the TimeFormat value from the ProviderConfiguration section.

Nullable

This setting defines if the field can accept null values. Accepts true or false values. Default value is true.

Required

This setting defines whether the field is required or optional to fill in. Accepts true or false values. Default value false.

DMLKey

This setting defines if you can use this field in the WHERE condition of the UPDATE or DELETE operations to facilitate a record search and processing. Accepts true or false values.

Createable

Boolean setting, which defines if the field supports the INSERT operation. Accepts true or false values. Default value is True.

You must declare the InsertOperation section to use the INSERT operation.

Updatable

Boolean setting, which defines if this field supports the UPDATE operation. Accepts true or false values. Default value is True.

You must declare the UpdateOperation section to use the UPDATE operation.

ReadOnly

This boolean setting defines if the field value can be modified. Accepts true or false values. Default value is False.

DefaultValue

This setting defines the default value for the field.

1
2
3
4
5
6
{
    "Name": "Amount",
    "APIPath": "amount",
    "DbType": "Decimal",
    "DefaultValue": 0
}

NullAs

This setting defines a value the source uses as an empty value. This setting impacts the web requests for CRUD operations.

For example, some source uses "name": "" instead of "name": null. In this case, you specify "NullAs": "".

ExcludeNullValuesFromUpdate

Use this setting to omit fields with null values during the UPDATE operation. The connector will omit fields, for which null values are passed, in the request.

ExtendedRequest

Source API may return only part of the fields for some objects when querying multiple records. To query the values of lacking fields, Skyvia can perform additional extended requests for each record of such an object. However, this can decrease performance and significantly increase the number of API calls.

This setting defines whether it is necessary to perform such additional web requests by a record ID to get this field value for each record.

Skyvia performs additional requests only if at least one field with ExtendedRequest = true is present in the web request. If no fields with ExtendedRequest = true are selected in the request, then no additional requests exist.

Use this setting only if the RetrieveSingleOperation setting is determined for the object.

ExtendedRequest is boolean and accepts the true and false value. Default value is false.

For example, Pipedrive has the Filters object. When querying all records, the value in the Conditions field returns an empty result.

If you set the ExtendedRequest to true for the Conditions field, Skyvia will perform an additional web request to each record. It will obtain all Conditions field values by record IDs.

Consider the source API limits when using this setting. We recommend using it to get data from really necessary or required fields.

Enum

This setting defines if the field has a set of specific valid values. Enum and accepts True or False values.

Use this setting always together with EnumValues setting.

MultiEnum

This setting defines if the field is an array of specific valid values. MultiEnum accepts True or False values.

Use this setting always together with EnumValues setting.

EnumValues

This setting defines the set of specific valid values for the field if Enum or MultiEnum is true.
It is represented as key-value pairs: [{Name1:value1}, {Name2:value2}]. For example, the Status field valid values are: canceled, chargeable, consumed, failed.

The Columns setting for the Status field will look like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
{
    "Name": "Status",
    "APIPath": "status",
    "DbType": "String",
    "Enum": true,
    "EnumValues": [{
            "Name": "Canceled",
            "Value": "canceled"
        },
        {
            "Name": "Chargeable",
            "Chargeable": "chargeable"
        },
        {
            "Name": "Consumed",
            "Value": "consumed"
        },
        {
            "Name": "Failed",
            "Value": "failed"
        }
    ]
}

Here, Name is a name of the list item, displayed to the Skyvia user. Value is the internal data source API value of the corresponding list item.

StrictEnumValues

Determines whether you can assign only values from the EnumValues list to this field, and custom values are not allowed. The default value is False.

ExcludeFromFieldsParameter

Use this setting to exclude a field from the list, passed in the FieldsParameterName parameter.

For example, it is necessary if the data source always returns this field, and it does not allow passing the field name in the field list.

Expression

This setting defines a field value using an expression. You can use it both to define a completely virtual calculated field and to redefine the value of an existing field. A virtual field defined by expression becomes Createable = false and Updateable = false by default.

However, you can define a real field with an Expression setting and can explicitly set Createable = true or Updateable = true and specify APIName, InsertAPIName or UpdateAPIName for such field.

When specifying an expression, use the API names of the response object properties. You must specify API names without parentheses or quotes. If the expression uses the name of a nested JSON property, separate the names with the dot (for example, shipping.country).

You can find the Expression syntax and the list of supported functions and operators in our Expression Syntax documentation.

For example, Mailjet API returns null for the UpdatedDate field if this field has never been updated since its creation. Thus, we declared the Expression setting for the never-updated records to accept the CreatedDate field if the UpdatedDate is null:

1
2
3
4
5
6
{
    "Name": "UpdatedDate",
    "APIPath": "ModifiedAt",
    "DbType": "DateTime",
    "Expression": "datetime(is_null_or_empty(string(ModifiedAt)) ? CreatedAt: ModifiedAt)"
}

FilterOperations

This section contain filter operation configurations, which determine the filtering operations natively supported by the data source API. For example,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
    "Name": "Amount",
    "APIPath": "amount",
    "DbType": "Int32",
    "FilterOperations": [{
            "Operation": "Equals", 
            "ParameterName": "Amount",
            "ParameterType": "UrlPath"
        },
        {
            "Operation": "LessThan",
            "ParameterName": "Amount",
            "ParameterType": "UrlPath"
        }
    ]
}

Every FilterOperation setting has the following nested settings:

Operation

Defines the type of filter operation supported by source API. Valid values are Equals, LessThan, LessThanOrEquals, GreaterThan, GreaterThanOrEquals.

ParameterType

Defines how the filter value is passed in the web request. Valid values:

ParameterName

Name of the URL parameter (when ParameterType = UrlParameter) or web request property (when ParameterType = BodyParameter) where the filter value is passed.

Required

Defines whether the filter is required or not. Can accept the true or false value. Default value is false.

If the filter is required, you can’t execute the web request to this object without a filter by this field.

DbType

Redefine the filter argument data type. If omitted, the argument data type is inherited from the field DbType.

SubType

Redefines the filter argument subtype. If omitted, argument subtype is inherited from the field SubType.

TimeStampStoreMode

Redefines the filter argument TimeStampStoreMode. If omitted, argument TimeStampStoreMode is inherited from the field TimeStampStoreMode.

DateTimeFormat

Redefines the filter argument DateTimeFormat. If you omit it, the argument DateTimeFormat is inherited from the field DateTimeFormat.

DateFormat

Redefines the filter argument DateFormat. If you omit it, the argument DateFormat is inherited from the field DateFormat.

TimeFormat

Redefines the filter argument TimeFormat. If you omit it, the argument TimeFormat is inherited from the field TimeFormat.

NullAs

Redefines the filter argument NullAs. If you omit it, the argument NullAs is inherited from the field NullAs.

ParentRelations

This section contains parent relationship configurations that define the parent relationships of the current object with another object.

Parent relationship configurations have the following settings:

Field

The foreign key field that refers to the parent object.

RelatedTable

The name of the parent object.

RelatedField

Key field in the parent object by which the foreign key relation is built.

AggregateParent

Some data sources allow getting records from some child objects only by specifying the corresponding parent primary key (ID) values. This setting defines whether querying data from this object is performed via the parent primary key, corresponding to this relation.

Accepts true or false values. Default value is false.

When you define child object foreign key fields, that act as references to parent objects, their APIPath or JPath values must relate to the parent object web response.

Skyvia does not require the ID of the parent object from user in such cases. If you don’t specify the IDs of the parent objects (for example, in a filter), Skyvia queries all the parent object records first, takes their IDs and then queries child object records for each parent object record. This allows querying child objects without knowing their parents, but this method takes more time and consumes more API calls. It uses at least one API call for every parent object record.

Example 1 Invoice and InvoiceLineItems Relationship

There is a parent Invoices object and a child InvoiceLineItems object. The InvoiceLineItems object references the Invoices object by the InvoiceId field.

Source API allows retrieving records from the InvoiceLineItems object only via IDs from the parent Invoices object.

To retrieve each set of records from the InvoiceLineItems, we specify the InvoiceId in the request URL.

We set the AggregateParent = true in the ParentRelations. It will automatically process such a situation and get the records from the InvoiceLineItems.

Example of the parent Invoice object:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
{
    "Name": "Invoices",
    "Url": "/invoices",
    "ResultPath": "data",
    "Columns": [{
            "Name": "Id",
            "APIPath": "id",
            "DbType": "String",
            "Primary": true,
            "Createable": false,
            "Updateable": false
        },
        {
            "Name": "CustomerId",
            "APIPath": "customer",
            "DbType": "String",
            "Required": true,
            "Updateable": false
        },
        {
            "Name": "Total",
            "APIPath": "total",
            "DbType": "Int32",
            "Createable": false,
            "Updateable": false
        }
    ]
}

Example of the child InvoiceLineItems object:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
{
    "Name": "InvoiceLineItems",
    "Url": "/invoices/<InvoiceId>/lines",
    "ResultPath": "data",
    "Columns": [{
            "Name": "Id",
            "APIPath": "id",
            "DbType": "String",
            "Primary": true,
            "Createable": false,
            "Updateable": false
        },
        {
            "Name": "InvoiceId",
            "APIPath": "id",
            "DbType": "String",
            "Length": 50,
            "FilterOperations": [{
                "Operation": "Equals",
                "ParameterName": "InvoiceId",
                "ParameterType": "UrlPath"
            }]
        },
        {
            "Name": "Amount",
            "APIPath": "amount",
            "DbType": "Int32"
        }],
    "ParentRelations": [{
        "Field": "InvoiceId",
        "RelatedTable": "Invoices",
        "RelatedField": "Id",
        "AggregateParent": true
    }]
}

In this example, the child object foreign key InvoiceId field APIPath is set to "APIPath": "id", because such APIPath relates to the parent object web response.

Example 2 ParentRelations for the Objects with Nested Relationships.

This is how the ParentRelations settings are defined in our SurveyMonkey connector. There are three objects Surveys, SurveyPages, and SurveyPageQuestions.

APIPath of the SurveyId field from the SurveyPages and SurveyPageQuestions objects is set to id, because it is set related to the parent Surveys object web response.

The PageId field from the SurveyPageQuestions object APIPath is set to id (related to the parent Pages object web response).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
{
    "Name": "Surveys",
    "Url": "/surveys",
    "ResultPath": "data",
    "Columns": [{
            "Name": "Id",
            "APIPath": "id",
            "DbType": "String",
            "Length": 190,
            "Primary": true,
            "Createable": false,
            "Updateable": false
        },
        {
            "Name": "Title",
            "APIPath": "title",
            "DbType": "String",
            "Required": true,
            "Length": 1000
        },
        {
            "Name": "Nickname",
            "APIPath": "nickname",
            "DbType": "String"
        },
        {
            "Name": "ResponseCount",
            "APIPath": "response_count",
            "DbType": "Int32",
            "Createable": false,
            "Updateable": false
        },
    ],
}


{
    "Name": "SurveyPages",
    "Url": "/surveys/<survey_id>/pages",
    "ResultPath": "data",
    "Columns": [{
            "Name": "Id",
            "APIPath": "id",
            "DbType": "String",
            "Length": 190,
            "Primary": true,
            "Createable": false,
            "Updateable": false
        },
        {
            "Name": "SurveyId",
            "APIPath": "id",
            "DbType": "String",
            "Length": 190,
            "Required": true,
            "Updateable": false,
            "FilterOperations": [{
                "Operation": "Equals",
                "ParameterName": "survey_id",
                "ParameterType": "UrlPath"
            }]
        },
        {
            "Name": "Title",
            "APIPath": "title",
            "DbType": "String",
            "Length": 1000
        },
        {
            "Name": "Description",
            "APIPath": "description",
            "DbType": "String",
            "Length": 4000
        },
        {
            "Name": "QuestionCount",
            "APIPath": "question_count",
            "DbType": "Int32",
            "Createable": false,
            "Updateable": false
        },

    ],
    "ParentRelations": [{
        "Field": "SurveyId",
        "RelatedTable": "Surveys",
        "RelatedField": "Id",
        "AggregateParent": true
    }]
}


{
    "Name": "SurveyPageQuestions",
    "Url": "/surveys/<survey_id>/pages/<page_id>/questions",
    "ResultPath": "data",
    "Columns": [{
            "Name": "Id",
            "APIPath": "id",
            "DbType": "String",
            "Length": 190,
            "Primary": true,
            "Createable": false,
            "Updateable": false
        },
        {
            "Name": "PageId",
            "APIPath": "id",
            "DbType": "String",
            "Length": 190,
            "Required": true,
            "DMLKey": true,
            "FilterOperations": [{
                "Operation": "Equals",
                "ParameterName": "page_id",
                "ParameterType": "UrlPath"
            }]
        },
        {
            "Name": "SurveyId",
            "APIPath": "id",
            "DbType": "String",
            "Length": 190,
            "Required": true,
            "DMLKey": true,
            "FilterOperations": [{
                "Operation": "Equals",
                "ParameterName": "survey_id",
                "ParameterType": "UrlPath"
            }]
        },
        {
            "Name": "Heading",
            "APIPath": "heading",
            "DbType": "String",
            "Length": 2000,
            "Createable": false,
            "Updateable": false
        },
        {
            "Name": "Position",
            "APIPath": "position",
            "DbType": "Int32"
        },
        {
            "Name": "Subtype",
            "APIPath": "subtype",
            "DbType": "String",
            "Required": true,
            "ExtendedRequest": true,
            "Updateable": false
        },

    ],
    "ParentRelations": [{
            "Field": "PageId",
            "RelatedTable": "SurveyPages",
            "RelatedField": "Id",
            "AggregateParent": true
        },
        {
            "Field": "SurveyId",
            "RelatedTable": "Surveys",
            "RelatedField": "Id"
        }
    ]
}

RetrieveSingleOperation

This section defines the operation of fetching a record by its primary key.

We highly recommend specifying this setting if the source API allows getting the object records by their IDs.

For example, the connector performs the request SELECT * FROM Object WHERE Id = <id_value> via 1 API call, with the RetrieveSingleOperation defined. If you omit RetrieveSingleOperation, the connector will read all the object records into the cache and then perform the request to this cache instead of calling the object directly. It takes more time and more API calls.

1
2
3
4
5
6
7
8
"RetrieveSingleOperation": {
    "UrlSuffix": "/<id>",
    "Arguments": [{
        "ColumnName": "Id",
        "ParameterName": "id",
        "ParameterType": "UrlPath"
    }]
},

Url

Absolute or relative URL to retrieve a single object. Use this parameter, if the base URL for retrieving a single object is different from the object URL.

UrlSuffix

Suffix for the URL parameter. It is added at the end of the URL. For example, the Customers object URL is /customers, and the URL suffix in RetrieveSingleOperation is /customer_id. In this case, web request with WHERE-condition by primary key field will have the URL */customers/*.

ResultPath

The path to the field that contains the result record JSON object in the response. ResultPath should be specified in the RetrieveSingleOperation if it differs from the ResultPath, specified in the object configuration. For example, the whole object ResultPath is “issues”, and the ResultPath for the request by ID is “issue”:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
{
    "Name": "Issues",
    "Url": "/issues.json",
    "ResultPath": "issues",
    "Columns": [{
            "Name": "Id",
            "JPath": "$.id",
            "DbType": "Int32",
            "Primary": true
        }],
    "RetrieveSingleOperation": {
        "Url": "/issues/<issue_id>.json",
        "ResultPath": "issue",
        "Arguments": [{
            "ColumnName": "Id",
            "ParameterName": "issue_id",
            "ParameterType": "UrlPath"
        }]
    }

Method

Web request method. If skipped, the object’s Method field value is used.

Arguments

This section contains argument configurations which define the arguments of RetrieveSingleOperation.

Argument configurations contain the following settings:

ColumnName

Name of the key field for RetrieveSingleOperation.

ParameterName

Name of the web request parameter.

ParameterType

Defines the type of the argument. Valid values are the following:

You must specify all the fields needed for the record search in the Arguments setting. If the object URL refers to a parent object, you must also specify the field by which the object is related in the Arguments setting.

For example,

The TransferReversals object relates to the Transfers object (its URL is /transfers/TransferId/reversals) by aggregate relation. You can get the TransferReversals records only specifying the TransferId value.

We specify the TransferId setting in Arguments when declaring the RetrieveSingleOperation for such object.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
{
    "Name": "TransferReversals",
    "Url": "/transfers/<TransferId>/reversals",
    "ResultPath": "data",
    "Columns": [{
            "Name": "Id",
            "APIPath": "id",
            "DbType": "String",
            "Primary": true,
            "Createable": false,
            "Updateable": false
        },
        {
            "Name": "Amount",
            "APIPath": "amount",
            "DbType": "Int32",
            "Updateable": false
        },
        {
            "Name": "TransferId",
            "APIPath": "id",
            "DbType": "String",
            "Required": true,
            "Updateable": false,
            "FilterOperations": [{
                "Operation": "Equals",
                "ParameterName": "TransferId",
                "ParameterType": "UrlPath"
            }]
        }
    ],
    "ParentRelations": [{
        "Field": "TransferId",
        "RelatedTable": "Transfers",
        "RelatedField": "Id",
        "AggregateParent": true
    }],
    "RetrieveSingleOperation": {
        "UrlSuffix": "/<reversal_id>",
        "Arguments": [{
                "ColumnName": "Id",
                "ParameterName": "reversal_id",
                "ParameterType": "UrlPath"
            },
            {
                "ColumnName": "TransferId",
                "ParameterName": "TransferId",
                "ParameterType": "UrlPath"
            }
        ]
    }
}

CRUD Operations

Settings for INSERT, UPDATE, and DELETE operations for the connector’s objects are defined in the InsertOperation, UpdateOperation and DeleteOperation sections. If any of these sections is not specified in an object configuration, the corresponding DML operation is not available for the object.

Every CRUD operation section contains the following settings:

UrlSuffix

A suffix added in the end of operation URL.

For example, we perform the POST request to the Lists object. Web request URL should look like /lists.create. We declare the following UrlSuffix: "UrlSuffix":".create".

Url

Specify the URL for CRUD operation if it significantly differs from the GET request URL.

Method

Web request method. Default values are POST for InsertOperation, PUT for UpdateOperation, and DELETE for DeleteOperation.

BodyPattern

Request body format. More details are available in the BodyPattern section of ProviderConfiguration topic.

Headers

Additional HTTP request headers that are sent for this operation. Specify Headers as a JSON array of objects with the Key and Value properties. They are merged with Headers specified in the ProviderConfiguration section as well as with headers in the corresponding setting of an object.

ConstantParameters

Allows to set constant parameters for the specific operation.

These parameters are merged with ConstantParameters specified in the ProviderConfiguration section as well as with ConstantParameters specified on the object level.

For example,

1
2
3
4
5
6
7
8
"ConstantParameters": [
    {
        "ParameterName": "resource_type",
        "ParameterType": "BodyParameter",
        "DbType": "String",
        "Value": "lead"
    }
]

InputType

Defines how the request parameters are passed in the web request. Valid values are:

Arguments

This section contains argument configurations which define the arguments of the corresponding DML operation.

Argument configurations contain the following settings:

ParameterName

Argument name.

ParameterType

The type of the argument. Valid values are the following:

ColumnName

Name of the key field for the current operation.

ReturningStrategy

Section that determines how to process the returned data fields after the INSERT or UPDATE operation was performed. See the ReturningStrategy in the ProviderConfiguration section.

Example: Declaring all CRUD Operations for the Customers Object

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
{
    "Name": "Customers",
    "Url": "/customers",
    "ResultPath": "data",
    "Columns": [{
            "Name": "Id",
            "APIPath": "id",
            "Primary": true,
            "Createable": false,
            "Updateable": false
        },
        {
            "Name": "Email",
            "APIPath": "email"
        }],
    "InsertOperation": {
        "UrlSuffix": "",
        "Method": "POST",
        "InputType": "RestParameters",
        "ReturningStrategy": {
            "Type": "Row"
        }
    },
    "UpdateOperation": {
        "UrlSuffix": "/<customer_id>",
        "Arguments": [{
            "ColumnName": "Id",
            "ParameterName": "customer_id",
            "ParameterType": "UrlPath"
        }],
        "Method": "PUT",
        "InputType": "RestParameters",
        "ReturningStrategy": {
            "Type": "Row"
        }
    },
    "DeleteOperation": {
        "UrlSuffix": "/<customer_id>",
        "Arguments": [{
            "ColumnName": "Id",
            "ParameterName": "customer_id",
            "ParameterType": "UrlPath"
        }],
        "Method": "DELETE"
    }
}