Table of Contents
Data Editor - Filtering
Data Editor has a powerful feature - FILTERS, which allows you to select sub-set of records of table specifying simple search conditions.
This menu contains commands to add filter and remove it, to store and manage favorite filters and others. Let's consider in details each command.
Add Filter Row
This command adds the first row of filter or one more OR-row to existed filter. You can see filter row(s) of the green color on the top of the table grid.
In filter row(s), you can define a search condition for each field. The rules of search condition are 100% the same as in SQL query. Valentina Studio combines all these search conditions to produce SQL query and execute it.
It is easy to see that search conditions of the same green row are combined by AND in the result SQL query:
SELECT * FROM T WHERE ((condition_i) AND (condition_j) AND ...)
TIP: You can see WHERE part of the result SQL query in the tooltip if you move mouse cursor over icon of filter row.
Inside of a single cell you can write, for example, the following search conditions:
= 55 = 55 OR =77 >= 55 AND < 90 = other_field_of_this_table LIKE 'str'
For NUMERIC fields you can type just “55” in the Filter Cell, VStudio will produce then “f = 55” for SQL query.
For string-kind fields you can type just a string value “something”, VStudio will produce on default “f LIKE '%something%'”.
Add Filter Row with Cell Value
This command is kind of optimization. When you see in come cell value, which you want to use for FILTER of records, you can just click cell and do this command. You will see a new filter row with this value assigned to that field.
Note that filter is not yet applied. You will need do “Execute Filter” command yet.
Add 'Where' Row
This command adds a special filter row in which you can define by yourself the WHERE clause of result SQL query.
Note, that you can use only one of filters at the same time: a normal filter or a WHERE-filter.
Filter by Cell Value
This command is even bigger optimization than “Add Filter Row with Cell Value”, because it do the same – adds a new filter row with value of cell, but also executes filter immediately.
This command removes the filter:
- green rows are removed from the table grid;
- all records of table are displayed again.
Remove Last Filter Row
If you have add too many rows to a filter, you can use this command to remove a last row(s).
This command executes SQL query using the filter conditions and display only records that satisfy them.
Show All Records
You can use Record → Show All Records (CMD/CTRL + G) menu command, to show all records in table exactly as before filter was applied. So you can may be edit your filter future adding more conditions.
Note, that this command do not trash filter itself. You still can see green lines and edit them.
Filter to SQL
This command allows you to get SQL query, which is used by the current filter. You will be thrown into SQL Editor, which contains corresponded query.
This feature allows gives you one more way to construct SQL query step by step for future use in your application code. Another ways are to use
- Query Editor;
- Generate SQL in Schema Editor;
Allows you to save and reuse the filter in the future. To add a new favorite filter first of all you should to create it by Add Filter Row row and setup the filter clauses. Then you should to select the current menu item and choose Add… in the submenu and set the name of the filter in the modal dialog. In the future, you can use this filter via same submenu.