Data

Data

In the data component, you can configure search, filter, find, sort, prefilters, and conditional layouts for your application.

Search

A search field is permanently visible on a screen. It can be used to search on several columns at once. In the results, only the rows with at least one field containing the entered search value are displayed. All other rows are removed.

Include columns in Search

To include columns in the Search field:

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Search

1. Select the checkbox Incl. in search to include a column in the Search field.

note

The Search field is independent of whether the filter is enabled or not in menu User interface > Subjects > tab Settings > tab Permissions > checkbox Filter. If this checkbox is cleared, any column allowed in Search will still show in the Search field in the GUI.

Add columns to a Search field

Column order in the Search tooltip

Universal GUI

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Filter

In your application, the tooltip for the Search field displays the columns used for searching. You can determine the column order in the Filter tab. In the example below, the subject column is placed in front of the employee_id primary key column, which you would expect to be the first in the original list of columns in the data model (menu User interface > Subjects > Columns).

The order of columns in the Search tooltip

Filter and Find

Availability of filter and find tabs

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Filter or tab Find

The tabs for Filter and Find are only available if users are allowed to use these features according to the settings in the tab Permissions. The same applies to tasks for setting up a filter or find in variants.

See Data permissions.

Optimize filter and find

note

Find is only available in the Windows GUI.

To optimize the filter and find functions:

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Filter or tab Find

1. Select the column visibility (Visible for filter/Visible in Find)

   • Always - for frequently used filter/find terms. They are always displayed, regardless of whether the filter/find dialog is expanded.
   • Never - for fields that should not be filtered or found. Use this, for example, for columns containing binary data.
   • Extended - for the remaining fields. These are only shown when a user expands the filter/find dialog .

2. If necessary, select a filter/find condition for each column. See Filter and find conditions.

3. If necessary, drag and drop the columns to change their order.

tip

It is possible to copy filter/find order and visibility settings. See Copy settings to filter and Copy settings to find.

Prefilters in these tabs:

• Possibly visible - hides all the columns that will be hidden in the application (type: Hidden). Hidden fields are usually not used for searching or filtering.
• Possibly editable - only shows the columns that can be edited in the application (type: Editable).

Optimize Filter for a table in tab Data

Optimize Find for a table in tab Data

Filter and find conditions

note

Find is only available in the Windows GUI.

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Filter or tab Find

In the Filter condition field, you can specify the default way of filtering or finding for a specific column in a subject.

Universal GUI For the following control types, filter conditions are not supported:

• File/image
• HTML/multiline
• Label

Filter condition settings for a specific column

Copy settings to filter

To copy order and visibility settings to the filter:

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Filter

• Execute the task Copy from find to copy search settings to the filter.
• Execute the task Copy from grid to copy grid settings to the filter.
• Execute the task Copy from form to copy form settings to the filter.

The order will be copied by default, one-to-one. To also include the visiblity, select Visibility in the pop-up.

If necessary, the visibility will be transformed based on the Type of the source:

• If it is editable or read-only, the visibility will be transformed to always.
• If it is hidden, the visibility will be transformed to never.

Copy settings to find

To copy order and visibility settings to Find:

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Find

• Execute the task Copy from filter to copy filter settings to the Find.
• Execute the task Copy from grid to copy grid settings to the Find.
• Execute the task Copy from form to copy form settings to the Find.

The order will be copied by default, one-to-one. To also include the visiblity, select Visibility in the pop-up.

If necessary, the visibility will be transformed based on the Type of the source:

• If it is editable or read-only, the visibility will be transformed to always.
• If it is hidden, the visibility will be transformed to never.

Sort columns

A default sort can be specified for each table. You can sort on one or more columns.

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Sort

1. Select on which columns sorting is allowed with the checkbox Allow sort. For example, because it concerns a large text column for which no index is defined.
2. In the column Sort order, select for each column if it should be sorted in ascending or descending order.
3. Select the checkbox Default sort if you want to group one or more columns in a grid. See Group columns.

tip

To quickly set the sort order for a selected subject based on its primary key and the table's look-up display column, execute the task Set up initial sort order . This is especially useful when you need to set up the sort order for multiple tables. The sort order is based on all primary key columns or, if a look-up display column is defined, on all but the last primary key column and the look-up display column.

Setting sort values for a table in Data

Group columns

If the checkbox Default sort is selected, you can group one or more columns in a grid.

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Sort

1. Select the checkbox Group until to mark the last column by which to group. All columns up to and including the column with Group until selected become part of the group. In the example below, persons are grouped by country, then by province, and then by city.

Windows GUI You can set grouped columns in a grid to automatically expand when a table is opened, see Expand a grouped grid by default.

Windows GUI Web GUI You can also let users dynamically group columns, see Allow grouping in a grid.

Group persons by country, province, and city

Prefilters

Introduction to prefilters

Prefilters are predefined filters that a user can select in your application.

• Windows GUI Prefilters are displayed in the ribbon or the context menu.
• Universal GUI Prefilters are displayed in the prefilter bar, possibly in the overflow menu. For more information, see also Prefilter in the Universal user interface documentation.

The Search field and some prefilters. The underlined prefilter is active

You can create prefilters for your application, and also allow users to create their own prefilters in your application, see:

• User-defined prefilters in the Universal UI
• User-defined prefilters in the Windows UI

Create a prefilter

note

If you want to add your prefilter to a prefilter group, we recommend to create the prefilter group first. See Create a prefilter group.

To create a prefilter:

menu User interface > Subjects > tab Subjects > tab Default/Variants > tab Data > tab Prefilters > tab Prefilter > tab Form

1. Enter a name for the Prefilter that you want to add to the selected table.
2. Select the Prefilter group from the drop-down list. See Create a prefilter group. As soon as a prefilter is assigned to a group, the group appears in the grid so you can drag/drop other prefilters into it.
3. Select the Prefilter type. See Prefilter type.
4. Select the Prefilter state - this defines the visibility, status, and accessibility of the prefilter in different contexts. See Default prefilter state.
5. Select an Icon for the prefilter.
6. Select the Sequence no. With this setting you can influence the order in which the prefilter appears.
7. Optional: select a keyboard shortcut that can be used to enable or disable the prefilter. Make sure that the screen does not contain multiple prefilters using the same shortcut. Also, avoid using a shortcut that is already used by the GUI, such as Ctrl + R. For more information, see Shortcuts (used by GUI's). Validations are in place to prevent you from selecting such shortcuts.
8. Optional. To show a prefilter in a Prefilter bar or a (Custom) action bar, select the same Screen area as the bar. For more information, see Screen areas.
9. Optional. Select a Custom display type.

   For example, Icon + text (> text > icon) means that the report is displayed as an icon with text. On a smaller screen with less space, it is displayed as text (the first fallback: > text). With even less space, it is displayed only as an icon (the second fallback: > icon). The overflow menu is always the last fallback type, except for the display type Hidden.

tip

You can create prefilters based on the elements of a domain. Start the task Generate prefilters based on domain elements and select the column with the domain containing the desired elements for your prefilters. Optionally, you can create a new prefilter group or choose an existing one to add the prefilters to. See also the enrichment Create prefilters based on domain elements.

Prefilter type

A prefilter can be based on a query or you can specify conditions based on columns.

menu User interface > Subjects > tab Data > tab Prefilters > tab Form

• Prefilter columns - Add the conditions in the Prefilter columns tab. Select a Column, Filter condition, and Filter value (the database value, not the translation). You can specify multiple columns for each prefilter.

• Query - Enter the query into the corresponding field. It is placed in the where clause by the GUI. Always use the alias t1 to access the current table. Smoke tests contain a validation to check the use of the alias t1.

  t1.order_date < getdate()

note

If the application is started in developer mode, you can view the resulting query in the debug screen.

Setting a prefilter with a query

Default prefilter state

A prefilter can have a different status in different contexts. The context can be the main screen, a detail screen, or a lookup screen.

To set the default prefilter state in a context:

menu User interface > Subjects > tab Data > tab Prefilters > tab Form

• The following fields are available for each context:
  • Main state
  • Detail state
  • Look-up state
• The following states are available for each context:
  • On - the prefilter is visible and active, and can be deactivated by a user.
  • On hidden - the prefilter is not visible to the user but is active.
  • On locked - the prefilter is visible and active but cannot be deactivated by a user.
  • Off - the prefilter is visible and deactivated, but can be activated by a user.
  • Off hidden - the prefilter is not visible to the user and deactivated.

You can use the following states for data authorization:

• On hidden
• On locked
• Off hidden - in combination with prefilter groups that are:

  • Mandatory - See Mandatory prefilter groups.

  • Match any (OR) - See Multiple active prefilters.

    Example

    Prefilter group color is Mandatory and multiple prefilters are allowed to be active (Match any (OR)).
    Available prefilters:

    • Red
    • Green
    • Blue (state: Off hidden)

    Result: users can see records that comply with prefilters Red, Green, or both. They cannot see or use prefilter Blue or records that comply with it.

Prefilter access control

The Main state, Detail state, and Look-up state can be set for each prefilter. See Default prefilter state.

To configure the user access to a prefilter in a specific table for a specific role:

menu Access control > Model rights > tab Tables > tab Prefilters > tab Roles

1. Select a table.

2. Select a prefilter.

3. Select a role and start the task Assign rights .

   To assign rights to prefilters for the Current object:

4. To make the prefilter visible to users assigned to this role, select the checkbox Visible.

5. If users in a user group assigned to the role are not allowed to disable the prefilter, select the checkbox Always on.

   To change the Parent rights:

6. To make the prefilter available in the parent objects, select the checkbox Make available. The affected parent objects are displayed in the field Affected objects.

tip

To inspect the filter modes of your prefilters when setting up the access control for prefilters:

• In relation to the model rights of your users: menu Access Control > Model Rights > tab Tables > tab Prefilters.
• In relation to your user roles: menu Access Control > Roles > tab Tables > tab Prefilters.

The role rights for prefilters

Examples of prefilter combinations

In the following examples, the data set that is filtered is a set of memberships that can have a certain status but also contain some additional characteristics. A record cannot have two statuses from the same membership simultaneously. For example, a record cannot be a Draft and Active, or Paid and Payment rejected, but it can be Active and Paid, because those values belong to different memberships.

The following combinations are now possible:

Multiple active prefilters allowed	Filter mode	Mandatory	Description
Yes	Match all (AND)	No	If you activate a prefilter that filters on status, for example, Draft, you will only see records with the status Draft. If you activate a second prefilter that filters on the status Active, no results will be shown. If you activate a second prefilter on a different membership, for example, Paid, all records with the status Draft that have been Paid will be shown.
Yes	Match all (AND)	Yes	If you activate a prefilter that filters on status, for example, Draft, you will only see records with the status Draft. If you activate a second prefilter that filters on the status Active, no results will be shown. If you activate a second prefilter on a different membership, for example, Paid, all records with the status Draft that have been Paid will be shown. At least one prefilter must be enabled.
Yes	Match any (OR)	No	If you activate a prefilter that filters on status, for example, Draft, you will only see records with the status Draft. If you activate a second prefilter that filters on the status Active, all records with either the status Draft or the status Active will be shown. If the prefilter Paid is also activated, the resulting set of data will include all records that are either Draft, Active, or Paid.
Yes	Match any (OR)	Yes	If you activate a prefilter that filters on status, for example, Draft, you will only see records with the status Draft. If you activate a second prefilter that filters on the status Active, all records with either the status Draft or the status Active will be shown. If the prefilter Paid is also activated, the resulting set of data will include all records that are either Draft, Active, or Paid. At least one prefilter must be enabled. This could be used if all records with the status Draft, Active, or Paid may be displayed, but records with another status, for example, Payment rejected and Removed, may not.
No	N/A	No	If multiple active prefilters are not allowed, activating one prefilter will deactivate the other one. If the prefilter Draft was activated, only records with the status Draft will be shown. However, as soon as the prefilter Active is activated, the result will only include the records with the Active status. Deactivating all prefilters in this group is possible.
No	N/A	Yes	If multiple active prefilters are not allowed, activating one prefilter will deactivate the other one. If the prefilter Draft was activated, only records with the status Draft will be shown. However, as soon as the prefilter Active is activated, the result will only include the records with the Active status. At least one prefilter must be enabled.

Prefilter groups

Create a prefilter group

Prefilters within a table can be grouped together to display them logically.

To create a prefilter group:

menu User interface > Subjects > tab Data > tab Prefilters > tab Prefilter groups > tab Form

1. Enter a name for the Prefilter group that you want to add to the selected table.

2. Optional: select the checkbox Submenu to display the report group through a submenu instead of using separators.

3. Optional: select an icon.

4. Select the Sequence no. With this setting you can influence the order in which the prefilter group appears.

5. Select whether you want to allow Multiple active prefilters.

   • If you clear this checkbox, only one of the prefilters in the group can be active at any time. They are mutually exclusive. You do not have to select a Filter mode. See Exclusive prefilter groups.
   • If you select this checkbox, multiple prefilters can be active simultaneously. Specify the desired behavior by selecting the Filter mode. See Multiple active prefilters.

6. Select whether the prefilter group is Mandatory. See Mandatory prefilter groups.

The prefilter group is now available for selection if you create or edit a prefilter.

Exclusive prefilter groups

You can set the prefilters in your prefilter group to be mutually exclusive.

menu User interface > Subjects > tab Data > tab Prefilters > tab Prefilter groups > tab Form

For example, if your prefilter group contains the prefilters Available and Not available, enabling them simultaneously makes no sense. An item cannot be available and not available simultaneously.

In that case, make sure that Multiple active prefilters is cleared in your prefilter group.

An enabled prefilter in an exclusive group can be locked or hidden, via settings in the model or rights. When this is the case, all other prefilters in this group will also be locked or hidden, as they cannot be activated.

When multiple prefilters are enabled due to settings in the model or user preferences, the GUI will only enable the prefilter with the highest order number. When multiple enabled prefilters are locked or hidden, no data will be shown.

Exclusive prefilter groups can be configured to be mandatory. The user will not be able to disable the active prefilter. If no prefilter is enabled because of model settings, no data will be shown until a prefilter in the mandatory exclusive prefilter group is activated.

See Examples of prefilter combinations.

Multiple active prefilters

menu User interface > Subjects > tab Data > tab Prefilters > tab Prefilter groups > tab Form

If you want to allow multiple prefilters in your prefilter group to be active simultaneously, select Multiple active prefilters in the prefilter group. When Multiple active prefilters is selected, you also need to specify the Filter mode.

The Filter mode defines whether the combination of prefilters is evaluated as a logical AND or as a logical OR:

• Match all (AND) means that if multiple prefilters are activated, the resulting data set will have to comply with all the selected prefilters' conditions. For example, if there are two status prefilters for new and active, all records that have both the status new and the status active will be included in the result.
• Match any (OR) means that if multiple prefilters are activated, the resulting data set will have to comply with at least one of the selected prefilters' conditions. For example, if there are two status prefilters for new and active, all the records that either have the status new or the status active will be included in the result.
  Windows GUI The Windows GUI does not support Match any (OR). If you select this option, only one prefilter can be active at any moment.

See also Examples of prefilter combinations.

Mandatory prefilter groups

When a prefilter group is mandatory, users are not allowed to deselect all the prefilters in that group. All data remains hidden if:

• The prefilter group is marked as Mandatory
• All prefilters in this group are Off. See Prefilter state.

This includes the situation where all prefilters in the group are Off by default and Hidden from the user through role permissions (see Prefilter access control). If you want users to see data, they need to be able to see and activate the prefilters or the prefilter group should not be mandatory.

To make a prefilter group mandatory:

menu User interface > Subjects > tab Data > tab Prefilters > tab Prefilter groups > tab Form

1. Select the checkbox Mandatory.

Conditional layout

Conditional layout for the Windows GUI

Windows GUI

You can use conditional layouts in grids and forms to format the font and background color of a field.

To set up a conditional layout for the Windows GUI:

menu User interface > Subjects > tab Default/Variants > tab Data > Tab Conditional layout > tab Form > tab Conditional layout

1. Select a table.
2. Enter a name for the Conditional layout.
3. To apply the layout, select the checkbox Show conditional layout.
4. Select the Column where the layout must be applied. If no column is selected, the layout is applied to the entire row.
5. In the field Apply to, select the component where the layout must be applied: Grid, Form and Edit, Grid and Form, Grid only, or Form only.
6. Select a Font and a Background color.
7. Specify the conditions under which the conditional layout must be applied. See Set up conditional layout conditions.

Set up a conditional layout for a table in the Windows GUI

For conditional layouts with help text entered, the Thinkwise platform automatically generates an overview of the conditional layouts of a subject in the help text.

Conditional layouts in the help text of the Software Factory

Conditional layout for Universal GUI

Universal GUI

In grids and forms, you can format the font and background color of fields based on specific conditions using conditional layouts. For the Universal GUI, you can set the font and background colors separately for your light and dark themes.

The following controls are not supported by the conditional layout:

• Radio button
• Signature
• Checkbox
• HTML

To set up a conditional layout for the Universal GUI:

menu User interface > Subjects > tab Default/Variants > tab Data > Tab Conditional layout > tab Form > tab Conditional layout

1. Select a table.

2. Enter a name for the Conditional layout.

3. To apply the layout, select the checkbox Show conditional layout.

4. Select the Column where the layout must be applied. If no column is selected, the layout is applied to the entire row.

5. In the field Apply to, select the component where the layout must be applied: Grid, Form and Edit, Grid and Form, Grid only, or Form only.

6. Select a Font and a Background color.

7. Optional. Go to the tab Universal to further configure the conditional layout for the Universal GUI:

   • Select for the Light theme the Background color and Font color.
   • Select for the Dark theme the Background color and Font color.
   • Select Bold or Italic for the font.

8. Specify the conditions under which the conditional layout must be applied. See Set up conditional layout conditions.

Set up a conditional layout for a table in the Universal GUI

Set up conditional layout conditions

For each conditional layout, you can specify one or more conditions that determine when the conditional layout is applied. Expression fields are allowed to model conditional layouts based on queries, for example, by checking whether a date has expired.

note

To apply conditional layouts to progress bars, the extended property FormProgressBar must be present and set to 'True'.

To set up conditional layout conditions:

menu User interface > Subjects > tab Subjects > tab Default > tab Data > tab Conditional layout > tab Conditional layout condition

1. Select the Table for which you want to set the conditional layout.

2. Select the Conditional layout for which you want to set the condition.

3. Select the Column where the layout should be applied.

4. Select zero, one, or more Conditions from the drop-down list:

   • If you set no conditions, the conditional layout is always applied.

   • If you set multiple conditions, all must be met to apply the conditional layout.

     Available conditions

     • Equal to / Not equal to
     • Contains / Does not contain
     • Starts with / Does not start with
     • Ends with / Does not end with
     • Greater than / Greater than or equal to
     • Smaller than / Smaller than or equal to
     • Between / Not between
     • Is empty / Is not empty

5. Select the Type of value for the condition, Constant or Column.

6. Select the required values or columns for the condition.

Condition setting for a specific conditional layout