Table Operation: Update Record

2019-05-022026-07-14

Overview¶

You can update records using the API. In addition to changing the contents of a record, you can also add or delete "Attachment" and change the "Record Access Control".

Limitations¶

You must have "Update" permission for the target "Record".
You must have "Manage Permissions" permission to change "Record Access Control".
The record access control update function requires Pleasanter 1.1.36.0 or later, Pleasanter .NET Framework version 0.50.260 or later.

Preparations¶

Please Create an API key before performing API operations.

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/{record ID}/update(*1)
Body	Please refer to the json data below

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

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¶

1st Level	2nd Level	3rd Level	Description	Example
ImageHash	Body	HeadNewLine	Specify whether to insert a newline at the beginning of the image with true/false. If omitted, there will be no newline.	true
		EndNewLine	Specifies whether to insert a newline at the end of the image with true/false. If omitted, there will be no newline.	true
		Position	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.	3
		Alt	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.	hayato
		Extension	Specifies the file extension to register in the Binaries table. If omitted, ".png" will be set.	.jpeg
		Base64	Specify the Base64 encoded binary data of the image as a string. If you specify ImageHash, this cannot be omitted.	iVBORw0KG…（the following omitted）
	Comments	（same as above）	（same as above）	-
	DescriptionA	（same as above）	（same as above）	-
	DescriptionB	（same as above）	（same as above）	-

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) Regular Updates¶

When setting column such as Class or Num, please write "{column name}Hash" as shown below.

JSON¶

{
    "ApiVersion": 1.1,
    "ApiKey": "ad7816s5sD2safFafaD...",
    "Title": "Develop new function XX 2",
    "Body": "Body 2",
    "CompletionTime": "2018/3/31",
    "ProcessId": 1,
    "ClassHash": {
        "ClassA": "Classification 2",
        "ClassB": "Unclassified 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
    },
    "AttachmentsHash": {
        "AttachmentsA": [
            {
                "ContentType": "text/plain",
                "Name": "Readme.txt",
                "Base64": "4O5O4jjfui..."
            }
        ]
    },
    "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..."
        }
    }
}

(To set a date column to NULL, refer to FAQ: Set a date column to NULL using the API.)

(b) Delete Attachments¶

For GUID in AttachmentsA, specify the GUID of the file.
When deleting an attachment, be sure to specify the GUID in uppercase.
Also, specify 1 for Deleted in AttachmentsA.

JSON¶

{
    "ApiVersion": 1.1,
    "ApiKey": "2bcb8a909da1d8827c99c...",
    "AttachmentsHash": {
        "AttachmentsA": [
            {
                "Guid": "7AB84732...",
                "Deleted": 1
            }
        ]
    }
}

Attachment Guid Retrieval Method¶

Follow the link below to get a record with an attachment.
API Function: Retrieve Single Record
The Guid of the attachment is listed in the "Attachments" column of the obtained json file, so use that value.

© Set Record Access Control¶

Specify RecordPermissions to change record access control. You must specify an ApiKey with "Permission Management" permission. In the example below, write permission is granted to dept ID:1, management permission is granted to group ID:1, and read permission is granted to user ID:10. When you specify record access control, the existing settings are deleted and replaced with the specified ones. You cannot add or delete part of them.

JSON¶

{
    "ApiVersion": 1.1,
    "ApiKey": "2bcb8a909da1d8827c99c...",
    "RecordPermissions": [
        "Dept,1,31",
        "Group,1,511",
        "User,10,1"
    ]
}

Permission Type¶

The permission types are as follows. Calculate and set the total value of the permissions you want to grant. For example, to grant read, create, and update, use 1+2+4=7. To grant all permissions, use 511.

No	Permission	Value
1	Read	1
2	Create	2
3	Update	4
4	Delete	8
5	Email	16
6	Export	32
7	Import	64
8	Site Management	128
9	Permission Management	256

Response¶

The json data in the following format will be returned.

JSON¶

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

Code Samples¶

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

1. Update by directly specifying the record ID

Updates a record by directly specifying the record ID.

Python(api_record_update_p1.py)¶

# Library for communicating with websites and APIs
import requests

# Library for JSON operations
import json

# Set the target URL and API key
BASE_URL = "【Target URL】"
API_KEY = "【API key】"

# ID of the record to update
RECORD_ID = 【Record ID】

# Build the request
payload = {
    "ApiKey": API_KEY,
    "Title": "Title changed",
    "ClassHash": {
        "ClassA": "Defect",
    },
}

# Call the API
url = f"{BASE_URL}/api/items/{RECORD_ID}/update"
response = requests.post(
    url,
    headers={"Content-Type": "application/json"},
    data=json.dumps(payload),
)

# Check the result
if response.ok:
    print("Update succeeded")
else:
    print("Update failed")

print(response.status_code, response.text)

Run¶

>python api_record_update_p1.py

Execution Result¶

Update succeeded
200 {"Id":9999,"StatusCode":200,"Message":"\" Sample Project C \" has been updated."}

2. Update multiple records

Gets the records that meet a condition and updates those records.

Python(api_record_update_p2.py)¶

# Library for communicating with websites and APIs
import requests

# Library for JSON operations
import json

# Set the target URL and API key
BASE_URL = "【Target URL】"
API_KEY = "【API key】"

# ID of the site to update
SITE_ID = 【Site ID】

# 1. Get the records with Status=100
# Retrieval parameters: specify the Status=100 filter
get_payload = {
    "ApiKey": API_KEY,
    "View": {"ColumnFilterHash": {"Status": '["100"]'}},
}
# Call the API
get_url = f"{BASE_URL}/api/items/{SITE_ID}/get"
response = requests.post(
    get_url,
    headers={"Content-Type": "application/json"},
    data=json.dumps(get_payload),
)

# Check the result
if not response.ok:
    print("API call failed")
    print(response.status_code, response.text)
    exit()

# List of retrieved records (array)
results = response.json()
if len(results["Response"]["Data"]) <= 0:
    print("0 target records")
    exit()

# 2. Update the retrieved records in order
for item in results["Response"]["Data"]:
    # Get the ID of the record to update
    record_id = item.get("ResultId")
    # Build the update request
    update_payload = {
        "ApiKey": API_KEY,
        "Id": record_id,
        "Status": "910",  # On hold
    }
    # Call the API
    update_url = f"{BASE_URL}/api/items/{record_id}/update"
    update_response = requests.post(
        update_url,
        headers={"Content-Type": "application/json"},
        data=json.dumps(update_payload),
    )

    # Check the result
    if update_response.ok:
        print(f"Update succeeded: Id={record_id}")
    else:
        print(f"Update failed: Id={record_id}")

    print(update_response.status_code, update_response.text)

Run¶

>python api_record_update_p2.py

Execution Result¶

Update succeeded: Id=9999
200 {"Id":9999,"StatusCode":200,"Message":"\" Sample Project B \" has been updated."}
Update succeeded: Id=9999
200 {"Id":9999,"StatusCode":200,"Message":"\" Sample Project C \" has been updated."}

3. Delete attachments

Deletes all attachments attached to a specific record.

Python(api_record_update_p3.py)¶

# Library for communicating with websites and APIs
import requests

# Library for JSON operations
import json

# Set the target URL and API key
BASE_URL = "【Target URL】"
API_KEY = "【API key】"

# ID of the record to update
RECORD_ID = 【Record ID】

# 1. Get the record
get_payload = {"ApiKey": API_KEY}
# Call the API
get_url = f"{BASE_URL}/api/items/{RECORD_ID}/get"
response = requests.post(
    get_url, headers={"Content-Type": "application/json"}, data=json.dumps(get_payload)
)

# Check the result
if not response.ok:
    print(f"Failed to get the record: Id={RECORD_ID}")
    print(response.status_code, response.text)
    exit()
# List of retrieved records (array)
results = response.json()

# 2. Get AttachmentsA
attachments = (
    results["Response"]["Data"][0]["AttachmentsHash"].get("AttachmentsA") or []
)

# Convert the JSON string to an object
attachments = json.loads(json.dumps(attachments))

# End the process if there are no attachments
if len(attachments) == 0:
    print(f"No update needed because there are no attachments: Id={RECORD_ID}")
    exit()

# 3. Set Deleted=1 for all attachment Guids and update
# Build the update request
update_payload = {
    "ApiKey": API_KEY,
    "Id": RECORD_ID,
    "AttachmentsHash": {
        "AttachmentsA": [{"Guid": att["Guid"], "Deleted": 1} for att in attachments]
    },
}

# Call the API
update_url = f"{BASE_URL}/api/items/{RECORD_ID}/update"
update_response = requests.post(
    update_url,
    headers={"Content-Type": "application/json"},
    data=json.dumps(update_payload),
)

# Check the result
if update_response.ok:
    print(f"Update succeeded: Id={RECORD_ID}, deleted={len(attachments)}")
else:
    print(f"Update failed: Id={RECORD_ID}")

print(update_response.status_code, update_response.text)

Run¶

>python api_record_update_p3.py

Execution Result¶

Update succeeded: Id=9999, deleted=3
200 {"Id":9999,"StatusCode":200,"Message":"\" Sample Project C \" has been updated."}

4. Set record access control

Sets record access control on a specific record.

Python(api_record_update_p4.py)¶

# Library for communicating with websites and APIs
import requests

# Library for JSON operations
import json

# Set the target URL and API key
BASE_URL = "【Target URL】"
API_KEY = "【API key】"

# ID of the record to update
RECORD_ID = 【Record ID】

# Build the request
payload = {
    "ApiKey": API_KEY,
    "RecordPermissions": ["Group,1,511"],
}

# Call the API
url = f"{BASE_URL}/api/items/{RECORD_ID}/update"
response = requests.post(
    url,
    headers={"Content-Type": "application/json"},
    data=json.dumps(payload),
)

# Check the result
if response.ok:
    print("Update succeeded")
else:
    print("Update failed")

print(response.status_code, response.text)

Run¶

>python api_record_update_p4.py

Execution Result¶

Update succeeded
200 {"Id":9999,"StatusCode":200,"Message":"\" Sample Project C \" has been updated."}

5. Execute a process

Executes a process.

Python(api_record_update_p5.py)¶

# Library for communicating with websites and APIs
import requests

# Library for JSON operations
import json

# Set the target URL and API key
BASE_URL = "【Target URL】"
API_KEY = "【API key】"

# ID of the record to update
RECORD_ID = 【Record ID】

# Build the request
payload = {
    "ApiKey": API_KEY,
    "ProcessId": 1,
}

# Call the API
url = f"{BASE_URL}/api/items/{RECORD_ID}/update"
response = requests.post(
    url,
    headers={"Content-Type": "application/json"},
    data=json.dumps(payload),
)

# Check the result
if response.ok:
    print("Update succeeded")
else:
    print("Update failed")

print(response.status_code, response.text)

Run¶

>python api_record_update_p5.py

Execution Result¶

Update succeeded
200 {"Id":9999,"StatusCode":200,"Message":"\" Sample Project C \" 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

Supported Versions¶

Supported versions	Body
1.4.19.0 and later	Added the ability to insert images using ImageHash in Wiki

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'.