Once a grid or form view is opened from the dashboard, you will be able to click on the Filters button in the left upper corner. When you click on it, the query builder panel appears on the left side of the application.
Click on Add new (or icon) and use fuzzy-search to faster find a field or simply select a field from the list by clicking. The selected field will be added to the query builder as a search term. On the query panel, the default query operator became visible. Next to the query operator, one or two text fields appear to define the search value for each criterion. You can change the operator by clicking on the operator field and selecting the appropriate item from a dropdown list. The following query operators are available for different data types:
Numeric integer values: <, >, <>, >= , <= , between, is null, is not null, is void, not in list, in list;
Numeric decimal values: <, >, between, is null, is not null, is void ;
Text values: equals, does not equal, starts with, does not start with, ends with, does not end with, any of, none of, in list, not in list, contains, does not contain, is null, is not null, is void;
Date values: on, today, last 7 days, last 14 days, after, before, between, last, is null, is not null, is void .
Most of these operators take a single value. Exceptions to this rule are:
The between operator, where you have to specify the lower and the upper boundary of the interval you want to find with the search;
The in list and not in list operators both require a comma-separated list of values;
The last date query operator requires a number and a value selected from a drop-down list. For example, last 5 months .
In general, values that are missing or present in a field are also searchable using the 'is null', 'is not null' and 'is void' operators. The 'is null' operator returns all the rows in the selected field that have NULL value defined. The 'is void' operator returns all the rows in the selected field that have NULL value defined and all the rows that do not have a value defined. The 'is not null' operator returns all the rows in the selected field that have a defined value. Differences between the behavior of the 'is null', 'is not null' and 'is void' operators are mostly significant in case of relational (parent-child) tables.
Since version 20.17.0, the “=” ,”>=”, “<=” operators are also available for filtering of numeric decimal values. Considering the 'eq uals' operator for decimal numbers, it actually represents using a range. For example, x = 203.22 means actually "x between 203.215 and 203.224999", x>=203.22 means actually x>=203.215 and x<=203.22 means actually x <=203.224999. And if you try to test the equality to the limit, like x>=203.215 , the limit will shift to 203.2145... and so on.
You can add new conditions to the query from the context menu as well. Left-click on the dropdown arrow of the column header in the grid, and select Add to filter.
When you finished building up the query, the search can be started either by pressing Enter or by clicking the Filter button below query conditions. Whenever you modify the query (e.g., you change the criteria or add/remove a condition), the Filter button will become available to run the search again. During searching, results are being loaded continuously into the active database view, so you can keep browsing in your data without any interruption. You can run complex query just by adding multiple fields to your query builder.
If you want to undo filtering by a certain search condition, click on the button in the top right corner of the condition to remove it from the query, and then you can run the search again. To remove all query criteria from the query builder, use icon or Clear all button.
Filter child data
The filter child data feature will present in result only relevant child data records that fit to the definition set in the query. The Filter child option in the query builder can be found next to the Clear all button. If the Filter child toggle button is turned on , only that records of the child data table appear as filtering results, which correspond to child data filtering criterion. If the toggle button is turned off , all the records of the child data table are displayed – in this case, the child data table contains at least one record which corresponds to child data filtering criterion.
The model query on the Wombat demo data below uses search for benzene substructure in the "Wombat structures" entity AND L929 cell in the "Wombat activities" entity. When the filter is turned off, three records from the "Wombat activities" table are shown in the hit list, although only one of them contains “L929 cell” value in the BIO.CELL field. The row containing “L929 cell” value is highlighted.
When the same search is repeated with the toggle button turned on, only the record from the Wombat activities table containing “L929 cell” value in the BIO.CELL field is displayed.
In version 20.15.0, support for pick list functionality has been added. When a pick list field (a field predefined in Instant JChem as discrete set of values) is queried, the list items are only offered for using them as search values. When querying picklists, only operators relevant to the queried field are offered.