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.
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])
Storage format view
price 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.
Counting total number of rows
Storage format viewTotal 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
Counting total amount for price field
Storage format viewTotal 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
Group by description and count total price for each item
Storage format viewHere '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
Amount:(price) bboard false
Group by description and count total price for each item, but also take into account 'count' field
Storage format viewTotal amount:(price) means, count price (sum), multiply it by the value found in 'count' field and name it as 'Total amount' in the table
Amount:(price*count) bboard false
Calculate average price of good in stock
Storage format viewAverage:(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
List of math operators and functions supported in ConfiForms Field macro, when used inside ConfiForms TableView Merger macro
|%||Remainder operator (Modulo)|
|<=||Less than or equal to|
|>=||Greater than or equal to|
*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.
|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.
|PI||The value of PI, exact to 100 digits|
|TRUE||The value one|
|FALSE||The value zero|