## Overview You can create or update multiple records using the API. If there is a record that matches the specified key column, that record will be updated. If there is no matching record, a new record will be created (*1). If there is no key column, all records will be created. If the process is completed successfully, the "「Notification」" will be sent after the import. (*1) If the KeyNotFoundCreate parameter is false, new records will not be created. ## Notes 1. If an error occurs during the process, subsequent processing will be canceled. ## Limitations 1. The target "「Site」" must have "Create" and "Update" permissions. 1. If there are records that exceed the value set in the "BulkUpsertMax" parameter in the "[General.json](/en/manual/general.json)" parameter, processing will not be performed. ## Preparation Please [Create an API key](/manual/api-key) before operating the API. ## Request Send json data in the following request format. |Setting column|Value| |:--|:--| |HTTP method|POST| |Content-Type |application/json| |Character code|UTF-8| |URL|http://{server name}/api/items/{site ID}/bulkupsert(*2)| |Body|Refer to the json data below| (*2)Edit the {server name} and {site ID} parts as appropriate to your environment. 　　For pleasanter.net, the format is as follows: 　　https\://pleasanter.net/fs/api/items/{site ID}/bulkupsert ##### JSON ``` { "ApiVersion": "1.1", "ApiKey": "345yuAjA6789dA09d8uj6...", "Keys": [ "ClassA" ], "KeyNotFoundCreate": true, "Data": [ { "Title": "Develop new function XX1", "ClassHash": { "ClassA": "RC0001" } }, { "Title": "Develop new function XX2", "ClassHash": { "ClassA": "RC0002" } } ] } ``` |Column|Settings| |:--|:--| |Keys|Specify the key column. Multiple columns can be specified. (When omitted: Create all new records)| |KeyNotFoundCreate|Create new records if there are no records matching the key. (When omitted: true)| |Data|Array of records| #### About the specified key column Create and update (upsert) records using API based on the specified key column. Specify the key column using the following parameter. If no key column is specified, all records will be created. For details, see (a) In the case of no key, (b) In the case of single key, and (c) In the case of composite key below. #### Insert Image by API You can insert an image into the "「Body」", "「Comment」" and "「Description」" column by specifying an ImageHash in the Body. When updating a record using this function with an update API (update/upsert), the corresponding column of the existing record will be overwritten in the "「Body」" and "「Description」" columns, and added in the "「Comment」" column. In addition, if you specify only ImageHash without specifying Body or DescriptionHash, which specify the string to be registered in the description field in an update API, it will be added rather than overwritten. ##### How to Specify ImageHash <style type="text/css"> .tg {border-collapse:collapse;border-spacing:0;} .tg td{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px; overflow:hidden;padding:10px 5px;word-break:normal;} .tg th{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px; font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;} .tg .tg-0lax{text-align:left;vertical-align:top} </style> <table class="tg"> <thead> <tr> <th class="tg-0lax">1st Level</th> <th class="tg-0lax">2nd Level</th> <th class="tg-0lax">3rd Level</th> <th class="tg-0lax">Description</th> <th class="tg-0lax">Example</th> </tr> </thead> <tbody> <tr> <td class="tg-0lax" rowspan="9">ImageHash</td> <td class="tg-0lax" rowspan="6">Body</td> <td class="tg-0lax">HeadNewLine</td> <td class="tg-0lax">Specify whether to insert a newline at the beginning of the image with true/false. If omitted, there will be no newline.</td> <td class="tg-0lax">true</td> </tr> <tr> <td class="tg-0lax">EndNewLine</td> <td class="tg-0lax">Specifies whether to insert a newline at the end of the image with true/false. If omitted, there will be no newline.</td> <td class="tg-0lax">true</td> </tr> <tr> <td class="tg-0lax">Position</td> <td class="tg-0lax">Specifies the position of the image to insert when setting a string in the target item in the same request. If -1 is specified or omitted, it will be inserted at the end.</td> <td class="tg-0lax">3</td> </tr> <tr> <td class="tg-0lax">Alt</td> <td class="tg-0lax">Specifies the string to insert into the alt attribute (text displayed instead of the image when the image cannot be displayed in the web browser). If omitted, "image" will be set.</td> <td class="tg-0lax">hayato</td> </tr> <tr> <td class="tg-0lax">Extension</td> <td class="tg-0lax">Specifies the file extension to register in the Binaries table. If omitted, ".png" will be set.</td> <td class="tg-0lax">.jpeg</td> </tr> <tr> <td class="tg-0lax">Base64</td> <td class="tg-0lax">Specify the Base64 encoded binary data of the image as a string. If you specify ImageHash, this cannot be omitted.</td> <td class="tg-0lax">iVBORw0KG…（the following omitted）</td> </tr> <tr> <td class="tg-0lax">Comments</td> <td class="tg-0lax">（same as above）</td> <td class="tg-0lax">（same as above）</td> <td class="tg-0lax">-</td> </tr> <tr> <td class="tg-0lax">DescriptionA</td> <td class="tg-0lax">（same as above）</td> <td class="tg-0lax">（same as above）</td> <td class="tg-0lax">-</td> </tr> <tr> <td class="tg-0lax">DescriptionB</td> <td class="tg-0lax">（same as above）</td> <td class="tg-0lax">（same as above）</td> <td class="tg-0lax">-</td> </tr> </tbody> </table> #### Executing Processes via API You can execute a process by specifying the Process ID in the request data. ##### Preconfiguration PLease set up the "「Process」" in advance. ##### Limitations When executing a process via the API, input validation set for the process ##### Process Specifying Method Either ProccessId or ProccessIds should be set. If both are set, ProccessIds is applied. Note that when ProccessIds is set, the specified multiple process IDs will be executed in the order in which they appear in the list of "「Process」" set in advance. |Setting Item|Description|Example| |:--|:--|:--| |ProccessId|Specify the ID of the process.|1| |ProccessIds|Specify IDs for multiple processes.|[1,2,3]| #### (a) In the case of no key If there is no Keys parameter, all records will be created. The value of the KeyNotFoundCreate parameter is ignored. ##### JSON ``` { "ApiVersion": 1.1, "ApiKey": "145Afa9AF2A10SafaA21641...", "Data": [ { "Title": "Develop new function XX1", "Body": "Body1", "CompletionTime": "2018/3/31", "ProcessId": 1, "ClassHash": { "ClassA": "RC0001", "ClassB": "Class２" }, "NumHash": { "NumA": 100 }, "DateHash": { "DateA": "2019/01/01" }, "DescriptionHash": { "DescriptionA": "Description1" }, "CheckHash": { "CheckA": false }, "ImageHash": { "Body": { "HeadNewLine": true, "EndNewLine": true, "Position": 3, "Alt": "imageBody", "Extension": ".jpeg", "Base64": "iVBORw0KG..." }, "DescriptionA": { "HeadNewLine": true, "EndNewLine": true, "Position": 3, "Alt": "imageDescriptionA", "Extension": ".jpeg", "Base64": "iVBORw0KG..." } } }, { "Title": "Develop new function XX2", "Body": "Body2", "CompletionTime": "2018/3/31", "ProcessId": 1, "ClassHash": { "ClassA": "RC0002", "ClassB": "Class３" }, "NumHash": { "NumA": 100 }, "DateHash": { "DateA": "2019/01/01" }, "DescriptionHash": { "DescriptionA": "Description2" }, "CheckHash": { "CheckA": false } } ] } ``` #### (b) In the case of single key Set the name of the key column in the Keys parameter in array format. Search for records that match the value specified in the parameter for the column specified in Keys. In the example below, search for a record with "RC0001" in the "ClassA" column of the first record. Search for a record with "RC0002" in the "ClassA" column of the second record. The following process is performed depending on the result of the record search. 1. If the target record does not exist: A new record is created. However, if the KeyNotFoundCreate parameter is false, a new record is not created. 1. If one target record exists: That record is updated. 1. If multiple target records exist: No record is created or updated, and an error response is returned. ##### JSON ``` { "ApiVersion": 1.1, "ApiKey": "145Afa9AF2A10SafaA21641...", "Keys": [ "ClassA" ], "KeyNotFoundCreate": true, "Data": [ { "Title": "Develop new function XX1", "Body": "Body1", "CompletionTime": "2018/3/31", "ProcessId": 1, "ClassHash": { "ClassA": "RC0001", "ClassB": "Class２" }, "NumHash": { "NumA": 100 }, "DateHash": { "DateA": "2019/01/01" }, "DescriptionHash": { "DescriptionA": "Description1" }, "CheckHash": { "CheckA": false }, "ImageHash": { "Body": { "HeadNewLine": true, "EndNewLine": true, "Position": 3, "Alt": "imageBody", "Extension": ".jpeg", "Base64": "iVBORw0KG..." }, "DescriptionA": { "HeadNewLine": true, "EndNewLine": true, "Position": 3, "Alt": "imageDescriptionA", "Extension": ".jpeg", "Base64": "iVBORw0KG..." } } }, { "Title": "Develop new function XX 2", "Body": "Body2", "CompletionTime": "2018/3/31", "ProcessId": 1, "ClassHash": { "ClassA": "RC0002", "ClassB": "Class３" }, "NumHash": { "NumA": 100 }, "DateHash": { "DateA": "2019/01/01" }, "DescriptionHash": { "DescriptionA": "Description2" }, "CheckHash": { "CheckA": false } } ] } ``` #### (c) In the case of composite key If multiple column names are set in the Keys parameter, records that match the values ​​specified in the parameters will be searched for for all columns specified in Keys. In the example below, the first record will be searched for a record whose "ClassA" column has a value of "RC0001" and whose "ClassB" column has a value of "001". The second record will be searched for a record whose "ClassA" column has a value of "RC0001" and whose "ClassB" column has a value of "002". 1. If the target record does not exist: A new record will be created. However, if the KeyNotFoundCreate parameter is false, a new record will not be created. 1. If one target record exists: That record will be updated. 1. If multiple target records exist: No record will be created or updated, and an error response will be returned. ##### JSON ``` { "ApiVersion": 1.1, "ApiKey": "145Afa9AF2A10SafaA21641...", "Keys": [ "ClassA", "ClassB" ], "KeyNotFoundCreate": true, "Data": [ { "Title": "Develop new function XX 1", "Body": "Body1", "CompletionTime": "2018/3/31", "ProcessId": 1, "ClassHash": { "ClassA": "RC0001", "ClassB": "001" }, "NumHash": { "NumA": 100 }, "DateHash": { "DateA": "2019/01/01" }, "DescriptionHash": { "DescriptionA": "Description1" }, "CheckHash": { "CheckA": false }, "ImageHash": { "Body": { "HeadNewLine": true, "EndNewLine": true, "Position": 3, "Alt": "imageBody", "Extension": ".jpeg", "Base64": "iVBORw0KG..." }, "DescriptionA": { "HeadNewLine": true, "EndNewLine": true, "Position": 3, "Alt": "imageDescriptionA", "Extension": ".jpeg", "Base64": "iVBORw0KG..." } } }, { "Title": "Develop new function XX 2", "Body": "Body2", "CompletionTime": "2018/3/31", "ProcessId": 1, "ClassHash": { "ClassA": "RC0001", "ClassB": "002" }, "NumHash": { "NumA": 100 }, "DateHash": { "DateA": "2019/01/01" }, "DescriptionHash": { "DescriptionA": "Description2" }, "CheckHash": { "CheckA": false } } ] } ``` ## Response The json data in the following format will be returned. ##### JSON If a record is newly created ``` { "Id": 12345, "StatusCode": 200, "LimitPerDate": 10000, "LimitRemaining": 9994, "Message": "Record table: 1 added, 0 updated." } ``` If the record is updated ``` { "Id": 12345, "StatusCode": 200, "LimitPerDate": 10000, "LimitRemaining": 9994, "Message": "Record table: 0 added and 1 updated." } ``` If an error occurs on a site-by-site basis ``` { "Id": 12345, "StatusCode": 401, "Message": "Authentication failed." } ``` If an error occurs on a record-by-record basis ``` { "Id": 12345, "StatusCode": 500, "Message": "Record table: An error occurred. 0 added and 0 updated.\nIndex:1(ClassA=RC00011)\nError content: Multiple records match the conditions." } ``` If there are records that exceed the value set in "BulkUpsertMax" of the parameter "[General.json](/en/manual/general.json)". ``` { "Id": 12345, "StatusCode": 500, "Message": "You cannot import more than 1000 rows of data at once." } ``` ## Compatible versions |Compatible versions|Body| |:--|:--| |1.4.6.0 and later|Added function| ## Things to check when an error occurs [・Notes on using the API and things to check if an error occurs](/manual/faq-api) [・FAQ: Things to check if the changed configuration file or API request (JSON format) is not recognized correctly](/manual/faq-json-format)