Table Operation: Create/Update Record

Overview¶

You can create or update records using the API.
If a record with a matching key is found, the record will be updated. If no matching record is found, a new record will be created.

Limitations¶

1. You must have "Create" and "Update" permissions for the target "Record".

Preparations¶

Please Create an API Key before performing API operations.

Request¶

Send json data in the following request format:

(*1) Please edit the {server name} and {site ID} parts to suit your environment as appropriate.
For Pleasanter.net, the format is as follows:
https://pleasanter.net/fs/api/items/{site ID}/upsert

Key Column to be Specified¶

Create and update (upsert) records using the API based on the specified key column. Specify the key column using the following parameters.

For details, please refer to (a) Single key and (b) 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¶

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.

(a) Single Key Case¶

Set the name of the key column in the Keys parameter in array format. For the column specified in Keys, search for records that match the value specified in the parameter.

In the example below, we search for records whose "ClassA" column value is "RC0001".
The following process will be executed depending on the result of the record search.

1. If the target record does not exist: A new record will be created.
2. If one target record exists: That record will be updated.
3. 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"
    ],
    "Title": "Develop new function XX 2",
    "Body": "Body 2",
    "CompletionTime": "2018/3/31",
    "ProcessId": 1,
    "ClassHash": {
        "ClassA": "RC0001",
        "ClassB": "Category 2",
        "ClassC": "Other 2"
    },
    "NumHash": {
        "NumA": 100,
        "NumB": 200
    },
    "DateHash": {
        "DateA": "2019/01/01",
        "DateB": "2020/01/01"
    },
    "DescriptionHash": {
        "DescriptionA": "Description 2",
        "DescriptionB": "Overview 2",
        "DescriptionC": "Supplement 2"
    },
    "CheckHash": {
        "CheckA": false,
        "CheckB": true
    },
    "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..."
        }
    }
}

(b) Composite Key Case¶

If multiple column names are specified in the Keys parameter, the search will look for records that match the values ​​specified in the parameter for all column specified in Keys.
In the example below, we search for records where the "ClassA" column value is "RC0002" and the "ClassB" column value is "01".

JSON¶

{
    "ApiVersion": 1.1,
    "ApiKey": "ad7816s5sD2safFafaD...",
    "Keys": [
        "ClassA",
        "ClassB"
    ],
    "Title": "Develop new function XX 2",
    "Body": "Body 2",
    "CompletionTime": "2018/3/31",
    "ClassHash": {
        "ClassA": "RC0002",
        "ClassB": "01",
        "ClassC": "Other 2"
    },
    "NumHash": {
        "NumA": 100,
        "NumB": 200
    },
    "DateHash": {
        "DateA": "2019/01/01",
        "DateB": "2020/01/01"
    },
    "DescriptionHash": {
        "DescriptionA": "Explanation 2",
        "DescriptionB": "Summary 2",
        "DescriptionC": "Note 2"
    },
    "CheckHash": {
        "CheckA": false,
        "CheckB": true
    },
    "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..."
        }
    }
}

Response¶

The json data in the following format will be returned.

When a new record is created¶

{
    "Id": 12345,
    "StatusCode": 200,
    "LimitPerDate": 10000,
    "LimitRemaining": 9994,
    "Message": "\"Develop new feature XX2\" created."
}

When a record is updated¶

{
    "Id": 12345,
    "StatusCode": 200,
    "LimitPerDate": 10000,
    "LimitRemaining": 9994,
    "Message": "\"Developing new feature XX 2\" updated."
}

When unable to create or update¶

{
    "Id": 12345,
    "StatusCode": 401,
    "Message": "Authentication failed."
}

Code Samples¶

Modify 【 ... 】 in the code as necessary.¶

# Library for communicating with websites and APIs
import requests

# Library for converting images to Base64
import base64

# Library for JSON operations
import json

# Library for file operations and regular expressions
import mimetypes

# Other standard libraries
import re

# Library for data structure operations
from collections import defaultdict

# Library for file path operations
from pathlib import Path

# Destination URL
BASE_URL = "【URL】"
# Site ID
SITE_ID = 【Site ID】
# API key
API_KEY = "【API key】"
# Target folder
TARGET_FOLDER = r"【Path】"

# Regular expression for extracting the key code
KEY_REGEX = re.compile(r"^(KC\d+)$")

# Key column for Upsert
UPSERT_KEYS = ["ClassA"]

def extract_keycode(filename: str) -> str | None:
    # KC001_FILENAME.txt -> KC001 / None if it does not follow the rule
    if "_" not in filename:
        return None
    head = filename.split("_", 1)[0].strip()
    if not head:
        return None
    m = KEY_REGEX.match(head)
    return m.group(1) if m else None

def file_to_attachment_obj(path: Path) -> dict:
    # Create an element of Pleasanter AttachmentsA
    ctype, _ = mimetypes.guess_type(path.name)
    if not ctype:
        ctype = "application/octet-stream"
    b64 = base64.b64encode(path.read_bytes()).decode("ascii")
    return {
        "ContentType": ctype,
        "Name": path.name,
        "Base64": b64,
    }

def upsert_attachments(
    session: requests.Session, keycode: str, attachments: list[dict]
) -> dict:
    url = f"{BASE_URL}/api/items/{SITE_ID}/upsert"
    payload = {
        "ApiVersion": 1.1,
        "ApiKey": API_KEY,
        "Keys": UPSERT_KEYS,
        "ClassHash": {
            "ClassA": keycode,
        },
        "AttachmentsHash": {"AttachmentsA": attachments},
    }

    r = session.post(url, data=json.dumps(payload))
    if not r.ok:
        raise RuntimeError(f"Upsert failed key={keycode} HTTP {r.status_code}\n{r.text}")
    return r.json()

def main():
    folder = Path(TARGET_FOLDER)
    if not folder.is_dir():
        raise SystemExit(f"Folder not found: {folder}")

    # Group the attachments by key code (multiple files for the same KC are registered together)
    grouped: dict[str, list[Path]] = defaultdict(list)

    for p in folder.iterdir():
        if not p.is_file():
            continue

        keycode = extract_keycode(p.name)
        if not keycode:
            # Files that do not follow the rule are not processed
            continue

        grouped[keycode].append(p)

    if not grouped:
        print("No files to process (no files match the naming rule)")
        return

    session = requests.Session()
    session.headers.update({"Content-Type": "application/json"})

    for keycode, paths in grouped.items():
        attachments = [file_to_attachment_obj(p) for p in paths]
        try:
            res = upsert_attachments(session, keycode, attachments)
            # Display the Upsert response
            print(f"OK: key={keycode} attachments={len(attachments)} / response={res}")
        except Exception as e:
            print(f"NG: key={keycode} attachments={len(paths)}")
            print(e)

if __name__ == "__main__":
    main()

>python api_record_upsert_p1.py

OK: key=KC001 attachments=2 / response={'Id': 9999, 'StatusCode': 200, 'Message': '" KC001 " has been updated.'}
OK: key=KC002 attachments=1 / response={'Id': 9999, 'StatusCode': 200, 'Message': '" KC002 " has been updated.'}
OK: key=KC003 attachments=1 / response={'Id': 9999, 'StatusCode': 200, 'Message': '" KC003 " has been updated.'}
・・・・

Confirmation Column in Case of Error¶

・Precautions when using the API and things to check if an error occurs
・FAQ: What to check if modified configuration files or API requests (JSON format) are not recognized correctly

Specification Changes¶

*API specifications have been partially changed since October 2019.**
- Classification, Numerical Value, Date, Description, and Check Column have been changed from being directly entered in the JSON to being entered within "～Hash".

*API specifications have been partially changed since November 2018.**
- The URL format has been changed from '/pleasanter/api_items/xxxx' to '/pleasanter/api/items/xxxx'.
- The Content-Type specification has been changed from 'application/x-www-form-urlencoded' to 'application/json'.