Manage Table: Editor: Column Advanced Settings: Option List: Filter (narrow the option list by the value of other columns)
## Overview
By specifying ColumnFilterExpressions in the "[Option List](/en/manual/table-management-choices-text)", you can narrow down the option list by the value of other columns.
## Supported Versions
1. Pleasanter 1.3.16.0 or later
## Limitations
1. Can only be used with "[Owner Column](/en/manual/table-management-owner)", "[Manager Column](/en/manual/table-management-manager)", and "[Class Column](/en/manual/table-management-class)".
## Prerequisites
1. "Site Administration Permission" is required to configure settings.
## Operation Procedure
Open the advanced settings for the classification in the Editor tab, and describe the method for filtering the option in JSON format in the option list.
### How to Specify ColumnFilterExpressions
|Type of expression|Example|Description|
|:--|:--|:--|
|Filter by Name Displayed|[ClassA]|If the option list for the specified column is set with a value and a name displayed, it will be filtered by the "[Name Displayed](/en/manual/table-management-label-text)" of the column. It will be converted to a JSON array and filtered. |
|Filter by Value|[@ClassA]|If the option list for the specified column is set with a value and a name displayed, it will be filtered by the "value" of the column. It will be converted to a JSON array and filtered. |
|Filter by value as is|=[@ClassA]|Filter by the column value without converting to a JSON array. Use this when you want to match the exact value of the column specified in the filter. |
*If "[Option List](/en/manual/table-management-choices-text)" is not set (free input) or "[Option List](/en/manual/table-management-choices-text)" is only a value, it will filter by the "Value" of the column regardless of the type of expression.
#### Set Example
If you write it as shown below, it will search for ClassX in the linked table by the "[Name Displayed](/en/manual/table-management-label-text)" of ClassA on the screen. If you set a postback for ClassA on the screen, any changes to ClassA will be reflected in the list immediately.
##### JSON
```
[
{
"SiteId": 12345,
"View": {
"ColumnFilterExpressions": {
"ClassX": "[ClassA]"
}
}
}
]
```
If ClassA on the screen is written as "Test", the internal operation will be as follows.
If ClassX has no options (passed to ColumnFilterHash as is)
##### JSON
```
[
{
"SiteId": 12345,
"View": {
"ColumnFilterHash": {
"ClassX": "Test"
}
}
}
]
```
If ClassX has options (if there are options on the left side, it is automatically converted to a JSON array and passed)
##### JSON
```
[
{
"SiteId": 12345,
"View": {
"ColumnFilterHash": {
"ClassX": "[\"Test\"]"
}
}
}
]
```
If you write it as shown below, the "Value" of ClassA on the screen will search for ClassX in the linked table. If ClassA has a value and a name displayed, add @ when you want to extract the value.
##### JSON
```
[
{
"SiteId": 12345,
"View": {
"ColumnFilterExpressions": {
"ClassX": "[@ClassA]"
}
}
}
]
```
If you write it like this, you can pass the "Value" of ClassA on the screen directly without converting it to a JSON string. For example, if ClassA is a multiple-selection column, [@ClassA] already contains a JSON-formatted string. If there are options on the left side, there is a mechanism to auto-convert it to a JSON array, but if there is already JSON in it, write = to prevent conversion.
##### JSON
```
[
{
"SiteId": 12345,
"View": {
"ColumnFilterExpressions": {
"ClassX": "=[@ClassA]"
}
}
}
]
```
Ensure that if you use a numerical value on the right side, the search will be performed with a string with symbols and units unless you add @.
##### JSON
```
[
{
"SiteId": 12345,
"View": {
"ColumnFilterExpressions": {
"ClassX": "=[@NumA]"
}
}
}
]
```
#### Operation Image
If you set the following to the selection list for ClassB to be filtered, ClassC of the table with site ID: 12345 will be filtered with records that match the name displayed of classA of the target table. ClassA has "[Auto Postback](/en/manual/table-management-auto-postback)" turned on.
##### JSON
```
[
{
"SiteId": 12345,
"View": {
"ColumnFilterExpressions": {
"ClassC": "[ClassA]"
}
}
}
]
```
##### Example 1: Filter by name displayed
In the example below, the above JSON is written in the "[Option List](/en/manual/table-management-choices-text)" of "Book Title (ClassB)" in the "Book Review Collection" table to narrow down the options. When "Publisher (ClassA)" is selected on the editing screen, the automatic postback process filters the option for "Book Title (ClassB)" by the name displayed of "Publisher (ClassA)". In "Book Title (ClassB)", only the titles of books whose "Publisher (ClassC)" registered in the "Book Management" table (Site ID: 12345) match the name displayed of the selected "Publisher (ClassA)" is displayed.
・Edit screen for the "Book Review Collection" table
・Screen index for the "Book Management" table (site ID: 12345)
##### Example 2: When the filter destination class column is multiple selection
- Screen index of "Book Management" table (site ID: 12345)
- When the "[Option List](/en/manual/table-management-choices-text)" of the "Book Name (ClassB)" expression is written in "Filter by Value" format
Option C of site ID: 12345 does not match the value of ClassA in the target table as a JSON format string, so no options are displayed in ClassB.
・If you write the "[Option List](/en/manual/table-management-choices-text)" for "Book Title (ClassB)" in the "Filter by Value" as it format.
Option C of site ID: 12345 matches the value of ClassA in the target table as a JSON format string, so the filtered options are displayed in ClassB.
### How to display the user's group in the option list
Display the group to which the user set in the assignee field belongs in the option list of any class column.
1. Open the relevant table.
1. Click "Manage" - "[Manage Table](/en/manual/table-management)" from the navigation menu.
1. Enable ClassA from the Editor tab.
1. Enter the following JSON in the list of options from the advanced settings screen of the enabled ClassA.
#### JSON
Groups that meet either of the following conditions will be displayed in the list of options.
1. Groups to which the user belongs
2. Groups to which the user's department belongs
Therefore, the search will be limited to groups to which the user belongs directly or groups to which the user belongs via department.
**The prerequisite is that "GroupMembers" is a user column.**
```
[
{
"TableName": "Groups",
"View": {
"ColumnFilterExpressions": {
"GroupMembers":"[@Owner]"
}
}
}
]
```
## Related Information
<div id="ManualList"><ul><li><a href="/en/manual/table-management">Manage Table</a><span>10.02.2024 up</span></li></ul></article>
<ul><li><a href="/en/manual/table-management-manager">Manage Table: Column: Manager</a><span>10.02.2024 up</span></li>
<li><a href="/en/manual/table-management-owner">Manage Table: Column: Owner</a><span>10.11.2024 up</span></li>
<li><a href="/en/manual/table-management-class">Table Management: Item: Classification</a><span>08.13.2024 up</span></li></ul></article>
<ul><li><a href="/en/manual/table-management-label-text">Table Management: Editor: Item Detail Settings: Display Name</a><span>08.13.2024 up</span></li>
<li><a href="/en/manual/table-management-choices-text">Manage Table: Editor: Column Advanced Settings: Option List</a><span>01.15.2025 up</span></li>
<li><a href="/en/manual/table-management-auto-postback">Table Management: Editor: Item Detail Settings: Auto Postback</a><span>08.13.2024 up</span></li></ul></article></div><input id="SearchTextHidden" type="hidden" value="" />
