Cli-kintone Tutorial 4 : Updating and re-sorting by query

Introduction

This article introduces how to filter and re-sort records using queries after gaining a basic understanding of cli-kintone. For those new to cli-kintone, please refer to tutorials tutorial 1, tutorial 2, and tutorial 3 first to learn how to get, add, delete, and update records using cli-kintone.

Below are the basic ways to write queries. You can also see further details about queries on the Get Records page.

Writing Queries

How to Write 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.

▼ Basic expression format

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

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"

The expression above can handle most cases. However, there are exceptions when using select operators.

If you use "in" and "not in" as the operators, use the following expression.

▼ When using "in" and "not in" operators

Using Double Quotation Marks Around Values

All values must be enclosed in double quotation marks (") in queries. Functions don’t need to be enclosed.

▼ Example of using double quotation marks around values

Grouping an Expression

In some cases, a single expression is enough to specify a condition. However, there are cases where you would want to specify more detailed conditions.

In such cases, you can group expressions by enclosing them in parentheses "()". You can also use "and" and "or" to express conditions.

Combining multiple expressions with these operators enables you to specify more detailed conditions.

▼ Example of grouping expressions

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".

Specifying Fields Contained in Related Records

You can use either one of the following expressions when specifying fields contained in the Related Records field.

▼ Basic expression to specify a related record

▼ When using "in" and "not in" operators

The Authenticated User When Using cli-kintone

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

As the basics of writing query expressions have been covered previously, this section shows the types of fields you can use with each operator and function.

▼ 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, you must use "in" and "not in" 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
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
THIS_MONTH()	Created datetime, Updated datetime, Date, Date and time
LAST_MONTH()	Created datetime, Updated datetime, Date, Date and time
THIS_YEAR()	Created datetime, Updated datetime, Date, Date and time

Options for Queries

Specifying the Sort Order

You can sort records 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

▼ Expression to sort in descending order

Specifying the Number of Output Records

This limits the number of records to be output.

▼ Expression to specify the number of records to be output

Skip a Certain Number of Output Records

You can skip any chosen number of records 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>".

▼ Expression to skip a certain number of records to be output

Filtering Records Using Queries

This section shows how to output records using queries in cli-kintone.

The Expense Report app from the market place is used in the following examples.

Tips for Creating Queries

If you need to filter records 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

In this section, we 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"

▼Expression to specify "Sales" or "Marketing" in "department"

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

▼ Specifying to sort by descending order of "Total Expense"

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

▼ Example of a query made by combining expressions

This is how you would state this in cli-kintone:

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>".

Although the previous articles didn’t mention the need of double quotation marks ("), the whole query must be enclosed in double quotation marks (") because the query always contains spaces.

Therefore, double quotation marks (") inside the query must be escaped by using a backslash (\).

If you use shells such as PowerShell or Bash, you can pass command options 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

In this last section, we will filter records using fields in a table.

You must use "in" and "not in" 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.

With that in mind, we will filter the records that match either one of the following conditions.

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"

▼ "Category" is not "Food" or "Other"

▼ "Description" is "Train"

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

 

This concludes Tutorial 4.

We hope this helped you learn how to write queries on Kintone with cli-kintone. You are close to being completely proficient in the functions of cli-kintone.

The next article will explain how to work with attachment files.

 

 

<<< Go back to Cli-kintone Tutorial 3: Update Records