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. ANone
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 |
---|---|
| Returns 1 (i.e. true) if |
| Conversion of a number or string into an integer number. Similar to standard Python function |
| Conversion of a number or string into an integer number. Returns |
| Conversion of a number or string into a floating-point number. Similar to standard Python function |
| Conversion of a number or string into a floating-point number. Returns |
| Return |
| Calculates the sum over all numbers in |
| Returns the result of |
| Calculates the mean over all numbers in |
| Returns the result of |
| Calculates the standard deviation over all numbers in |
| Returns the result of |
| Calculates the median over all numbers in |
| Returns the result of |
| Finds the minimum of all numbers in |
| Returns the result of |
| Finds the maximum of all numbers in |
| Returns the result of |
| 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