Backend

The formula definition is stored using the cvterm_ids of the input and the target variables, so that if the name changes the formula is not affected.

Tables

formula: Stores the formula definition string and the target variable association. Variable ids should be stored in {{double brackets}} to be interpreted as arguments of the formula

formula_input: Stores the association between the formula an the input variables (forumula_id/cvterm_id)

Calculation Engine

We've implemented JEXL to manage the formula parsing and operations.

JEXL is a library intended to facilitate the implementation of dynamic and scripting features in applications and frameworks written in Java. JEXL implements an Expression Language based on some extensions to the JSTL Expression Language supporting most of the constructs seen in shell-script or ECMAScript. Its goal is to expose scripting features usable by technical operatives or consultants working with enterprise platforms.

Functions (new!)

General considerations

Function should preceed the namespace "fn:" for proper parsing by the calculation engine.

fn:daysdiff({‌{Date1}‌},{‌{Date2}‌})

Calculates the difference in dates between two dates. Input variables should have a scale such that the datatype of the scale is Date. This result of this function can be combined with other operations

fn:sum({‌{input}}) - (v14)

Aggregation function that calculates the sum of the variable/variables added as argument, taking the values at the sub observation level. This result of this function can be combined with other operations

fn:avg({{‌input}}) -(v14)

Aggregation function that calculates the average of the variable/variables added as argument, taking the values at the sub observation level. This result of this function can be combined with other operations

Syntax guide:

http://commons.apache.org/proper/commons-jexl/reference/syntax.html

NOTE: Conditional operations are not supported as they are described in the JEXL documentation given that we are not supporting scripting in the formulas definition. For if/else conditions you can use the ternary conditional (condition ? if_true : if_false)

Examples

Forumula

formula_id	target_variable_id	definition	active	name	description
1	51547	{{20456}} + {{20456}}	1	Opertors example 1	AleuCol_E_1to5 = {{PH_M_cm}} + {{PH_M_cm}}
2	51492	{{20326}} / 4	1	Operators example 2	AntAnCol_E_1to5 = {{GCol_E_1to9}} / 4
3	20447	{{51577}} > 1000 ? {{51577}} : {{51572}}	1	Ternary operator example 1	GY_DW_kgha = {{GY_DW_gPlot}} > 1000 ? {{GY_DW_gPlot}} : {{GY_FW_kgha}}
4	50846	({{20307}} * 2) == 10 ? {{50849}} : {{50849}} > 10 ? {{50849}} + 2 : 0	1	Ternary operator example 2	({{Ant_DT_day}} * 2) == 10 ?
formula_id	target_variable_id	definition	active	name	description
--------	-Some Examples from Maize-	---CGM----	------	---	------------
1	50898	100*({{50858}}/{{51498}})	1	PCT BHC	100*(BHskCovInc_Ct_plntPlot/PHvst_Ct_plntPlot)
2	51571	({{50854}}/1000)((100-{{20337}})/100)(10000/{{20358}})*({{50857}}/100)	1	Dry weight basis GY calculation (t)	(EW_FW_kgPlot/1000)((100-GMoi_Cmp_pct)/100)(10000/PlotArea_m2)*(Shell_Cmp_pct/100)
3	20328	({{50854}}/1000)((100-{{20337}})/(100-12.5))(10000/{{20358}})*({{50857}}/100)	1	GY 12.5 pct moisture calculation (t)	(EW_FW_kgPlot/1000)((100-GMoi_Cmp_pct)/(100-12.5))(10000/PlotArea_m2)*(Shell_Cmp_pct/100)