Generic inquiry (SM208000)

This topic gives you information about the elements that you find in this window in Visma.net ERP, such as fields, field values, buttons, and check boxes.

By using this window, you can create inquiries on the fly, without programming. You can view how the selected or newly designed inquiry looks, and you can test its functionality by selecting parameters and viewing the results.

Button Description
View inquiry Displays the inquiry defined by or selected on the window.

In this area, you can specify the titles to be used for the newly created inquiry window, the area of the inquiry window that contains the inquiry parameters, and the area that contains the inquiry results. Also, for the created inquiry window, you can specify how to arrange the inquiry parameter elements and how many resulting records are displayed. Click Add inquiry to sitemap to specify the position of the inquiry in the site map or the workspace and the section within the workspace to hold this newly designed inquiry.

Element Description
Inquiry title The title to be used for the inquiry window. Type a name to add a new inquiry or select an inquiry for editing from the list of existing inquiries.
Sitemap title The name of the inquiry window as it will be displayed on the sitemap. You can enter any name by using alphabetic or numeric characters.

You must specify the sitemap title if you want to replace a entry window with this inquiry window.

Location in the menu The location of the new inquiry window on the sitemap. You can select a required location manually.

You must specify the sitemap location if you want to replace an entry window with this inquiry window.

Window ID The ID of the inquiry window.
Arrange parameters in x columns The number of columns in which the elements for parameters should be arranged in the parameters area of the inquiry window.
Select top x records The maximum number of records to be displayed as results.
Records per page The maximum number of records to be displayed on the page.
Export top x records  

The tabs in this window

Descriptions of the different tabs are expandable in the list below.

This table holds the list of database tables to be used for the inquiry or the list of data access classes (DACs) that are used to represent the data from the system database tables.

Column Description
Table name The name of the table or DAC that provides access to database tables.
You can select a table from the list of available tables (DACs).
Alias

The alias to be used in SQL statements to designate the table.
If no alias is specified, the Table name is used.

In the Table relations area of this tab, you can specify relations between pairs of tables.
For each pair of related tables, you specify links between the columns of these two tables in the Data field links for active relations area.

Here you specify relations between pairs of tables.
Column Description
Active A check box that indicates (if selected) that the record is active and is used to specify relations.
Parent table The name (alias) of the first table in a JOIN statement of SQL.
Join type The type of JOIN between participating tables or DACs, which can be one of the following options:
  • Inner: Returns rows when there is at least one match in both tables.
  • Right: Returns all rows from the right table, even if there are no matches in the left table.
  • Left: Returns all rows from the left table, even if there are no matches in the right table.
  • Full: Returns rows when there is a match in one of the tables.
  • Cross: Returns all records in which each row from the first table is combined with each row from the second table. The size of a resulting set is the number of rows in the first table multiplied by the number of rows in the second table.
You can concatenate multiple joining conditions between different parent tables to the same child table into one ON clause. For example, if you cross join Custom week and Employees, you can also join time cards that exist for the Custom week-Employees pairs.
Child table The second table to be used in the JOIN statement.

Data field links for active relations table columns.
In this area, you define links between pairs of fields for the related tables selected in the Table relations area. Data field links for active relations table columns.
In this area, you define links between pairs of fields for the related tables selected in the Table relations area.
Element Description
Brackets The opening bracket or brackets for composing a logical expression with multiple conditions.
Parent field The field from the parent table. Click the icon to select a field, or click the icon to open the Formula editor dialog box and create a formula.
Condition One of the following logical conditions:
  • Equals:
    Returns TRUE if the value of the specified parent field is equal to the value of the specified child field.
  • Does not equal:
    Returns TRUE if the parent field value is not equal to the child field value.
  • Is greater than:
    Returns TRUE if the parent field value is greater than the child field value.
  • Is greater than or equal to:
    Returns TRUE if the parent field value is greater than or equal to the child field value.
  • Is less than:
    Returns TRUE if the parent field value is less than the child field value.
  • Is less than or equal to:
    Returns TRUE if the parent field value is less than or equal to the child field value.
  • Is between:
    Not applicable for parent and child field values.
  • Contains:
    Returns TRUE if the parent field value (string) contains the child field value.
  • Ends with:
    Returns TRUE if the parent field value ends with the same character or string as the child field value contains.
  • Starts with:
    Returns TRUE if the parent field value contains at the beginning the child field value.
  • Is null:
    Returns TRUE if the parent field value is null.
  • Is not null:
    Returns TRUE if the parent field value is not null.
Child field The field from the second table.
Click the icon to select a field, or click the icon to open the Formula editor dialog box and create a formula.
Brackets The closing bracket or brackets for composing a logical expression with multiple conditions.
Operator The logical operator between conditions, which can be And or Or.

You use this tab to specify the types of fields to be used in the parameters area of the inquiry window.

Button Description
Move row up Moves the selected row up by one row.
Move row down Moves the selected row down by one row.
Combo box values Brings up the Combo box values dialog box, which you can use to enter the options to be used for the drop-down list of options for this parameter.
Column Description
Active A check box that indicates (if selected) that the parameter is active and will be added to the inquiry area that provides fields for inquiry parameters.
Is required A check box that indicates (if selected) that this field is required in the inquiry window.
Name The name of the parameter.
Schema field An optional field indicating the database field selected as the schema for this field.
If no field is specified, the parameter field will be a simple data input field.
Display name

The name for the field to be displayed in the window.

This field supports the multi-language functionality, where you can translate the entered text to multiple languages. For more information, see:About multi-language support
From schema A check box that indicates (if selected) that the field will be based on (copied from) the schema field.
Default value The default value of the field.

For the data fields of the date type that are based on schema fields (that is, the From schema check box is selected), you can select one of the following date-relative parameters in the Calendar dialog box:

  • @Today:
    The current day.
  • @WeekStart and @WeekEnd:
    The start and end, respectively, of the current week.
    The start and end of the week are determined according to the default system locale or the locale you selected when you signed in to Visma.net ERP.
    The system locales are specified and configured in the System locales (SM200550) window.
  • @MonthStart and @MonthEnd:
    The start and end of the current month accordingly.
  • @QuarterStart and @QuarterEnd:
    The start and end of the current quarter accordingly.
  • @PeriodStart and @PeriodEnd:
    The start and end of the current financial period accordingly.
    The financial periods are defined in the Financial year (GL101000) window.
  • @YearStart and @YearEnd:
    The start and end, respectively, of the current calendar year.

All the date-relative parameters use the date of the server used to run the Visma.net ERP instance as the current date.

Additionally, you can modify the date-relative parameters by adding or subtracting integers.
The unit of measure of the parameter is determined automatically and the reference point is moved according to the measurement of the parameter, for example, @WeekStart+1 relate to the start of the next week.
Column span The number of columns in the parameters area in the inquiry window this column will span.
Control size The size that represents the height and width of the control in pixels.
Column width The width of the grid column.
Field size The size of the field in the inquiry. Options between XXS and XL.
Hidden When this check box is selected, the field is hidden in the inquiry.
Label size The size of the label in the inquiry. Options between XXS and XL.

In this dialog box, you can specify options for a drop-down list for the combo box field that will be used as the parameter field for the inquiry.

Element Description
Value The value assigned to an option to be added to the combo-box list.
Label A text string to be displayed as an option.
OK (button) Saves the combo-box options for the parameter.

On this tab, you can specify the conditions to be met for the rows to be returned; the system uses these conditions to generate the WHERE SQL request.
To include a parameter value in the condition, use the [ParameterName] format.
You configure the fields for inquiry parameters on the Parameters tab (to be displayed in the selection area of the new inquiry window); once they have been configured, they appear on the list of fields shown in the Data field column on this tab.

Button Description
Move row up Moves the selected row up by one position.
Move row down Moves the selected row down by one position.
Column Description
Active A check box that indicates (if selected) that the row is an active condition.
Brackets The opening bracket or brackets for composing a logical expression with multiple conditions.
Data field The field whose value the condition should be applied to.
Condition One of the following logical conditions, which will be applied to the value of the field specified as the Data field and the values in the Value 1 and Value 2 fields if applicable:
  • Equals:
    Returns TRUE if the value of the Data field field is equal to the value specified as Value 1.
  • Does not equal:
    Returns TRUE if the field value is not equal to the Value 1 value.
  • Is greater than:
    Returns TRUE if the field value is greater than the Value 1 value.
  • Is greater than or equal to:
    Returns TRUE if the field value is greater than or equal to the Value 1 value.
  • Is less than:
    Returns TRUE if the field value is less than the Value 1 value.
  • Is less than or equal to:
    Returns TRUE if the field value is less than or equal to the Value 1 value.
  • Is between:
    Returns TRUE if the field value is between the Value 1 and Value 2 values.
    (For this option, you must specify both Value 1 and Value 2.)
  • Contains:
    Returns TRUE if the field value (string) contains the Value 1 value.
  • Does not contain:
    Returns TRUE if the field value (string) does not contain the Value 1 value.
  • Starts with:
    Returns TRUE if the field value contains at the beginning the Value 1 value.
  • Ends with:
    Returns TRUE if the field value ends with the Value 1 value.
  • Is null:
    Returns TRUE if the field value is null.
  • Is not null:
    Returns TRUE if the field value is not null.

If you try to filter the inquiry results by using a string with the underscore, the result will also contain the values with the same string with any symbol instead of the underscore.
For example, if you try to filter the inquiry by a customer name that contains the Customer_Name string, the system will return all the customers whose name contains any of the following strings: Customer_Name, Customer-Name, and Customer Name.
The reason that all of these strings will be returned is that the underscore is used as a wildcard character.
From schema A check box that indicates (if selected) that Value 1 will display the specified data field's options in the system.
Value 1

The value to be used in the selected condition.
Click the icon to select a field, or click the icon to open the Formula editor dialog box and create a formula.

For the date-related data fields you can use the date-relative parameters either by selecting the parameter in the Calendar dialog box (if the field is based on a schema field and the From schema check box is selected) or by using the date-relative parameter in a formula (if the field is not based on a schema field and you use the formula editor).
The following date-relative parameters are available:

  • @Today:
    The current day.
  • @WeekStart and @WeekEnd:
    The start and end, respectively, of the current week.
    The start and end of the week are determined according to the default system locale or the locale you selected when you signed in to Visma.net ERP. The system locales are specified and configured in the System locales (SM200550) window.
  • @MonthStart and @MonthEnd:
    The start and end of the current month accordingly.
  • @QuarterStart and @QuarterEnd:
    The start and end of the current quarter accordingly.
  • @PeriodStart and @PeriodEnd:
    The start and end of the current financial period accordingly.
    The financial periods are defined in the Financial year (GL101000) window.
  • @YearStart and @YearEnd:
    The start and end, respectively, of the current calendar year.

All the date-relative parameters use the date (in UTC) of the server used to run the Visma.net ERP instance as the current date.

Additionally, you can modify the date-relative parameters by adding or subtracting integers. The unit of measure of the parameter is determined automatically and the reference point is moved according to the measurement of the parameter, for example, @WeekStart+1 relate to the start of the next week.
Value 2

The second value to be used, if the selected condition requires one.
Click the icon to select a field, or click the icon to open the Formula editor dialog box and create a formula.

For the date-related data fields that are not based on a schema field (that is, the From schema check box is cleared), you can use one of the date-relative parameters, as described in Value1.
Brackets The opening bracket or brackets for composing a logical expression with multiple conditions.
Operator The logical operator to join conditions in a logical expression, which can be And or Or.

On this tab, you specify the grouping conditions according to which the results should be displayed on the inquiry window.
One result row is returned for each group. SUM is the aggregate function that is applied to the result columns with the numeric type by default.
MAX is the aggregate function that is applied to the other result columns by default.

You can select an aggregate function value for each result column in the Aggregate function column on the Results grid tab.

Button Description
Move row up Moves the selected row up by one position.
Move row down Moves the selected row down by one position.
Column Description
Active A check box that indicates (if selected) that the row is active and is used in grouping the inquiry results.
Data field

The field whose value the grouping should be applied to. You cannot select the attribute fields as a value of this column.

In the entry and maintenance window of a class, the attribute fields are listed on the Attributes tab.

On this tab, you specify the order in which the results should be displayed in the new inquiry window.

Button Description
Move row up Moves the selected row up by one position.
Move row down Moves the selected row down by one position.

Column Description
Active A check box that indicates (if selected) that the row is active and is used in sorting the inquiry results.
Data field The name of the field in the inquiry window.
Sort order An option describing how values should be ordered in this column: in Ascending or Descending order.

By using this tab, you can specify how the results of the search in the database tables should be displayed.

Button Description
Up Moves the selected row up by one row.
Down Moves the selected row down by one row.

Column Description
Active A check box that indicates (if selected) that the row is active and thus is used in selecting the results.
Object The name (alias) of the table.
Data field

The field of the table or the formula that includes fields and constants.
Click the icon to select a field, or click the icon to open the Formula editor dialog box and create a formula.

Schema field The field to be used as the source of properties for this field.
Width (pixels) The width of the grid column in pixels.
Visible A check box that indicates (if selected) that this field will appear in the resulting grid.
If the check box is cleared, the field will be hidden by default but can be added to the grid by a user.
Default navigation

A check box that indicates (if selected, which is the default value) that the field value can be a link to the default window, which the user can open by clicking the link, specified in the source code. For example: For the field that holds the invoice reference number, the default window is the Sales invoices (AR301000) window.

If the check box is cleared, the field value can be a link to the window selected in the Navigate to field.

If the Default navigation check box and the Navigate tofield are cleared, the field cannot be a link.

If you select the Default navigation check box, you should clear the Navigate to field.
Navigate to

A window specified on the Navigation tab that the user can open by clicking the link in the column.

If you select any window in the field, the Default navigation check box is cleared automatically.
Aggregate function A function that defines how the resulting value should be calculated for the grouped values in this column.
The following aggregate functions are available:
  • AVG: Returns the average of all non-null values of the group.
  • COUNT: Returns a count of all values of the group.
  • MAX: Returns the maximum value of all values of the group.
  • MIN: Returns the minimum value of all values of the group.
  • SUM: Returns the sum of all values of the group.

The following aggregate functions are applied by default, when no function is selected:

  • SUM is applied to the columns with the numeric type.
  • MAX is applied to the other columns.
Caption

The name for the column header to be displayed in the window.

This field supports the multi-language functionality, where you can translate the entered text to multiple languages. For more information, see:About multi-language support
Quick filter A check box that indicates (if selected) that the system should add a button with the quick filter for this field to the filtering area of the generic inquiry form. If multiple tabs are displayed on this generic inquiry form, the button with the quick filter is added to the filtering area of the All records tab. By default, this check box is cleared.

By using this tab, you can match the selected inquiry (called the substitute window in this context) to a data entry or maintenance window (called the entry window in this context). You can then replace the entry window with the inquiry in the navigation pane.
Once you have replaced the entry window with this inquiry, when you try to click the name of the entry window in the navigation pane, you are redirected to the inquiry.

If you select a record in the list, the entry window opens and displays the details of the selected record.
Also, if you create a new record from the inquiry, the entry window opens.

Additionally, you can configure the actions to be available on the inquiry. For more information, see:see: Manage substitutes of entry windows - overview.

Element Description
Entry window

The entry window to be associated with this inquiry.
The list of available windows is filtered according to the data access classes selected for the inquiry on the Tables tab.

When you select an entry window, this window is added to the Navigation tab automatically.
The navigation parameters, which are the key fields in the entry window and the inquiry parameters that should be passed to these fields, are filled in automatically, but you can also specify these parameters manually.
Replace entry window with this inquiry in menu A check box you select to replace the entry window selected in the Entry window column with the inquiry (that is, to display the inquiry instead of the entry window when the user clicks the menu item in the navigation pane).
Element Description
Enable mass actions for records

A check box that you select to allow users to perform the actions you select on the records in the inquiry window.
With this check box selected, the Mass actions tab appears in this window.
On this tab, you can specify the action or actions that will be available in the Actions menu in the window toolbar of the inquiry window.

If this box is selected, the selected commands will appear in the Actions menu in the window toolbar and the Selected column in the table of the substitute window. A user can select one record or multiple records, and then apply any available command to the selected records.
Automatically confirm customized mass actions
This check box and the Enable mass actions for record must both be selected, in order to make the system confirm automatically any confirmation dialog pop-up that would be raised by the system while executing the action.
Enable mass deletion

A check box you select to allow users to delete multiple records from the list in the inquiry window.

If this check box is selected, the Delete button appears in the window toolbar and the Selected column appears in the inquiry window. A user can select one or multiple records, and then delete them.
Automatically confirm customized mass deletion This check box and the Enable mass record deletion must both be selected, in order to have the system automatically confirm the deletion of records when a user clicks Delete.
Enable mass update

A check box that you select to allow users to update multiple records from the list in the inquiry window.
If you select this check box, the Mass update fields tab appears in the window. Use this tab to select the field (or fields) that users should be able to update.

If this check box is selected, the Update and Update all commands appear in the Actions menu in the window toolbar and the Selected column in the table of the inquiry window. A user can select one record or multiple records, and then change the specified fields of the selected records.
Enable new creation

A check box you select to allow users to create new records from the inquiry window.

If this check box is selected, the Add record button appears in the window toolbar in the table of the inquiry window.
When a user clicks the button, the entry window opens so the user can add a new record.

This table is available only if the Enable new record creation check box is selected.
In this table, you can specify the default values for the records that can be created in the inquiry window.

Column Description
Field The name of the field in the entry window.
Value The default value for the selected field.

In the Windows area of this tab, you can specify the list of screens to be used for navigation from the inquiry.
For each window, in the Navigation parameters area, you can specify navigation parameters and select the way to open the window.

In this area, you can specify the list of screens to be used for navigation from the inquiry.
You can select any data entry or maintenance window as well as an inquiry or a report.

By using this area, for each window, you can specify navigation parameters and select the way to open the window.

Column Description
Field The name of the field of the window, which can be the field in the entry window, the inquiry parameter, or the report parameter.
Parameter The default value for the selected field.

By using this element, you can select the way the window is opened.

Element Description
Window mode The way the window is opened. The following modes are available:
  • Same tab: The window opens in the same browser tab instead of the inquiry. This is the default mode.
  • New tab: The window opens in a new browser tab.
  • Pop-up window: The window opens in a new pop-up window.
  • Side panel: The window opens in the side panel, allowing to quickly edit the desired values, while you still have the main generic inquiry window open. For more information, see:About side panels and Enable the side panel

If the window is selected as the entry window on the Entry point tab and replaced with the inquiry, the window mode of this window is Inline. This means that this window opens in the same browser tab when a user is adding a new record or viewing the details of an existing record. You cannot change this mode.

This tab appears only if the Enable mass record update check box is selected on the Entry point tab.

By using this tab, you can specify the fields of the entry window that can be updated in the table if a user clicks Update all in the Actions menu in the window toolbar of the inquiry.

Column Description
Select

An unlabelled check box that you use to select the fields you want to be updated.

When you add a field to this table, it becomes active, with the Select check box automatically selected.
You can clear the check box any time.

Field name The name of the field that should be updated.

This tab appears only if the Enable mass actions on records check box is selected on the Entry point tab.

By using this tab, you can specify the options that will be available in the Actions menu in the window toolbar of the inquiry.

Column Description
Select

An unlabelled check box that you use to select an action.

When you add an action to this table, it becomes active, with the Select check box automatically selected.
You can clear this check box any time.

Action The name of the action that will be available in the Actions menu in the window toolbar of the inquiry.

In some columns of the Relationship, Conditions, and Result grid tabs, you can use formulas to calculate the values of these columns by using the formula editor, which is invoked by clicking the edit control button in the columns.

The formula editor includes standard and window-specific functions.

For the list of standard functions, see: About operators and About functions.

The window-specific function is the Concat() function.
If the software of your Visma.net ERP database is MySQL Server, for generic inquiries, use the Concat() function instead of + to sum the values of the string data type, as follows.

=Concat('My first string', 'My second string', [Object.DataField])

Related concepts

About side panels

Related tasks

Design an inquiry based on inventory data

Enable the side panel