What Is the Aggregate Table?
The setting of an aggregation table refers to the process of aggregating data from a single or multiple forms by selecting data source forms, setting dimensions, indicators, and verifying data submission, in order to achieve real-time data statistics or control over excessive submission.
What Does It Look Like?
For example, in inventory management, you can calculate Returnable Quantity to track available stock for returns or exchanges in real time.
Key Configuration Rules
To successfully create an aggregate table, ensure:
At least one data source form is added.
At least one dimension is configured.
At least one measure is configured, and each data source form is referenced by at least one measure. Unused forms should be deleted.
For example, to calculate the XXX and YYY of AAA, BBB, and CCC, the items before "of" are measures, and the items after are dimensions.
How to Set up Aggregate Tables?
Step 1 Selecting the Source Form
The first step for setting an aggregate table is to select the source form. Select a single or multiple form(s) by clicking them from the drop-down list.
Note:
A maximum of 50 data source forms can be added.
Selecting Subforms
If you plan to use subform fields in dimension or measure configurations, select the relevant subform. Otherwise, click “OK.”
After selecting a subform, for dimensions, both master form fields and subform fields are available; for measures, only Number-type fields from the selected subform are available.
Adding Filter Conditions
You can filter out data that should not participate in aggregation to ensure calculation accuracy. For example, to include only purchase orders from the year 2025, set the filter accordingly.
Note:
If your filter includes Workflow Status, data submission validation will be disabled.
Step 2 Setting Dimensions
Dimensions define how data is grouped in the aggregate table. They are the fields you want to display and use for aggregation.
Configuring Dimensions
Single Source Form: Select fields directly from that form.
Multiple Source Forms: Build dimensions by mapping identical field types across forms. For instance, to consolidate daily purchase inventory details, link purchase order and return forms via common fields such as Order No. or Product Details.
Notes:
1. Up to 30 dimensions can be added.
2. Enabling “Filter out Nulls” excludes records with blank dimension fields from the table.
3. Changing the system timezone may affect Date&Time fields in dimensions and require table recalculation.
Field types that can be used as dimensions include:
Single Line
Radio
Single Select
Serial No.
Member
Department
Date&Time
WeChat OpenID
Extension fields
Editing Dimension Display
Click the icon next to a dimension to rename it or adjust its configuration. For Date&Time-type dimensions, you can change their display granularity.
Step 3 Setting Measures
Measures are numerical data results obtained by performing aggregate calculations on source form fields.
Setting Data Format
Click Add Measure, jump to the Measure Settings page, and choose its data format:
Thousand Separator
Percentage
Decimal Places
In the formula configuration area, you can use:
Aggregate variable: Number-type fields (main or subform) or records amount from source forms. You can configure:
Aggregate Method: SUM, AVERAGE, COUNT;
Aggregate Filter: Add filters to include only records meeting certain criteria.
Measure: existing measures within the current aggregate table for further calculation.
Operators and functions that can be used in the formula include:
Operators: +, -, *, /, ==, <, >, <=, >=, !=
Mathematical Functions: INT, ROUND
Logical Functions: AND, IF, IFS, OR
Parentheses: ()
When editing the aggregate filter, you can set:
Form Fields: Single Line, Multi Line, Number, Date&Time, Radio, Checkbox, Single Select, Multi Select, Member&Members, Department&Departments, Image, Attachment, Signature
System fields: Submission time, WeChat nickname/OpenID, extension fields
Filter Relations: Vary by field type, e.g., equals, does not equal, contain, does not contain, is empty, is not empty.
Filter Value: Vary by field and filter relation. It can be an option, text, or empty.
The relationship between multiple filter conditions is "and". Only data that meets all filtering conditions will be calculated.
Notes:
1. You can add up to 100 measures.
2. Each data source form can be used in up to 10 aggregate variables.
3. Each aggregate variable supports up to 10 filter conditions.
4. When a subform is selected as a source, only its Number fields can be used as aggregate variables.
5. The first argument for IF and IFS functions must be Boolean (True/False), not a number.
Editting Measures
Click icon next to a measure to update its settings as needed.
Setting Rules for Form Submission Validation
Enable Validations on source form submission and Add Condition. If a record does not meet the specified formula condition, submission will be blocked, and a prompt (custom) for failed check will be displayed.
Validation Rule Details
1. Validation conditions can use equality or inequality expressions.
Symbol | Meaning |
> | ... is greater than ... |
< | ... is less than ... |
== | ... is equal to ... |
!= | ... is not equal to ... |
>= | ... is greater than or equal to ... |
<= | ... is less than or equal to ... |
2. The mathematical operators in formula conditions:
Name | Operator |
Addition | + |
Subtraction | - |
Multiplication | * |
Division | / |
Parentheses | () |
3. For formula conditions, you can call the value of the measured field in the aggregate table or enter a custom number.
4. A notification can be added for each validation rule. Certain notifications will prompt for failed validations when submitting the data.
5. Certain actions on the data of the source form in the aggregate table can trigger validations. For details:
Action | Validation Triggered |
Add | √ |
Edit | √ |
Delete one record | √ |
Select multiple records | √ |
Import | × |
Bulk edit | × |
Clear all data | × |
I have finished reading. 🏆
👉I can't wait to Try for myself.
👉I need more HELP in Discord Forum.