Table joining can be defined as one of the reporting tools, as it can apply with aggregation and instant calculation to summarize the data from different data sources. Before joining the tables, the column of “Category” and “Value” should be distinguished.
Category Column: provide value(s) for other data source(s) to map with
Value Column: the value(s) can be aggregated, instant calculated or merged to single table view
Independent Data Sources
Joined Data Sources
Example View of Joined Table
Item_01
Computer
190
92
98
Item_02
Keyboard
250
91
159
Item_03
Mouse
0
0
0
Information !
For those aggregated values, it can be broken down by the function of “Drill Down”.
Example in Run Time:
C2025_01
Code Ltd.
2025-06-12
C2025_02
Soft Co.
2025-08-03
C2025_01
Code Ltd.
2025-06-12
3
C2025_02
Soft Co.
2025-08-03
60
Information !
The value in “Additional Column” is not saved in the database, it’s calculated instantly.
e.g. Due Date = Now() - Payment Deadline
Conditional Calculation Formula is supported, and the widgets in Form, Page, and Value Stock can be selected.
The column type can be defined as “String”, "Integer”, “Decimal”, “Percent”, "Date”, “Time”, “Date and Time”, "User”, “Group”, “User and Group”, "Dropdown”, “Selection Box”
C2025_01
Code Ltd.
1,000
C2025_02
Soft Co.
2,500
C2025_01
Code Ltd.
300
C2025_01
Code Ltd.
1,300
C2025_02
Soft Co.
2,500
Information !
The value in “Aggregation Column” is not saved in the database, it’s calculated instantly.
Multiple Columns can be grouped.
e.g. to group “Client ID” and “Client Name”
The value of multiple columns can be aggregated by “Max”, “Min”, “Average”, “Sum” and “Count”.
e.g. to sum the value of “Outstanding Payment”
3.1
Fulfil Filter
✓
INNER JOIN
3.2
Fulfil Filter
ㄨ
FULL OUTER JOIN
3.3
Not Fulfil Filer
✓
NOT INNER JOIN
3.4
Not Fulfil Filer
ㄨ
NO RECORD
3.5
Optional Filter
✓
LEFT OUTER JOIN
3.6
Optional Filter
ㄨ
FULL OUTER JOIN
C2025_01
Code Ltd.
IT
C2025_02
Soft Co.
Marketing
C2025_03
Free Org.
Marketing
C2025_01
2025-06-12
C2025_02
2025-08-03
C2025_01
Code Ltd.
IT
C2025_01
2025-06-12
C2025_02
Soft Co.
Marketing
C2025_02
2025-08-03
Information !
Mapping Rule is required for inner join.
e.g. Client ID in source 2 = Client ID in source 1
Select ‘Fulfil Filter’ with mapping rule will perform the inner join of source tables
Inner Join: Returns records that have matching related column values in both tables
Client ID is the related column. C2025_03 in Source Table 2 did not match with any Client ID in Source Table 1. Therefore, no records of C2025_03 will be joined.
C2025_01
Code Ltd.
IT
C2025_02
Soft Co.
Marketing
C2025_03
Free Org.
Marketing
C2025_01
2025-06-12
C2025_02
2025-08-03
C2025_01
Code Ltd.
IT
C2025_01
2025-06-12
C2025_01
Code Ltd.
IT
C2025_02
2025-08-03
C2025_02
Soft Co.
Marketing
C2025_01
2025-06-12
C2025_02
Soft Co.
Marketing
C2025_02
2025-08-03
C2025_03
Free Org.
Marketing
C2025_01
2025-06-12
C2025_03
Free Org.
Marketing
C2025_02
2025-08-03
Select ‘Fulfil Filter’ without mapping rule will perform the full outer join of source tables.
Full Outer Join: Returns all records when there is a match in either the left or the right table.
Full Outer Join can potentially return a very large result set.
Mapping Rule is absent for a full outer join.
The total records of Full Outer Join
= (Total number of records in Source Table 1) * (Total number of records in Source Table 2)
C2025_01
Code Ltd.
IT
C2025_02
Soft Co.
Marketing
C2025_03
Free Org.
Marketing
C2025_01
2025-06-12
C2025_02
2025-08-03
C2025_03
Free Org.
Marketing
C2025_03
(Empty)
Information !
Select ‘Not Fulfil Filter’ with mapping rule will perform not inner join of source tables
Not Inner Join: Returns records that have no matching related column values in both tables
Mapping Rule is required for no inner join.
e.g. Client ID in source 2 = Client ID in source 1
Client ID is the related column. C2025_03 in Source Table 1 did not match with any Client ID in Source Table 2. Moreover, C2025_03 has no records in column “Payment Deadline’' on Source Table 2. Therefore, C2025_03 will be joined but with an empty value in the Payment Deadline Column.
C2025_01
Code Ltd.
IT
C2025_02
Soft Co.
Marketing
C2025_03
Free Org.
Marketing
C2025_01
2025-06-12
C2025_02
2025-08-03
Information !
Select ‘Not Fulfil Filter’ without a mapping rule will show no records.
Mapping Rule is absent for empty records.
Not Fulfil Filter is the opposite of Fulfil Filter. When the situation is fulfilled by a filter without mapping rule, the result is full outer join which shows maximum records. The opposite, not fulfilling the filter without mapping rule will show the minimum record, no record.
C2025_01
Code Ltd.
IT
C2025_02
Soft Co.
Marketing
C2025_03
Free Org.
Marketing
C2025_01
2025-06-12
C2025_02
2025-08-03
C2025_01
Code
IT
C2025_01
2025-06-12
C2025_02
Soft co.
Marketing
C2025_02
2025-08-03
C2025_03
Free Org.
Marketing
C2025_03
Information !
Select ‘Optional Filter’ with mapping rule will perform left join of source tables
Left Join: Returns all records from the left table, and the matched records from the right table
Mapping Rule is required for left join.
e.g. Client ID in source 2 = Client ID in source 1
All records in Source Table 1 will be shown. Client ID is the related Column. Therefore, records in Source Table 2 with matched Client ID in Source Table 1 will be joined with the Source Table 1. As C2025_03 did not exist in Source Table 2, its Payment Deadline will be empty in the result table.
C2025_01
Code Ltd.
IT
C2025_02
Soft Co.
Marketing
C2025_03
Free Org.
Marketing
C2025_01
2025-06-12
C2025_02
2025-08-03
C2025_01
Code Ltd.
IT
C2025_01
2025-06-12
C2025_01
Code Ltd.
IT
C2025_02
2025-08-03
C2025_02
Soft Co.
Marketing
C2025_01
2025-06-12
C2025_02
Soft Co.
Marketing
C2025_02
2025-08-03
C2025_03
Free Org.
Marketing
C2025_01
2025-06-12
C2025_03
Free Org.
Marketing
C2025_02
2025-08-03
Information !
Select ‘Optional Filter’ without mapping rule will perform the full outer join of source tables.
Full Outer Join: Returns all records when there is a match in either the left or the right table.
Full Outer Join can potentially return a very large result set.
Mapping Rule is absent for a full outer join.
The total records of Full Outer Join
= (Total number of records in Source Table 1) * (Total number of records in Source Table 2)
Item_01
Computer
Item_02
Keyboard
Item_03
Mouse
Item_01
100
Item_02
200
Item_02
50
Item_01
60
Item_01
30
Item_02
79
Item_01
54
Item_01
23
Item_01
15
Item_02
12
Item_01
Computer
190
92
Item_02
Keyboard
250
91
Item_03
Mouse
0
0
Information !
Data grouping within each data source before table joining
The value in “Aggregation Column” is not saved in the database, it’s calculated instantly.
Item_01
Computer
190
92
98
Item_02
Keyboard
250
91
159
Item_03
Mouse
0
0
0
Information !
Default Calculation is required.
e.g. Balance Quantity = Total Purchased - Total Sold
The value in “Additional Column” is not saved in the database, it’s calculated instantly.
Case 4.2 based on the result table of Case 4.1
Item_01
Computer
Item_02
Keyboard
Item_03
Mouse
Item_01
2025-12-04
79
Item_01
2025-12-03
54
Item_01
2025-12-01
15
Item_01
2025-12-01
12
Item_01
2025-12-04
79
Item_01
2025-12-04
54
Item_01
2025-12-02
12
2025-12-04
Computer
79
133
2025-12-03
Computer
54
2025-12-02
Computer
12
2025-12-01
Computer
27
Information !
Data grouping within each data source before table joining
e.g. to group “Product ID” & “Issue Date”
The value of multiple columns can be aggregated by “Max”, “Min”, “Average”, “Sum” and “Count”.
e.g. to sum the Quantity of “Purchase” & “Invoice”
Additionally Mapping with Virtual Calendar for merging the value to the same data from different data sources
e.g. Product ID in source 2 = Product ID in source 1
& Issue Date in source 2 = Calendar Date in Virtual Calendar
The value in “Aggregation Column” is not saved in the database, it’s calculated instantly.
Information !
To gather information from the database, “Table Advanced Mode” is very useful, but it’s not the only solution.
INV_01
Code Ltd.
INV_02
Soft Co.
INV_03
Free Org.
INV_01
Item_01
100
INV_01
Item_02
200
INV_02
Item_02
50
INV_02
Item_01
60
INV_02
Code Ltd.
Item_01
100
INV_01
Code Ltd.
Item_02
200
INV_02
Soft Co.
Item_02
50
INV_02
Soft Co.
Item_01
60
“Result Table” can be performed by using “Case 3.1 Inner Join - Fulfil filter with mapping rules”. At the same time,
It can be handled by adding one more field in “Purchase Item” to store the value of “Company Name”.
Table Advanced Mode
Pros
Adjust the joined tables with at any time
Flexible to add new logic on request
Always show the real-time result
Values can be re-calculated by filtering
Support the function of drill down
Cons
Workflow load is centralized on opening table
Poor performance for joining too much or adding too much instant calculations. Worstly, it can’t be loaded
Saving Values to another Form
Pros
Better performance for loading data
Better for handling complex logic
Keep the value at certain state
Cons
Workflow load is shared to each action for auto updating its value
Pre-defined structure or logic
Data Redundancy
Relay on the values in the database and need time to update all existing records for the modification of logic
Information !
Table Advanced Mode” & “Saving Values to another Form” are not mutually exclusive, and they can be used at the same time for complementarity.
Go to Page Builder.
2. Click "Data Source Settings" on the Quick Setting Panel of Form Table widget.
3. Select the first data source.
4. Enable/ Tick the check box of “Advanced Mode” which is located on the top right hand side corner.
Click "Advance" and then click “Add New Column”.
2. Click "New Column" it to show the setting page.
3. Click "Add New" and then click “Input / Integer“ to create a column for the integer type.
4. Modify the Column Title as “Due Date” (for example), and set the Formula for the created column.
5. Click “Save” on the top right hand side corner, and continue the table’s setting.
Information !
Multiple checking conditions are supported.
Widget(s) in Page and Data Source can be used for calculation and condition checking.
Click "Advance" and then click “Add Data Grouping”.
2. Click "New Column" it to show the setting page.
3. Select widget(s) to be grouped by clicking
4. Click “+ New Aggregation” to add aggregation conditions.
5. Select “Column to be aggregated”, “Method of aggregation”, and type the “Display Name”.
Expected Result:
6. Click “Save” on the top right hand side corner, and continue the table’s setting.
Information !
Non-grouped/ aggregated column(s) can NOT be shown/ selected in the “Column Settings”.
Click “+ More Data Source” to add additional data sources.
2. Select the second data source.
3. Click "add Condition" to add mapping rule(s).
Expected Result:
4. Click “Save” on the top right hand side corner, and continue the table’s setting.
Information !
The record filtering of the Joined Data Source can be set in “Record Filters” of the table's setting.