Delete Insert Records

When you want to update records using the API, you generally use the PUT API.

However, there are situations where you need to delete all records under certain conditions and then recreate them, considering specifications and safety.

This article introduces a code sample for such cases.

Sample Code

Function Definition

First, define a function to delete multiple records by specifying a query.

/**
 * @param { number | string } app Target application ID
 * @param { string } query Query to narrow down the deletion targets
 * @returns { Promise<{}> } Result of executing the DELETE API
 */
const deleteByQuery = async (app, query) => {
  /** API endpoint */
  const url = kintone.api.url('/k/v1/records.json', true);

  /** Records to be deleted (ID only) */
  const records = await kintone.api(url, 'GET', {
    app,
    query,
    fields: ['$id'],
  });

  // Define an array of record IDs
  const ids = records.map((r) => r.$id.value);

  return kintone.api(url, 'DELETE', { app, ids });
};

In the above code, only up to 100 records can be processed in one execution due to the DELETE record limit.

If you want to use it without considering the limit, please refer to the following article.

Example 1: Update by Specifying a Date

/** Sample data */
const CLIENTS = [
  ['Sample Corporation', '2022-01-01', 'A'],
  ['Test Corporation', '2022-01-05', 'B'],
  ['Dummy Corporation', '2022-01-10', 'C'],
];

const deleteInsert = async () => {
  /** Target application ID */
  const app = 15;

  /** List of records to be registered */
  const records = CLIENTS.map((client) => ({
    CustomerName: { value: client[0] },
    Date: { value: client[1] },
    Probability: { value: client[2] },
  }));

  /** Query to narrow down the deletion targets */
  const query = 'Date >= "2021-01-01" and Date <= "2021-01-31"';

  // Bulk delete records that match the query
  await deleteByQuery(app, query);

  // Bulk register the created records
  await kintone.api(kintone.api.url('/k/v1/records.json', true), 'POST', {
    app,
    records,
  });
};

About Code Safety

When deleting and registering records, you need to be careful about cases where the process terminates abnormally after deletion.

If you write code that waits for the deletion to complete before registering, there is a risk that only the deletion process will be completed, and the registration will not be executed, causing data inconsistency.

To avoid this, using bulkRequest is effective.

Although bulkRequest also has a limit, if an error occurs in either the deletion or registration, it can roll back the process and return to the state before execution.

Bulk Processing of Records for Multiple Applications

A sample using bulkRequest is also introduced in the following article.