Developer Function: JSON Data Layout: View

01.06.2025

Developer Function: JSON Data Layout: View

## Overview This is a JSON format data layout that is specified for "[Filter](/en/manual/table-management-filter)" and "[Sort](/en/manual/table-record-sort)" when manipulating records with "[API](/en/manual/api)" or "[Server Script](/en/manual/table-management-server-script)". ## Restrictions 1. Search results may differ when using SQL Server and PostgreSQL. SQL Server uses the LIKE clause or full-text search, while PostgreSQL uses the ILIKE clause or full-text search with pg_trgm. ## Data Layout |Column name/type|Property name|Data type|Notes| |:--|--|--|:--| |Incomplete|Incomplete|Boolean|| |Own|Own|Boolean|| |Near Completion Time|NearCompletionTime|Boolean|| |Delay|Delay|Boolean|| |Overdue|Overdue|Boolean|| |Search|Search|String|| |Column filter|ColumnFilterHash|Object (string)| For how to specify the value, see "How to specify ColumnFilterHash" below. | |Column filter search type|ColumnFilterSearchTypes|Enumeration| For how to specify the value, see "How to specify ColumnFilterSearchTypes" below. | |Negation|ColumnFilterNegatives|Array (string)| For how to specify the value, see "How to specify ColumnFilterNegatives" below. When using this property, "[Use Negative Filters](/en/manual/table-management-filter-use-negative-filter)" must be enabled. | |Sort|ColumnSorterHash|Object (string)| For how to specify the value, see "How to specify ColumnSorterHash" below. | |-|ApiDataType|Enumeration| For how to specify the value, see "How to specify ApiDataType" below. | |-|ApiColumnKeyDisplayType|Enumeration| For how to specify the value, see "How to specify ApiColumnKeyDisplayType" below. This property is only valid when ApiDataType is "KeyValues". | |-|ApiColumnValueDisplayType|Enumeration| For how to specify the value, see "How to specify ApiColumnValueDisplayType" below. This property is only valid when ApiDataType is "KeyValues". | |-|ApiColumnHash|Object (string)| For how to specify the value, see "How to specify ApiColumnHash" below. This property is only valid when ApiDataType is "KeyValues". | |-|GridColumns|Array (string)| For how to specify the value, see "How to specify GridColumns" below. This property is only valid when ApiDataType is "KeyValues". | |-|MergeSessionViewFilters|Boolean|If the value is true, the filter conditions specified by the API and the filter conditions existing in the session are merged. If the same filter conditions exist, the filter conditions specified by the API are given priority. If the value is false, the filter conditions existing in the session are not merged. The default value when this property is omitted is false. | |-|MergeSessionViewSorters|Boolean|If the value is true, the sort conditions specified by the API and the sort conditions existing in the session are merged. If the same sort conditions exist, the sort conditions specified in the API will take precedence. If the value is false, the sort conditions existing in the session will not be merged. The default value when this property is omitted is false. | ### How To Specify ColumnFilterHash For the ColumnFilterHash value, multiple selection categories, numerical values, and dates can be specified as an array. If multiple values are specified, an OR search will be performed. For dates and numerical values, a range can be specified using a comma-separated string. If multiple search conditions are specified, an AND search will be performed. __ClassA is "mandarin orange" or "grape"__ ##### JSON ``` "ClassA":"[\"mandarin orange\",\"grape\"]" ``` __NumA is 10 or 20 or 30__ ##### JSON ``` "NumA":[10,20,30] ``` __NumA is 5 to 10 or 15__ ##### JSON ``` "NumA":"[\"5,10\",15]" ``` __NumA is 100 or less__ ##### JSON ``` "NumA":"[\",100\"]" ``` __DateA is 2021/1/31 (all day)__ ##### JSON ``` "DateA":"[\"2021/01/31 00:00:00,2021/01/31 23:59:59\"]" ``` __DateA is after 2021/1/1__ ##### JSON ``` "DateA":"[\"2021/01/01 00:00:00,\"]" ``` __ClassA is "apple" or "banana", AND NumA is 10 or 20 or 30__ ##### JSON ``` "ColumnFilterHash":{ "ClassA":"[\"apple\",\"banana\"]", "NumA":[10,20,30] } ``` __NumA is 300 to 900 or 200, AND DateA is 2021/1/1 to 2021/1/31, AND NumB is less than or equal to 100, AND DateB is after 2021/3/1__ ##### JSON ``` "ColumnFilterHash":{ "NumA":["300,990",200], "DateA":"[\"2021/01/01 00:00:00,2021/01/31 23:59:59\"]", "NumB":[",100"], "DateB":"[\"2021/03/01 00:00:00,\"]" } ``` __To set "Not set"__ If you want to filter records with (Not set) in the drop-down list, specify a tab character. ##### JSON ``` "ColumnFilterHash":{ "ClassA":"[\"\t\"]" } ``` __When filtering records that are not filled in__ If you want to filter records that are not filled in by classification or description, specify a half-width space. ##### JSON ``` "ColumnFilterHash":{ "ClassA":" " } ``` __When filtering records that are not filled in by date field__ If you want to filter records that are not filled in by date field, specify a tab character. ##### JSON ``` "ColumnFilterHash":{ "DateA":"[\"\t\"]" } ``` __When filtering records by the logged-in user himself__ If you want to filter by the logged-in user or the department to which he belongs in a field that has Users or Depts specified in the [Option List](/en/manual/table-management-choices-text), specify Own. ##### JSON ``` "ColumnFilterHash": { "ClassA": "[\"Own\"]" } ``` __When setting OR conditions and AND conditions__ You can perform "filtering" using OR or AND conditions by adding a property with an arbitrary name starting with or_ or and_ to ColumnFilterHash and setting the value that is a string of the JSON of the search conditions. The following JavaScript example is a code example that generates JSON data that extracts records where ClassA is "Design" and ClassD is "3", or records where ClassB is "Test" and ClassD is "7". ##### JavaScript ``` let data1 = {}; data1.ClassA = '["Design"]'; data1.ClassD = '["3"]'; let data2 = {}; data2.ClassA = '["Test"]'; data2.ClassD = '["7"]'; let data = {}; data.and_Filter1 = JSON.stringify(data1); // The name starts with and_, so it is an AND condition. The condition JSON must be converted to a string and assigned. data.and_Filter2 = JSON.stringify(data2); // Equivalent to the above line. let filter = JSON.stringify(data); let send_json = { // Generate JSON data to send via API ApiVersion: 1.1, ApiKey: 'efe7...', View: { ApiDataType: 'KeyValues', GridColumns: ['Title','ClassA','ClassD'], ColumnFilterHash: { 'or_Filter': filter // // Name starts with or_, so it is an OR condition }, ColumnFilterSearchTypes:{ ClassA: 'ExactMatch', ClassD: 'ExactMatch' }, } }; ``` ### How To Specify ColumnFilterSearchTypes - Specify the search method. Can be used to search for title, content, description, and class column. Used with ColumnFilterHash. #### Specify one search string Specify one string in ColumnFilterHash and specify the search method in ColumnFilterSearchTypes. You can specify one of the search methods: "PartialMatch", "ExactMatch", or "ForwardMatch". - In the following example, records whose title field value starts with "Nakano" will be matched. ##### JSON ``` ColumnFilterHash: { Title: "Nakano" }, ColumnFilterSearchTypes:{ Title: "ForwardMatch" } ``` #### Specify Multiple Search Strings Specify strings as an array in ColumnFilterHash, and specify the search method in ColumnFilterSearchTypes. You can specify one of the search methods: "PartialMatchMultiple", "ExactMatchMultiple", or "ForwardMatchMultiple". This is an OR condition that matches any of the multiple strings specified in ColumnFilterHash. - In the following example, records whose title field value starts with "Nakano" or "Shinjuku" will be matched. ##### JSON ``` ColumnFilterHash: { Title: "[\"Nakano\",\"Shinjuku\"]" }, ColumnFilterSearchTypes:{ Title: "ForwardMatchMultiple" }, ``` ### How To Specify ColumnFilterNegatives Specify the column to be the negated condition. This parameter is used in conjunction with ColumnFilterHash. - In the following example, records with a status column other than unset (100) are filtered. ##### JSON ``` ColumnFilterHash: { Status: "[\"100\"]" }, ColumnFilterNegatives: [ "Status" ] ``` ### How To Specify ColumnSorterHash - Specify the sort order as either asc (ascending) or desc (descending). - In JSON, specify the pair {"column name":"sort order"} {"DateA":"asc", "ClassA":"desc"}. ##### JSON ``` "ColumnSorterHash":{ "DataA":"asc", "Title":"asc" } ``` ### How To Specify ApiDataType - This property allows you to switch the API format. Specify one of the following. - If you specify "KeyValues", the format of each column in the returned response will be the name displayed. If you do not specify "GridColumns", the column enabled in the "「Index」" tab of "[Manage Table](/en/manual/table-management)" will be obtained. - Also, if "ApiDataType" is not specified (default value), the format of each column in the response will be the column name for Key and the value (value registered in the database) for Value. ##### JSON ``` ApiDataType: "KeyValues" ``` ### How to specify ApiColumnKeyDisplayType - This property allows you to specify the display format of the key for each column in the returned response. Specify one of the following. - If you specify "LabelText", the key will be the name displayed. - In the case of "ColumnName", the key will be the column name. - The default value is "LabelText". ##### JSON ``` ApiColumnKeyDisplayType: "ColumnName" ``` ### How To Specify ApiColumnValueDisplayType - This property allows you to specify the display format of the Value of each column in the returned response. Specify one of the following. - In the case of "DisplayValue", the Value will be the name displayed. - In the case of "Value", the Value will be the value (the value registered in the database). - In the case of "Text", the Value will be the value including the format and unit you set. - The default value is "DisplayValue". ##### JSON ``` ApiColumnValueDisplayType: "DisplayValue" ``` ### How To Specify ApiColumnHash - This property allows you to specify the display format of the Key and Value for each column. You can specify the key for each column with "KeyDisplayType" and the value with "ValueDisplayType". - Specify either "LabelText" or "ColumnName" for "KeyDisplayType". The default value is "LabelText". - Specify either "DisplayValue", "Value" or "Text" for "ValueDisplayType". The default value is "DisplayValue". ##### JSON (Example) When you want to display the value of Updater as the name displayed ``` ApiColumnHash: { Updator: { ValueDisplayType: "DisplayValue" } } ``` ### How To Specify GridColumns - Specify in array format when you want to control the column returned. If you do not specify "GridColumns", the column enabled in the "「Index」" tab of "[Manage Table](/en/manual/table-management)" will be retrieved. ##### JSON ``` GridColumns: ["ClassA","ClassB"] ``` - You can also specify columns on the parent table side. (Example) When you want to retrieve the value of ClassB in the parent table (site ID: 11) * The following example is when ClassA in the child table is linked. ##### JSON ``` GridColumns: ["ClassA","ClassA~11,ClassB"] ``` ## Related Information <div id="ManualList"><ul><li><a href="/en/manual/table-record-sort">Table Function: Sort Records (Sort)</a>10.07.2024 up</li></ul></article> <ul><li><a href="/en/manual/table-management">Manage Table</a>10.02.2024 up</li></ul></article> <ul><li><a href="/en/manual/table-management-choices-text">Manage Table: Editor: Column Advanced Settings: Option List</a>01.15.2025 up</li> <li><a href="/en/manual/table-management-choice-json">Manage Table: Editor: Column Advanced Settings: Option List: Filter, Sort, Display Format</a>10.02.2024 up</li></ul></article> <ul><li><a href="/en/manual/table-management-filter">Manage Table: Filter</a>10.02.2024 up</li> <li><a href="/en/manual/table-management-filter-use-negative-filter">Manage Table: Filter: Use Negative Filters</a>10.03.2024 up</li></ul></article> <ul><li><a href="/en/manual/table-management-server-script">Table Management: Server Script</a>08.13.2024 up</li></ul></article></div><input id="SearchTextHidden" type="hidden" value="" />