Skip to content

I want to get the name displayed via API

Answer

Specify the ApiDataType property of "View" as "KeyValues".

Overview

When retrieving information about a child table using the API, the value of the linked column in the response is usually the record ID (the target record in the parent table), but by specifying the ApiDataType property of "View" as "KeyValues", the response will be returned with the name displayed (the title of the target record in the parent table).

Sample Data

This FAQ is written based on the parent-child table and record information below.

Customer Master (Parent Table)
Record ID Customer Name Contact Person Address Contact Information
696 Work Style Reform Promotion Organization Brown Emily Tachikawa City, Tokyo 258-258-258
697 Open Source Technology Co., Ltd. Yoichi Suzuki Toshima Ward, Tokyo 555-555-555
698 Productivity Improvement Committee Keiko Tanaka Ota Ward, Tokyo 999-999-999
699 Web Database Development Co., Ltd. Hiroshi Watanabe Nerima Ward, Tokyo 789-789-789
700 Information Sharing Innovation Research Institute Yoko Yamamoto Shinjuku Ward, Tokyo 333-333-333
701 Non Programming Co., Ltd. Nakamura Takashi Kokubunji, Tokyo 456-456-456
702 Management Comfort Holdings Makoto Kato Nakano, Tokyo 123-123-123
703 Pleasanter Co., Ltd. Miki Saito Chuo, Tokyo 111-111-111
Negotiations (child table)



The Customer (Class A) option list is linked to the customer master.

Record ID Subject Expected order date Status Manager Contact Customer Product category Probability Sales Total purchase Gross profit Gross profit margin
704 Testing AI technology 45219 Inquiry Haruka Sasaki Haruka Sasaki Management Comfort Holdings Consulting 0.9 1.5 million 1.1 million 4 million 26.7
705 Migrating groupware to a cloud environment 45226 Proposal Brown Emily Brown Emily Pleasanter Co., Ltd. System development 0.9 4200000 0 4200000 100
706 Development of environmental monitoring system in food factories 45212 Negotiations Yoichi Suzuki Yoichi Suzuki Open Source Technology Co., Ltd. System development 0.5 4500000 3207000 1293000 28.7
707 Sales of graphing tools 45212 Inquiries Keiko Tanaka Keiko Tanaka Non Programming Co., Ltd. Software sales 0.5 320000 280000 40000 12.5
708 Business improvement consulting 45221 Proposal Hiroshi Watanabe Hiroshi Watanabe Hiroshi Work style reform promotion organization Consulting 0.3 1,350,000 638,000 712,000 52.7
709 Development of additional functions for sales support tools 45228 Negotiations Yoko Yamamoto Yoko Yamamoto Information Sharing Innovation Research Institute System development 0.9 540,000 440,000 100,000 18.5
710 Sales of document management software 45233 Contracts Takashi Nakamura Yoshiko Kobayashi Web Database Development Co., Ltd. Software sales 0.5 688,000 549,000 139,000 20.2

①Normal Response

Sample code (Python)



In this sample, we will get record information in the sales negotiation (child table) where the product category (Class B) is set to System development.

import requests
import json

# Specify site ID
site_id = 620

# Specify API key
api_key = "de06bdbced9d395f1cf3f7afb8b084f95718e2cfb7f0759efd13210e386b4b954dbf70cbfe6f56e32492c098816a45e37616f1ec2da0ea71d2f10527795225a2"

# Specify API URL
url = f"http://localhost/api/items/{site_id}/get"

# Specify data to send
data = {
    "ApiVersion": 1.1,
    "ApiKey": api_key,
    "View": {
        "ColumnFilterHash": {
            "ClassB": "[\"System Development\"]"
        }
    }
}

# Send POST request
response = requests.post(url, json=data)

# Process response
if response.status_code == 200:
    response_data = json.loads(response.content)
    print(json.dumps(response_data, indent=4, ensure_ascii=False))
else:
    print(f"ErrorCode: {response.status_code}")
    print(response.text)
Response

The response's customer (Class A) value is returned as the ID of each record in the customer master (parent table).

{
    "StatusCode": 200,
    "Response": {
        "Offset": 0,
        "PageSize": 200,
        "TotalCount": 3,
        "Data": [
            {
                "SiteId": 620,
                "UpdatedTime": "2023-09-27T10:41:00",
                "IssueId": 709,
                "Ver": 1,
                "Title": "Development of additional functions for sales support tools",
                "Body": "We will add an overtime management function to the personnel system that we have been developing from scratch for the past five years.\r\nWe will place an order with R Company, which has been in charge of this system since its inception, and develop it.",
                "StartTime": "2023-10-05T00:00:00",
                "CompletionTime": "2023-10-29T00:00:00",
                "WorkValue": 0.0,
                "ProgressRate": 0.0,
                "RemainingWorkValue": 0.0,
                "Status": 200,
                "Manager": 13,
                "Owner": 13,
                "Locked": false,
                "Comments": "[]",
                "Creator": 13,
                "Updator": 13,
                "CreatedTime": "2023-09-27T10:41:00",
                "ItemTitle": "Development of additional functions for sales support tools",
                "ApiVersion": 1.1,
                "ClassHash": {
                    "ClassA": "700",
                    "ClassB": "System development",
                    "ClassC": "90%"
                },
                "NumHash": {
                    "NumA": 540000.0,
                    "NumB": 440000.0,
                    "NumC": 100000.0,
                    "NumD": 18.5
                },
                "DateHash": {},
                "DescriptionHash": {},
                "CheckHash": {},
                "AttachmentsHash": {
                    "AttachmentsA": []
                }
            },
            {
                "SiteId": 620,
                "UpdatedTime": "2023-09-26T16:56:00",
                "IssueId": 705,
                "Ver": 1,
                "Title": "Migrating groupware to a cloud environment",
                "Body": "Due to the aging of the hardware, we will be migrating the groupware to a cloud environment.\r\nWe will be using technical consulting from Company I to convert the data to the new version. \r\n\r\n",
                "StartTime": "2023-09-29T00:00:00",
                "CompletionTime": "2023-10-27T00:00:00",
                "WorkValue": 0.0,
                "ProgressRate": 0.0,
                "RemainingWorkValue": 0.0,
                "Status": 150,
                "Manager": 9,
                "Owner": 9,
                "Locked": false,
                "Comments": "[]",
                "Creator": 9,
                "Updator": 9,
                "CreatedTime": "2023-09-26T16:56:00",
                "ItemTitle": "Migration of groupware to cloud environment",
                "ApiVersion": 1.1,
                "ClassHash": {
                    "ClassA": "703",
                    "ClassB": "System development",
                    "ClassC": "90%"
                },
                "NumHash": {
                    "NumA": 4200000.0,
                    "NumB": 0.0,
                    "NumC": 4200000.0,
                    "NumD": 100.0
                },
                "DateHash": {},
                "DescriptionHash": {},
                "CheckHash": {},
                "AttachmentsHash": {
                    "AttachmentsA": []
                }
            },
            {
                "SiteId": 620,
                "UpdatedTime": "2023-09-26T09:22:00",
                "IssueId": 706,
                "Ver": 1,
                "Title": "Development of environmental monitoring system in food factories",
                "Body": "We received a request to develop a system that places sensors to measure temperature and humidity in six food factories in the Kanto region and monitors changes in condition.\r\nWe propose to implement the sensors with Arduino and connect them to a data center via on-site WiFi.\r\nWe propose to customize the D company package for the monitoring and analysis system. \r\n",
                "StartTime": "2023-10-02T00:00:00",
                "CompletionTime": "2023-10-13T00:00:00",
                "WorkValue": 0.0,
                "ProgressRate": 0.0,
                "RemainingWorkValue": 0.0,
                "Status": 200,
                "Manager": 10,
                "Owner": 10,
                "Locked": false,
                "Comments": "[]",
                "Creator": 10,
                "Updator": 10,
                "CreatedTime": "2023-09-26T09:22:00",
                "ItemTitle": "Development of environmental monitoring system in food factory",
                "ApiVersion": 1.1,
                "ClassHash": {
                    "ClassA": "697",
                    "ClassB": "System Development",
                    "ClassC": "50%"
                },
                "NumHash": {
                    "NumA": 4500000.0,
                    "NumB": 3207000.0,
                    "NumC": 1293000.0,
                    "NumD": 28.7
                },
                "DateHash": {},
                "DescriptionHash": {},
                "CheckHash": {},
                "AttachmentsHash": {
                    "AttachmentsA": []
                }
            }
        ]
    }
}

②When The Response Format Is Changed to The Name Displayed

You can change the response format to return the name displayed by specifying the ApiDataType property in the view of the data to be sent. For details on the ApiDataType property, please refer to the following manual.
Developer's Guide: JSON Data Layout: View

Sample code (Python)



Added the ApiDataType property and the related ApiColumnKeyDisplayType and ApiColumnValueDisplayType properties to the View, so that the Key of each response item is returned as the column name (ColumnName) and the Value is returned as the name displayed (DisplayValue).

import requests
import json

# Specify site ID
site_id = 620

# Specify API key
api_key = "xxxxx..."

# Specify API URL
url = f"http://localhost/api/items/{site_id}/get"

# Specify data to send
data = {
    "ApiVersion": 1.1,
    "ApiKey": api_key,
    "View": {
        "ApiDataType": "KeyValues",
        "ColumnFilterHash": {
            "ClassB": "[\"System Development\"]"
        },
        "ApiColumnKeyDisplayType": "ColumnName",
        "ApiColumnValueDisplayType": "DisplayValue"
    }
}

# Send POST request
response = requests.post(url, json=data)

# Process response
if response.status_code == 200:
    response_data = json.loads(response.content)
    print(json.dumps(response_data, indent=4, ensure_ascii=False))
else:
    print(f"ErrorCode: {response.status_code}")
    print(response.text)
Response



Unlike the response above, customers (ClassA) are returned by name displayed, not record ID.

{
    "StatusCode": 200,
    "Response": {
        "Offset": 0,
        "PageSize": 200,
        "TotalCount": 3,
        "Data": [
            {
                "IssueId": 709,
                "ClassA": "Information Sharing Innovation Research Institute",
                "TitleBody": "Development of additional functions for sales support tools\nWe will add an overtime management function to the personnel system that has been developed from scratch for the past five years.\r\nWe will place an order with R Company, which has been in charge of this system since its inception, and carry out the development.",
                "Comments": null,
                "ClassB": "System development",
                "CompletionTime": "2023-10-29T00:00:00",
                "Status": "Negotiation",
                "ClassC": "90%",
                "NumA": 540000.0,
                "NumB": 440000.0,
                "NumC": 100000.0,
                "NumD": 18.5,
                "Manager": "Yoko Yamamoto",
                "Owner": "Yoko Yamamoto",
                "Updator": "Yoko Yamamoto",
                "UpdatedTime": "2023-09-27T10:41:00",
                "Title": "Development of additional functions for sales support tool"
            },
            {
                "IssueId": 705,
                "ClassA": "Pleasanter Co., Ltd.",
                "TitleBody": "Migration of groupware to cloud environment\nDue to the aging of the hardware, we will migrate the groupware to a cloud environment.\r\nSince it is necessary to convert the data to the new version, we will use the technical consultant of I company. \r\n\r\n",
                "Comments": null,
                "ClassB": "System Development",
                "CompletionTime": "2023-10-27T00:00:00",
                "Status": "Proposal",
                "ClassC": "90%",
                "NumA": 4200000.0,
                "NumB": 0.0,
                "NumC": 4200000.0,
                "NumD": 100.0,
                "Manager": "Brown Emily",
                "Owner": "Brown Emily",
                "Updator": "Brown Emily",
                "UpdatedTime": "2023-09-26T16:56:00",
                "Title": "Migration of groupware to cloud environment"
            },
            {
                "IssueId": 706,
                "ClassA": "Open Source Technology Co., Ltd.",
                "TitleBody": "Development of environmental monitoring system in food factory\nWe received a request to develop a system that places sensors to measure temperature and humidity in six food factories in the Kanto region and monitors changes in condition.\r\nWe propose a method to implement the sensors with Arduino and connect them to a data center via WiFi on the premises.\r\nWe propose a method to customize and introduce a D company package for the monitoring and analysis system.\r\n",
                "Comments": null,
                "ClassB": "System development",
                "CompletionTime": "2023-10-13T00:00:00",
                "Status": "Negotiation",
                "ClassC": "50%",
                "NumA": 4500000.0,
                "NumB": 3207000.0,
                "NumC": 1293000.0,
                "NumD": 28.7,
                "Manager": "Yoichi Suzuki",
                "Owner": "Yoichi Suzuki",
                "Updator": "Yoichi Suzuki",
                "UpdatedTime": "2023-09-26T09:22:00",
                "Title": "Development of environmental monitoring system in food factories"
            }
        ]
    }
}