ItemList
Obtain a list of items
Description
Specify search condition to obtain a list of items.
Method
POST
Request URL Format
/api/v0/applications/:app-id/datastores/:datastore-id/items/search
URL Params
app-id : Application ID（Specify the ID entered from the Hexabase screen）
datastore-id : Data store ID（Specify the ID entered from the Hexabase screen）
Payload
conditions : Specify search condition
use_or_condition : Searches with OR conditions (if false or not specified, AND conditions will be applied).
per_page : A Number of search results (If omitted or 0 is specified, all results are retrieved).
page : Number of pages
unread_only : If true is specified, a narrowing condition of only 「Items with unread history」 will be added to the conditions.
sort_field_id : Assign sort field ID (If the sort key is only 1 field).
sort_order : "asc" for ascending order "desc" for descending order (when the sort key is only 1 field).
sort_fields : Specified when there are multiple sort keys. It is used for sorting in preference to sort_field_id. [{id: "FIELD_A", order: "asc"},{id: "FIELD_B", order: "desc"}]
Specify the field display ID in "id" and the sort order in "order". If omitted, the order will be ascending.
The order specified in the array is applied in the form of the first sort key, and second sort key.
use_default_search : true or false Specify true to apply the default search condition.
include_links : If true, you will get an array of IDs of related items.
include_lookups : If true is specified, the reference item information of the database reference type will be included in the result.
return_count_only : If true is specified, only totalItems will be returned. items[] (empty array) will become empty.
include_fields_data : If you specify true, it will return including the information of fields.
omit_total_items : If true, totalItems is not counted (faster) totalItems is zero.
data_result_timeout_sec : Specifies the number of seconds to time out before the result of the view is retrieved. If timeout occurs, items will be [] (empty array).
total_count_timeout_sec : Specifies the number of timeout seconds before the number of cases is retrieved. If timeout occurs, -1 is returned.
return_number_value : If true, numeric type data is output as Number (by default, numbers are returned as string ("123")).
select_fields : Narrow down and specify field items to be returned in the response items (at a faster pace).
select_fields_lookup: Narrow down and specify the field items returned in the response lookup_items by data store (at a faster pace).
format : If "csv" is specified, the output is in CSV format.
(Note 1：The default search condition is specified on the list screen of the Hexabase Management UI by checking the box.)
(Note 2：If you use per_page, you must specify sort_fields.)
Conditions
Conditions About Parameter Specification
- For DateTime, time, and numeric type fields, the first value of
search_valuemeans From and the second value means To. - The value specified for the date/time type must be specified in UTC (Universal Coordinated Time), ISO 8601 based format [ YYYYY-MM-DDThh:mm:ss.uuuZ ]. Example of getting time in ISO format :
new Date(Date.UTC(2020, 1, 2, 3, 4, 5)).toISOString(); - In the case of DateTime, time, and numeric type fields, if null is specified for either, searches such as From~ and To~ are possible.
- In the case of a DateTime type, a string
"TODAY"in the value allows searches such as "today~". - For text type or multi-line text, the string specified in
search_valuewill be matched by partial matching. - For text type or multi-line text,
search_valuecan be a regular expression. Regular expressions are specified in Perl compatible format (PCRE). For example, OR search can be performed by separating with "|". - In the case of text type or multi-line text, if "exact_match": true, the search is performed by exact match.
- "exact_match": if true, multiple strings in the search_value array will result in an OR search. search_value: ["A", "B", "C"] => match A or B or C
- "not_match": if true, the NOT condition is applied to the specified value.
Nest of Conditions
Conditions can be specified within conditions, and when used with the use_or_condition flag, a combination of AND/OR conditions can be specified.
For example, the following logic:
AND : {
OR: {
{"FieldA" == "X"},
{"FieldB" == "Y"}
}
AND: {
{"FieldA" is including "ABC"},
{"FieldC" != "Z"},
}
}
It is possible to specify conditions by nesting conditions as follows.
{
"conditions": [
{
"conditions": [
{"id": "FieldA", "search_value": ["X"], "exact_match": true},
{"id": "FieldB", "search_value": ["Y"]}
],
"use_or_condition": true // OR Search
},
{
"conditions": [
{"id": "FieldA", "search_value": ["ABC"]},
{"id": "FieldC", "search_value": ["Z"], "not_match": true}
] // When searching AND, "use_or_condition" can be omitted
}
],
"use_or_condition": false, // AND Search
"page": 1,
"per_page": 0, // return all
"use_display_id": true,
"return_number_value": true
}
(Note) Although any number of nesting hierarchies can be specified, we recommend that you keep the design simple to avoid the risk of code complexity and performance degradation.
Parameters that can be specified for the condition
"exact_match": true, // Search by exact match
"not_match": false, // If set to true, the NOT condition is applied
"search_value": [
"ABC|DEF",
"GHI",
"JKL", // ABC or DEF or GHI or JKL Search
]
- For text type or multi-line text, regular expressions are available for the first value of search_value if "exact_match": false is specified. For example, the following specification can be used to perform an OR search. PCRE (Perl compatible regular expression) is followed as the regular expression language.
"exact_match": false,
"search_value": [
"ABC|DEF", // Search ABC or DEF
]
（Conditions Specified Sample）
{
"conditions": [
{
"id": "FIELD_ID", // Specify ID entered on the Hexabase screen
"search_value": [
"fa"
],
"exact_match": true, // Search by exact match
"not_match": true, // Search for items that are not the value specified in search_value (NOT condition)
"include_null": true // Contains blank spaces
},{
"id": "FIELD_ID2",
"search_value": [
"value"
],
"exact_match": false, // Search by partial match
"include_null": false // Excluding blank spaces
},{
"id": "i_id", // If "i_id" is specified for the id, the specified item can be targeted
"search_value": [
"58272f4efb90a148d850qwer", // item_id
"5846636efb90a1024d29as12", // item_id
"5846636efb90a1024d29asdf" // item_id (Multiple items can be specified)(OR Condition)
]
},{
"id": "updated_by", // If you specify "updated_by" or "created_by" for id, you can search for items updated or created by the specified user (u_id).
"search_value": [
"58272f4efb90a148d850qwer", // user_id
"5846636efb90a1024d29as12", // user_id
"5846636efb90a1024d29asdf" // user_id (Multiple cases can be specified as OR)(OR Condition)
]
},{
"id": "updated_at", // If you specify "updated_at" or "created_at" for id, range search by update date/creation date is possible.
"search_value": [
"2020-01-24T10:42:07.880Z",
null, // Specify items updated after the specified date
]
},{
"id": "58bbaa27fbfcba773851339f", // "TODAY" keyword can be specified for date type fields
"search_value": [
"TODAY",
null //Target Items from today onwards
],
},{
"id": "58bbaa27fbfcba773851339f", // Numeric type
"search_value": [
null,
"100" // Search for values less than 100
],
},{
"id": "5e5f334e8250710006078dbd", // Status ID of Field ID
"search_value": [
"5e5f334e8250710006078dc0" // Status_id
]
}
],
"page": 1,
"per_page": 100,
"sort_fields": [
{"id": "Status", "order": "desc"}, // The first sort key id: specifies screen D
{"id": "Category", "order": "asc"} // The second sort key asc: order can be omitted
],
"sort_field_id": "FIELD_ID", // Specifies screen D. Used when there is only one sort key. This specification is ignored if sort_fields is specified
"sort_order": "asc",
"include_links" : true, // true or false. If true, include an array of i_id associated with each Item in the retrieved result
"select_fields": ["6260dc08d1b96dbcdc3b755b", "Assignee"], // Specify f_id and display_id to be included in items response
"select_fields_lookup": {
"m_department": ["address", "6260dc41d1b96dbcdc3b764f"], // Specify the f_id and display_id to be included in the lookup_items response, using the d_id and display_id of the data store from which the lookup originated as the key
"6260dc46d1b96dbcdc3b7683": ["department_id"]
}
}
Request URL Sample
POST https://api.xxx.com/api/v0/applications/5c6363d984f4be7de0350445/datastores/DATABASEID/items/search
Response Sample
If display_id is specified and "return_number_value": true
Example of search with the following options:
"use_display_id": true,
"return_number_value":true
Response
{
"items": [
{
"Assignee": "Y",
"Category": "C",
"Checkbox": "A,B,C",
"DueDate": "2015-12-31T15:00:00Z",
"Status": "作業中",
"Title": "TaskC",
"User": "hi-hexabase",
"created_at": "2020-08-16T02:59:45Z",
"created_by": "IMPORT",
"d_id": "5f38a11baa395581685afdb4",
"i_id": "5f38a121aa395581685afdc2",
"p_id": "5f25956528dc5c55b463bc7b",
"rev_no": 4,
"status_id": "5f38a11baa39556e74845a42",
"title": "TaskC",
"unread": 0,
"updated_at": "2020-09-02T17:48:04Z",
"updated_by": "5f25952c28dc5c55b463bc76"
},
{
"Assignee": "X",
"Category": "B",
"Checkbox": "B",
"DueDate": "2015-12-31T15:00:00Z",
"Status": "確認",
"Title": "TaskD",
"User": "hi-hexabase",
"created_at": "2020-08-16T02:59:45Z",
"created_by": "IMPORT",
"d_id": "5f38a11baa395581685afdb4",
"i_id": "5f38a121aa395581685afdc1",
"p_id": "5f25956528dc5c55b463bc7b",
"rev_no": 4,
"status_id": "5f38a11baa39556e74845a41",
"title": "TaskD",
"unread": 0,
"updated_at": "2020-09-30T14:04:30Z",
"updated_by": "5f25952c28dc5c55b463bc76"
},
{
"Assignee": "X",
"AutoNum1": "PREFIX_6",
"Category": "A",
"Checkbox": "A",
"DueDate": "2015-12-31T15:00:00Z",
"LU2": null,
"Lookup1": "タスクC",
"Num1": 5,
"Num2": 12,
"Status": "完了",
"Title": "TaskE",
"Total": 17,
"created_at": "2020-08-16T02:59:45Z",
"created_by": "IMPORT",
"d_id": "5f38a11baa395581685afdb4",
"i_id": "5f38a121aa395581685afdc0",
"p_id": "5f25956528dc5c55b463bc7b",
"rev_no": 7,
"status_id": "5f38a11baa39556e74845a3e",
"title": "TaskE",
"unread": 0,
"updated_at": "2021-02-01T13:25:24Z",
"updated_by": "5f25952c28dc5c55b463bc76"
}
],
"totalItems": 3
}
include_links : true is specified
Link destination d_id, i_id information of the related item is added to "item_links" field.
"return_number_value": if true is not specified
Numeric values are converted to strings and returned.
{
"items": [
{
"Assignee": "",
"AutoNum1": "PREFIX_4",
"Category": "A",
"Checkbox": "C,A",
"DateTime": "2020-12-23T01:41:14Z",
"DueDate": null,
"User": "h.iwasaki",
"LU2": "タスクB",
"Lookup1": "タスクE",
"Status": "受付",
"Title": "Title sample AA",
"created_at": "2020-08-16T04:08:11Z",
"created_by": "5f25952c28dc5c55b463bc76",
"d_id": "5f38a11baa395581685afdb4",
"i_id": "5f38b12aaa39558bfca2964f",
"item_links": {
"db_count": 1,
"item_count": 3,
"links": [
{
"d_id": "5f38b0afaa395581685afdf6",
"item_count": 3,
"items": [
{
"i_id": "5f38b0b6aa395581685afdff",
"type": "lookup"
},
{
"i_id": "5f38b0b6aa395581685afdff",
"type": "lookup"
},
{
"i_id": "5f38b0b6aa395581685afe02",
"type": "lookup"
}
]
}
]
},
"p_id": "TestApp",
"rev_no": "10",
"status_id": "5f38a11baa39556e74845a40",
"title": "Title sample AA",
"unread": "0",
"updated_at": "2021-01-22T16:55:59Z",
"updated_by": "5f25952c28dc5c55b463bc76"
}
],
"totalItems": 1
}
include_lookups : true is specified
The "lookup_items" field will be populated with the item information of the corresponding database lookup (Lookup) destination.
In cases where the Lookup destination Item recursively references the same database, it will be expanded up to two times, but no further calls will be made (to avoid infinite loops).
{
"items": [
{
"Assignee": "",
"AutoNum1": "PREFIX_4",
"Category": "A",
"Checkbox": "C,A",
"DateTime": "2020-12-23T01:41:14Z",
"DueDate": null,
"LU2": "タスクB", // Lookup type 1
"Lookup1": "タスクE", // Lookup type 2
"Status": "受付",
"Title": "Title sample AA",
"created_at": "2020-08-16T04:08:11Z",
"created_by": "5f25952c28dc5c55b463bc76",
"d_id": "5f38a11baa395581685afdb4",
"i_id": "5f38b12aaa39558bfca2964f",
"lookup_items": {
"LU2": { // Item values for Lookup type 1
"Assignee": "Y",
"Category": "A",
"DueDate": "2015-12-31T15:00:00Z",
"Fld-tSWAyL4f": "0",
"Status": "受付",
"Title": "タスクB",
"created_at": "2020-08-16T04:06:14Z",
"created_by": "IMPORT",
"d_id": "5f38b0afaa395581685afdf6",
"i_id": "5f38b0b6aa395581685afe02",
"p_id": "5f25956528dc5c55b463bc7b",
"rev_no": "2",
"status_id": "5f38b0afaa39558bfca2963e",
"title": "タスクB",
"updated_at": "2021-01-22T07:50:24Z",
"updated_by": "5f25952c28dc5c55b463bc76"
},
"Lookup1": { // Item values for Lookup type 2
"Assignee": "X",
"Category": "B",
"DueDate": "2015-12-31T15:00:00Z",
"Fld-tSWAyL4f": "3",
"LookupMySelf": "タスクE", // Lookup type 3 (LookupField in the Lookup Item)
"Status": "完了",
"Title": "タスクE",
"created_at": "2020-08-16T04:06:14Z",
"created_by": "IMPORT",
"d_id": "5f38b0afaa395581685afdf6",
"i_id": "5f38b0b6aa395581685afdff",
"lookup_items": {
"LookupMySelf": { // Items for Lookup Item 3
"Assignee": "X",
"Category": "B",
"DueDate": "2015-12-31T15:00:00Z",
"Fld-tSWAyL4f": "3",
"LookupMySelf": "タスクE", // Lookup Item 3 (Recursive Link... )
"Status": "完了",
"Title": "タスクE",
"created_at": "2020-08-16T04:06:14Z",
"created_by": "IMPORT",
"d_id": "5f38b0afaa395581685afdf6",
"i_id": "5f38b0b6aa395581685afdff",
"lookup_items": {
"LookupMySelf": { // Recursive call will be limited by twice for the same datastores in the result
"Assignee": "X",
"Category": "B",
"DueDate": "2015-12-31T15:00:00Z",
"Fld-tSWAyL4f": "3",
"LookupMySelf": "5f38b0b6aa395581685afdff",
"Title": "タスクE",
"created_at": "2020-08-16T04:06:14Z",
"created_by": "IMPORT",
"d_id": "5f38b0afaa395581685afdf6",
"i_id": "5f38b0b6aa395581685afdff",
"p_id": "5f25956528dc5c55b463bc7b",
"rev_no": "4",
"status_id": "5f38b0afaa39558bfca2963c",
"title": "タスクE",
"updated_at": "2021-01-22T17:10:37Z",
"updated_by": "5f25952c28dc5c55b463bc76"
}
},
"p_id": "5f25956528dc5c55b463bc7b",
"rev_no": "4",
"status_id": "5f38b0afaa39558bfca2963c",
"title": "タスクE",
"updated_at": "2021-01-22T17:10:37Z",
"updated_by": "5f25952c28dc5c55b463bc76"
}
},
"p_id": "5f25956528dc5c55b463bc7b",
"rev_no": "4",
"status_id": "5f38b0afaa39558bfca2963c",
"title": "タスクE",
"updated_at": "2021-01-22T17:10:37Z",
"updated_by": "5f25952c28dc5c55b463bc76"
}
},
"p_id": "TestApp",
"rev_no": "10",
"status_id": "5f38a11baa39556e74845a40",
"title": "Title sample AA",
"unread": "0",
"updated_at": "2021-01-22T16:55:59Z",
"updated_by": "5f25952c28dc5c55b463bc76"
}
],
"totalItems": 1
}