Difference between revisions of "Rollup Summary Fields"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Β 
(52 intermediate revisions by the same user not shown)
Line 1: Line 1:
Rollup Summary Fields provide quick access to complex metrics in large databases. Β 
Rollup Summary Fields provide quick access to complex metrics in large databases. Β 
{{Note|Rollup Summary Fields are available only for self-hosted installations of the platform, as the load placed on the system can impact all tenants.}}
__TOC__
__TOC__
===About Rollup Summary Fields===
===About Rollup Summary Fields===
Rollup calculations are done in the background, on a schedule specified when the field is created, and include such calculations as: Record Count, Sum, Average, and Max/Min operations.
''Rollup Summary Fields'' summarize information contained in ''related records'' (records that have a [[Lookup]] to the current object). A Rollup Summary Field can be used to get a record Count, Sum, Average, Max, or Min value from those records.
{{tenantfeatures|}}
{{:Option:Rollup Summary Fields}}
Β 
This screen capture shows the kind of data that can be produced. Here, sales are totaled for each order's order items:
:[[File:rollup.png|Rollup summary of OrderItems to make a Grand Total for each Order]]


====Using Rollup Summary Fields====
Rollup Summary Fields can be used with the following platform elements:
Rollup Summary Fields can be used with the following platform elements:
*''Master Objects'' (created via [[Master-Detail relationship]])
*''Master Objects'' (created via [[Master-Detail relationship]])
*[[Account]]s (available on Accounts with the [[Opportunity]] object)
*Elements that use [[Filter Expressions]]:
*[[{{leadprospect}}]] Objects
:*[[Filters in Views]]
*Elements that use [[Filter Expressions in Reports and Data Policies|Filter Expressions]]:
:*[[Custom Access Criteria]]
:*[[Data_Policy#Build_a_Calendar_Based_Data_Policy|Calendar Based Data Policy]]
:*[[Filter_by_Field_Value|Filters in Views]]
:*[[Report_Filter#Filter_by_Field_Value|Filters in Reports]]
:*[[Report_Filter#Filter_by_Field_Value|Filters in Reports]]
:*[[Computed_fields#Computed_Fields|Computed Fields]]
:*[[Computed_fields#Computed_Fields|Computed Fields]]
:*[[Layout Rules]]
:*[[Layout Rules]]


In these one-to-many relationships, a [[Master-Detail relationship]] is created, where data from the detail object can be summarized and the resulting calculations are ''rolled up'' to the master-level object. Β 
In these one-to-many relationships, a [[Master-Detail relationship]] is created, where data from the detail object can be summarized and ''rolled up'' to the master-level object.
Β 
;Considerations:
:* Rollup calculations are done in the background, on a schedule specified when the field is created.
:* By default, the calculations are ''Asynchronous'', which means that values are recalculated on a regular schedule, defined at the server level.
:* ''Synchronous'' Rollup Summary Fields are recalculated whenever data changes.


:[[File:rollup.gif|Rollup summary of movie sales.]]
====Synchronous Rollup Summary Fields====
Rollup Summary Fields are calculated asynchronously by default, which is adequate for most applications.


===How it Works===
A ''Synchronous'' calculation whenever a value the Rollup Summary Field depends on is updated. This arrangement is tantamount to a live data feed, with up-to-the-minute metrics.
In an order management system, a Master object and a Detail object are defined. In the resulting Master object view, the Rollup Summary Fields provide quick analysis of order history, by Customer name with total sales listed for each customer.
{{Note|The Synchronous option is available only for self-hosted installations of the platform, as the load placed on the system can impact all tenants.}}


* The Master object is ''Orders''
;Considerations:
[[File:Rollupsummaryfield.gif|right|thumb]]
:* To use Synchronous calculations for Rollup Summary Fields, the ''Synchronous Rollup Summary Fields'' option in [[Manage Tenant Capabilities]] must be enabled.
:*The Master object contains these fields:
:* The ''Synchronous'' option can cause a delay in displaying the Rollup Summary Field values, especially if a large number of records must be accessed for calculations.
:*By default, a maximum of five (5) Rollup Summary Fields are permitted per [[Tenant]]
:*The number of Rollup Summary Fields allowed is defined by the Service Provider, in [[Manage Tenant Capabilities]].
Β 
===Working with Rollup Summary Fields===
Β 
====Adding a Rollup Summary Field====
To add a Rollup Summary Field:
# Use objects in a [[Master-Detail relationship]], or [[Lookup#Create_a_Lookup_Field|Create a Lookup Field]] in a subordinate object like Order Details to create a many-to-one relationship to records in a master object like Orders.
# In the Detail Object, make the Lookup field the first field in an [[Index]]
# Optionally, define [[Lookup#Record_Selection_Criteria|Record Selection Criteria]] to determine which records are displayed in the user's [[Lookup Window]]<br>''Learn more:'' [[Lookup#Customize_Lookup_Field_Options]]
# In the master object, [[Add a Field]]
# Fill in the settings:
#*'''Label -''' Order Amount
#*'''Display Type -''' Rollup Summary
#*'''Detail Object -''' Select an object that has a Lookup field to this one
#*'''Rollup Type -''' Choose Record Count, Sum, Average, Minimum, Maximum
#*'''Field to Summarize -''' Choose a field in the Detail Object
#*'''Summary Calculation Mode -''' Asynchronous (default), or Synchronous (if allowed)
# Click '''[Save]'''
Β 
{{Note|The Lookup field that targets the current object must be the first field in an [[Index]] defined on the detail object. That performance requirement makes it possible to find related detail records as rapidly as possible.}}
Β 
====Example: Rollup Summary for Orders====
As example, consider an Order Management system. The Orders object contains the master records, and Order Items contains the detail records. A Rollup Summary Field summarizes the total amount for each order.
Β 
:* The Master object ''Orders'' contains these fields:
::*<tt>customer_name</tt>
::*<tt>customer_name</tt>
::*<tt>order</tt>
::*<tt>order</tt>
::*Rollup Summary Field, <tt>order_amount</tt>, with these parameters:
::*<tt>order_amount</tt> - the Rollup Summary Field, with these parameters:
:::*Label: Order Amount
:::*'''Label -''' Order Amount
:::*Display Type: Rollup Summary
:::*'''Display Type:''' Rollup Summary
:::*Summary Object: Line Items
:::*'''Detail Object:''' Order Items
:::*Rollup Type: SUM
:::*'''Rollup Type:''' SUM
:::*Summary Field: <tt>amount</tt>
:::*'''Field to Summarize:''' <tt>amount</tt>
:::*Summary Calculation Mode: Asynchronous
:::*'''Summary Calculation Mode:''' Asynchronous
*The Detail object is ''Line Items'' Β 
:* The Detail object ''Order Items'' contains these fields:
:*The Detail object contains these fields:
::*<tt>order_record</tt> - the [[Lookup]] to Orders
::*[[Lookup]] field to orders, <tt>order</tt>
::*<tt>item_number</tt>
::*<tt>item_number</tt>
::*<tt>item_description</tt>
::*<tt>item_description</tt>
Line 48: Line 77:
::*<tt>unit_price</tt>
::*<tt>unit_price</tt>
::*<tt>amount</tt>
::*<tt>amount</tt>


;Order Data:
;Order records:
:{| border="1" cellpadding="5" cellspacing="0"
:{| border="1" cellpadding="5" cellspacing="0"
!<tt>order</tt>
!<tt>order</tt>
Line 60: Line 88:
|}
|}


;Line Item Data:
;Order Item records:
:{| border="1" cellpadding="5" cellspacing="0"
:{| border="1" cellpadding="5" cellspacing="0"
!<tt>order</tt>
!<tt>order</tt>
Line 80: Line 108:
|1015||C56||carrot cake||1 ||large||$9.95||$9.95
|1015||C56||carrot cake||1 ||large||$9.95||$9.95
|}
|}


;Rollup Summary Action:
;Rollup Summary Action:
Line 95: Line 122:
|}
|}


;Considerations:
====Eligible Field Types based on Calculation Type====
*By default, calculations on Rollup Summary Fields are ''Asynchronous'', which means that values are updated (recalculated) on a regular schedule, defined at the server level
:{| border="1" cellpadding="5" cellspacing="0"
:''Learn more: [[#Synchronous Rollup Summary Fields|Synchronous Rollup Summary Fields]]
Β 
*Available Rollup Calculation Types are:
:*Count
:*Sum
:*Average
:*Min
:*Max
*See [[#Eligible Field Types based on Calculation Type|Eligible Field Types based on Calculation Type]] for more information
Β 
==Add a Rollup Summary Field==
To add a Rollup Summary Field:
#Using objects in a [[Master-Detail relationship]] (or alternate objects, listed above), follow the instructions at [[Lookup#Create_a_Lookup_Field|Create a Lookup Field]]
#Optionally, define [[Lookup#Record_Selection_Criteria|Record Selection Criteria]] to determine the records to display in a [[Lookup Window]]; learn more: [[Lookup#Customize_Lookup_Field_Options|Customize Lookup Field Options]]
Β 
===Synchronous Rollup Summary Fields===
Β 
The option for ''Synchronized'' calculations means that records are updated immediately, whenever a value dependent on the Rollup Summary Field is updated or changed in any way. This creates a live data feed, with up-to-the-minute metrics.
Β 
Rollup Summary Fields are calculated asynchronously by default, which is adequate for most applications. In order to use Synchronous calculation in Rollup Summary Fields, the ''Synchronous Rollup Summary Fields'' option in [[Manage Tenant Capabilities]] must be enabled.
Β 
;Considerations:
:*The ''Synchronous'' option can cause a delay in displaying the Rollup Summary Field values, especially if a large number of records must be accessed for calculations
:*A maximum of five (5) Rollup Summary Fields are permitted per [[Tenant]]
::*The number of Rollup Summary Fields allowed are defined in [[Manage Tenant Capabilities]]
Β 
==Eligible Field Types based on Calculation Type==
:::{| border="1" cellpadding="5" cellspacing="0"
!Calculation Type
!Calculation Type
!Eligible Field Types
!Eligible Field Types
Line 140: Line 139:
|}
|}


====Technical Considerations====


==Technical Considerations==
:*A Rollup Summary Field can be deleted, which deletes all associated parameters, including: Name, Title, Summary Object, Rollup Type, Summary Field and Summary Calculation Mode
Β 
:*When updating a Rollup Summary field, these fields can be modified: Title, Lookup Filters and Summary Calculation Mode
*Rollup Summary Fields cannot be created for a Detail object that is checked out (''Learn more: [[Versioning]]'')
::* However, these fields cannot be changed: Display Type, Summary Object, Rollup type or Summary Field
*A Rollup Summary Field can be deleted, which deletes all associated parameters, including: Name, Title, Summary Object, Rollup Type, Summary Field and Summary Calculation Mode
:*When a Rollup Summary field is added, or the definition of an existing Rollup Summary changes, a background job is scheduled to recalculate values for existing records. Use the [[Scheduled Job Log]] to monitor job progress.
*When updating a Rollup Summary Field, these fields can be modified: Title, Lookup Filters and Summary Calculation Mode
:*A Summary Field that is used in a Rollup Summary cannot be deleted (because the Summary Field links to a field in the Detail Object)
:*However, these fields cannot be changed: Display Type, Summary Object, Rollup type or Summary Field
:*If a Detail object is deleted and a Rollup Summary Field exists in the Master object, then the field display type reverts from ''Rollup Summary'' to match the field display type originally specified by the field in the Detail object
*A Summary Field that is used in a Rollup Summary cannot be deleted (because the Summary Field links to a field in the Detail Object)
:*If the ''Synchronized Rollup Fields'' option is enabled in [[Manage Tenant Capabilities]], then the [Asynchronous] and [Synchronous] calculation options become available when creating a [[Lookup]] field in a [[Master-Detail relationship]]
*If a Detail object is deleted and a Rollup Summary Field exists in the Master object, then the field display type reverts from ''Rollup Summary'' to match the field display type originally specified by the field in the Detail object
:*When a record in a Detail object is deleted, the Rollup Summary Field is recalculated according to the defined Asynchronous/Synchronous option
*If the ''Synchronized Rollup Fields'' option is enabled in [[Manage Tenant Capabilities]], then the [Asynchronus] and [Synchronous] calculation options become available when creating a [[Lookup]] field in a [[Master-Detail relationship]]
:*If a record in a Master object is accessed from a [[View]] while the Rollup Summary Field is being calculated, an icon will be displayed in place of the value, indicating that a calculation is in progress
*When a record in a Detail object is deleted, the Rollup Summary Field is recalculated according to the defined Asynchronus/Synchronous option
*If a record in a Master object is accessed from a [[View]] while the Rollup Summary Field is being calculated, an icon will be displayed in place of the value, indicating that a calculation is in progress


;Implementation:
;Implementation:


*Rollup Summary Fields:
:* Rollup Summary Fields:
:*are universal, read-only fields
::*are universal, read-only fields
:*should not be used to build filter criteria for Formulas, Data Policies, Validations, Indexes or Searches
::*should not be used to build filter criteria for Formulas, Data Policies, Validations, Indexes or Searches
:*cannot be created based on a Rollup Summary Field
::*cannot be created based on a Rollup Summary Field
:*can be created only in objects of type Master, Account and Prospect
::*can be created only in Master-record objects
*ISCHANGED() function cannot be used on Rollup summary fields.
:*The ISCHANGED() function cannot be used on Rollup summary fields.


;Packaging:
;Packaging:


*If the object is a master object with rollup summary field, the detailed object needs to be added as a dependent item
:* If the object is a master object with rollup summary field, the detailed object needs to be added as a dependent item
*Mass Update cannot be performed on Rollup Summary Fields
:* Mass Update cannot be performed on Rollup Summary Fields
*Use [[ISNULL]] in [[:Category:Formula Functions|Formula Functions]] with Rollup Summary Fields
:* Use [[ISNULL]] in [[:Category:Formula Functions|Formula Functions]] with Rollup Summary Fields.<noinclude>
Β 
[[Category:Tenant Capabilities]]
</noinclude>

Latest revision as of 18:46, 8 October 2015

Rollup Summary Fields provide quick access to complex metrics in large databases.

About Rollup Summary Fields

Rollup Summary Fields summarize information contained in related records (records that have a Lookup to the current object). A Rollup Summary Field can be used to get a record Count, Sum, Average, Max, or Min value from those records.

Lock-tiny.gif

Note:
This is a compute-intensive option that affects all tenants. It should be enabled only when absolutely necessary.

This screen capture shows the kind of data that can be produced. Here, sales are totaled for each order's order items:

Rollup summary of OrderItems to make a Grand Total for each Order

Using Rollup Summary Fields

Rollup Summary Fields can be used with the following platform elements:

In these one-to-many relationships, a Master-Detail relationship is created, where data from the detail object can be summarized and rolled up to the master-level object.

Considerations
  • Rollup calculations are done in the background, on a schedule specified when the field is created.
  • By default, the calculations are Asynchronous, which means that values are recalculated on a regular schedule, defined at the server level.
  • Synchronous Rollup Summary Fields are recalculated whenever data changes.

Synchronous Rollup Summary Fields

Rollup Summary Fields are calculated asynchronously by default, which is adequate for most applications.

A Synchronous calculation whenever a value the Rollup Summary Field depends on is updated. This arrangement is tantamount to a live data feed, with up-to-the-minute metrics.

Notepad.png

Note: The Synchronous option is available only for self-hosted installations of the platform, as the load placed on the system can impact all tenants.

Considerations
  • To use Synchronous calculations for Rollup Summary Fields, the Synchronous Rollup Summary Fields option in Manage Tenant Capabilities must be enabled.
  • The Synchronous option can cause a delay in displaying the Rollup Summary Field values, especially if a large number of records must be accessed for calculations.
  • By default, a maximum of five (5) Rollup Summary Fields are permitted per Tenant
  • The number of Rollup Summary Fields allowed is defined by the Service Provider, in Manage Tenant Capabilities.

Working with Rollup Summary Fields

Adding a Rollup Summary Field

To add a Rollup Summary Field:

  1. Use objects in a Master-Detail relationship, or Create a Lookup Field in a subordinate object like Order Details to create a many-to-one relationship to records in a master object like Orders.
  2. In the Detail Object, make the Lookup field the first field in an Index
  3. Optionally, define Record Selection Criteria to determine which records are displayed in the user's Lookup Window
    Learn more: Lookup#Customize_Lookup_Field_Options
  4. In the master object, Add a Field
  5. Fill in the settings:
    • Label - Order Amount
    • Display Type - Rollup Summary
    • Detail Object - Select an object that has a Lookup field to this one
    • Rollup Type - Choose Record Count, Sum, Average, Minimum, Maximum
    • Field to Summarize - Choose a field in the Detail Object
    • Summary Calculation Mode - Asynchronous (default), or Synchronous (if allowed)
  6. Click [Save]

Notepad.png

Note: The Lookup field that targets the current object must be the first field in an Index defined on the detail object. That performance requirement makes it possible to find related detail records as rapidly as possible.

Example: Rollup Summary for Orders

As example, consider an Order Management system. The Orders object contains the master records, and Order Items contains the detail records. A Rollup Summary Field summarizes the total amount for each order.

  • The Master object Orders contains these fields:
  • customer_name
  • order
  • order_amount - the Rollup Summary Field, with these parameters:
  • Label - Order Amount
  • Display Type: Rollup Summary
  • Detail Object: Order Items
  • Rollup Type: SUM
  • Field to Summarize: amount
  • Summary Calculation Mode: Asynchronous
  • The Detail object Order Items contains these fields:
  • order_record - the Lookup to Orders
  • item_number
  • item_description
  • order_number
  • quantity
  • each
  • unit_price
  • amount
Order records
order customer_name
1001 Bob's Bakery
1015 Sally's Sweets
Order Item records
order item_number item_description quantity each unit_price amount
1001 B102 sticky buns 2 doz $3.95 $7.90
1001 C220 chocolate chip cookies 1 doz $2.95 $2.95
1015 W32 whole wheat bread 1 loaf $4.50 $4.50
1015 B43 butter cookies 3 doz $1.55 $4.65
1015 C56 carrot cake 1 large $9.95 $9.95
Rollup Summary Action

Open the Orders object, and see the Rollup Summary calculations in the default View:

order customer_name order_amount
1001 Bob's Bakery $10.85
1015 Sally's Sweets $19.10

Eligible Field Types based on Calculation Type

Calculation Type Eligible Field Types Return Types
Count All All
Sum Number (integer), number with decimals, currency, percent (%), formula fields Number (integer), number with decimals, currency or percent (%)
Average Number (integer), number with decimals, currency, percent (%), formula fields Number (integer), number with decimals, currency or percent (%)
Min Number (integer), number with decimals, currency, percent (%), formula fields Number (integer), number with decimals, currency, percent (%), date or date/time
Max Number (integer), number with decimals, currency, percent (%), formula fields Number (integer), number with decimals, currency, percent (%), date or date/time

Technical Considerations

  • A Rollup Summary Field can be deleted, which deletes all associated parameters, including: Name, Title, Summary Object, Rollup Type, Summary Field and Summary Calculation Mode
  • When updating a Rollup Summary field, these fields can be modified: Title, Lookup Filters and Summary Calculation Mode
  • However, these fields cannot be changed: Display Type, Summary Object, Rollup type or Summary Field
  • When a Rollup Summary field is added, or the definition of an existing Rollup Summary changes, a background job is scheduled to recalculate values for existing records. Use the Scheduled Job Log to monitor job progress.
  • A Summary Field that is used in a Rollup Summary cannot be deleted (because the Summary Field links to a field in the Detail Object)
  • If a Detail object is deleted and a Rollup Summary Field exists in the Master object, then the field display type reverts from Rollup Summary to match the field display type originally specified by the field in the Detail object
  • If the Synchronized Rollup Fields option is enabled in Manage Tenant Capabilities, then the [Asynchronous] and [Synchronous] calculation options become available when creating a Lookup field in a Master-Detail relationship
  • When a record in a Detail object is deleted, the Rollup Summary Field is recalculated according to the defined Asynchronous/Synchronous option
  • If a record in a Master object is accessed from a View while the Rollup Summary Field is being calculated, an icon will be displayed in place of the value, indicating that a calculation is in progress
Implementation
  • Rollup Summary Fields:
  • are universal, read-only fields
  • should not be used to build filter criteria for Formulas, Data Policies, Validations, Indexes or Searches
  • cannot be created based on a Rollup Summary Field
  • can be created only in Master-record objects
  • The ISCHANGED() function cannot be used on Rollup summary fields.
Packaging
  • If the object is a master object with rollup summary field, the detailed object needs to be added as a dependent item
  • Mass Update cannot be performed on Rollup Summary Fields
  • Use ISNULL in Formula Functions with Rollup Summary Fields.