Tables

This feature of the Asset Manager is only available if the logged-in user has the Administrator role.

Tables within the Asset Manager are used to enter, edit, and delete data. Once a table has been created, every user initially has full access to the records until other permissions are granted.

Rules

When working with tables, there are a few rules to keep in mind:

Table and column names must not begin with an underscore.
Once a table has been created, it cannot be renamed.
The name and data type of a column cannot be changed after the fact.
The maximum length, total number of decimal places, and number of decimal digits for columns cannot be changed after the fact.

In general, when making subsequent changes to table definitions, you should always consider that users may have already entered data based on the existing definition, which could become invalid with a modified definition. This applies, for example, to minimum and maximum values, regular expressions, drop-down lists, default values, and date formats. It is the administrator’s responsibility to consider potential impacts in advance. The Asset Manager itself accepts entries made before the table definition was changed. However, these can only be modified within the scope of the new definition.

Data types

A table consists of one or more columns. Columns can contain different data types:

For the detailed view of a data type, please click on the respective column header.

Textbox

Description

Used to enter single-line text, such as a name, street, ZIP code, or city.

SQL data type

NVARCHAR [(n)]

Value range

The maximum length can be specified as 1–4000.

A common misconception is the assumption that in NVARCHAR(n), the n defines the number of characters. In NVARCHAR(n), however, n defines the length of the string in byte pairs (0–4,000). n never defines the number of characters that can be stored.

This misunderstanding arises because when using characters defined in the Unicode range 0–65,535, one character can be stored per byte pair. In higher Unicode ranges (65,536–1,114,111), however, a single character can use two byte pairs.

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Maximum Length: 1 - 4000, Default: 50
Regular Expression: Must be satisfied during input if specified; internally, a regex.
Default Value: The default value for this field.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Multiline Textbox

Description

Used to store multi-line textboxes, such as a description of something. This field can also contain URIs; when clicked, the links are passed to a browser and displayed there.

SQL data type

NVARCHAR [(MAX)]

Value range

Up to 2 GB of data (more of a theoretical limit).

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Default Value: The default value for this field.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Date Picker

Description

Used to enter date values, such as a birthday.

SQL data type

DATETIME

Value range

01.01.1753 - 31.12.9999

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Min Value: The smallest value that can be entered.
Max Value: The largest value that can be entered.
Default Value: The default value for this field.
Custom Format: Defines the formatting for display in the grid.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Numeric Field (int)

Description

Used to enter negative and positive integers within the specified range.

SQL data type

INT

Value range

-2³¹ (-2.147.483.648) - 2³¹-1 (2.147.483.647)

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Min Value: The smallest value that can be entered.
Max Value: The largest value that can be entered.
Default Value: The default value for this field.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Numeric Field (big int)

Description

Used to store negative and positive integers within the specified range. This data type is intended for use when integer values might exceed the range supported by the int data type.

SQL data type

BIGINT

Value range

-2⁶³ (-9.223.372.036.854.775.808) - 2⁶³-1 (9.223.372.036.854.775.807)

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Min Value: The smallest value that can be entered.
Max Value: The largest value that can be entered.
Default Value: The default value for this field.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Numeric Field (Decimal)

Description

Used to enter decimal numbers, such as prices, distances, sizes, or weights.

SQL data type

DECIMAL[ (p[ ,s] )]

Value range

With a maximum total number of decimal digits (38) and no decimal places (0), valid values range from -10³⁸ +1 to 10³⁸ - 1

The total number of decimal digits (precision) is the number of all digits in a number. The number of decimal places indicates the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a total of 5 decimal digits and 2 decimal places. More information.

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Precision: The total number of digits in a number.
Numeric Scale: The number of digits to the right of the decimal point in a number.
Min Value: The smallest value that can be entered.
Max Value: The largest value that can be entered.
Default Value: The default value for this field.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Dropdown List (values)

Description

Defines a list of static values from which one can be selected, e.g., yes/no, purchase/lease/financing, or all federal states: Baden-Württemberg/[...]/Thuringia.

SQL data type

NVARCHAR [(MAX)]

Value

Bis zu 2 GB Daten (eher theoretisch).

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Default Value: The default value for this field.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Dropdown List (SQL)

Description

Defines a list of values returned by an SQL query, from which one can be selected, such as all inventoried computers or all manufacturers.

SQL data type

NVARCHAR [(MAX)]

Value range

Up to 2 GB of data (more of a theoretical limit).

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Query: An SQL script that returns a column from which a value can be selected.
Default Value: The default value for this field.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Dropdown List (SQL MC)

Description

Defines a list of values provided via an SQL query, from which multiple items can be selected, such as all cities in Germany or the users of a license.

SQL data type

NVARCHAR [(MAX)]

Value range

Up to 2 GB of data (more of a theoretical limit).

Input mask

Column Name: The name of the column (required).
Column Postfix: Can always be left blank. Included only for compatibility with the previous version.
Query: An SQL script that returns a column from which multiple values can be selected.
Default Value: The default value for this field.
Is Required: If required, a value must be specified. The field is marked in red.
Read-Only: Defines whether the field is read-only.
Is Visible: Specifies whether the field is visible to the user.

Creating Tables

An editor is available for creating and editing tables:

The following video shows how to create a table with columns:

Tables with History

If there is a need to document the lifecycle of a data record, a table can be equipped with a history function.

This function can be enabled once when creating a table and cannot be changed afterward. This also applies to existing tables that were created without history.

To enable the history table feature, the Enable Table History option must be selected in the settings.

For tables with the history function enabled, a second table is maintained in the background alongside the original table. This table not only contains all columns of the original table but also two additional columns that record the date of the last change and the type of the last action (INSERT, UPDATE, DELETE).

Naming convention: If a table named myTable is created, the name of the history table is myTable_History.

Tables with history can—just like regular tables—be modified later, provided the above rules are followed.

Before using the history function, you should carefully consider whether this feature is truly necessary. Based on the lifecycle of a data record, it is created once, modified n times, and deleted once. If you assume only one modification per record for 100,000 records, this results in 300,000 records in the history table. The respective administrator is responsible for deleting the records in the history tables.

The following video shows how to create a table with a history function:

Tables with Variables and Filtering

We already learned about variables in the Queries chapter. They are used to filter the requested data in advance based on a condition, so that not all data is returned every time. This also works with tables by creating a variable and referencing it within the WHERE clause:

However, variables within tables are also required when the table is referenced via a drill-down. This reference can only be set up if the table has a variable.

After selecting a column, you can automatically create a variable via the context menu using Create Variable.

The following video demonstrates how to use variables and filters in conjunction with a table:

Importing Data

Instead of creating individual records manually, you can use the Asset Manager to import data from sources such as Excel spreadsheets. The individual columns of a data record must be separated by a horizontal tab (➜ = 0x09), and each line must end with a CR/LF (↲ = 0x0D, 0x0A), e.g.:

Value1 ➜ Value2 ➜ Value3 ↲

There are two options available for data import:

Data with a header

Data can include a header row specifying the names of the columns to be imported. In this case, the columns do not need to be listed in the order they appear in the table. Additionally, the columns “Last Update” (Last edited date) and “Last User” (Last edited by) may also be included, as they will be recognized and filtered out.

Column3 ➜ Column2 ➜ Column1 ➜ Last Update ➜ Last User ↲
Value3 ➜ Value2 ➜ Value1➜ 05.12.2022 09:37 ➜ Domain\User ↲

Importing data with a header is the preferred method because it prevents confusion between first and last names or ZIP codes and cities, etc.

Data without a header

The columns of data without a header must be listed in the same order as they appear in the table; otherwise, a valid assignment may not be possible. Additionally, the fields for Last Update and Last User must not be included.

Value1 ➜ Value2 ➜ Value3 ↲

Unique Column

When importing data, you can specify how existing records are handled. To do this, select a column under Unique Column in the import dialog. The values in this column are used to match the imported records to records in the target table, for example by name or asset number.

If the target table already contains a record with the same value in this column, that record is handled according to the selected import mode. Otherwise, a new record is created.

The following requirements apply to the unique column:

The column must be included in the import data.
Its value must be populated in every record being imported.
Each value may occur only once within the import data.
The selected column must also uniquely identify records in the target table.

If no unique column is selected, the import data is not matched against existing records. Each imported record is created as a new record. The Import Mode selection is disabled in this case.

Import Mode

Once a unique column has been selected, four import modes are available. In the descriptions below, source refers to the data being imported. The target is the table receiving the data. The import mode can be selected independently of whether the data was provided with or without a header row.

In all four modes, records without a matching record in the target are created as new records, provided that the data meets the table's requirements.

1. Do Not Overwrite Existing Records in the Target

This is the default setting. Existing records remain completely unchanged. Empty fields in these records are not filled either. Only records whose unique value does not yet exist in the target are imported.

2. Overwrite Existing Records in the Target

For existing records, the source values replace the current values in the corresponding target fields.

Empty source fields are also imported. A previously populated target field is therefore cleared if the corresponding source field is empty.

3. Fill Empty Fields in the Target During Import

For existing records, only empty target fields are filled with populated source values. Target fields that already contain a value retain their current value, even if the source contains a different value.

If the corresponding source field is also empty, the current target value is preserved.

4. Do Not Import Empty Fields from the Source

For existing records, only populated source fields are imported. These values overwrite the corresponding target fields even if those fields already contain a value.

Empty source fields are omitted. The corresponding target fields retain their current values.

Empty source fields are also omitted when new records are created. Any database default values defined for these fields apply. If no such default exists, the field remains empty if the database allows this. If the database requires a value, that value must be supplied before the record can be created.

Empty Fields and Required Values

For text fields, a missing value (NULL) and an empty string ("") are considered empty. For other data types, a missing value is considered empty. A numeric value such as 0 is an actual value and is not treated as empty.

The way an import mode handles empty fields is separate from the validation of required values:

In the first three import modes, imported columns defined as required must already be populated in the source. An existing target value does not satisfy this source validation. This also applies when filling empty target fields.
In Do Not Import Empty Fields from the Source mode, empty source fields are also excluded from this required-field validation. When new records are created, the imported values, together with any database default values, must still meet the database's requirements.
The selected unique column must always be populated, regardless of the import mode.

Example of the Four Import Modes

In the following example, Name is selected as the unique column. The record PC-001 already exists in the target. Department and Notes are not required fields. The word empty indicates an empty field and is not text to be imported.

Existing record and data to be imported
Data	Name	Location	Department	Notes
Existing record in the target	PC-001	Berlin	empty	Existing device
Data to be imported from the source	PC-001	Hamburg	Purchasing	empty

Starting from this existing record, the selected import mode produces one of the following results:

Result for PC-001 after the import
Import Mode	Name	Location	Department	Notes
Do Not Overwrite Existing Records in the Target	PC-001	Berlin	empty	Existing device
Overwrite Existing Records in the Target	PC-001	Hamburg	Purchasing	empty
Fill Empty Fields in the Target During Import	PC-001	Berlin	Purchasing	Existing device
Do Not Import Empty Fields from the Source	PC-001	Hamburg	Purchasing	Existing device

The following video demonstrates the import of data with and without a header.