Create/Update/Upsert methods

These three methods are similar by the way they perform, format of the data passed in and returned back.

Create method always tries to create a record and will fail if value of the key column is passed in and the record with such key already exists. Column with no value supplied will be assigned with default value.

Update method, in contrast, requires either internal record’s id or a key column to be present and the record to exist in the database. If both are supplied, record’s id takes precedence.

Upsert combines the functionality of former two, updating the record if it exists or creating new one.

Please note that workflow rules are enabled by default and each record is processed individually. For each record passed in methods return short status description to indicate whether processing was successful or failed.

All three methods return an array of short status descriptors, one for each row. Descriptor consist of status code (200 - updated, 201 - created, 304 - not modified or 4xx code in case of error), row’s id and key and an array of standard error descriptors if any.

Parameters

table

the singular name of the table or its alias as returned by the Describe (Database) method. Please note that this parameter is embedded into the URL before the method name.

workflow

pass 0 to suppress running workflow rules. The user should have ManageData administrative permissions otherwise method call will fail.

match

applicable to Update and Upsert methods only and allows to override matching logic. If omitted, methods try to find update the record using key column. If present, it’s value should be the name or alias of unique column. Methods then will try to find the record based on the value of unique column. This might be useful for integration with external systems that use different record keying scheme, by e-mail, for example.

Input

All three methods are invoked via POST request. While method extension defines output format, Content-Type HTTP header is required and defines the type of the content you are passing in - application/json for JSON payload or text/xml for XML payload, making it possible to send XML and receive JSON or vice versa.

JSON and XLM data formats match those returned by data retrieval methods but requirements to the format somewhat relaxed. JSON input should be an array of objects. Each object consists of key-value pairs; key name should match column name or its alias. XML document should consist of a root element with any name; second level elements denote rows and also can have any names; third level elements denote columns within a row and should be named after column names or their aliases. Since rows processed individually, they do not have to have uniform structure - you can pass one set of data for one row and another set of data for another row.

Format of the column value in both JSON and XML depends on a column type as described in a following table (assuming column name is "c").

Request

POST https://www.teamdesk.net/secure/api/v2/{appid}/{table}/upsert.{json|xml}
Content-Type: {application/json|text/xml}

Pass batch of 3 records for table Test to Upsert method in JSON format

POST https:/​/www.teamdesk.net/secure/api/v2/21995/Test/upsert.json
Content-Type: application/json

[
    {   // will update by key
        "Id": "68",
        "Text": "Update #1",
        "Date": "2014-12-18",
        "Multiline": null
    },
    {   // will update by id
        "@row.id": 12,
        "Id": "69",
        "Text": "Update #2",
    },
    {   // will create
        "Date": "2014-12-18",
        "Time": "12:34:50"
    }
]

The method will respond with something like:

[
    {
        "status": 200, // updated
        "id": 20,
        "key": "68"
    },
    {
        "status": 400, // error
        "id": 12,
        "key": "69",
        "errors": [
            {
                "error": 409,
                "message": "Cannot write duplicate value \"69\" into column \"Id\""
            }
        ]
    },
    {
        "status": 201, // created
        "id": 48,
        "key": "91"
    }
]

Let’s do the same in XML format

POST https://www.teamdesk.net/secure/api/v2/21995/Test/upsert.xml
Content-Type: text/xml

<Request>
    <row>
        <Id>68</Id>
        <Text>Update #1</Text>
        <Date>2014-12-18</Date>
        <Multiline/>
    </row>
    <row id="12">
       <Id>69</Id>
       <Text>Update #2</Text>
    </row>
</Request>

The response will be a sort of

<Response>
    <row>
        <id>20</id>
        <key>68</key>
        <status>304</status>
    </row>
    <row>
        <id>12</id>
        <key>69</key>
        <status>400</status>
        <errors>
        <Error>
            <error>409</error>
            <message>Cannot write duplicate value "69" into column "Id"</message>
        </Error>
        </errors>
        <id>12</id>
        <key>69</key>
        <status>400</status>
    </row>
</Response>

Sending attachment data

In SOAP API we had separate SetAttachment method but now workflow triggers are run by default and setting the data first and then modifying attachment may result in running modification triggers multiple times. In order to avoid that, modification methods support sending files side by side with records data.

In this case the data format resembles plain text e-mail messages with attachments - the body of the request should be formatted according to multipart/related rules, for example:

POST https://www.teamdesk.net/secure/api/v2/21995/Test/upsert.json
Content-Type: multipart/related; boundary=example-1

--example-1
Content-ID: <file-1>
Content-Type: text/plain;
Content-Disposition: attachment; filename="sample.txt"

This is sample text file

--example-1
Content-Type: application/json

[{ "Id": "69", "File": "cid:file-1" }]

--example-1--

Request’s Content-Type header should be set to multipart/related and its boundary parameter should result to some unique string. Two minus signs followed by boundary string indicates beginning of the part. The request should end with two minus signs followed by boundary string and two more minus signs. Part’s headers are separated from the data by an empty line.

The part considered a file if Content-Disposition header is present and contains filename parameter. Content-ID header is used to uniquely identify file’s data. The part without filename parameter assumed to contain record’s data. Now to refer to a file simply set the value of attachment column to cid:file-content-id.

The order of the parts plays no role. Data part may precede, follow or placed in between file parts.