Vertec search

The global Vertec search – this is how the query is structured.

Product line

Standard

Expert

Operating mode

CLOUD ABO

ON-PREMISES

Modules

Services & CRM

Budget & Phases

Purchases

Resource Planning

Business Intelligence

Created: 01.02.2010

Updated: 13.09.2023 | Screenshots updated.

In the Desktop App and the Cloud App, the search term can be entered directly into the address bar:

Another option in the Desktop App, Cloud App and Web App is the navigation view global search :

The search is based on a Vertec SQL folder. The search is formulated in this folder. A search folder is already predefined in System settings > General. It is called Vertec search and is automatically created under public folders:

For the search to work, the root folder with the Vertec Search folder must be visible to the user. If you want to have the folder in a different location, e.g. because the users do not have access to the public folders, you can move it to a different / new root folder that all users can see and adjust the path accordingly in the system settings.

By default, this search folder searches in address.alias and project.code, description and regarding.

Structure of the search query

As the Vertec search should return objects from different classes, the class for the SQL folder must be the common base class of all possible result objects. The top class, and therefore the base class of all Vertec objects, is BusinessClassesRoot, therefore, BusinessClassesRoot is specified as the class for the Vertec search.

However, BusinessClassesRoot has no defined attributes and allows only BOLD_ID and BOLD_TYPE as data fields via SQL. The actual SQL query must therefore be formulated in subqueries, which are linked to the BusinessClassesRoot table via the SQL operators in via Bold_id.

The syntax is as follows:

((Type-Filter) and (bold_id in (select bold_id from Tabelle where Eigenschaften-Filter))

Type filter: The bold_type is the information to which class the object belongs. So each class can be uniquely assigned to a bold_type. The whole query is much faster if you specify which bold_type the objects in the table belong to. It can also be used for filtering (see below).

Table: Specifies the table in which to search.
Property filter: The so-called where clause. Here, the entry is compared with the entered search term.

The structure of the SQL always looks the same and can be expanded as required with the command “or”.

Default search in Vertec

The default search provided by Vertec looks like this:

((bold_type in (102,84,85,345,88) and exists(select 1 from adress adr where 
adr.bold_id=businessclassesroot.bold_id and adr.alias like '%\1%'))) or ((bold_type=18) and 
exists(select 1 from project proj where proj.bold_id=businessclassesroot.bold_id and (proj.code 
like '%\1%' or proj.description like '%\1%' or proj.regarding like '%\1%')))

Searches the Alias field of all addresses, as well as the Code, Description, and Regarding fields of projects.

Default search extended by the location of the addresses

Also searches in the location (default location) field for addresses:

((bold_type in (102,84,85,345,88) and exists(select 1 from adress adr where 
adr.bold_id=businessclassesroot.bold_id and (adr.alias like '%\1%' or adr.standardcity like '%\1%')))) 
or ((bold_type=18) and exists(select 1 from project proj where proj.bold_id=businessclassesroot.bold_id 
and (proj.code like '%\1%' or proj.description like '%\1%' or proj.regarding like '%\1%')))

Default search extended with activities (bold_type 304)

Also searches in the title of activities.

((bold_type in (102,84,85,345,88)) and (bold_id in (select bold_id from address adr where adr.alias like '%\1%'))) or
 ((bold_type=18) and (bold_id in (select bold_id from project proj where proj.code like '%\1%' or proj.description like
 '%\1%' or project like '%\1%'))) or ((bold_type in (304)) and (bold_id in (select bold_id from activity where title
 like '%\1%')))

Default search extended with phases (bold_type 176)

Also searches in the code and description of phases.

((bold_type in (102,84,85,345,88)) and (bold_id in (select bold_id from address adr where adr.alias like '%\1%'))) or
 ((bold_type=18) and (bold_id in (select bold_id from project proj where proj.code like '%\1%' or proj.description like '%\1%' or
 project like '%\1%'))) or ((bold_type=176) and (bold_id in (select bold_id from projektphase phase where phase.code
 like '%\1%' or phase.description like '%\1%')))

Default search extended by search on additional class0 (bold_type 223)

Also searches for the additional field('url’) on additionalclass0.

((bold_type in (102,84,85,345,88,223) and bold_id in (select bold_id from address adr where adr.alias like '%\1%')))
or ((bold_type=18) and bold_id in (select bold_id from project proj where proj.code like '%\1%' or proj.description like '%\1%' 
or proj.regarding like '%\1%')) or (bold_type=223) and bold_id in (SELECT user entry FROM custom field item WHERE Metaadditional field in 
(SELECT bold_Id from Custom field WHERE fieldname=”url”) AND Value like '%\1%')

Search with filter

The search is filtered by bold_type to make it faster. Since certain (sub)classes in the database are grouped together in a table, bold_type can also be used to filter in such cases. Accounts, contacts, persons, simple addresses, and couples, for example, are all in the address table, but each have a different bold_type.

As an example, the above address query is restricted to accounts and contacts (bold_type 84 and 85):

((bold_type in (84,85)) and bold_id in (select bold_id from address where alias like '%\1%')))

or in the default search:

((bold_type in (84,85)) and (bold_id in (select bold_id from address adr where adr.alias like '%\1%'))) or
 ((bold_type=18) and (bold_id in (select bold_id from project proj where proj.code like '%\1%' or proj.description like
 '%\1%' or proj.regarding like '%\1%')))

Finding the bold_type

The bold_types can only be found in the database. The relevant table is called “bold_type,”. You can query the database with “select * from bold_type”.

Here is a list of the most frequently used tables, fields and bold_types:

Class name	bold_type	Table	Fields
Account	84	address	alias, name, supplement, remark
Contact	85	“	“
Person	88	“	“
Couple	345	“	“
Activity	304	activity	title, remark
Project	18	project	code, description, regarding, remark
Phase	176	phase	code, description, remark
User	33	user	name, abbreviation, remark
OpenService	67	OpenService	text, remark
ChargedService	70	ChargedService	text, remark
OpenExpense	77	OpenExpense	text, remark
ChargedExpense	80	ChargedExpense	text, remark
OpenOutlay	282	OpenOutlay	text, remark
ChargedOutlay	283	ChargedOutlay	text, remark

Additional tables can be found directly in the database.