Update records

Introduction

Export record data and Add and delete records, the first two tutorials in the cli-kintone series explained how to get, add, and delete records using cli-kintone.

This article continues to use the Customer Database from the previous tutorials, and uses cli-kintone to update records. 

Update records by specifying the Record ID

STEP 1: Prepare CSV data of records containing $id columns

This step will update department information to three records in the App used in the previous tutorial.

To update records, prepare a CSV that includes an $id column in the first column ($id is a shorthand way of expressing the record number field).

Editing an exported CSV file would be the easiest way to prepare this file. Refer to the Export record data tutorial for how to output records to a CSV file.

Fields that are not listed in the CSV will not be updated.

 

Below is a CSV with data that updates records with ids 4, 5, and 6:

Save the file as update_customers.csv.

STEP 2: Update the records

The records can be updated by using the same command for adding records.

 

If customers.csv is saved in the same location as the executable file

When specifying the file path of customers.csv

Update records by specifying the key field

STEP 1: Configure settings for the Key Field

With cli-kintone, other fields can be specified to be the key for updating your App's records instead of the record ID. The key field must have the Prohibit duplicate values option turned on.

In the App's settings, open the Field Settings for the Company Name field (field code company_name), and check the Prohibit duplicate values option.

cli-kintonePart3_1.PNG

STEP 2: Create a CSV containing * symbols in the key field name

For this CSV, do not include the $id column.

Cli-kintone will determine which field is the key, by looking for field code names in the CSV that start with the * symbol.

Since a field with the company_name field code is being used as the key,  CSV data will need to be created that has *company_name in the first row of the CSV.

The following is a CSV with updated names and new phone numbers.

Save it as update_customers_by_company.csv.

STEP 3: Update the Records

Use the same command as before to update the records.

Update and add records at the same time

STEP 1: Prepare a CSV File

To update and add records at the same time, specify the record ID in the CSV file.

Rows are written differently in the CSV files for records that will be added and records that will be updated.


For records that will be added, write "" in the $id column.
For records that will be updated, enter the corresponding record numbers in the $id column. For field values that should not be changed, enter the value it originally has. Otherwise, enter a new value.

 

Here is an example CSV for adding and updating record data:

Save it as update_and_post_customers_by_company.csv.

STEP 2: Update and add records

Run the same command used for adding and updating records:

Update records after deleting records

STEP 1: Prepare a CSV File

Just like when records were added in the previous tutorial, records in an App can be deleted, before updating other records.

Use a CSV file that either updates by the record ID or by the key field.

In this example, update_customers.csv that was used earlier in this tutorial will be used again.

STEP 2: Delete records and update other records

Update records after deleting other records by specifying the -D and -f options.

In order to exclude records that will be updated from the scope of the deletion, specify a query using the -q option.

Since update_customers.csv only updates records with IDs 4, 5, and 6, try deleting the two latest records in the App that don't have these record IDs, and then proceed with updating other records.

Records can also be added in this process, as long as $id is used as the key field for the CSV.

Update records with table data by specifying the Record ID

Records that include table data can be updated in the same way by creating CSVs that contain table expressions.

As in the previous tutorials, the Expense Report App will be used.

STEP 1: Create a CSV File containing $id and table data

The notation method for table data in the previous tutorial was to add an "*" symbol in the first column of the first row, and in the first column of all rows containing the first line of table data for each record.

The following CSV file represents the case where records with record IDs 1, 2 and 3 contain table data. The data inside expense_date will be updated for all records, and business_purpose will be updated for one record (record 1):

Save the file as update_subtable.csv.

STEP 2: Update the record

Update the records with the following command:

Update records with table data by specifying the key field

STEP 1: Configure settings for the key field

Records that contain table data can also be updated by specifying a field in the App to become the key field. This field will also need the Prohibit duplicate values option to be turned on.

In this example, turn this setting on for the Expense Code field (field code expense_code).

cli-kintonePart3_2.PNG

STEP 2: Create a CSV containing * symbols in the key field name

Add the * symbol next to the field code that will become the key. In this example, *expense_code will be used as the key in the CSV, and a line will be added to each table of each record.

Save this file as update_subtable_by_title.csv.

STEP 3: Update the Record

Run the following command to update the records:

 

The contents of the CSV file looks like the below when opened in Excel.

cli-kintonePart3_3.PNG

 

These are the screenshots of the three records that have new rows of data in their tables. The data in the colored rectangles above match the data in the colored rectangles below.

cli-kintonePart3_4.PNG

 

There are many ways to update records with cli-kintone. Data gets harder to prepare when stating which fields to update, and when data includes tables. Make sure to edit an exported csv file to reduce broken CSV files.

 

 

<<< Go back to Add and Delete Records

Was this article helpful?
0 out of 0 found this helpful
Do you have any questions or issues related to this article?
Please share your views with us in the Community forums!