Merge and aggregation for ConfiForms

Since version 1.4.14, ConfiForms plugin has a new macro to enable aggregation and merging of different ConfiForms forms/datasets.

This is done using a combination of ConfiForms TableView Merger, ConfiForms Field and ConfiForms TableView macros.

ConfiForms TableView Merger is used as a container which has fields to show defined (ConfiForms Field macros) and datasets to merge, using ConfiForms TableView macros.

Important note about ConfiForms Field macros: you can define expressions to be calculated on your data. This is a very powerful feature, as you will learn below.

Macro has 2 modes:

  • to merge the data from different sources, with filtering and sorting
  • to aggregate and do some math on the datasets provided

Macro output is compatible with Confluence chart macro. This comes handy when you want to make nice charts from the data you have. Below you can find some examples on how the macro can be configured and what it is capable of, so far. Please note that the macro is still in BETA and we strive to improve it constantly.

Merging datasets

Showing the content from two ConfiForms forms (we use the structure for Bulletin board, available in ConfiForms blueprint)

Forms have same fields defined, one form is defined on the page 'Company bulletin board' and another one on the page 'Local bulletin board', referenced here by inner 'confiform-table'

3 fields are defined to be shown: idesc and price as in the blueprint and we have added one more count to make examples more interesting

These fields could be referenced in expressions, additionally there are 2 special variables:

  • - [total] to reference total number of rows in the dataset (useful when you need to calculate an average, for example)
  • - [count] to reference number of rows grouped (useful when you need to calculate an average for concrete grouped dataset)
  • - [count_FIELD_NAME=VALUE] - to count fields where FIELD_NAME has this VALUE. Example: Count activities with value equals to "2" and name the column as "Walking": Walking:([count_activity=2])

WYSIWYG view

Storage format view

  
    
      

idesc

price

bboard false

 

bboard false

 

You can have as many forms/tables to aggregate as you want. But, you have to consider the fact, that data reading for each form is serial and this might affect the page loading performance.

Aggregation

Counting total number of rows

WYSIWYG view

Storage format view

Total count:(1) - means to count rows, each row adds 1 to totals. And user 'Total count' as table cell header
  
    
      


Total count:(1)

bboard false

 

bboard false

 

Counting total amount for price field

WYSIWYG view

Storage format view

Total count:(price) - means to sum up the values found in 'price' fields. And user 'Total count' as table cell header
  
    
      


Total count:(price)

bboard false

 

bboard false

 

Group by description and count total price for each item

WYSIWYG view

Storage format view

Here 'idesc' field value is used as key to group the records and Amount:(price) means on the second ConfiForms field means: to count price (sum) per each grouped row and name it as 'Amount' in the table
  
    
      

idesc
Amount:(price)

bboard false

 

bboard false

 

Group by description and count total price for each item, but also take into account 'count' field

WYSIWYG view

Storage format view

Total amount:(price) means, count price (sum), multiply it by the value found in 'count' field and name it as 'Total amount' in the table
  
    
      

idesc
Amount:(price*count)

bboard false

 

bboard false

 

Calculate average price of good in stock

WYSIWYG view

Storage format view

Average:(price*count)/[total] means, count price (sum), multiply it by the value found in 'count' field, divide it by "special" variable [total] and name it as 'Average' in the resulting table
  
    
      

Average:(price*count)/[total]

bboard false

 

bboard false

 



List of math operators and functions supported in ConfiForms Field macro, when used inside ConfiForms TableView Merger macro

Supported Operators

Mathematical Operators
Operator Description
+ Additive operator
- Subtraction operator
* Multiplication operator
/ Division operator
% Remainder operator (Modulo)
^ Power operator
Boolean Operators*
Operator Description
= Equals
== Equals
!= Not equals
<> Not equals
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
&& Boolean and
|| Boolean or

*Boolean operators result always in a BigDecimal value of 1 or 0 (zero). Any non-zero value is treated as a true value. Boolean not is implemented by a function.

Supported Functions

Function* Description
NOT(expression) Boolean negation, 1 (means true) if the expression is not zero
IF(condition,value_if_true,value_if_false) Returns one value if the condition evaluates to true or the other if it evaluates to false
RANDOM() Produces a random number between 0 and 1
MIN(e1,e2) Returns the smaller of both expressions
MAX(e1,e2) Returns the bigger of both expressions
ABS(expression) Returns the absolute (non-negative) value of the expression
ROUND(expression,precision) Rounds a value to a certain number of digits, uses the current rounding mode
FLOOR(expression) Rounds the value down to the nearest integer
CEILING(expression) Rounds the value up to the nearest integer
LOG(expression) Returns the natural logarithm (base e) of an expression
SQRT(expression) Returns the square root of an expression
SIN(expression) Returns the trigonometric sine of an angle (in degrees)
COS(expression) Returns the trigonometric cosine of an angle (in degrees)
TAN(expression) Returns the trigonometric tangens of an angle (in degrees)
SINH(expression) Returns the hyperbolic sine of a value
COSH(expression) Returns the hyperbolic cosine of a value
TANH(expression) Returns the hyperbolic tangens of a value
RAD(expression) Converts an angle measured in degrees to an approximately equivalent angle measured in radians
DEG(expression) Converts an angle measured in radians to an approximately equivalent angle measured in degrees

*Functions names are case insensitive.

Supported Constants

Constant Description
PI The value of PI, exact to 100 digits
TRUE The value one
FALSE The value zero