Control - Rollup
Since the records in the worksheet are associated with multiple records in other worksheets, there will be a need to summarize the associated records.
Example 1: In the application Order Management, an order record contains multiple order details, which can be sub-forms or associated records. In the worksheet Order, the amount of each product needs to be automatically summarized.
Example 2: In the application Project Management, a project may have multiple subtasks, so it is necessary to count the number of uncompleted tasks, the number of completed tasks, etc., that is, only to count the data in a certain status.
Requirements like the above can be fulfilled with the [Rollup] control.
How to set the control [Rollup]
- Select the associated record or sub-form.
- Select the target fields and how to count them.
- Set the summary scope.
- Set the display format of the results.
Scenario
1. In the worksheet Order, count the total amount of the orders
First go to the editing page of the form and add the [Rollup] control.
Configuration Items：
A-Field Name:
Set a name for the statistics, such as [Total Amount].
B-Associated Worksheet:
Select the associated worksheet to be counted. Select one of the associated fields in this worksheet, that is, the sub-form Order Details.
Only the associated fields (multiple row) in the current worksheet can be selected.
C-Rollup:
Select the fields that need to be counted and how to summarize them, for example, choose to sum the amount in the sub-form Order Details.
You can choose different summary methods for different fields:
Fields like amount and numeric: sum, max, min, average
Fields like time and date: latest, earliest
Other fields: filled, not filled
D-Summary scope:
Sometimes it is not necessary to count all the associated records, but only the filtered records. See more details in Scenario 2 below.
E-Display format of the results:
For the results, you can set the display format, such as the decimal place, displaying thousandth or by percentage, and also the unit.
Display by percentage
Once checked, if the result is 1, it will be displayed as 100%; if the result is 0.99, it will be displayed as 99%.
If [Display by percentage] is checked, the units (prefix/suffix) are no longer displayed.
2. In the worksheet Project, count the number of uncompleted tasks and display the percentage
First go to the editing page of the worksheet Project and add the [Rollup] control.
Check [Set up the filter] and add filters.
1). Set the summary scope
You can set one condition, or multiple conditions.
If multiple, the relationship between these conditions is either [and] or [or].
It should be noted that condition values cannot be set to dynamic values currently. For example, if the time equals “today”, it is a dynamic value.
2). When counting the partial data, you can choose to display the value directly, or display the percentage.
When counting the number of records, or summing the numeric and amount controls, the results can be displayed as a percentage.
Percentage = value of the counted records / value of all records.
The [Rollup] can be converted to [Numeric].
More scenarios for the control [rollup]
Application Scenarios for Rollup
I. Scenarios
1: Small amount of data (usually no more than a hundred)
For example: to summarize orders/purchase orders/inbound/outbound/, reimbursement details, costing, number of leads/contacts in customer worksheets, etc.
2: Data is not frequently modified (usually no more than ten thousand)
For example: to summarize the number of registrations for an event, the proportion of men and women, the actual number of attendees, etc. Although the number of registrations may be large, there is usually a time interval, and the data does not change again.
II. Not suitable scenarios
There is a lot of data and it will keep increasing over time.
Example 1. Calculate total account income, total expenses and account balance
The number of details under an account increases over time and is usually very large. It is not suitable to use the summary control. You can summarize total revenue and total account expenses separately, and then get the account balance.
Example 2: Calculate inventory of goods
It is not suitable to use the rollup control. You can summarize the total incoming and total outgoing stock separately, and then get the inventory.
Example 3. Rollup the historical high and low prices of products
Sales or purchase records keep increasing over time.
Example 4. Rollup and analyze data based on the configuration sheet
- Membership level sheet: summarize average age, average spending, etc.
- Product type/color/unit: summary of product sales and historical average price, etc.
- Member/department/team: summarize the number of tasks, total sales, days off work, etc.
Ⅲ. Recommended solutions
There are two methods as follows:
- Select a workflow to operate the balance/inventory field.
- Select statistical charts to view other statistics, such as historical maximum and minimum values, average values.
Example 1. Balance
Add two amount controls, total income and total expense, and use workflow to accumulate the change amounts after generating the running details.
Then get the balance by formula.
Note: When generating a running balance, only the value is added after audit, and the data cannot be changed.
Example 2. Inventory
Add two numeric controls for inbound and outbound inventory, and use workflow to accumulate the change amount after generating the change details.
Note: When the document has a status, the value is accumulated only after being audited, and the quantity cannot be changed, and the inventory can only be adjusted by adding new details.
Example 3. Historical highest or lowest price
Separately configure the amount of the highest price or lowest price field in history, when there is a new price, you can use the workflow to compare the previous highest or lowest price to determine whether to update the price.
Example 4. Analyze data
Rely on statistical charts to analyze data (historical average prices, etc.) and give feedback to consultants if there are needs that the product does not meet.
Have questions about this article？ Send us feedback