Calculation
Calculation related column widgets.

Overview

You can perform related calculations using the widget in this category.

Formula

Use this widget to display value calculated based on a formula. The formula shall be defined in the Default Value of the column.
Readonly view:
Edit view:
Not supported
Form view:
Not supported

Configuration

{
"columns": [ # (optional),
{"sid":"xxx", "name":"xxx"}, # The column sid of ForeignKey, ForeignKeys, NestedFields, optional
],
"queries": [ # (optional)
{
"name": "xxx",
"table": {"sid":"xxx", "name":"xxx"},
"query": "name=xxx"
}
],
"formula": "", # (required), the formula, see the Syntax below
# data[column_sid], value of a column of current row
# data[row_sid__column_sid], value of column of a fixed row
# data[`${data['column__sid'].sid}`__column_sid]
# data[column1_sid__column2_sid], column1_sid, the ForeignKey column listed in rows;
# count[table_sid__column_sid], the count value
"format": {}, # (optional), the number in the wNumb format.
# Learn more at https://refreshless.com/wnumb/
"should_auto_update": false, # (optional), default to false.
# If true, the widget will perform live calculation
# If false, the user have to click reload to update the value
"should_auto_update_when_empty": true, # (optional), default to true.
# If true, the widget will perform live calculation when the data is empty
# If false, the results will not be calculated on default
}

Syntax

data[Field 1] + data[Field 2] + data[Source column sid__Target solumn sid]
Wheres, Field is the SID of a column. For example:
  • data[8adg0a40x52520ejotyDI] + data[69be0a40x52508chmrwBG]
  • data[8adg0a40x52520ejotyDI].substring(1,2)
  • data[8adg0a40x52520ejotyDI].substring(1,3) == "ATCG" ? "Good" : "Bad"
  • data[8adg0a40x52520ejotyDI__69be0a40x52508chmrwBG].substring(1,2)

Operator

  • + Addition, a + b
  • - Subtraction,
  • * Multiplication
  • ** Exponentiation (ES2016)
  • / Division
  • % Modulus (Remainder)
  • sum - get sum value of array
  • mean - get mean value of array
  • sd - get standard deviation of array
  • se - get standard error of array
  • Priority calculate with () is supported, (100 + 50) * a;
  • If condition: true ? a : b, if the condition is true, then return a, else, return b
  • Substring: string.substring(start, end)
  • Case:
    • Upper: str.toUpperCase()
    • Lower: str.toLowerCase()
  • Date: new Date(dateString), learn more at here.
  • Math:
    • abs(x) Returns the absolute value of x
    • acos(x) Returns the arccosine of x, in radians
    • asin(x) Returns the arcsine of x, in radians
    • atan(x) Returns the arctangent of x as a numeric value between -PI/2 and PI/2 radians
    • atan2(y, x) Returns the arctangent of the quotient of its arguments
    • ceil(x) Returns the value of x rounded up to its nearest integer
    • cos(x) Returns the cosine of x (x is in radians)
    • exp(x) Returns the value of Ex
    • floor(x) Returns the value of x rounded down to its nearest integer
    • log(x) Returns the natural logarithm (base E) of x
    • max(x, y, z, ..., n) Returns the number with the highest value
    • min(x, y, z, ..., n) Returns the number with the lowest value
    • pow(x, y) Returns the value of x to the power of y
    • random() Returns a random number between 0 and 1
    • round(x) Returns the value of x rounded to its nearest integer
    • sin(x) Returns the sine of x (x is in radians)
    • sqrt(x) Returns the square root of x
    • tan(x) Returns the tangent of an angle
Import value:
Not supported

Numbering

Use this widget to generate your own ID with auto-increment. The Numbering can be generated automatically and can also be manually updated.
Readonly view:
Click the reload button to update the ID
Edit view:
Not supported
Form view:
Not supported
Configuration
{
"format": "YY-ID", # (required), the format of the id
# YYYY - 2020, 4 digits of year
# YY - 20, 2 digits of year
# MM - 07, 2 digits of month
# DD - 01, 2 digits of day
# ID, the serial id
# FOREIGNKEYCOLUMN - refer to the data from a foreignkey
"digits": 4, # (optional), number of digits of ID to display
"should_include_archived_records": false, # (optional), whether to add archived records to the INDEX/INDEXLETTER
"reset_month": 10, # (optional), the month to reset the id
"reset_day": 1, # (optional), the day to reset the id
"reset_column": {"sid":"xxx", "name":"xxx"}, # (optional), the column to reset the ID
}
The default value will be ignored for the ID widget.
Import value:
The ID value.

RowCount

Use this widget to display the count of a Row. For example, the number of biospecimens generated from a host.
To make it work, provide the query string into column configuration.
Readonly view:
Edit view:
Not supported
Form view:
Not supported
Configuration
{
"table": {"sid":"xxx", "name":"xxx"}, # the table to limit the query
"query": "name__icontains=test", # (requried)
# paste the query
# Some variables can be added to the link. The widget will replace it for you:
# {{SID}}, the sid of the current record.
# {{UID}}, the uid of the current record.
# {{NAME}}, the name of the current record.
"should_auto_update": false, # default to false.
# If true, the widget will perform live calculation
# If false, the user have to click reload to update the value
"format": {}, # the number in the wNumb format.
# Learn more at https://refreshless.com/wnumb/
}
Import value:
Not supported

Consumption

Use this widget to display the remaining volume of a sample or reagent.
Readonly view:
Edit view:
Not supported
Form view:
Not supported
Configuration:
  • Column "Volume Value" with Number widget
  • Table "Consumption"
    • Column "Sample" or "Reagent" with ForeignKey widget
    • Column "Amount" with Number widget
{
"volume": {"sid":"xxx", "name":"xxx"},
# required, the sid of the column "Volume Value"
"consumption": {"sid":"xxx", "name":"xxx"},
"source": {"sid":"xxx", "name":"xxx"},
# required, the sid of the column "Sample" or "Reagent" from the consumption table
"amount": {"sid":"xxx", "name":"xxx"},
# required, the sid of the column "Amount" from the consumption table
"threshold": 10,
# the color of the value will turn red if the remaining value lower than the threshold
"should_auto_update": true, # default to true.
# If true, the widget will perform live calculation
# If false, the user have to click reload to update the value
"format": {}, # the number in the wNumb format.
# Learn more at https://refreshless.com/wnumb/
}
Default value:
{
"value": 10,
"is_low": false
}
Import value:
Not supported
Last modified 15d ago
Copy link
On this page
Overview
Formula
Numbering
RowCount
Consumption