Plexus Connect - Searching in Your Database

On this page:

• Simple search

• One click filtering

• Filter child data

• Pick lists

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.

Simple search

First, the dataset need to be specified where you want to perform search. There are two options selectable in a dropdown in 'Search in' section: your whole database and your saved lists. To select a saved list containing the preferred dataset for searching, choose the list from a dropdown which contains all available lists' names.
In the text box of the query builder panel, fuzzy search can be used to find fields containing data you need to search. Fields can also be selected from the fields list which appears by clicking on the text box. The selected fields will be added to the query builder as search terms. In the opened grid or form view, the selected fields will become highlighted with a red outline.
On the query panel, the default query operator is displayed. 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 'equals' 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 your query from the context menu as well. Left-click on the dropdown arrow of a column header in the grid view (or a widget header in the form view), and select Add to filter to add the selected field to your query. In the opened grid or form view, all the fields present in your filter will be marked with a red outline.

When you finished building up the query, the search can be started either by pressing Enter or by clicking the Filter button below your 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.

One click filtering

Since version 21.1.0 users are able to create filter conditions simply by clicking on widget with desired data. One click filtering is activating by Select field icon

There are two possibilities of usage:

• Clicking on the widget header selects a bound field.

 

• Clicking into a widget selects a bound field and fills the filter with data actually displayed in the widget.

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.

Pick lists

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.