How to work with SQL folders
Product line
Standard
|Expert
Operating mode
Cloud Suite
|ON-PREMISES
Modules
Services & CRM
Budget & Phases
Purchases
Resource Planning
Business Intelligence
SQL folders are recommended when you want to select certain objects from a large amount of data based on a simple condition. Unlike the expression folder, the selection of data is done by the database server, which in most cases is faster. The disadvantage of SQL folders is that they do not automatically respond to changes in data and that the flexibility of queries is limited compared to expression folders (with OCL).
An example of an SQL folder is the folder Adressen
in the root data of Vertec. If you select this folder, you will see the search dialog above the list, in which you can specify certain criteria. Only the entries that match these criteria will appear in the folder.
The query for an SQL folder consists of a designation and an SQL condition. The SQL condition must be a valid SQL expression that returns a Boolean value. For evaluation, the SQL condition is used as the WHERE condition in an SQL-SELECT command. The condition (whereAttributes) can have a maximum length of 8000 symbols.
The Properties dialog of an SQL folder allows dialog-based editing of the SQL queries. Right-click on the folder in which you want to create the SQL folder and select Neu > SQL-Ordner
.
In the SQL Folder dialog, click Abfragen definieren...
. The following window appears:
For simple queries, you can now enter the appropriate SQL expression in the SQL
field. A normal text field appears to enter the value.
Most database fields are identical to the attribute names used in OCL, but there are differences in the case of calculated attributes and links. For a collection of SQL folder examples, see SQL Expressions for SQL Folders.
If you want to use several fields at the same time for a search or if you need a field of another type (e.g. a date field), you can define search fields. A maximum of 9 fields are allowed per query.
Using the button $vtc__000__888_0right under Fields a new search field is inserted and named. A search field is structured as follows:
designation |
The search field is shown in the query dialog with this designation. It should show what you are looking for. |
Field type |
The following types of search fields are supported:
|
Default value |
Here you can enter a default value using an OCL expression. This is already in the field when the user invokes the query, but can be changed. ![]() In order to use the logged-in user as the default value, the expression self.asstring can be entered here.If the search field is of the Date field type, a drop-down list will also appear from which you can select one of the following default values:
If the search field is Boolean (True/False), enter the default value as follows:
|
On the interface, the fields then appear in the query for selection:
From Vertec 6.2 onwards, the folder search can also be executed directly by activating the Sofort Suche
in the query dialog. This allows the search to be executed directly when the folder is displayed with the default search parameters (e.g. the logged-in user) without having to click on Suchen
.
Further searches can be made as usual by changing the search parameters and using the search button.
For the search dialog to appear on the interface, the option Suchdialog anzeigen
must be clicked in the SQL folder Configuration. If no search dialog is displayed, the first query is always used to calculate the folder contents.
If this option is enabled, it will have a Show All button next to the Search button. Clicking on this button will show all entries, regardless of queries or criteria.
For projects and addresses, a checkbox Only active is shown by default.
Whether the check mark is set by default or not can be controlled with the system setting General > Consider deactivated entries in search dialogs.
As of Vertec 6.3, this option can also be hidden. In the query definition dialog there is an option 'Nur aktive' ausblenden
:
This has the following effects:
You can also define multiple queries per folder, even those with multiple fields if required. To create a new query, click the + on the left of the queries.
If the folder is configured in this way, the different queries are available as tabs above the search dialog:
The fields are queried in order per query, from top to bottom. The variables are \1
for the first field, \2
for the second field, \3
for the third field, etc.
For example, a search by date range would look like this:
datum >= '\1' and datum <= '\2'
You can also define this query so that only a start date or an end date can be entered and the query still works. In this case, it must read:
(('\1'='') or (CreationDateTime>'\1')) and (('\2'='') or (CreationDateTime<'\2'))
If you extend a query with a Boolean query, note the following:
Suppose a query queries all invoices. What happens if we add a True/False query to query whether to include open or settled invoices?
As an example, use a query for date and number. This is:
datum < '\1' AND (nummer like '\2%')
Since the query now has to be expanded by the True/False field, this means that the meaning of the original query changes. For example, if you add a checkbox “nur verrechnete
” to the invoice query, the previous query has to be expanded as follows:
... AND verrechnet=\3
This means that if nothing is specified, it will now only search for open ones!
To work around this, you can add a CASE WHEN to the query:
AND verrechnet=(CASE WHEN \3=1 THEN 1 ELSE verrechnet END)
If parameter 3 (our checkbox) is equal to 1 (i.e. it is checked), then we compare the charged field with 1 (only the charged ones), if parameter 2 is equal to 0 (i.e. unchecked), we compare charged with charged, i.e. all are found.
In this case, the whole query is:
datum < '\1' AND (nummer like '\2%') AND verrechnet=(CASE WHEN \3=1 THEN 1 ELSE verrechnet END)
Important information:
True
or False
. For text fields, a message will appear if nothing is entered. If a checkbox is defined as a search field, then such a message will not appear, even if there is nothing in it in all text fields, because an inactive checkbox has the value False
and the search is started with it.Aktiv
status, as these classes have a checkbox Also deactivated, which already implements this (see section Also deactivated, above).Starting with version 6.1 it is possible in the new apps to access the entered search parameters in the column expression. This makes it possible, for example, to use date ranges used for the SQL query also in the OCL expressions for the list columns.
An OCL variable is created for each search field in the SQL folder dialog. The naming convention is based on the OCL variables in Word reports: var<Feldname>.
When constructing the variable name based on the field name, only letters and numbers from the ASCII range are taken into account. All other symbols (e.g. spaces, hyphens, umlauts, etc.) are filtered out and not taken into account for the variable name.
Thus, a field with designation To Date results in a variable varToDate.
The created variables receive a corresponding OCL type according to the type of the search field:
For example, you want a list of projects with open services up to the cut-off date. This is already possible with the SQL expression bold_id IN (SELECT project FROM open service WHERE datum<='\1').
Now you also want to see the accrued fee up to the cut-off date in a column, and this is now possible with the new column variables:
Here in the example, the three columns from the effective date have the following expressions:
For fields without names (this is the default for SQL folders with only one query, without extra search field), the field name is Search. The resulting variable is then called varSearch. For example, the folder invoices (search). In this case, the column expression is: invoices->select(number=varSearch).
Using search field variables only works for single-query SQL folders. In multi-query SQL folders, the variables may show incorrect values (from the other queries) or display error messages (if variables are used that do not appear in all queries).