SQL folder

SQL folders are recommended if you want to select certain objects from a large amount of data based on a simple condition. Unlike the expression folder, the data is selected 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 a SQL folder is the Adressen folder in the root data of Vertec. If you select this folder, you will see the Search dialog above the list where 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 a WHERE condition in an SQL SELECT command. The condition (whereAttributes) can be up to 8000 symbols.

The properties dialog of a SQL folder allows a dialog-based user to edit 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 will appear:

For simple queries, you can now enter the corresponding SQL expression in the SQL field. A normal text box appears for entering 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 joins. 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 a different 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 is being searched for.
Field type	The following types of search fields are supported: Symbol: Normal text search field. Number: Search field for a numeric value. Checks whether it is a valid numeric value. Date: Date search field with date picker for date selection. True / False: Checkbox for a Boolean query. Selection: Displays a drop-down list from which a value can be selected. Once this type is set, a field for the expression appears just below it. The expression must return a list of strings as a result. A selection field is then shown on the interface, from which the desired value can be selected. By default, the entry specified under Default value is shown, if one is defined. If a default value is defined, it is shown by default. If the specified default value is not present in the drop-down list or it has no default value, the first entry in the drop-down list is taken.
Default value	Here you can enter a default value using an OCL expression. This is already in the field when the user calls the query, but can be changed. When you open the OCL expression editor, the base class is always Projektbearbeiter, even if other objects are queried. This is the logged-in user. In order to use the logged-in user as the default value, the expression self.asstring can be entered here. If it is a search field with the Date field type, a drop-down list will also appear from which you can select one of the following default values: No date Today’s date First of the month Last of the month First of last month Last of last month If the search field is Boolean (True/False), enter the default value as follows: Default value TRUE: Enter value 1 as a number. Default value FALSE: Enter nothing.

On the interface, the fields will then appear in the query for selection:

Starting with Vertec 6.2, the folder search can also be performed immediately by activating the Sofort Suche in the query dialog. This allows the search to be performed directly when the folder with the default search parameters (e.g. the logged-in user) is displayed, without having to click on Suchen.

Further searches can be carried out 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 in the SQL folder Configuration must be clicked. If no search dialog is shown, the first query is always used to calculate the folder contents.

If this option is enabled, it has 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 > Include disabled entries in search dialogs.

Starting with 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:

• The Only active checkbox is no longer shown on the interface.
• If the checkbox is hidden, the search is always performed in all entries, i.e. in active and inactive. If the option is hidden, the above mentioned system setting Include disabled entries in search dialogs is ignored.

You can also define multiple queries per folder, including multiple fields if needed. To create a new query, click on the + to the left of the queries.

If the folder is configured in this way, the various 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 date range search 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, consider the following:

Suppose that a query queries all invoices. Now what happens if we add a True/False query to query whether to include open or settled invoices?

An example is a query for date and number. This is:

datum < '\1' AND (nummer like '\2%')

Since the query has to be expanded by the True/False field, this means that the meaning of the original query changes. If you add a checkbox “nur verrechnete” to the invoice query, for example, the previous query has to be completed as follows:

... AND verrechnet=\3

This means that if nothing is specified, only open ones will be searched!

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 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:

• Checkboxes always have a value. They are either True or False. For text fields, a message will appear if nothing is entered. If a checkbox is defined as a search field, such a message will not appear even if all text fields contain nothing, because an inactive checkbox has the value False and will start the search with it.
• When querying addresses (class address entry) and projects (class project), no checkbox should be used to query the Aktiv status, as these classes have a Search also disabled checkbox by default, which already implements this (see section Search also disabled 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 in the OCL expressions for the list columns.

For each search field in the SQL folder dialog, an OCL variable is created. The naming convention is based on the OCL variables in Word reports: var<Feldname>.

When forming 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 are not taken into account for the variable name.

Thus, a field with the designation To Date results in a variable varToDate.

The created variables receive a corresponding OCL type according to the type of the search field:

• String: String
• Boolean: Boolean
• Date: DateTime
• Number: Float

For example, you would like a list of projects with open services up to the due 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 reporting 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:

• Effective date: varEffective date
• Open fee until effective date: openeneLeistungen->select(date<=varStichdatum).wertext->sum
• Total open services: totalOpen services

For unnamed fields (this is the default for SQL folders with only one query, without an extra search field), the field name is Search. The resulting variable is 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 SQL folders with only one query. For SQL folders with multiple queries, the variables may show incorrect values (from the other queries) or may show error messages (if variables that do not occur in all queries are used).