Cli-kintone Tutorial 3: Update Records

Introduction

Tutorial 1 and Tutorial 2 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

In this step, we 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 Tutorial 1 for how to output records to a CSV file.

Fields that are no 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

If you specify the file path of customers.csv

Update records by specifying the key field

STEP 1: Configure settings for the Key Field

With cli-kintone, you can specify other fields 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 we are using the field with the "company_name" field code as the key, you will need to create CSV data 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 we did 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, you will need to 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. If you do not wish to change the value of a certain field, 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 we 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 your App can be deleted, before updating other records.

You can use a CSV file that either updates by the record ID or by the key field.

In this example, we will use update_customers.csv that we used earlier in this tutorial.

STEP 2: Delete records and update other records

You can 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, you will need to specify a query using the -q option.

Since update_customers.csv only updates records with IDs 4, 5, and 6, let's delete 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

You can update records that include table data in the same way by creating CSVs that contain table expressions.

We will use the Expense Report app in this example as we did in the previous tutorials.

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, we will 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

 

Wow, what a ride - there are so 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 Cli-kintone Tutorial 2: Add and Delete Records

Was this article helpful?
0 out of 0 found this helpful
Comments
Please sign in to leave a comment.