Advanced aggregations in TableView Merger
In this tutorial you will learn some new advanced tricks to use with ConfiForms's TableView Merger macro
Basic features for TableView Merger macro are to aggregate and merge the data from different ConfiForms forms.
But there are some advanced features as well: as grouping (additionally to filtering and ordering).
As grouping operation can use dynamic math expressions it makes it a very powerful tool
Consider having a form with the following structure
Storage format for the form:
user person Person
false[1=Running|2=Walking|3=Swimming|] select activity Activity
'Activity; field is defined as a dropdown with 3 values: 1=Running, 2=Walking, 3=Swimming. And field 'person' is of type 'User'.
Our goal is to count user activity (each activity) for every user who has participated. We define TableView Merger macro like this.
User field is here used as a group by key and all other fields are expressions.
For each activity we want to calculate totals for the user. This is done by defining the following expression: Running:([count_activity=1]). This translates to the following: count activities with value 1 and name this column as 'Running'
For other activities, such as Walking and Swimming you define the field expressions accordingly: Walking:([count_activity=2]) and Swimming:([count_activity=3])
Here is the storage format for the TableView Merger mcro
Total by user:([count])
That's it! Here is the end result - data is grouped by user and calculated for each activity.