Data

Data

In the data component, it can be determined what has to happen with searching, filtering, sorting, prefilters, and applying conditional layouts.

Filter and Search

Definitions of filter, combined filter, and search

The difference between a filter, a combined filter, and search:

• Filter: The result is filtered, which means that all other rows are removed as long as the filter is active.
• Combined filter: A combined filter is permanently visible on a screen. It can be used to filter on several fields at once. Only the rows in which at least one of the fields contains the entered filter value are displayed.
• Search: The result is marked, which means that all other rows remain visible.

In addition, prefilters are predefined filters that a user can select from the ribbon, the context menu, or a prefilter bar.

Optimizing filter and search

menu User interface > Subjects > tab Subjects > tab Default > tab Data > tab Filter and tab Search

Prefilters in these tab pages:

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

To optimize the filter and search functions:

1. Select Always for frequently used filter/search terms. They are always displayed, regardless of whether the filter/search dialog is expanded.
2. Select Never for fields that should not be filtered/searched on. For example, you can do this for fields containing binary data.
3. Select Extended for the remaining fields. They are only shown when you expand the filter/search dialog . By default, the dialog will not be expanded.
4. If necessary, drag and drop the columns to change their order.
5. If necessary, select a different search or filter condition.

It is also possible to derive the filter/search order and visibility settings. See Copy settings to filter and Copy settings to search.

Setting filter values for a table in Data

Setting search values for a table in Data

Filter conditions

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

In the Filter condition field, you can specify the default way of filtering 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

Column order in the Search tooltip

Universal GUI only

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

In your application, the tooltip for the Search field displays the columns that are used for searching. The column order is determined by 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

Combined filter

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

menu User interface > Subjects > tab Subjects > tab Default > tab Data > tab Combined filter

1. Select the In combined filter checkbox to include a column into the combined filter.

note

The combined filter is independent of whether the filter is enabled or not in menu User interface > Subjects > tab Settings > tab Permissions > checkbox Filter. If the filter is disabled there, any column allowed in a combined filter will still cause the GUI to show the combined filter component.

Add columns to a combined filter

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

To copy order and visibility settings to the search:

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

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

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

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

A default sort can be specified for each table. You can sort on one or more columns. You can indicate for each column if it should be sorted in ascending or descending order.

Additionally, you can specify on which columns sorting may and may not be allowed. For example, because it concerns a large text column for which no index is defined.

Setting sort values for a table in Data

Group columns

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

You can group one or more columns if Default sort is selected in a grid. This grouping is fixed on one or more columns.

The Group until option specifies 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.

Group persons by country, province, and city

Windows GUI Web GUI

You can also let users dynamically group columns. See: grouping in grid.

Prefilters

Prefilters are predefined filters that a user can select. Prefilters have their own name and appear in the ribbon, the context menu and possibly a prefilter bar.

menu User interface > Subjects > tab Data

Prefilters are linked to a table. Prefilters can be defined in two ways.

• Attach conditions to columns
• With the help of a query (where clause)

Prefilters can be configured individually (see Create a prefilter), and related prefilters can be combined into groups (see Prefilter groups).

You can inspect the prefilters in your model in menu Models > Model content > tab Tables. Select a table to view its prefilters on tab Prefilters.

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

1. Select the table to which you want to add the prefilter from the list.
2. In the form, select the tab Prefilters.
3. Select Add to create a new prefilter for the selected table.
4. Give your Prefilter a logical name.
5. Select the Prefilter group from the drop-down list. See Prefilter groups.
6. Select the Prefilter type. See Prefilter type.
7. Select the Prefilter state - this defines the visibility, status, and accessibility of the prefilter in different contexts. See Prefilter state.
8. Select an Icon for the prefilter.
9. Select the Sequence no. With this setting you can influence the order in which prefilters appear. See Prefilter icon and sequence number.
10. Optional: select a keyboard shortcut which can be used to turn the prefilter on or off. Make sure that the screen does not contain multiple prefilters using the same shortcut. Also do not use 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.
11. Save the prefilter.

Prefilter groups

Prefilters within a table can be grouped together to display them logically. Both the groups and the prefilters within a group can be put into a sequence.

menu User interface > Subjects > tab Data > tab Form

You can customize the behavior of the entire prefilter group. See Create a prefilter group.

When prefilters are grouped, they can be displayed in two ways in the context menu:

• Via a sub-menu in the context menu.
• Through the use of separators in the context menu.

Only separators are used in the ribbon, which is also the case for the sub-menus.

You can inspect the prefilter groups in your model in menu Models > Model content > tab Tables. Select a table to view its prefilter groups on tab Prefilter groups.

Create a prefilter group

To create a prefilter group:

menu User interface > Subjects > tab Data > tab Form > lookup Prefilter group

1. Select the table to which you want to add a prefilter group.

2. Select the Prefilter groups lookup.

3. Click Add .

4. Enter a name for the Prefilter group.

5. Optionally, select an icon.

6. 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.

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

8. Save the prefilter group.

The prefilter group is now available for selection if you create or edit a prefilter. Continue with Add prefilter to a prefilter group.

Add a prefilter to a prefilter group

To add a prefilter to a prefilter group:

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

1. Edit the prefilter.
2. Select the Prefilter group from the drop-down list.
3. Save the prefilter.

Exclusive prefilter groups

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

menu User interface > Subjects > tab Data > tab Form > lookup Prefilter group

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 turn off 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 Form > lookup Prefilter group

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 Examples of prefilter combinations.

Mandatory prefilter groups

menu User interface > Subjects > tab Data > tab Form > lookup Prefilter group

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

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

• If you select Query, enter it into the corresponding field. The query is placed in the where-clause by the GUI. The current table can be accessed using the alias t1.

  t1.order_date < getdate()

• If you select 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.

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

Prefilter state

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

It is possible to specify what the default Main state, Detail state, and Look-up state of a prefilter should be (off, off hidden, on, on hidden, on locked). The last two are actually a way to add authorization.

Prefilter icon and sequence number

menu User interface > Subjects > tab Data > tab Form

You can add an icon to the prefilter in order to present it more intuitively to the user. The sequence number determines the sequence if the table has several prefilters.

Access control

You can indicate for each role that a prefilter is intended to authorize data.

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

If you select the Always on checkbox, the prefilter cannot be disabled by users assigned to this role.

Setting the role rights for prefilters

You can inspect the filter modes of your prefilters when setting up your access control:

• 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.

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.

Conditional layout

Conditional layout for Windows and Web GUI

Windows GUI Web GUI

In grids and forms, the font and background color of fields can be formatted and adjusted based on specific conditions using conditional layouts.

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

1. Select a Font and Background.
2. To apply where the layout is applied, select the Show conditional layout box.
3. Select the Column the layout should be applied to. When no column is selected, the layout is applied to the entire row.
4. In field Apply to, select the component the layout should be applied to: Grid, Form and Edit, Grid and Form, Grid only, or Form only.

A conditional layout contains zero or more conditions that determine when the layout is applied.

Set conditional layout for a table

The Thinkwise platform will automatically generate an overview of the conditional layouts of a subject in the help text (based on the visible columns for the user) for conditional layouts that have a help text entered.

Conditional layouts in the help text of the Software Factory

note

Expression fields can be used to model conditional layouts based on queries, for example, by checking whether a date has expired.

You can apply conditional layouts to progress bars. This requires the extended property FormProgressBar to be present and set to 'True'.

Conditional layout for Universal GUI

Universal GUI

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

The Thinkwise Universal GUI user interface uses different conditional layout settings than the classic user interfaces. Instead of choosing a font, containing the font face, size, color and style, the text can be formatted and styled independently of the chosen font using separate color and style settings for light and dark themes.

The following controls are not supported by the conditional layout:

• Radiobutton
• Signature
• Checkbox
• HTML.

Conditional layouts for the Universal GUI

Conditional layout conditions

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

In the Condition field, you can specify the condition under which the conditional layout will be applied.

• If you set no conditions, the conditional layout will always be applied.
• If you set multiple conditions, all of them must be met for the conditional layout to be applied.

You can use the following 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

Condition setting for a specific conditional layout