Export Queried Record Data with the Command Line Tool (v0)

Contents

Overview

This article introduces how to use queries with the Kintone Command Line Tool v0 (cli-kintone v0) when exporting Kintone App records. Various examples are suggested for filtering and sorting with queries.

caution
Attention

As of October 31st 2023, support for cli-kintone v0 has ended. For the latest features and security support it is highly recommended to upgrade to cli-kintone v1.

For more information, refer to the End of Support Notice for cli-kintone v0.

Refer to the Query string article when looking through the examples.

How to Write Queries

Basic expressions

To filter records that deal with field values or functions, expressions consisting of field code (or system identifier), operator, and value (or function) are used.

1

<field code or identifier> <operator> <value or function>

For example, to retrieve records with a value of 10 or more in the mynumber field, the expression is as follows:

1

mynumber >= 10

The expression is written by combining the field code mynumber, the operator >=, and the value of 10 in the order of field code, operator, and value as a set.

Using "in" and "not in"

When using in and not in as the operators, use the following expression.

1

<field code or identifier> <in or not in> (<one or multiple values or functions separated by commas>)

Using double quotation marks around values

All values must be enclosed in double quotation marks (") in queries. Functions do not need to be enclosed.

1

$id in ("1", "4", "5"), Author in (LOGINUSER(), "login_name")

Grouping an expression

In some cases, a single expression is enough to specify a condition. However, there are cases where more detailed conditions should be specified. In such cases, expressions can be grouped by enclosing them in parentheses "()". The operators and and or can be used to express conditions. Combining multiple expressions with these operators enables more detailed conditions to be specified.

1

(mynumber >= "100" and mytext like "Test") or lastupdateddate >= LAST_MONTH(1)

In this example, records that satisfy either one of the two conditions will be extracted.

  • Records that have a value of 100 or more in the mynumber field and a value containing the string Test in the mytext field.
  • Records whose value of the lastupdateddate field is the first day of the previous month.

Priorities of “and” and “or”

If the expressions are not grouped, the logical and operator is prioritized. In the example above, the condition remains the same with or without the brackets because and is processed with a higher priority than or.

Either one of the following expressions can be used when specifying fields contained in the Related Records field.

1

<field code of related records field>.<field code of a field inside the related records> <operator> <value or function>
When using "in" and "not in" operators
1

<field code of related records field>.<field code of a field inside the related records> <in or not in> (<one or multiple values or functions separated by commas>)
tips
Note

For API token authentication the Administrator is used as the authenticated user, i.e. records created by this method will be created by the Administrator user. For password authentication, the user set for in the log in credentials will be the authenticated user. Therefore, when using the LOGINUSER() or PRIMARY_ORGANIZATION() functions, the function is processed based on the authenticated user.

List of Operators, Functions, and Available Fields

Operators and available fields

Operator Field or system identifier
= Record number, $id, Created datetime, Updated datetime, Text, Link, Number, Date, Time, Date and time, Status
!= Record number, $id, Created datetime, Updated datetime, Text, Link, Number, Date, Time, Date and time, Status
> Record number, $id, Created datetime, Updated datetime, Date, Time, Date and time
< Record number, $id, Created datetime, Updated datetime, Date, Time, Date and time
>= Record number, $id, Created datetime, Updated datetime, Date, Time, Date and time
<= Record number, $id, Created datetime, Updated datetime, Date, Time, Date and time
in Record number, $id, Created by, Updated by, Text, Link, Number, Check Box, Radio Button, Drop-down, Multi-choice, User Selection, Department Selection, Group Selection
not in Record number, $id, Created by, Updated by, Text, Link, Number, Check Box, Radio Button, Drop-down, Multi-choice, User Selection, Department Selection, Group Selection
like Text, Link, Text Area, Rich Text, Attachment
not like Text, Link, Text Area, Rich Text, Attachment

* For fields inside a table, in and not in must be used instead of = and !=.

Function and available fields

Function Field
LOGINUSER() Created by, Updated by, User selection
PRIMARY_ORGANIZATION() Department selection
NOW() Created datetime, Updated datetime, Date and time
TODAY() Created datetime, Updated datetime, Date, Date and time
YESTERDAY() Created datetime, Updated datetime, Date, Date and time
TOMORROW() Created datetime, Updated datetime, Date, Date and time
FROM_TODAY() Created datetime, Updated datetime, Date, Date and time
THIS_WEEK() Created datetime, Updated datetime, Date, Date and time
LAST_WEEK() Created datetime, Updated datetime, Date, Date and time
NEXT_WEEK() Created datetime, Updated datetime, Date, Date and time
THIS_MONTH() Created datetime, Updated datetime, Date, Date and time
LAST_MONTH() Created datetime, Updated datetime, Date, Date and time
NEXT_MONTH() Created datetime, Updated datetime, Date, Date and time
THIS_YEAR() Created datetime, Updated datetime, Date, Date and time
LAST_YEAR() Created datetime, Updated datetime, Date, Date and time
NEXT_YEAR() Created datetime, Updated datetime, Date, Date and time

Options for Queries

Specifying the Sort Order

Records can be sorted based on the value of the field code or the system identifier by specifying them with asc or desc.

Expression to sort in ascending order
1

order by <field code or identifier> asc
Expression to sort in descending order
1

order by <field code or identifier> desc

Specifying the Number of Output Records

This limits the number of records to be output.

1

limit <number of records to export>

Skip a Certain Number of Output Records

Any chosen number of records can be skipped to be output. The records to be skipped are counted in the order specified using "order by <field code or system identifier> <asc or desc>".

1

offset <number of records to skip>

Filtering Records Using Queries

This section shows how to output records using queries in cli-kintone. The Expense Report app from the Kintone Marketplace (External link) is used in the following examples.

Tips for creating queries

If records need to be filtered using queries in cli-kintone, try each expression individually, and then try connecting the conditions using and or or.

Specifying how to filter/sort in regular fields

This section will extract records whose

  • Status is Approved

and

  • Department is Sales or Marketing

or

  • Total Expense is $10,000 or more

and sort them in the specified descending order of Total Expense.

  • Expression to specify Approved in Status

    1

    Status = "Approved"

  • Expression to specify Sales or Marketing in department

    1

    department in ("Sales", "Marketing")

  • Expression to specify $10,000 or more in Total Expense

    1

    Total_Expenses >= 10000

  • Specifying to sort by descending order of Total Expense

    1

    order by Total_Expenses desc

The following query can be made by combining the expressions above.

  • Example of a query made by combining expressions:

    1

    Status = "Approved" and department in ("Sales", "Marketing") or Total_Expenses >= \"10000\" order by Total_Expenses desc"

    In cli-kintone this would be stated as:

    1

    > cli-kintone.exe --export -a APPID -d FQDN -t APITOKEN -q "Status = \"Approved\" and department in (\"Sales\", \"Marketing\") or Total_Expenses >= \"10000\" order by Total_Expenses desc"

To reduce the number of records to be output, add limit <number> to the query. To specify the number of records to skip, add offset <number>.

The query must be enclosed in double quotation marks (") because the query always contains spaces. Therefore, additional double quotation marks (") inside the query must be escaped by using a backslash (\). When using shells such as PowerShell or Bash, command options can be passed as strings enclosed in single quotation marks ('). In this case, there is no need to escape double quotation marks (") with a backslash (\).

Filtering fields in tables

This last section will filter records using fields in a table. The operators in and not in must be used instead of = and != when you specify fields in a table in the query. All records that have any table row matching the query will be included in the filtered list.

Filtering conditions
  • Amount is $500 or more
  • Category is not Food or Other
  • Description is Train (Train exists in a table)

The following expressions are made based on these conditions.

  • Amount is $500 or more

    1

    amount >= 500

  • Category is not Food or Other

    1

    category not in ("Food", "Other")

  • Description is Train

    1

    description in ("Train")

To combine the expressions and run it on cli-kintone, use the following.

1

> cli-kintone.exe --export -a APPID -d FQDN -t APITOKEN -q "amount >= 500 or description in (\"Train\") or category not in (\"Food\", \"Other\")"