Specify Record List via Query String
You can use queries to get specific records that have been filtered by certain criteria.
How to write a query
The query mainly describes the expression according to the following format.
[fieldID] [Operations] [Value or Functions]
- Sample 1
-
Gets records where the value of the text field (field_1_) is "sample" and the value of the date field (field_2_) is earlier than today.
field_1_ = "sample" and field_2_ < TODAY()
- Sample 2
-
Gets records where the value of the dropdown field (field_3_) contains "option2" or "option3" and the value of the date field (field_2_) is this month's date.
field_3_ in ("option2", "option3") and field_2_ > FROM_THISMONTH("-1", "day") and field_2_ < FROM_THISMONTH("1", "month") - Sample 3
-
Gets records where the value of the text field (field_1_) contains "sample" and the value of the user field (field_4_) contains the logged-in user.
field_1_ like "sample" and field_4_ in (LOGIN_USER)
- Sample 4
-
Gets records where the value in the datetime field (field_5_) is today's date and the value in the checkbox field (field_6_) does not contain "option2" or the value in the radio field (field_7_) does not contain "option3".
field_5_ = TODAY() and (field_6_ not in ("option2") or field_3_ not in ("option3"))
Query Operators
| Operator | Sample | Description |
|---|---|---|
| = | field_1_ = "test" | Gets records where the value of the field ID specified before the operator matches the value specified after the operator. |
| != | field_1_ != "test" | Gets records where the value of the field ID specified before the operator is different from the value specified after the operator. |
| > | field_2_ > 10 | Gets records where the value of the field ID specified before the operator is greater than the value specified after the operator. |
| < | field_2_ < 10 | Gets records where the value of the field ID specified before the operator is less than the value specified after the operator. |
| >= | field_2_ >= 10 | Gets records where the value of the field ID specified before the operator is greater than or equal to the value specified after the operator. |
| <= | field_2_ <= 10 | Gets records where the value of the field ID specified before the operator is less than or equal to the value specified after the operator. |
| in | field_3_ in ("A", "B") | Gets records where the value of the field ID specified before the operator matches any of the strings listed in parentheses after the operator. |
| not in | field_3_ not in ("A", "B") | Gets records where the value of the field ID specified before the operator does not match any of the strings listed in parentheses after the operator. |
| like | field_1_ like "test" | Gets records where the value of the field ID specified before the operator contains the value specified after the operator. |
| not like | field_1_ not like "test" | Gets records where the value of the field ID specified before the operator does not contain the value specified after the operator. |
| match | field_4_ match 10 | Gets records where the value of the record ID of the source record in the lookup field specified before the operator matches the value specified after the operator. |
| not match | field_4_ not match 10 | Gets records where the value of the record ID of the source record in the lookup field specified before the operator is different from the value specified after the operator. |
| or | field_2_ < 10 or field_2_ > 20 | Use the above operators to OR the two conditional expressions. |
| and | field_2_ > 10 and field_2_ < 20 | Use the above operators to AND the two conditional expressions. |
You can group conditional expressions by enclosing them in parentheses "()".
Query Functions and session variables
| Function / Variable | Sample | Description |
|---|---|---|
| LOGIN_USER | field_5_ in (LOGIN_USER) |
Convert to the user who called the API. The parentheses "()" are not attached. |
| TODAY() | field_6_ = TODAY() |
Converts to the day the API is called. When targeting the datetime field, convert it to the period from 0:00 to 23:59 of the current day. |
| FROM_TODAY(number, period) | field_6_ < FROM_TODAY("3", "day") |
Converts to the result of adding the specified period based on the date when the API is called. The unit of the period is day, month, year. |
| FROM_THISWEEK(number, period) | field_6_ < FROM_THISWEEK("7", "day") |
Converts to the result of adding the specified period based on the Sunday of the week when the API is called. The unit of the period is day, month, year. |
| FROM_THISMONTH(number, period) | field_6_ < FROM_THISMONTH("1", "month") |
Converts to the result of adding the specified period based on the first day of the month when the API is called. The unit of the period is day, month, year. |
| FROM_THISYEAR(number, period) | field_6_ < FROM_THISYEAR("-1", "year") |
Converts to the result of adding the specified period based on January 1st of the year when the API is called. The unit of the period is day, month, year. |
Available Operators and Functions for each field
| Field | Operators | Functions |
|---|---|---|
| text | = != like not like |
TODAY() FROM_TODAY() FROM_THISWEEK() FROM_THISMONTH() FROM_THISYEAR() |
| textarea | = != like not like |
TODAY() FROM_TODAY() FROM_THISWEEK() FROM_THISMONTH() FROM_THISYEAR() |
| number | = != > < >= <= | |
| date | = != > < >= <= |
TODAY() FROM_TODAY() FROM_THISWEEK() FROM_THISMONTH() FROM_THISYEAR() |
| datetime | = != > < >= <= |
TODAY() FROM_TODAY() FROM_THISWEEK() FROM_THISMONTH() FROM_THISYEAR() |
| time | = != > < >= <= | |
| radio | in not in | |
| dropdown | in not in | |
| checkbox | in not in | |
| file | like not like | |
| user | in not in |
LOGIN_USER |
| department | in not in | |
| group | in not in | |
| lookup | = != like not like match not match | |
| address | = != like not like | |
| color | = != | |
| canvas | = != | |
| id | = != > < >= <= | |
| autonumber | = != > < >= <= like not like | |
| creator | in not in |
LOGIN_USER |
| createdtime | = != > < >= <= |
TODAY() FROM_TODAY() FROM_THISWEEK() FROM_THISMONTH() FROM_THISYEAR() |
| modifier | in not in |
LOGIN_USER |
| modifiedtime | = != > < >= <= |
TODAY() FROM_TODAY() FROM_THISWEEK() FROM_THISMONTH() FROM_THISYEAR() |
About specifying the search value in the user field
In addition to the userID, the user field can also search for users who belong to a specific department or group.
When specifying the ID, it is necessary to specify the target, so add an identification character to the beginning of each ID.
- Sample 1
-
Get records that include users with user ID "2".
field_1_ in ("u2") - Sample 2
-
Get records that include users with user ID "2" or users belonging to department with department ID "3".
field_1_ in ("u2", "d3") - Sample 3
-
Get records that include users with user ID "2" or users belonging to groups with group ID "5".
field_1_ in ("u2", "g5")
If there is no identification character, the user ID will be searched.