Aggregate Table
Feature Overview
The aggregate table allows for easy processing of data from multiple worksheets to generate a virtual table, which can be used as a data source for worksheets, workflows, and charts.
The current beta version of the aggregate table can only be used as a data source for charts, and is not yet available for use in worksheets and workflows.
Use Case:
In the Inventory Management application, there are four worksheets (Products, Purchase Details, Sales Details, and Returns). Users can use the aggregate table to obtain the total quantity of each product in stock, total quantity sold, total quantity returned, and calculate inventory for real-time updates.
The data in the aggregated table can be presented to users through pivot tables in custom pages.
Creation Steps
Use Case
In the inventory application, there are different worksheets recording the quantity of purchases, sales, and returns for each product. By creating aggregated tables, these data can be aggregated and get the inventory.
1. Entrance to Aggregate Table
Application Admins go to [App Management] > [Aggregate Table], and click [Create Aggregate Table].
2. Data Source
Select Worksheets
Aggregate tables help users calculate data from one or more worksheets. Click the [+ Worksheet] button and select the necessary worksheets. Each worksheet can only be selected once, and worksheets from other applications can also be chosen.
Set Filters
Set filters for each worksheet so that only filtered data will be included in the aggregated table. In this example, we are only considering products that are on shelf.
3. Grouping
Add fields to be displayed in the aggregated table and group them to summarize the data.
In this example, we need to group by product name and product code. Each worksheet must have these two fields, and while the field names can be different, they should represent the same object.
If the grouping field is empty, the data will be filtered out and not displayed in the aggregated table.
Grouping fields can be renamed and adjusted their order.
Rules for grouping fields
After selecting fields in one worksheet, make sure to select fields of the same type in other worksheets.
| Initially selected fields | Fields that can be selected in other worksheets |
|---|---|
| Text (single line, multiple lines) | Text, Number, Currency, Email, Time, Telephone, ID Number, Concat Rollup, API Query |
| Foreign Field (storage only) | Will be supported soon. Temporarily use concat fields to combine foreign fields |
| Single select/Multiple select | Fields with the same option set |
| Telephone | Text, Telephone |
| Region/City | Region/City |
| Text, Email | |
| Time | Time |
| Date | Date, Formula (add or subtract time from a date/time) |
| Number | Number, Currency, Formual (excluding add or subtract time from a date/time), Level |
| Currency | Number, Currency, Formual (excluding add or subtract time from a date/time), Level |
| Formual (excluding add or subtract time from a date/time) | Number, Currency, Formual (excluding add or subtract time from a date/time), Level |
| Formula (add or subtract time from a date/time) | Date, Formula (add or subtract time from a date/time) |
| Department | Department |
| O-roles | O-roles |
| Members | Members, Owner, Creator, Modifier |
| Level | Number, Currency, Formual (excluding add or subtract time from a date/time), Level |
| ID Number | Text, ID Number |
| Relationship (single row) | Now only the current record ID can be selected |
| Cascading | Cascading |
| API Query | Text, Number, Currency, Email, Time, Telephone, ID Number, Concat Rollup, API Query (Convert date and formula fields to text fields with data integration) |
4. Aggregate
Aggregated fields refer to fields obtained by performing operations on fields in the data source worksheet. It can be used to calculate the sum, maximum value, minimum value, or average value of fields in the worksheet, and to create a "calculation field" to calculate other aggregated fields.
In this example, we need to get the quantity of each product purchased, sold, and returned, and then calculate the inventory (a new calculation field).
Data Fields
As shown below, click [+ Data Fields], select the fields that need to be calculated in the worksheet, set the aggregation method, and choose the data format.
Calculation Fields
Calculation fields can only be for customized fields in the aggregated table. As shown below, to get the inventory of each product, calculations need to be based on the total quantity purchased, total quantity sold, and total quantity returned for each product.
5. Preview data and publish
After configuration is complete, click [View Data] to generate preview data. Once confirmed, modify the name of the aggregated table and publish it. The published aggregated table can be used in other modules.
Use of Aggregate Table
Currently, the aggregate table can be used in the charts on custom pages. When configuring the data source, select the aggregate table.
Management of Aggregate Table
Application Administrators and developers can go to [App Management] > [Aggregate Table] to add, delete, modify, copy, enable, or disable the aggregate table.
Super Administrators and Application Administrators can go to [Org Admin] > [App Management] > [Aggregate Table] to enable or disable all aggregate tables in the organization.
Have questions about this article？ Send us feedback