Finding your data using the selection dialog.
The Utvalg (F9) button or the keyboard shortcut F9 in the Søking group brings up a dialog box. The internal format used by SQL and BIG is displayed in the upper half of the list in the dialog box.
Add
The upper part of the dialog box lists the selection conditions defined for the active page element. As a starting point, none of them are highlighted and the controls below the list are greyed out. The controls then show the selections that will be suggested if you press the Legg til button to the right of the list.
When you mark a selection criterion in the list, the values for this will be displayed in the controls below the list and they will be available for editing.
If you do not have access for changing the select conditions saved in the setup, only the select conditions which you have added can be edited. The others will be greyed out. Set access to change selection criteria via the dialog box for the Funksjonsadgang processing menu item on the row for the user's access group in the Adgangsgruppe table.
The list is updated as you edit.
If you click Legg til a new selection criterion will be added to the end of the list. The new element is selected and the values for it can be edited in the controls below the list.
Up arrow/Down arrow
You can move selected elements up or down the list using the buttons with Up arrow and Down arrow. The buttons are greyed out if the first and last elements respectively in the list are highlighted. An element remains selected after being moved so that you can move several positions by clicking the button several times in sequence.
Delete
You can delete highlighted elements using the Delete button to the right of the list. You can roll the list horizontally or vertically as necessary. Scroll boxes appear automatically if there is not room for everything at the same time.
Logical operator - "And" - "Or" - "Not"
The "And" or "Or" buttons connect the selection criteria. You can choose between them via buttons in the left part of the dialog box. Selecting one deselects the other. The buttons are greyed out for the first search criterion. You will not then see "And" or "Or" in the list. However, the system remembers the state if you move the element down the list.
"And" will be suggested automatically for new criteria that are added.
You can also select the "Not" checkbox. This also applies to the first element in the list.
Brackets around Select Conditions
You can enclose select conditions in brackets to alter the evaluation order. The rule in SQL, as in other languages, is that "And" will be evaluated before "Or". There is one button for left brackets and one button for right brackets.
As it only has significance to have at least two selection criteria within parentheses, the buttons are mutually exclusive. Selecting one deselects the other.
Left brackets come before the select condition where the button is selected and right brackets come after it. The button for right parenthesis is greyed out unless there is a earlier predominance of left parentheses in the selection list.
Below the two bracket buttons, there is an edit field with an up/down control (spin control) after it. The edit field shows the number of brackets. When both buttons are off, the value 0 is displayed. When one button is pressed down, the value changes to one.
You can add more brackets by increasing the value, either by entering the number in the edit field or by using the up button after it.
When the number of parentheses is changed, the corresponding number of parentheses will be displayed on the button.
If you increase the value from 0 to 1, the button for left brackets automatically goes in. If the value is reduced from 1 to 0, the buttons come out.
Comparison Operator
A select condition consists of two expressions which are compared with each other using a comparison operator. The left expression will be edited in the middle of the dialog box, the right below it.
You can select comparison operators at the bottom left of the dialog box. You can choose between the traditional operators:
One of them is always pressed in. When one of them is pressed down, the one which was previously pressed down comes up. "=" will be suggested for new criteria that are added.
The "On" and "Off" operators test whether the bits in the value for the right expression are on in the value for the left expression. The left expression can be as complex as it needs to be, including several levels of nested brackets and regardless of the database system. In database systems with bitwise operators, such as Microsoft SQL Server, there is no restriction on the right expression either.
The "Null" operator test weather the value is set as Null in the table.
The selection criteria in Visma Business are that bit operators operate on a left and a right expression like other operators. Even in queries which are produced for SQL Server, the right expression is repeated twice. However, the user does not need to worry about this.
Left and right expressions
Each of the expressions can consist of several operands linked by arithmetic operators.
Books and Pages
Each expression is represented by a book in the dialog box with one page for each pair of arithmetic operators and operands.
A new page will be added at the end of the book using the Legg til button (inside the book).
The active page can be moved to the left or right in the book using the buttons with left and right arrows on them. The buttons are greyed out on the first and last pages respectively.
Within the book, the active page in the book can be deleted using the Slett button. The button is greyed out if there is only one page in the book. An expression must have at least one operand.
The pages are numbered consecutively as they are added and adjust when pages are moved.
When a page is deleted, the following pages keep their number for the rest of the dialog box.
When the dialog box is opened, the pages will be consecutively re-numbered again.
Arithmetic operator
You can choose between the 4 basic calculation operations for:
Modulus (remainders for whole number division) and potency
Modulus is represented by %, both in SQL and in all other languages.
Power is represented by an up arrow, reproduced on the button in the Symbol font, and by a circumflex (^) in the selection list using the standard font.
Both modulus and power require the right expression to be an integer expression.
The modulus operation produces the remainder when the left expression is divided by the right expression. A classic example is that the remainder when dividing a year by 4 is 0 if it is a leap year. The remainder when dividing by 10 is the last digit in a number, the remainder when dividing by 100 is the last 2 digits, etc.
When using the power operator, the left operand is the basic number and the right operand the exponent. Powers are widely used in financial calculations, for example when calculating interest. Please note that not all database systems support this arithmetic operation. Microsoft SQL Server has it. In the queries, it is converted to the function Power, with the left and right operands as the parameters, separated by a comma. In other words, it is a special point in Visma Business that you can treat it as a normal operator.
When one of the buttons is pressed down, the one which was previously pressed down comes up.
Addition is suggested on any new pages added.
The arithmetic operators are greyed out on the first page in the book.
Operands
On each page in the books, you can choose either a column or a value as the operand, each represented by their own buttons. When you press the Column button, the Value button comes up, and vice versa. The controls below these two buttons change depending on which of them is pressed in.
When the Column button is pressed, a drop-down list with column names will be displayed. Under this you can select Systemopplysninger, Bedrift, Bedriftsopplysninger or Bruker table. If you select one of these tables, the list will be filled with the names of the columns in the Systemopplysninger, Bedrift, Bedriftsopplysninger or Bruker table, from which Org.nr, Bedriftsnr, Org.nr or Brukernavn will be suggested.
Otherwise the list shows columns in the active page element, including foreign columns which are visible or which are included in the collection of columns you are working with in BIG. The foreign columns are shown with the name of the underlying table and are prefixed by a full stop.
When the Value button is pressed, a selection can be made to search for a Fast verdi, Today's day or Current time.
When selecting Fast verdi, an edit field will be displayed in which you can enter a constant, i.e. a number or a text. You must enter the text without quotation marks or apostrophes. The program adds single quotes itself before the queries are sent to the database system if at least one text column is included in the search criterion.
After this value field is a button with an ellipsis (...) which opens to a lookup dialog box for the column on the active page in the other book, if applicable.
When you click OK in this kind of dialog box, the value field will automatically be filled in. This is useful for fields which keep flags for states and where the value is the sum of the bit values of the flags, for example the order status.
If you use wildcards in the value field and the comparison operator is = or <>, the "LIKE" or "NOT LIKE" operators will automatically be used in the SQL queries. The wildcards are "?" or "_" and represent an arbitrary symbol and "*" or "%" an arbitrary number of symbols, or no symbols. "_" and "%" are used in SQL. "?" and "*" that you are familiar with from, for example, searching for file names, are automatically substituted with these in the queries that are produced.
When selecting Today's day under the Value field, you can also add or subtract a number of days. When the program reads rows from the database, it will then use today's date plus the specified number of days (or deduct if you enter a negative number). This value will be displayed within parentheses behind the Today's day function name in the selection criteria list.
When selecting Current time under the Value field, you can also add or subtract a number of minutes. When Visma Business reads rows from the database, it will use the current time plus the specified number of minutes (or deduct if you enter a negative number). This value will be displayed within parentheses behind the Current time function name in the selection criteria list.
For new operands added to the expression, the same operand as on the previous page will be suggested. If it is a column, focus is switched to the pop-up list; otherwise the contents of the value field are selected.
In new selection criteria added, the active column will be suggested as a column in the left expression and places the cursor initially in the value field in the right expression.
After the Column and Value buttons, there is a Variable button on the pages in the books with left and right expressions in the selection criteria.
When you press the Variable button you can select "Namespace" and a variable from two drop-down lists. This also includes:
Brackets around Expressions
Two or more consecutive operands can be enclosed in brackets to alter the evaluation order. Otherwise the rule is that power has the highest priority. Then multiplication, division and modulus have higher priority than addition and subtraction. Evaluation takes place from left to right for operators with the same priority.
The buttons and number field for parentheses round expressions on the pages in a book, behave correspondingly to those that apply for complete search criteria referred to above.
Closing
The OK button will be greyed out if you have not made any changes in the dialog box.
Avbryt closes the dialog box without saving any changes.
When you click on OK an extra validation of the selection criteria and the expressions contained within them will be carried out.
If the program discovers an error, the select condition in question is selected and you are given an error message in a dialog box. This applies to:
Left and right expressions must both be either numerical or text expressions.
All operands in both expressions must be either numerical or text operands, and all columns must be either numerical columns or text columns.
If a value contains a symbol which is not allowed in numbers - apart from the joker symbol - when the comparison operator is = or ?1;, the value will be treated as text.
Otherwise the other operands determine whether the value is treated as a number or text.
Selection of foreign keys
If you want to select foreign columns, you import them first so that they are included in the column list in the dialog boxes. You can subsequently choose to hide them again.
The foreign columns are also included when setups are saved.