Skip to content

items.Upsert

Overview

If a record corresponding to a key column exists at the specified site, it is updated; if not, a new record is created.

Limitations

  1. The ID of the created or updated record cannot be retrieved from the return value of items.Upsert or from the variables used in items.Upsert.

Syntax

items.Upsert(siteId, json)

Parameters

Parameter Type Required Description
siteId object Yes Specify the site ID
json string Yes Specify the JSON string

About the Specified Key Column

The API creates and updates (upsert) records based on the specified key columns. Key columns are specified by the following parameters.

Property name Data type Description
Keys Array (string) Specify the key columns. Multiple columns can be specified.

Return Value

If the record was updated or a new record was created, true is returned; if not, false is returned.

Usage Example 1

In the example below, the key column is set to "ClassA" and the table with site ID 123 is updated if the corresponding record ("Ubuntu" for ClassA) exists; if not, a new record is created.
The information to update or create a new record is as follows:

  • Title: How to Install Pleasanter
  • Status: Running (200)
  • ClassA: Ubuntu
  • ClassB: PostgreSQL
JavaScript
let data = {
    Keys: ["ClassA"],
    Title: "How to Install Pleasanter",
    Status: 200,
    ClassHash: {
        ClassA: "Ubuntu",
        ClassB: "PostgreSQL"
    }
};
items.Upsert(123, JSON.stringify(data));

Usage Example 2

In the example below, the key column is the title, and if the corresponding record (with title "Sample") for site ID 123 exists, it is updated; if not, a new record is created.
In this case, the value entered in DateA is transcribed to DateC.
Date column is treated as UTC in the server script, so please convert them appropriately.

JavaScript
let date = model.DateA;
let jstDate = date.toLocaleString("ja-JP",{ timeZone: "JST" });
let data = {
    Keys: ["Title"],
    Title: "Sample",
    DateHash: {
        DateC: jstDate
    }
};
items.Upsert(123, JSON.stringify(data));

Code Samples

Modify 【 ... 】 in the code as necessary.
1. Keep data in sync between two tables

When you want the same information in two tables to always correspond one-to-one, sync (transcribe) the content to the other table whenever one of the tables is updated.

For this, prepare a column that stores the "source record ID (foreign key)" in the destination table so that the source record can be identified.

In Upsert, by specifying this foreign key column in Keys,

  • if a record with the same foreign key already exists, it is updated
  • if not, a new record is created

This is determined automatically. As a result, you can implement one-to-one sync between two tables simply, without writing the branching logic of "check whether it exists → update if it does / create if it does not".

JavaScript
// Specify the site name
const siteName = '【Site name】';
// Get the site information
const site = items.GetClosestSite(siteName);
if (!site) {
    logs.LogInfo(`${siteName} Failed to get the site information`);
    return false;
}
// "Foreign key" for one-to-one sync
// * Store the source record (ResultId) in ClassB of the destination table and specify it in Keys of Upsert
const sourceId = model.ResultId;
// Build the request
const data = {
    Keys: ['ClassB'], // Foreign key column (this value determines update or create)
    Title: model.Title,
    Status: model.Status,
    ClassHash: {
        ClassA: model.ClassA,
        ClassB: sourceId, // Foreign key (source ID)
    },
};
// Create or update the record
const result = items.Upsert(site.SiteId, JSON.stringify(data));
if (result) {
    logs.LogInfo(`Upsert succeeded (foreign key=${sourceId})`);
} else {
    logs.LogUserError(`Upsert failed (foreign key=${sourceId})`);
}
Execution Result
(Info):Upsert succeeded (foreign key=6830)
2. Import holiday data periodically

The holiday API returns all holiday data every time, so if you simply register the data, holidays with the same date are created as duplicates.

In this sample, the holiday date is treated as a unique key, and the date column (DateA) is specified in Keys of Upsert.

As a result,

  • if a record with the same date exists, it is updated
  • if not, a new record is created

This is determined automatically, so only the differences are reflected safely no matter how many times you run the holiday API.

JavaScript
// Name of the holiday calendar site
const siteName = '【Site name】';
// Get holiday data from the holiday API
const getNationalHolidays = () => {
    try {
        // HTTP request to the holiday API
        httpClient.RequestUri =
            'https://holidays-jp.github.io/api/v1/date.json';
        const response = httpClient.Get();
        if (httpClient.IsSuccess) {
            return JSON.parse(response);
        } else {
            logs.LogInfo(
                `Holiday API error: (${httpClient.StatusCode}) ${response}`
            );
            return null;
        }
    } catch (e) {
        logs.LogInfo(`Holiday API exception: ${e}`);
        return null;
    }
};
// Create a holiday record
const upsertHolidayRecord = (date, name, siteId) => {
    try {
        const data = {
            Keys: ['DateA'], // Use the holiday date as the unique key for Upsert
            ClassHash: { ClassA: name },
            DateHash: { DateA: date },
        };
        return items.Upsert(siteId, JSON.stringify(data));
    } catch (e) {
        logs.LogInfo(`Holiday record creation exception: ${e}`);
        return false;
    }
};
// Main process
const nationalHolidays = getNationalHolidays();
if (!nationalHolidays) {
    logs.LogInfo('Failed to get the holiday data');
    return false;
}
const site = items.GetClosestSite(siteName);
if (!site) {
    logs.LogInfo(`${siteName} Failed to get the site information`);
    return false;
}
// Create or update records based on the holiday data
for (const date in nationalHolidays) {
    const result = upsertHolidayRecord(
        date,
        nationalHolidays[date],
        site.SiteId
    );
    if (result) {
        logs.LogInfo(
            `Holiday record created/updated: ${date} ${nationalHolidays[date]}`
        );
    } else {
        logs.LogInfo(
            `Failed to create/update the holiday record: ${date} ${nationalHolidays[date]}`
        );
    }
}
Execution Result
(Info):Holiday record created/updated: 2024-01-01 元日
(Info):Holiday record created/updated: 2024-01-08 成人の日
(Info):Holiday record created/updated: 2024-02-11 建国記念の日
(Info):Holiday record created/updated: 2024-02-12 建国記念の日 振替休日
(Info):Holiday record created/updated: 2024-02-23 天皇誕生日
(Info):Holiday record created/updated: 2024-03-20 春分の日
(Info):Holiday record created/updated: 2024-04-29 昭和の日
(Info):Holiday record created/updated: 2024-05-03 憲法記念日
(Info):Holiday record created/updated: 2024-05-04 みどりの日
(Info):Holiday record created/updated: 2024-05-05 こどもの日
(Info):Holiday record created/updated: 2024-05-06 こどもの日 振替休日
・・・・