The module Dynamic items is the main module type for Wiser. As the name indicated, it's a module that can load data dynamically, based on its configuration. You can add as many modules of this type as you like, each with different configuration and data. This module has two different modes:

1. Treeview: This module shows a tree structure with data, which can have structures with multiple layers. It is ideal for smaller amounts of data, or categorized data.
2. Gridview: This module shows a grid with data, which can be filtered and sorted. It is ideal for large amounts of data where the structure matters less.

Whichever mode you use, the items that are opened in these modules are fully dynamic. Wiser can have an unlimited amount of entity types and each entity type can have an unlimited amount of tabs, groups and fields. These entities can be different for each tenant in Wiser (e.g. a product in tenant A can have completely different fields than a product in tenant B). All of this can be configured via the Admin module, or directly in the database via tables such as wiser_entity, wiser_entityproperty and wiser_link. Entities can have all kinds of different fields, such as text fields, date fields, file fields, etc. These fields can be configured in the wiser_entityproperty table. The values of these fields are stored in the wiser_itemdetail table. Each item in Wiser is stored in the wiser_item table, which contains the ID and entity type of that item. For more information, see the Entity properties document.

In the options field of the wiser_module table you can enter a JSON object with all the specific setting for either a Gridview or a Treeview module. These properties are basic, per module type below there is an additional list of properties for each specific module.

A gridview module consists primarily of a table containing data. Besides that, it's possible to show a detail pane on the left, right, top or bottom side of the screen. This detail pane can be used to show details of the selected item in the grid, or to show the details of a pre-defined item. This is done by setting the informationBlock property in the gridViewSettings object. Here is a schematic that shows some of these options:

The table of data in the gridview module should be populated with data from an SQL query. This query accepts certain variables, which are replaced by Wiser when the query is executed. The most important variables are {limit} and {sort}. These variables are replaced by Wiser with the appropriate SQL statements for filtering, pagination and sorting. You can read more about this in the sections below.

It's also possible to add custom buttons to the grid, either in the toolbar in the header of the grid, or in a column of each row. These can be general actions such as creating a new item, or other actions such as executing custom SQL queries. Those actions can use data from the selected row(s) in the grid. You can read more about these actions in the action buttons information.

Certain properties of the options column of the table wiser_module are specific to the gridview module. These properties are only used when the gridViewMode property is set to true. The following table shows all properties that can be used in the options column of the wiser_module table:

This is required for gridview modules, they will not show any data when this column is empty. This query must have at least the columns id, encrypted_id and entity_type (if you want to be able to access/edit items), which will then contain the ID and encrypted ID of each item. You can have the ID automatically encrypted by adding encrypt_withdate at the end of the column alias. You will then get encrypted_id_encrypt_withdate, for example.

Items in a grid can be opened by double-clicking, or by using the button in the rightmost column. To do this, however, there must be a column called encrypted_id in the query results. If it is not there, the functionality to open items will not work and the button for it will not be visible. This functionality can also be disabled by setting the disableOpeningOfItems option to true.

If you want the name of an item (or some other value) to be shown in the title of the popup when that item is opened, you must make sure that the query has a column called title containing the value to be shown. All other columns are entirely up to you.

Furthermore, you have to put the variables {limit} and {sort} in the right places in the query, otherwise sorting and virtual scrolling / pagination will not work. These variables are then replaced by Wiser (e.g. LIMIT 0, 100 and ORDER BY i.id ASC).

For serverside virtual scrolling/paging to work, it is necessary to know in advance the total number of items to be displayed. For this, you can enter a separate query in this column. That query should then do a 'COUNT' in the 'SELECT' and return that as the only value. This query needs to take any filters into account, so that it will always return the correct number of items. The query should also not contain any LIMIT or ORDER BY statements, as these are not needed in a count query. This query can be left empty, in which case Wiser will automatically create a count query for you. This is done by simply adding a COUNT(*) to the custom_query, and removing all other columns. This is done by Wiser, so you don't have to worry about it. However, this is not always optimal, and therefore it is better to create your own count query in most cases.

Example query, without filtering:

SELECT COUNT(*)
FROM wiser_item
WHERE entity_type = 'customer'

To enable users to filter the results of a grid view module, you'll have to add something to your queries for that module. This can be done in two different ways:

1. Use dynamic filtering functionality from Wiser, this will generate JOINS and WHERE statements dynamically/automatically for your query.
2. The second option is to add GCL if statements to your query, to handle the filtering yourself.

Dynamic filtering works great for filtering on wiser_item and wiser_itemdetail fields, and it supports having multiple filters on a single field (e.g. so that users can filter on a date range).

To make this work, you first should add a fieldMappings array to the options column of the wiser_module table. This object is to let Wiser know how and where to get each column value from. This is needed for mapping columns of your grid to properties of wiser_item and/or wiser_itemdetail, so that Wiser knows how to build the SQL query. For details about the objects in that array, see the additional options of a gridview module section.

Secondly, you need to add some variables to the query, to let Wiser know where to put the filtering. The variables you need to add are:

• {filters}: This is where Wiser will add the JOINs for the filters on wiser_itemdetail tables.
• {where}: This is where Wiser will add the WHERE statement for the filters on wiser_item tables.
• {hasWhere}: This is a variable that will be replaced with 1 if there are any filters on wiser_item, or with nothing if there are no filters on wiser_item.

Example query, with filtering, sorting and paging:

SELECT x,y,z 
FROM wiser_item AS i 
{filters}
WHERE i.entity_type = 'x' 
[if({hasWhere}=1)]AND ({where})[endif]
{sort}
{limit}

To actually allow users to select multiple filter conditions on a single column, you have to enable that for those columns in the columns array of the gridViewSettings. This is done by setting the filterable.extra property to true. This will allow users to select multiple filter conditions on a single column, and they can choose whether they want an "and" or an "or" filter. To do this, set the filterable.extra option to true in the column settings. It's also possible to change the allowed operators for each column, though Kendo decides them automatically based on the value type of each column. You can find more information about that in the Kendo documentation.

Here is an example of a column with the filterable.extra property set to true:

{
    "field": "birthdate_customer",
    "title": "Birthdate",
    "filterable": {
        "extra": true,
        "operators": {
            "string": {
                "eq": "Is gelijk aan",
                "lte": "Kleiner dan of gelijk aan",
                "gte": "Groter dan of gelijk aan"
            }
        }
    }
}

If you want to have more control of the JOIN and WHERE clauses of your query, you can do the manual way. But keep in mind that this way does not support multiple filters on a single field (e.g. no date ranges).

To use this method, you can use specific variable suffixes in combination with GCL if statements. You can use the following suffixes in your query:

• _has_filter: This will be replaced with 1 if the user has set a filter for that column, or with nothing if no filter is set.
• _filter: This will be replaced with the value of the filter, and the appropriate comparison operator (e.g. =, LIKE, etc.) will be added automatically.

Example of a custom JOIN clause with filtering:

[if({name_has_filter}!1)]LEFT[endif] JOIN wiser_itemdetail AS name ON name.item_id = i.id AND name.`key` = 'name' [if({name_hasfilter}=1)]AND name.value {name_filter}[endif]

For count queries, you usually don't need to add the JOIN clauses when there are no filters, so you can make the entire JOIN clause optional, like this:

[if({name_has_filter}=1)]JOIN wiser_itemdetail AS name ON name.item_id = i.id AND name.`key` = 'name' AND name.value {name_filter}[endif]

As you can see in the above examples, no conditions like = or LIKE are added to the query and no quotes are added around {name_filter}. That is because Wiser will do all that automatically, based on the filter operator that the user has selected.

When in Gridview mode, the module can be set up to show an information block. This block can be used to show details of a selected item in the grid, or to show a new/empty item. This is useful when you want to show details of an item in the grid, but you also want to allow users to create a new item without having to open a new window. For details about this object, see the additional options of a gridview module section.

Example settings of grid with information block:

{
  "gridViewMode": true,
  "gridViewSettings": {
    "informationBlock": {
      "position": "bottom",
      "height": "800px",
      "openGridItemsInBlock": true,
      "initialItem": {
        "itemId": 1,
        "entityType": "relatie",
        "readOnly": false
      }
    },
    "dataSelectorId": 7,
    "hideCommandColumn": false,
    "toolbar": {
      "hideExportButton": true
    },
    "columns": [
      {"field": "id", "title": "ID"},
      {"field": "firstname", "title": "First name"},
      {"field": "lastname", "title": "Last name"}
    ]
  }
}

The treeview module always shows a screen in 2 parts, on the left side a treeview and on the right side the opened item in the treeview. This module is ideally suited for data that is hierarchical and does not contain thousands of items (e.g. for a multilayer category/product classification or for managing data that is divided into several subfolders). It is not suitable for managing thousands of customer items, for example, unless they are divided into, say, dozens of branches with several dozen to hundreds of customers per branch. In short; consider carefully when a treeview module is desired and useful. One does not exclude the other. Data managed using a treeview or a grid module can also be managed using the other type. It is a matter of setup. Sometimes you want to offer both for the same type of data but for specific purposes you choose one or the other module.