Introduction

Custom table columns and filters allow a power user to create additional columns and filters by an expression. Both are row-based. That is, only the data of the row are used for calculation. Filters can be parametrized. The user who applies a parametrized filter is asked for values of the parameters. by introducing them in the expression as follows. Every table has it's own set of custom columns and filters.

Required User Roles

Custom table columns and filters can be created by a user with Instance Admin or Power User role.
Private columns and filters are visible only to the owner (registrator) and Instance Admins. Public columns and filters are visible and can be used by all the users.
Instance Admins are allowed to edit and delete all columns and filters in their instance. Power Users can edit and delete only the columns and filters that they registered.

Columns and filters are Python expressions which evaluate to a boolean value. The rule is that if the filter expression evaluates to true for a given table row, it will be kept. Otherwise - the record will be filtered out.

Expression Syntax

The syntax of an expression has to follow the syntax of a Python expression.
In addition

  • data and meta-data of a table row can be accessed,
  • specific functions including mathematical functions are available,
  • parameters can be specified.

Accessing Row Data and Meta-Data

The row object allows to access cell data of a row and meta-data of the columns.

Get Data by Column ID

This is a function to access the value of a certain cell in a table row.

Syntax

row.col(<column ID>)

where <column ID> is a string.
The return value is an arbitrary object.

Semantics

All columns have a unique ID which is usually quite different from the column label. When editing an expression you can click on the button Insert Columns to get a dialog showing for all columns their label and ID. Choose the appropriate column and click 'Ok' to use it in the expression.

This function returns a value which is often a string. Sometimes the data type is known. For example, if the column is a property of data type INTEGER or REAL the returned value will be number.

Examples

row.col('CODE')
row.col('property-USER-COLOR')

Get Data by Column ID for Vocabulary Columns

For columns that represent vocabularies the row.col(<column ID>) method returns values in a following format:

VOCABULARY_TERM_LABEL [VOCABULARY_TERM_CODE]

For example for a vocabulary column TEST_VOCABULARY_COLUMN and a term with code=TEST_CODE and label=TEST_LABEL:

row.col('TEST_VOCABULARY_COLUMN') // returns 'TEST_LABEL [TEST_CODE]'

To get just a vocabulary term code or label one can use row.colAsVocabulary(<column ID>) method that returns a VocabularyColumn object with code() and label() methods. For example:

row.colAsVocabulary('TEST_VOCABULARY_COLUMN').code() // returns 'TEST_CODE'

row.colAsVocabulary('TEST_VOCABULARY_COLUMN').label() // returns 'TEST_LABEL'

Please note that the row.colAsVocabulary(<column ID>) method will return 'None' value if the term hasn't been chosen. 

Query Meta-Data of Columns

This function retrieves column definitions. A column is defined by

  • a unique ID (column ID) and
  • a set of key-value pairs (column properties).

Syntax

row.colDefs(<property key>)

where <property key> is a string.
An array of ColumnDefinition objects are returned.

Semantics

This function retrieves all columns which have a property with key specified by the <property key>. If <property key> = None all column definitions are returned.

A ColumnDefinition has two functions:

  • id(): Returns the column ID as a string.
  • property(<property key>: Returns the property value for the specified key (<property key> is a string) as a string. A None is returned if the column definition doesn't have the requested property.

Examples

len(row.colDefs(None))

Semantics: Returns the total number of columns.

row.colDefs('PLASMA_PERCENTAGE')[-1].id()

Semantics: Return the column ID of the last column with property PLASMA_PERCENTAGE.

row.colDefs('PH')[0].property('PH')

Semantics: Return the property value of the first column with property PH.

Get Data by Column Property

This function returns an array of those cells of a row where the corresponding column has a property of specified value.

Syntax

row.cols(<property key>, <property value>)

where <property key> and <property value> are strings. An array of objects is returned.

Semantics

This function retrieves all column definitions which have property <property key> with value <property value>. The function returns the corresponding cell values as an array.

Examples

avg(row.cols('PLASMA_PERCENTAGE', '100'))

Semantics: Calculate the mean value over all columns with property PLASMA_PERCENTAGE equals 100.

Get Data Grouped by Column Property

This function returns cell values grouped by the different values of a column property.

Syntax

row.colsGroupedBy(<property key>)

where <property key> is a string. An array of ColumnGroup objects are returned.

Semantics

First, all columns having the specified property are retrieved. Second, these columns are grouped by the different values of this property. Finally, for each group a ColumnGroup is created which contains the actual property value (accessible by propertyValue()) and all corresponding cell values as an array (accessible by values()).

Examples

[g.propertyValue() for g in row.colsGroupedBy('PH')]

Semantics: Returns an array of the distinct property values of all columns with property PH.

len(row.colsGroupedBy('PLASMA_PERCENTAGE')[0].values())

Semantics: Returns the number of columns in the first group of columns with property PLASMA_PERCENTAGE.

Available Functions

Standard Python functions and Java Math functions and constants can be used. For strings also standard Python string functions are available. In addition the following functions have been defined:

Function

Description

matches(<regular expression>, <string>)

Returns 1 (i.e. true) if <string> matches the <regular expression>. Otherwise 0 (i.e. false) is returned. Syntax and semantics of the Java regular expression is used.

int(<x>)

Conversion of a number or string into an integer number. Similar to standard Python function int() but more robust in cases where the argument is either None, an empty string, or a string containing only white spaces. In all these cases the smallest possible integer is returned. Note: This function overloads the same Python function.

toInt(<x>, <default value>)

Conversion of a number or string into an integer number. Returns <default value> if <x> is either None, an empty string, or a string containing only white spaces.

float(<x>)

Conversion of a number or string into a floating-point number. Similar to standard Python function float() but more robust in cases where the argument is either None, an empty string, or a string containing only white spaces. In all these cases NaN ('not a number') is returned so that all comparison operations will return False. Note: This function overloads the same Python function.

toFloat(<x>, <default value>)

Conversion of a number or string into a floating-point number. Returns <default value> if <x> is either None, an empty string, or a string containing only white spaces.

choose(<condition>, <then>, <else>)

Return <then> only if <condition> is True. Otherwise <else> is returned.

sum(<array>)

Calculates the sum over all numbers in <array>. If an array element is a string it will be converted into a floating-point number. An array element which is either None, an empty string, or a string containing only white spaces will be ignored.

sumOrDefault(<array>, <double>)

Returns the result of sum if the array is non-empty; returns the provided <double> if the array is empty.

avg(<array>)

Calculates the mean over all numbers in <array>. If an array element is a string it will be converted into a floating-point number. An array element which is either None, an empty string, or a string containing only white spaces will be ignored.

avgOrDefault(<array>, <double>)

Returns the result of avg if the array is non-empty; returns the provided <double> if the array is empty.

stdev(<array>)

Calculates the standard deviation over all numbers in <array>. If an array element is a string it will be converted into a floating-point number. An array element which is either None, an empty string, or a string containing only white spaces will be ignored.

stdevOrDefault(<array>, <double>)

Returns the result of stdev if the array is non-empty; returns the provided <double> if the array is empty.

median(<array>)

Calculates the median over all numbers in <array>. If an array element is a string it will be converted into a floating-point number. An array elements which is either None, an empty string, or a string containing only white spaces will be ignored.

medianOrDefault(<array>, <double>)

Returns the result of median if the array is non-empty; returns the provided <double> if the array is empty.

min(<array>)

Finds the minimum of all numbers in <array>. If an array element is a string it will be converted into a floating-point number. An array elements which is either None, an empty string, or a string containing only white spaces will be ignored. Note: This function overloads the same Python and Java Math function.

minOrDefault(<array>, <double>)

Returns the result of min if the array is non-empty; returns the provided <double> if the array is empty.

max(<array>)

Finds the maximum of all numbers in <array>. If an array element is a string it will be converted into a floating-point number. An array elements which is either None, an empty string, or a string containing only white spaces will be ignored. Note: This function overloads the same Python and Java Math function.

maxOrDefault(<array>, <double>)

Returns the result of max if the array is non-empty; returns the provided <double> if the array is empty.

evalXPath(<xpath>, <xml>)

Evaluates the specified XPath expression onto the specified XML. For examples of usage see XML Properties.

createLink(<anchor>, <url>)
Creates a clickable link visible as specified by anchor string, leading to given url.
currentDate()
Returns the current date as a java.util.Date
getDateAsString(<date>)
Returns the specified <date> as a string in the following format: YYYY-MM-DD HH:MM:SS

Parameters

Expressions can be parameterized by introducing place holders in the expression text. These place holder will be filled by the user before the expression is evaluated. The syntax for a parameter is as follows

${<parameter name>}

Examples:

row.col('property-USER-TEMPERATURE') >= ${minimum} and row.col('property-USER-TEMPERATURE') <= ${maximum}

Semantics: Returns True if the value of the column property-USER-TEMPERATURE is in the range specified by the parameters minimum and maximum.

 

Complex examples

row.col('property-USER-SEQUENCER') == 'QGF HiSeq2000 (Rua) [SN792]' and row.col('property-USER-ILLUMINA_PIPELINE_VERSION') == 'RTA 1.13.48 [1.13.48]' and int(row.col('property-USER-CYCLES_REQUESTED_BY_CUSTOMER')) >= int('100')

Integer values are in this context treated like strings and therefore to be casted.

 

getDateAsString(row.col('REGISTRATION_DATE')) >= '2013-03-08'
getDateAsString(row.col('REGISTRATION_DATE')) > '${start}' and getDateAsString(row.col('REGISTRATION_DATE')) < '${end}'

Please note the additional quotation marks around the start and end variable