Skip to main content

Dynamic Columns

Dynamic columns can be used to create new columns whose value is defined by a simple Groovy script and are available for asset-, link- and user-importers.

The dynamic columns can/must be mapped to properties just like normal columns. Each dynamic column is defined by its name and a script that returns its value on execution. The name of the script must be unique. The provided script is executed once per row and must return the value of the column.

Valid return types are: String, numeric values like double, float, int, long, or short, while boolean, Date, and Instant for multi value properties just return an Iterable or Array of the types mentioned above.

The script has access to the values of the current row via the row object (see API section below). If a script fails to execute on a certain row, the error is logged within the importer log and the value is set to null.

Examples

The name of a server should always be uppercase:

return row.getString('name').toUpperCase();

A column contains a comma separated list of names which should be split into a multi value property.

def list = row.getString('supporters').split(',');
// list now contains something like ['John Doe', 'Janie Roe', ...]
return list;

A column contains a comma separated list of active users which should be counted

if(row.isNull('active_users')){
return 0;
}else{
def list = row.getString('active_users').split(',')
return list.length;
}

API

row.getString
String row.getString(String columnName)

Groovy scripts for filter and dynamic columns have access to the row object.

To access values of other columns the following getter methods are provided. The argument columnName is a string containing the case in name of the column (as seen in the preview).

Example
  • return row.getString('fistName')+' '+row.getString('lastname') to have the full name as a new column or
  • return row.getString('ip').startsWith('127') == false to filter out local ip addresses.
  • String get(String columnName) is a simple alias to getString.

Parameters:

columnNamename of the column (as seen in the preview)

Returns:

returns the string value of the given column.
row.getBoolean
boolean getBoolean(String columnName)

interprets the column value as boolean. Numeric values are interpreted as true if they are != 0. The literals true and false are parsed to their boolean equivalent.

Example

return row.getBoolean('active') == true to import only active entities

Parameters:

columnNamename of the column (as seen in the preview)

Returns:

boolean
getNumber
double getNumber(String columnName)

returns the column value as a number of type double.

Examples:

  • return row.getNumber('height') >= 10 to skip certain small items.

Parameters:

columnNamename of the column (as seen in the preview)
getDate
Date getDate(String columnName)

returns the column value as a Date. Numeric values are interpreted as milliseconds since 1970-01-01. Strings are parsed as ISO 8601 format.

Examples:

  • return row.getDate('sold').getDay() == 1 to only import entities sold on mondays.

Parameters:

columnNamename of the column (as seen in the preview)
getRaw
Object getRaw(String columnName)

returns the value of the column without any further conversion or check. This should not be needed normally.

Note that null values must be handled explicitly with row.isNull('name of column'):

If a certain column can contain null (stands for no value) a call to getString, getNumber etc would throw an Exception. You should therefore test the value if it is null with row.isNull('name of column').

Example
// the name of an imported server might not be set in the data source
if(row.isNull('serverName')){
return 'Unnamed Server'; // this can be used as name for all unnamed servers
}else{
// now it is save to call getString
return row.getString('serverName');
}

Parameters:

columnNamename of the column (as seen in the preview)

API Helpers

The row object also provides access to some helpers.

To create a value of type Range the following helpers can be used:

createRange
Range createRange(double lowerBound, double upperBound)

Creates a range from the given lower- and upper bounds.

Example

If you want to model the allowed temperature range of a server but the data is provided as two separate columns from your data source:

def min = row.getNumber('minTemperature');
def max = row.getNumber('maxTemperature');

// return a Range from two numeric values
return row.createRange(min, max);

If you want to model the allowed temperature range of a server and the data is in a single column:

// column allowedTemperature contains range in the form "xxxxx-yyyyy", e.g. "40-90"
def value = row.getString('allowedTemperature');
def splitValues = value.split('-');

def min = Double.parseDouble(splitValues[0]);
def max = Double.parseDouble(splitValues[1]);

return row.createRange(min, max);

Parameters:

lowerBoundthe lower bound of the range
upperBoundthe upper bound of the range
createRangeOpenEnded
Range createRangeOpenEnded(double lowerBound)

Creates an open ended range from the given lower bound.

Parameters:

lowerBoundthe lower bound of the range
createRangeOpenStart
Range createRangeOpenStart(double upperBound)

Creates a range from the given upper bound.

Parameters:

upperBoundthe upper bound of the range

CSV in Filter Scripts and Dynamic Columns

When working with a row object in a Dynamic Column or Filter script, you can fetch the content of a cell and parse it at the same time. Please refer to our Scripting section.