Formula Management

By default, CopyStorm inserts/updates records in the Salesforce backup database when the corresponding record timestamps change in Salesforce. When Salesforce formula fields are involved, a field value may change without the timestamp on a record changing. This article describes several mechanisms provided by CopyStorm to keep database columns in sync with the corresponding formula columns in Salesforce.

Two Types of Formula Fields

Salesforce supports two classifications of formula fields:

  • Row-based formulas
    • Determine their value based on the values of other columns/fields in their row/record.
    • Values for this type of formula change only when another field in the same record changes.
  • Variant formulas
    • Determine their value based on data found in other records (data stored anywhere but the same row/record).
    • This classification of fields includes:
      • Summary fields.
      • Fields computed from a changing value (e.g. “CloseDate – TODAY()”)

Row-Based Formula Fields

Maintenance of Row-Based Formula fields is fairly simple because there are only three conditions where the value of the formula field can change:

  1. A new formula field is created.
    • In this case all records modified AFTER the formula field is created will automatically be updated in CopyStorm.
    • Records whose last modification date is earlier than the formula creation date must be manually refreshed.
  2. The definition of an existing formula field is modified.
    • In this case all records modified AFTER the formula field is created will automatically be updated in CopyStorm.
    • Records whose last modification date is earlier than the formula creation date must be manually refreshed.
  3. The data used to compute the formula field changes.
    • Since the data used to compute a Row-Based Formula field is in the same row as the formula, this will change the modification timestamp on the record and changes will automatically be reflected in CopyStorm.

The following scenario illustrates the problem to solve:

  1. Suppose:
    • The Account table has 100 existing rows and no formulas.
    • CopyStorm has just copied all 100 rows to a database.
  2. A programmer adds a new formula column named “age__c” using the formula “1+2”.
  3. CopyStorm runs and it:
    • Creates a new database column named “age__c”.
    • Makes no changes to the database (because no records have been updated there are no modification dates later than the previous CopyStorm backup).
    • The age__c column defaults to NULL for all records in the CopyStorm database.
  4. A user makes a change to the account ACME.
  5. CopyStorm runs and it:
    • Detects that the ACME record has changed and updates the database.
    • Sets the age__c value for ACME to “3”.
    • No other Accounts are updated and retain NULL for age__c (because none have modification timestamps later than the previous CopyStorm backup).
  6. A programmer modifies the age__c column and changes the formula to “1+2+3”.
  7. A user makes a change to the account BIOSPHERE.
  8. CopyStorm runs and it:
    • Detects that the BIOSPHERE record has changed and updates the database.
    • The BIOSPHERE record has a value of “6” for the age__c column.
    • The ACME record retains the value of “3” for the age__c column.
    • All other Accounts retain the value of NULL for the age__c column.

Our recommended best practice is to force an update on a Row-Based Formula column any time:

  • A Row-Based Formula column is created.
  • A Row-Based Formula column is modified.

Forcing a column update only takes a few minutes (in the GUI or as a batch job) using the CopyStorm/Medic RefreshColumns tool. Alternatively, the CopyStorm/Medic RefreshFormulas tool can be used to refresh every formula in a table.

Another solution is to truncate or drop the corresponding database table and let CopyStorm rebuild it during the next backup.

The best practice for Row-Based Formula columns is to run CopyStorm/Medic RefreshColumns on the column when it is created and any time the formula definition changes.

Variant Formula Fields

A Variant Formula field has a value that changes even when the data in its corresponding row has not changed. These changes may occur every minute, hour, month, or irregularly. Examples of Variant Formula fields include:

  • The number of minutes a support case has been in an open state.
  • The number of hours it took for an account rep to open a new lead.
  • The number of months since a customer was last contacted.
  • The total amount of bookings from an account in the current year.

In every case, keeping a CopyStorm database in sync with Variant Formula fields often requires a business process decision to be made for each field.

The best practice is to:

  • Divide Variant Formula fields into different buckets based on how frequently their value in CopyStorm should be synced with Salesforce.
  • Create a CopyStorm/Medic configuration file for each bucket of formula fields. Each configuration file should be configured to update specific columns using the CopyStorm/Medic RefreshColumns tool.
  • Create a scheduled job for each CopyStorm/Medic configuration file.

If you have a lot of CopyStorm/Medic jobs to run due to having a complex set of Variant Formula fields then you may want to consider CopyStorm/Director. CopyStorm/Director is designed to manage and track the scheduling and status of many CopyStorm and CopyStorm/Medic jobs.